PHP RSS PHP RSS

INSERT INTO through PHP

Reply

•
•
Join Date: Apr 2009
Posts: 24
Reputation: kssi89 is an unknown quantity at this point 
Solved Threads: 0
kssi89 kssi89 is offline Offline
Newbie Poster

INSERT INTO through PHP

 
0
  #1
Apr 22nd, 2009
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:

PHP Syntax (Toggle Plain Text) 
  1. mysql_select_db("ecommerce");
  2.  
  3. $query = "INSERT INTO products VALUES (
  4. '00001', 'toothbrush',
  5. 'Brush your teeth with this.',
  6. 395.00, '2009-21-04'),
  7. ('00002', 'tooth paste',
  8. 'You will need this too.',
  9. 695.00, '2009-21-04'),
  10. ('00003', 'mouth wash',
  11. 'Good to use after toothbrush.',
  12. 1,250.00, '2009-21-04')";
  13.  
  14. $result = mysql_query($query)
  15.     or die(mysql_error());
  16. echo "Products added successfully!";

What do you guys think?
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2009
Posts: 137
Reputation: HITMANOF44th is an unknown quantity at this point 
Solved Threads: 19
HITMANOF44th HITMANOF44th is offline Offline
Junior Poster

Re: INSERT INTO through PHP

 
0
  #2
Apr 22nd, 2009
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2009
Posts: 340
Reputation: Josh Connerty is an unknown quantity at this point 
Solved Threads: 26
Josh Connerty's Avatar
Josh Connerty Josh Connerty is offline Offline
Posting Whiz

Re: INSERT INTO through PHP

 
1
  #3
Apr 22nd, 2009
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) 
  1. mysql_select_db("ecommerce");
  2.  
  3. $query = "INSERT INTO products (id, item, description, price, date) VALUES (
  4. '00001', 'toothbrush',
  5. 'Brush your teeth with this.',
  6. 395.00, '2009-21-04'),
  7. ('00002', 'tooth paste',
  8. 'You will need this too.',
  9. 695.00, '2009-21-04'),
  10. ('00003', 'mouth wash',
  11. 'Good to use after toothbrush.',
  12. 1,250.00, '2009-21-04')";
  13.  
  14. $result = mysql_query($query)
  15.     or die(mysql_error());
  16. 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) 
  1. mysql_select_db("ecommerce");
  2.  
  3. $query = "INSERT INTO products VALUES (
  4. '00001', 'toothbrush',
  5. 'Brush your teeth with this.',
  6. 395.00, '2009-21-04')";
  7. $query .= "INSERT INTO products VALUES ('00002', 'tooth paste',
  8. 'You will need this too.',
  9. 695.00, '2009-21-04')";
  10. $query .= "INSERT INTO products VALUES ('00003', 'mouth wash', 'Good to use after toothbrush.', 1,250.00, '2009-21-04')";
  11.  
  12. $result = mysql_query($query)
  13.     or die(mysql_error());
  14. 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.
Posts should be like mini-skirts, long enough to cover enough, but not too long that you cover too much.

My Liveperson: http://liveperson.com/josh-connerty/
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2009
Posts: 24
Reputation: kssi89 is an unknown quantity at this point 
Solved Threads: 0
kssi89 kssi89 is offline Offline
Newbie Poster

Re: INSERT INTO through PHP

 
0
  #4
Apr 23rd, 2009
•
•
•
•
Originally Posted by Josh Connerty View Post

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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2009
Posts: 340
Reputation: Josh Connerty is an unknown quantity at this point 
Solved Threads: 26
Josh Connerty's Avatar
Josh Connerty Josh Connerty is offline Offline
Posting Whiz

Re: INSERT INTO through PHP

 
0
  #5
Apr 23rd, 2009
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...
Posts should be like mini-skirts, long enough to cover enough, but not too long that you cover too much.

My Liveperson: http://liveperson.com/josh-connerty/
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 1,484
Reputation: cwarn23 has a spectacular aura about cwarn23 has a spectacular aura about cwarn23 has a spectacular aura about 
Solved Threads: 136
cwarn23's Avatar
cwarn23 cwarn23 is offline Offline
Nearly a Posting Virtuoso

Re: INSERT INTO through PHP

 
0
  #6
Apr 24th, 2009
•
•
•
•
Originally Posted by kssi89 View Post
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) 
  1. mysql_select_db("ecommerce");
  2.  
  3. $query = "INSERT INTO products VALUES (
  4. '00001', 'toothbrush',
  5. 'Brush your teeth with this.',
  6. 395.00, '2009-21-04'),
  7. ('00002', 'tooth paste',
  8. 'You will need this too.',
  9. 695.00, '2009-21-04'),
  10. ('00003', 'mouth wash',
  11. 'Good to use after toothbrush.',
  12. 1,250.00, '2009-21-04')";
  13.  
  14. $result = mysql_query($query)
  15.     or die(mysql_error());
  16. 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) 
  1. mysql_select_db("ecommerce");
  2.  
  3. $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')."')";
  4.  
  5. $result = mysql_query($query)
  6.     or die(mysql_error());
  7. 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.
Try not to bump 10 year old threads as it can be really annoying.
http://syntax.cwarn23.net/
Smilies: ^_* +_+ v_v -_- *~*`
My favourite PC. - MacGyver Fan
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the PHP Forum
Thread Tools Search this Thread



.htaccess action ajax apache api array auto beginner binary bounce broken cakephp checkbox class cms code cron curl database date display dynamic echo email error errorlog file files folder form format forms function functions google href htaccess html image include insert integration interactive ip java javascript joomla limit link login loop mail malfunctioning masterthesis menu mlm mod_rewrite multiple mysql nodes oop paypal pdf php popup problem query radio ram random recursion reference regex remote return script search server sessions sms soap source space sql syntax system table tutorial unset update upload url validation validator variable video web websitecontactform xml youtube
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC