[PHP] Re: php can't insert data mysql table
On 10/1/2012 12:32 AM, Tim Dunphy wrote:
Hello list,
I'm trying to learn and get comfortable with HTML forms in PHP. In one
example I am trying to include a file upload in one of the forms. I can
connect to the database but for some reason the MySQL query string is bad.
I've been over this for a while now and I think the problem is with the
form. I was hoping to get the esteemed opinion of the list in where I may
be going wrong.
This code:
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
head
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
titleGuitar Wars - Add Your High Score/title
link rel=stylesheet type=text/css href=style.css /
/head
body
h2Guitar Wars - Add Your High Score/h2
?php
if (isset($_POST['submit'])) {
// Grab the score data from the POST
$name = $_POST['name'];
$score = $_POST['score'];
$screenshot = $_FILES['screenshot']['name'];
if (!empty($name) !empty($score)) {
// Connect to the database
$dbc = mysqli_connect('localhost', 'admin', 'secretsauce', 'gwdb')
or die('Cannot connect to database');
// Write the data to the database
$query = INSERT INTO guitarwars VALUES (0, NOW(), '$name', '$score',
'$screenshot');
mysqli_query($dbc, $query)
or die('Cannot insert query');
// Confirm success with the user
echo 'pThanks for adding your new high score!/p';
echo 'pstrongName:/strong ' . $name . 'br /';
echo 'strongScore:/strong ' . $score . '/p';
echo 'pa href=index.phplt;lt; Back to high scores/a/p';
// Clear the score data to clear the form
$name = ;
$score = ;
mysqli_close($dbc);
}
else {
echo 'p class=errorPlease enter all of the information to add
your high score./p';
}
}
?
hr /
form enctype=multipart/form-data method=post action=?php echo
$_SERVER['PHP_SELF']; ?
input type=hidden name=MAX_FILE_SIZE value=32768 /
label for=nameName:/label
input type=text id=name name=name value=?php if
(!empty($name)) echo $name; ? /br /
label for=scoreScore:/label
input type=text id=score name=score value=?php if
(!empty($score)) echo $score; ? /
br /
label for=screenshotScreenshot:/label
input type=file id=screenshot name=screenshot /
hr /
input type=submit value=Add name=submit /
/form
/body
/html
Results in this output:
Guitar Wars - Add Your High Score Cannot insert query
Thanks again for your help. This is an awesome list!
Tim
You should have your error settings set to show everything.
I include these three lines in all my development work:
error_reporting(E_ALL);
ini_set('display_errors', '1');
set_time_limit(2);
In this case, I do think that your insert statement is incorrect - I
could be wrong. I think the VALUES clause s/b just 'VALUE'. Also if
you added MYSQLI_ERROR to your error handling you should get a very
helpful message.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: php can't insert data mysql table
On Mon, Oct 1, 2012 at 6:03 AM, Jim Giner jim.gi...@albanyhandball.com wrote: In this case, I do think that your insert statement is incorrect - I could be wrong. I think the VALUES clause s/b just 'VALUE'. Also if you added MYSQLI_ERROR to your error handling you should get a very helpful message. VALUES also work. See http://dev.mysql.com/doc/refman/5.5/en/insert.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: php can't insert data mysql table
On 10/1/2012 9:12 AM, Tommy Pham wrote: On Mon, Oct 1, 2012 at 6:03 AM, Jim Giner jim.gi...@albanyhandball.com wrote: In this case, I do think that your insert statement is incorrect - I could be wrong. I think the VALUES clause s/b just 'VALUE'. Also if you added MYSQLI_ERROR to your error handling you should get a very helpful message. VALUES also work. See http://dev.mysql.com/doc/refman/5.5/en/insert.html Yeah - I just saw it in one of my scripts. So - adding the error handling and the call to mysqli_error should help the OP diagnose his problem. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: php can't insert data mysql table
On 10/1/2012 12:32 AM, Tim Dunphy wrote:
Hello list,
I'm trying to learn and get comfortable with HTML forms in PHP. In one
example I am trying to include a file upload in one of the forms. I can
connect to the database but for some reason the MySQL query string is bad.
I've been over this for a while now and I think the problem is with the
form. I was hoping to get the esteemed opinion of the list in where I may
be going wrong.
This code:
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
head
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
titleGuitar Wars - Add Your High Score/title
link rel=stylesheet type=text/css href=style.css /
/head
body
h2Guitar Wars - Add Your High Score/h2
?php
if (isset($_POST['submit'])) {
// Grab the score data from the POST
$name = $_POST['name'];
$score = $_POST['score'];
$screenshot = $_FILES['screenshot']['name'];
if (!empty($name) !empty($score)) {
// Connect to the database
$dbc = mysqli_connect('localhost', 'admin', 'secretsauce', 'gwdb')
or die('Cannot connect to database');
// Write the data to the database
$query = INSERT INTO guitarwars VALUES (0, NOW(), '$name', '$score',
'$screenshot');
mysqli_query($dbc, $query)
or die('Cannot insert query');
// Confirm success with the user
echo 'pThanks for adding your new high score!/p';
echo 'pstrongName:/strong ' . $name . 'br /';
echo 'strongScore:/strong ' . $score . '/p';
echo 'pa href=index.phplt;lt; Back to high scores/a/p';
// Clear the score data to clear the form
$name = ;
$score = ;
mysqli_close($dbc);
}
else {
echo 'p class=errorPlease enter all of the information to add
your high score./p';
}
}
?
hr /
form enctype=multipart/form-data method=post action=?php echo
$_SERVER['PHP_SELF']; ?
input type=hidden name=MAX_FILE_SIZE value=32768 /
label for=nameName:/label
input type=text id=name name=name value=?php if
(!empty($name)) echo $name; ? /br /
label for=scoreScore:/label
input type=text id=score name=score value=?php if
(!empty($score)) echo $score; ? /
br /
label for=screenshotScreenshot:/label
input type=file id=screenshot name=screenshot /
hr /
input type=submit value=Add name=submit /
/form
/body
/html
Results in this output:
Guitar Wars - Add Your High Score Cannot insert query
Thanks again for your help. This is an awesome list!
Tim
ON second glance - your table seems to have 4 fields:
id,date,id,name,screenshot.
Your insert has 5 values: VALUES (0, NOW(), '$name', '$score',
'$screenshot')
That right there is going to throw an error.
BTW - You're saving the name of the screen shot file but are you
actually saving the uploaded file somewhere else in your code? Didn't
see it here and just wondered.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: php can't insert data mysql table
oops - I added a an extra id field. There were only 4 in OP's DESCRIBE email. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: php can't insert data mysql table
hey thanks guys adding debugging info worked. Actually it was mysqli_error() providing me with a specific error of where the problem was. Cannot insert query:Duplicate entry '0' for key 'PRIMARY' This is the data in the table mysql select * from guitarwars; ++-+---+---+---+ | id | date| name | score | screenshot| ++-+---+---+---+ | 0 | 2012-10-01 11:55:45 | Tommy Tutone | 2442 | bg_titlestrip.jpg | | 1 | 2012-10-01 08:34:18 | Dunphy| 2 | proof.jpg | | 2 | 2012-10-01 00:25:53 | ray davies| NULL | 2241 | | 3 | 2008-04-22 14:37:34 | Paco Jastorius| NULL | NULL | | 4 | 2008-04-22 21:27:54 | Nevil Johansson | NULL | NULL | | 5 | 2008-04-23 09:12:53 | Belita Chevy | NULL | NULL | | 6 | 2008-04-23 14:09:50 | Kenny Lavitz | NULL | NULL | | 7 | 2008-04-24 08:13:52 | Phiz Lairston | NULL | NULL | | 8 | 2008-04-25 07:22:19 | Jean Paul Jones | NULL | NULL | | 9 | 2008-04-25 11:49:23 | Jacob Scorcherson | NULL | NULL | ++-+---+---+---+ This was the query I was using: $query = INSERT INTO guitarwars (date, name, score, screenshot) VALUES (NOW(), '$name', '$score', '$screenshot'); It seems to be inserting a default value of 0 since the id is not being specified and that's when I realized that I had failed to auto_increment the id column. D'oh! So once I did that everything worked like a charm. @Ken First -- NEVER post code with your database username/password. Since you did, change your db password immediately. Well actually I did not. Did you really think 'secretsauce' was my password? :) But I guess you're right in that this may be a little ambiguous when seeking advice in lists so from now on I will take your advice on making login information unambiguously fake in the form of user='' and password='xxx'. @Stuart But take note of what everyone else is saying. You should be getting the error message when this happens which will tell you exactly what the problem is, above and beyond Cannot insert query (which, btw, makes no sense at all :)). Ok well I'm using an 'insert' query so I'm not sure why you say that this makes no sense at all. :)) If you don't mind giving this n00b advice what would be a better/more accurate error message? They take away from this for me was.. don't skimp on the error messages! The one I got was so clear that fixing the problem was easy at that point. But thanks again guys.. this list has been an indispensable source source of wisdom on my journey in learning PHP. Tim Thanks again guys, Tim -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: [PHP] Re: php can't insert data mysql table
On 10/1/2012 12:20 PM, Tim Dunphy wrote: hey thanks guys adding debugging info worked. Actually it was mysqli_error() providing me with a specific error of where the problem was. Cannot insert query:Duplicate entry '0' for key 'PRIMARY' This is the data in the table mysql select * from guitarwars; ++-+---+---+---+ | id | date| name | score | screenshot| ++-+---+---+---+ | 0 | 2012-10-01 11:55:45 | Tommy Tutone | 2442 | bg_titlestrip.jpg | | 1 | 2012-10-01 08:34:18 | Dunphy| 2 | proof.jpg | | 2 | 2012-10-01 00:25:53 | ray davies| NULL | 2241 | | 3 | 2008-04-22 14:37:34 | Paco Jastorius| NULL | NULL | | 4 | 2008-04-22 21:27:54 | Nevil Johansson | NULL | NULL | | 5 | 2008-04-23 09:12:53 | Belita Chevy | NULL | NULL | | 6 | 2008-04-23 14:09:50 | Kenny Lavitz | NULL | NULL | | 7 | 2008-04-24 08:13:52 | Phiz Lairston | NULL | NULL | | 8 | 2008-04-25 07:22:19 | Jean Paul Jones | NULL | NULL | | 9 | 2008-04-25 11:49:23 | Jacob Scorcherson | NULL | NULL | ++-+---+---+---+ This was the query I was using: $query = INSERT INTO guitarwars (date, name, score, screenshot) VALUES (NOW(), '$name', '$score', '$screenshot'); It seems to be inserting a default value of 0 since the id is not being specified and that's when I realized that I had failed to auto_increment the id column. D'oh! So once I did that everything worked like a charm. @Ken First -- NEVER post code with your database username/password. Since you did, change your db password immediately. Well actually I did not. Did you really think 'secretsauce' was my password? :) But I guess you're right in that this may be a little ambiguous when seeking advice in lists so from now on I will take your advice on making login information unambiguously fake in the form of user='' and password='xxx'. @Stuart But take note of what everyone else is saying. You should be getting the error message when this happens which will tell you exactly what the problem is, above and beyond Cannot insert query (which, btw, makes no sense at all :)). Ok well I'm using an 'insert' query so I'm not sure why you say that this makes no sense at all. :)) If you don't mind giving this n00b advice what would be a better/more accurate error message? They take away from this for me was.. don't skimp on the error messages! The one I got was so clear that fixing the problem was easy at that point. But thanks again guys.. this list has been an indispensable source source of wisdom on my journey in learning PHP. Tim Thanks again guys, Tim I think the comment about your cannot insert query was because it really did not make sense. Once truly cannot insert a query. Since you ask tho, a more approp message might be Insert query failed to execute.brError returned was . mysqli_error() . brQuery was $q As for your index issue - you are using an autoincrement(?) field as the primary key. Is this related to another record in your db? If not, why even have the primary key? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: php can't insert data mysql table
I think the comment about your cannot insert query was because it really did not make sense. Once truly cannot insert a query. Since you ask tho, a more approp message might be Insert query failed to execute.brError returned was . mysqli_error() . brQuery was $q As for your index issue - you are using an autoincrement(?) field as the primary key. Is this related to another record in your db? If not, why even have the primary key? Hey! I really like your error message. Borrowing it! :) Yes I am now using auto_increment, and that's what seemed to solve my issue. Looks like a simple omission in forgetting to use auto_increment and using primary key. I usually know better. I'll blame this one on the clonopin. lol thank you tim On Mon, Oct 1, 2012 at 12:30 PM, Jim Giner jim.gi...@albanyhandball.comwrote: On 10/1/2012 12:20 PM, Tim Dunphy wrote: hey thanks guys adding debugging info worked. Actually it was mysqli_error() providing me with a specific error of where the problem was. Cannot insert query:Duplicate entry '0' for key 'PRIMARY' This is the data in the table mysql select * from guitarwars; ++-+--**-+---+** ---+ | id | date| name | score | screenshot | ++-+--**-+---+** ---+ | 0 | 2012-10-01 11:55:45 | Tommy Tutone | 2442 | bg_titlestrip.jpg | | 1 | 2012-10-01 08:34:18 | Dunphy| 2 | proof.jpg | | 2 | 2012-10-01 00:25:53 | ray davies| NULL | 2241 | | 3 | 2008-04-22 14:37:34 | Paco Jastorius| NULL | NULL | | 4 | 2008-04-22 21:27:54 | Nevil Johansson | NULL | NULL | | 5 | 2008-04-23 09:12:53 | Belita Chevy | NULL | NULL | | 6 | 2008-04-23 14:09:50 | Kenny Lavitz | NULL | NULL | | 7 | 2008-04-24 08:13:52 | Phiz Lairston | NULL | NULL | | 8 | 2008-04-25 07:22:19 | Jean Paul Jones | NULL | NULL | | 9 | 2008-04-25 11:49:23 | Jacob Scorcherson | NULL | NULL | ++-+--**-+---+** ---+ This was the query I was using: $query = INSERT INTO guitarwars (date, name, score, screenshot) VALUES (NOW(), '$name', '$score', '$screenshot'); It seems to be inserting a default value of 0 since the id is not being specified and that's when I realized that I had failed to auto_increment the id column. D'oh! So once I did that everything worked like a charm. @Ken First -- NEVER post code with your database username/password. Since you did, change your db password immediately. Well actually I did not. Did you really think 'secretsauce' was my password? :) But I guess you're right in that this may be a little ambiguous when seeking advice in lists so from now on I will take your advice on making login information unambiguously fake in the form of user='' and password='xxx'. @Stuart But take note of what everyone else is saying. You should be getting the error message when this happens which will tell you exactly what the problem is, above and beyond Cannot insert query (which, btw, makes no sense at all :)). Ok well I'm using an 'insert' query so I'm not sure why you say that this makes no sense at all. :)) If you don't mind giving this n00b advice what would be a better/more accurate error message? They take away from this for me was.. don't skimp on the error messages! The one I got was so clear that fixing the problem was easy at that point. But thanks again guys.. this list has been an indispensable source source of wisdom on my journey in learning PHP. Tim Thanks again guys, Tim I think the comment about your cannot insert query was because it really did not make sense. Once truly cannot insert a query. Since you ask tho, a more approp message might be Insert query failed to execute.brError returned was . mysqli_error() . brQuery was $q As for your index issue - you are using an autoincrement(?) field as the primary key. Is this related to another record in your db? If not, why even have the primary key? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
