How can I send a PHP variable array to a MySQL database?

Question

So I have a variable array created from scraping a plaintext data string from a webpage (using Simple HTML DOM Parser class). This variable is the formatted to make it more concise and useful.

I now wish to export this data into a MySQL table where the table name is the webpage title (scraped separately) and the data input is an array, where each word extracted from the webpage is a separate data record.

Here is my code (where $trimmed is a formatted variable string of data scraped from a user input webpage):

$trimmed->plaintext=trim($trimmed->plaintext);
$array = (explode(" ", $trimmed->plaintext));
$printarray = print_r ($array);

mysql_select_db("test", $connect) or die ('Could not find database.');
$sql = "CREATE TABLE '$title'";

$myquery = sprintf("INSERT INTO WebPage '%s'  
    VALUES '%s'",
    mysql_real_escape_string($title->plaintext),
    mysql_real_escape_string($printarray));
$result = mysql_query($myquery);

if (!$result) {
    $message  = '<br /><br /><br /> Invalid query: ' . mysql_error() . "\n";
    $message .= '<br /><br /> Whole query entered here: ' . $myquery;
    die($message);
}

The error is recieve when trying this is:
Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Example Domain' VALUES '1'' at line 1

Whole query entered here: INSERT INTO WebPage 'Example Domain' VALUES '1'

I can provide more code if needed, and sorry in advance if I haven't explained this very well; I am quite new to this.

Thanks in advance.

On a side note, you should really switch to PDO or mysqli as the standard PHP mysql_ functions has now been deprecated. — RayViljoen
– RayViljoen, Commented Feb 19, 2013 at 13:57
I am not sure how to structure the table to make it easy for me to input the variable array, sorry if that isn't any help. 'Example Domain' is the name of the webpage i have been test my data on (example.com) I wanted the page that the data has been scraped from to be the name of the table. — Cam Westwood
– Cam Westwood, Commented Feb 19, 2013 at 14:03
The correct way to do this would be to break down the array before hand, then you can add each array value easily... Check out foreach()... php.net/manual/en/control-structures.foreach.php — Craig van Tonder
– Craig van Tonder, Commented Feb 19, 2013 at 14:03

sh03 · Accepted Answer · 2013-02-19 14:00:07Z

1

Your SQL:

INSERT INTO WebPage 'Example Domain' VALUES '1'

is not valid. Maybe you meant:

INSERT INTO `WebPage` ('Example Domain') VALUES ('1')

On a side note, if Example Domain is indeed a column name: you should really avoid spaces in field's names.

answered Feb 19, 2013 at 14:00

sh03

76.5k39 gold badges177 silver badges282 bronze badges

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

Comments

Julian · Accepted Answer · 2013-02-19 14:02:12Z

0

There are lot's of errors here.

First, The SQL you're generating for the insert looks incorrect:

INSERT INTO tableName (fields) VALUES (values)

Your code says:

INSERT INTO WebPage 'plainText' VALUES (array)

You should remove Webpage if you want to create a table named like the webpage title. Plus, it must be a single word (replace empty spaces with something like '_').

Second, you need to create the table. You need the proper CREATE TABLE structure prior to doing the insert.

Third, your echo print_r won't work for inserting a value per field (column). You need to iterate the array and for each key insert a value. But you should had already done this for creating the table columns.

answered Feb 19, 2013 at 14:02

Julian

3633 silver badges14 bronze badges

Comments

harryg · Accepted Answer · 2013-02-19 14:19:06Z

It looks as if you are trying to incorporate the output from print_r in your query. This isn't possible as print_r is a function that outputs data from an array to the page.

In order to store the contents of an array in the database you can use json_encode to convert the array to a string. Then use json_decode when retrieving it so change it back into a php array.

E.g.

$myquery = sprintf("INSERT INTO `WebPage` ('%s')  
VALUES ('%s')",
json_encode($title->plaintext),
json_encode($array)); //not $printarray as that is not an actual array

edit: As others have noted, mysql_real_escape_string is a deprecated function so other methods should be used to escape characters.

edit2: serialize could also be used in place of json_encode although I am not sure of the relative advantages/disadvantages. A more ideal method would be to restructure your database table to accommodate all contents of the array as a separate piece of data although this may sometimes not be practical.

Collectives™ on Stack Overflow

How can I send a PHP variable array to a MySQL database?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related