Page 1 of 2
This script doesn't work with MySQL
Posted: Fri Nov 17, 2006 4:17 pm
by Scottty
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Hello
I am currently using learning PHP MySQL and apache in 24 hours by Sams publishing
Here is my script
Code: Select all
<?php
$conn = mysql_connect("localhost", "root", "password");
mysql_select_db("test" ,$conn);
$sql = "INSERT INTO testTable values ('', '12')";
$result = mysql_query($sql, $conn) or die(mysql_error());
echo $result;
?>
I get this message,
Out of range value adjusted for column 'id' at row 1
What should I do to resolve this.
Thanks
Scott
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
[quote="[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1"][b]2.[/b] Use descriptive subjects when you start a new thread. Vague titles such as "Help!", "Why?" are misleading and keep you from receiving an answer to your question.[/quote]
[quote="[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1"][b]1.[/b] Select the correct board for your query. Take some time to read the guidelines in the sticky topic.[/quote]
Primary Key
Posted: Fri Nov 17, 2006 4:29 pm
by zeek
What column types are you using? It is very likely that your first column is the Primary Key, which probably requires an integer. You are trying to set it to ''. A better query would be:
Code: Select all
$sql = "INSERT INTO testTable (column_name_1, column_name_2) values ('', '12')";
Posted: Fri Nov 17, 2006 4:36 pm
by Scottty
As for column types I am using
The first column is an int(11) set on auto_increment
and the the second column is set to varchar(75)
Thanks
Scott
Posted: Fri Nov 17, 2006 10:29 pm
by Scottty
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
I tried what you suggested with the columns but the same message came up.
Code: Select all
<?php
$conn = mysql_connect("localhost", "root", "Bourque77");
mysql_select_db("test" ,$conn);
$sql = "INSERT INTO testTable (id, testField) values ('', '12')";
$result = mysql_query($sql, $conn) or die(mysql_error());
echo $result;
?>
Result:
Out of range value adjusted for column 'id' at row 1
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Fri Nov 17, 2006 11:25 pm
by Scottty
Just wanted to let you know I solved the issue
I needed to go into the MySQL folder and go into the my.ini file
and comment out the lines.
# Set the SQL mode to strict
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTI#ON"
Result:
Everything works great now!
Posted: Fri Nov 17, 2006 11:53 pm
by aaronhall
Replacing the quotes with an unquoted zero should also fix the problem.
Posted: Sat Nov 18, 2006 3:39 pm
by califdon
#1. You really shouldn't be trying to insert a value into an auto_increment field. The purpose of auto_increment is to automatically assign a value to the field each time you insert a record.
#2. I would strongly advise you NOT to remove the strict mode from your database system! That's one of the best protections you have against poor syntax (such as your leaving out the names of the columns you want to update) and potential corruption of your data.
Ain't it fun, learning a new technology?

Posted: Sun Nov 19, 2006 5:13 pm
by Scottty
Thanks for the input califdon
Your right I probably shouldn't be inserting data in the auto_increment field. So this book I'm going through doesn't seem to be very good. (Learning PHP, MySQL and Apache in 24 hours by Sams)
So does anyone know some good tutorials I could use instead to learn these applications.
I will also put Strict mode back on.
Thanks
Scott
Tutorials
Posted: Tue Nov 21, 2006 8:28 pm
by churt
I wouldn't give up completely on your book but here are some links that might help.
http://www.htmlgoodies.com/beyond/php/
http://www.w3schools.com/php/default.asp
Your code looks like it should work. Might try taking out the quotes for the auto number field.
Code: Select all
$sql = "INSERT INTO testTable values (, '12')";
Not sure if that will work or not. Anyway, hope the links are helpful.
Posted: Sat Nov 25, 2006 9:08 pm
by Scottty
Thanks for the links
I tried that line of code.
Code: Select all
$sql = "INSERT INTO testTable values (, '12')";
but it didn't work.
I'm gonna go back to my.ini file and turn strict mode on. I will try putting a zero as suggested by
aaronhall and see if that works.
I will post my results.
Posted: Sat Nov 25, 2006 9:46 pm
by feyd
NULL may garner better results.
Posted: Sun Nov 26, 2006 4:19 am
by dibyendrah
If Primary key is set to auto increment, following SQL statement will be valid :
Code: Select all
INSERT INTO testTable (id, testField) values ('NULL', '12')
Posted: Sun Nov 26, 2006 6:48 am
by volka
dibyendrah wrote:'NULL'
as string?
Posted: Sun Nov 26, 2006 11:55 am
by Scottty
I only had a second to check if it would work and it did!!! Thanks
Code: Select all
INSERT INTO testTable (id, testField) VALUES ('NULL', '12')
I'm at work right now and won't be home till late and I'm wondering if I change anything in the my.ini file in the Mysql directory when I get home will I have to restart my computer for changes to take effect.
The reason I'm asking is I want to turn strict mode back on as per advice I was given earlier.
Scott
Posted: Sun Nov 26, 2006 1:11 pm
by nickvd
I've always left out the auto-incrementing column from the insert query, if it's not included, mysql will treat it as null, and use the next incremented value.
Code: Select all
INSERT INTO testTable (testField) VALUES ('12')