Ad:

PHP Discussion Thread
Unsolved
Views: 945

Apr 22nd, 2009

INSERT INTO through PHP

Expand Post »

Hello,

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

Quote ...

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:

PHP Syntax (Toggle Plain Text)
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!";
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?

Similar Threads

need help with insert.php in PHP
Basic PHP Includes in PHP
How to insert PHP in HTML language? in PHP
phpBB to php-nuke integration help in PHP
New to PHP in PHP

kssi89

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

24 posts
since Apr 2009

Permalink

Apr 22nd, 2009

Re: INSERT INTO through PHP

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

HITMANOF44th

Reputation Points: 24

Solved Threads: 33

Posting Whiz in Training

Offline

283 posts
since Apr 2009

Permalink

Apr 22nd, 2009

Re: INSERT INTO through PHP

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:

php Syntax (Toggle Plain Text)
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!";
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?

php Syntax (Toggle Plain Text)
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!";
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.

Josh Connerty

Reputation Points: 31

Solved Threads: 27

Unverified User

Offline

342 posts
since Apr 2009

Permalink

Apr 23rd, 2009

Re: INSERT INTO through PHP

Click to Expand / Collapse Quote originally posted by Josh Connerty ...

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'?

Last edited by kssi89; Apr 23rd, 2009 at 10:54 am.

kssi89

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

24 posts
since Apr 2009

Permalink

Apr 23rd, 2009

Re: INSERT INTO through PHP

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...

Josh Connerty

Reputation Points: 31

Solved Threads: 27

Unverified User

Offline

342 posts
since Apr 2009

Permalink

Apr 24th, 2009

Re: INSERT INTO through PHP

Click to Expand / Collapse Quote originally posted by kssi89 ...

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:
PHP Syntax (Toggle Plain Text)
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!";
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:

php Syntax (Toggle Plain Text)
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!";
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.

cwarn23

Sponsor

Featured Poster

Reputation Points: 410

Solved Threads: 258

Occupation: Genius

Offline

3,004 posts
since Sep 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: How to display MSQL product data on web page?

Next Thread in PHP Forum Timeline: uploading wont work

DaniWeb Message

Cancel Changes

User Name
Password