creating a database to insert into from data in PHP?

Question

i am learning php/sql/wamp and have encountered much trouble on a learning project...

i have a txt file below ive finally figured out away to load it into php however i cant figure out how to approach creating the database sql needed to insert this data into and perform queries... is there a good link or tutorial or perhaps sample code? below is my php and txt code

<?php

error_reporting(E_ERROR);
$header = '<?xml version="1.0" encoding="UTF-8"?>'."\n<datalist>";
$content = $header."\n".file_get_contents("data.txt")."\n</datalist>";
$ob = simplexml_load_string($content);
$json = json_encode($ob);
$array = json_decode($json, true);
$alldata = $array["pub"];

foreach ($alldata as $key => $value) { //access all data in loop
$id = $value["ID"];
$title = $value["title"];
$year = $value["year"];
$booktitle = $value["booktitle"];
$pages = $value["pages"];
$authors = implode(",", $value["authors"]["author"]);
//All process data can available here .run your sql query for insert.
}

?>

and my txt file below.. same format but for 50mb of data

<pub>
<ID>0</ID>
<title>Regression Time Warping for Similarity Measure of Sequence</title>
<year>2004</year>
<booktitle>CIT</booktitle>
<pages>826-830</pages>
<authors>
        <author>Hansheng Lei</author>
        <author>Venu Govindaraju</author>
</authors>
</pub>
<pub>
<ID>1</ID>
<title>A Computational Model for Face Location Based on Cognitive Principles</title>
<year>1992</year>
<booktitle>AAAI</booktitle>
<pages>350-355</pages>
<authors>
        <author>Venu Govindaraju</author>
        <author>Sargur N. Srihari</author>
        <author>David B. Sher</author>
</authors>
</pub>

$ob = simplexml_load_string($content); $json = json_encode($ob); $array = json_decode($json, true); why are you loading it into an object, then json encoding it, then decoding it into an array? Am I missing the reason why you don't just use the object from the beginning? — skrilled
– skrilled, Commented Dec 9, 2013 at 20:10
There are probably thousands of tutorials around MySQL and how to interact with it in PHP both here on SO and to the intreped Googler. — Mike Brant
– Mike Brant, Commented Dec 9, 2013 at 20:14
I'm suggesting $ob = simplexml_load_string($content); is all that you need. You are creating an object, then converting it to another object, and then converting it to an array. You can access the properties you want via the $ob object. — skrilled
– skrilled, Commented Dec 9, 2013 at 20:16
Read about PDO or mysqli though in regards to your mysql-related question. I'll help clear up your unnecessary code though if you don't understand what I'm saying. — skrilled
– skrilled, Commented Dec 9, 2013 at 20:16

Community · Accepted Answer · 2023-11-17 20:33:26Z

2

Create your table in MySQL. Then use mysqli (or PDO) to insert the data.

/**
 *  Connect to database
 */
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

/**
 *  Loop data
 */
foreach ($alldata as $key => $value) { //access all data in loop
    /**
     *  Insert row.  This assumes that your table columns are in the 
     *  same order as the variables as they are given below
     */
    $authors = implode(",", $value["authors"]["author"]);
    $stmt = $mysqli->prepare("INSERT INTO your_table VALUES (?, ?, ?, ?, ?, ?)");
    $stmt->bind_param('ssssss', 
                       $value["ID"], 
                       $value["title"], 
                       $value["year"], 
                       $value["booktitle"], 
                       $value["pages"], implode(",", $value["authors"]["author"]));
    $stmt->execute();
}

From https://www.php.net/manual/en/mysqli-stmt.bind-param.php.

Note that it is possible to insert multiple records using MySQL. (Inserting multiple rows in mysql) You could do this to cut down on the number of inserts, but it makes building the query more complicated.

Also note that using a single table for this data isn't optimal. Ideally you would use three tables:

Books = all books
Authors = all authors
Author_book = the relationship between a book and its many authors

Think about the following problems with using a single table:

What happens when one of your <author> entries contains a comma? Your code uses commas to implode the list of authors, thus... <authors> <author>Venu Govindaraju</author> <author>Sargur N. Srihari</author> <author>David B. Sher, PhD</author> </authors> ...becomes Venu Govindaraju, Sargur N. Srihari, David B. Sher, PhD.
How would you find all books authored by Author A and Author B? You would have to create some overly complex method for keyword searching the author column in the DB.

edited Nov 17, 2023 at 20:33

CommunityBot

11 silver badge

answered Dec 9, 2013 at 20:47

Dave

3,6581 gold badge18 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Salazar Over a year ago

very indepth thank you i am attempting to work it... should i not be using wamp?

David Salazar Over a year ago

how did you do this without mentioning data.txt file? i dont understand how it could load if it doesnt know what file to load?

Jade · Accepted Answer · 2013-12-09 20:16:59Z

-1

Before your for loop you need to connect to the database and then inside your for loop you need to insert each row that you read from the data file. What does your table structure look like? Have you tried to write any of the SQL yourself?

Connecting to a database: http://jadendreamer.wordpress.com/2011/01/25/connecting-to-a-mysql-database-using-php/

Inserting into a database: http://www.w3schools.com/sql/sql_insert.asp

answered Dec 9, 2013 at 20:16

Jade

6516 silver badges14 bronze badges

2 Comments

skrilled Over a year ago

Downvoted for outdated article. Please suggest something that isn't deprecated (mysql_*) :p

David Salazar Over a year ago

should i not be using wamp?

Collectives™ on Stack Overflow

creating a database to insert into from data in PHP?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related