INSERT INTO through PHP

[kssi89]

Hello,

This query works when entered directly into MySQL but when used in a php page it fails and generates the following error:

Column count doesn't match value count at row 3

Also, the date values are not accepted in either the php page or mysql terminal, they default to '0000-00-00'.

Here is my query:

mysql_select_db("ecommerce");

$query = "INSERT INTO products VALUES (
'00001', 'toothbrush',
'Brush your teeth with this.',
395.00, '2009-21-04'),
('00002', 'tooth paste',
'You will need this too.',
695.00, '2009-21-04'),
('00003', 'mouth wash',
'Good to use after toothbrush.',
1,250.00, '2009-21-04')";

$result = mysql_query($query)
    or die(mysql_error());
echo "Products added successfully!";

What do you guys think?

[HITMANOF44th]

what is the error you get ?
i'm not a pro but i would say get rid of the ' around the words you want to insert or use ` i'm pretty sure that would do it

[Josh Connerty]

No I always use the ' to add data. Does this fill your fields exactly? and is this the order in wich yu have to put them in? I would try:

mysql_select_db("ecommerce");

$query = "INSERT INTO products (id, item, description, price, date) VALUES (
'00001', 'toothbrush',
'Brush your teeth with this.',
395.00, '2009-21-04'),
('00002', 'tooth paste',
'You will need this too.',
695.00, '2009-21-04'),
('00003', 'mouth wash',
'Good to use after toothbrush.',
1,250.00, '2009-21-04')";

$result = mysql_query($query)
    or die(mysql_error());
echo "Products added successfully!";

The extra bit I added ( (id, item ) should reflect the name of the fields in wich you are adding them to.

Another point is that you are not using your conection variable to tell MySql what server username or password to use. I understand you didn't want to post them but you must make sure that when you use the mysql_select_db function you use it like this[icode] mysql_select_db( "ecommerce" , $con ); con being your connection variable.

Also have you tried adding them individually?

mysql_select_db("ecommerce");

$query = "INSERT INTO products VALUES (
'00001', 'toothbrush',
'Brush your teeth with this.',
395.00, '2009-21-04')";
$query .= "INSERT INTO products VALUES ('00002', 'tooth paste',
'You will need this too.',
695.00, '2009-21-04')";
$query .= "INSERT INTO products VALUES ('00003', 'mouth wash', 'Good to use after toothbrush.', 1,250.00, '2009-21-04')";

$result = mysql_query($query)
    or die(mysql_error());
echo "Products added successfully!";

I did also notice that where you have submitted the price you have used a comma to seperate the digits. This could confuse MySql/PHP that you want to move onto the next field.

Just some ideas to reflect on.

[kssi89]

I did also notice that where you have submitted the price you have used a comma to seperate the digits. This could confuse MySql/PHP that you want to move onto the next field.

Just some ideas to reflect on.

Spot on, this fixed my query problem.

My dates still show up as a series of 0's though. "0000-00-00".

Do you have any thoughts on this? Also, while we're on this train of thought, is there any good way to insert commas into a numeric value in MySQL? Can you escape the commas with \backslashes\ as in '1\,250'?

[Josh Connerty]

Hmm I can't really think without knowing how your MySql database is structured. Can you export the database (or just the table) and post it here please. I'm not to sure why MySql would do that...

[cwarn23]

Hello,

This query works when entered directly into MySQL but when used in a php page it fails and generates the following error:

Also, the date values are not accepted in either the php page or mysql terminal, they default to '0000-00-00'.

Here is my query:

mysql_select_db("ecommerce");

$query = "INSERT INTO products VALUES (
'00001', 'toothbrush',
'Brush your teeth with this.',
395.00, '2009-21-04'),
('00002', 'tooth paste',
'You will need this too.',
695.00, '2009-21-04'),
('00003', 'mouth wash',
'Good to use after toothbrush.',
1,250.00, '2009-21-04')";

$result = mysql_query($query)
    or die(mysql_error());
echo "Products added successfully!";

What do you guys think?

There is at least one obvious reason and that is you should never have new lines in a mysql query. In addition you should escape each value. So your query should look like the following:

mysql_select_db("ecommerce");

$query = "INSERT INTO products VALUES ('".mysql_real_escape_string('00001')."', '".mysql_real_escape_string('toothbrush')."', '".mysql_real_escape_string('Brush your teeth with this.')."', '".mysql_real_escape_string('395.00')."', '".mysql_real_escape_string('2009-21-04')."'), ('".mysql_real_escape_string('00002')."', '".mysql_real_escape_string('tooth paste')."', '".mysql_real_escape_string('You will need this too.')."', '".mysql_real_escape_string('695.00')."', '".mysql_real_escape_string('2009-21-04')."'), ('".mysql_real_escape_string('00003')."', '".mysql_real_escape_string('mouth wash')."', '".mysql_real_escape_string('Good to use after toothbrush.')."', '".mysql_real_escape_string('1,250.00')."', '".mysql_real_escape_string('2009-21-04')."')";

$result = mysql_query($query)
    or die(mysql_error());
echo "Products added successfully!";

If that doesn't work then you may need to specify the column names. I think mysql and php have separate but simular languages for accessing a mysql database and the one ya tried to use was the mysql version and not php version. The reasion why I say that is that postgresql query and mysql query are both identical meaning php must be the interpreter and not mysql. So try converting your query to the php format like the above and as I said, in the above example, column names may need to be added.