This script doesn't work with MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Scottty
Forum Newbie
Posts: 11
Joined: Fri Nov 17, 2006 4:10 pm

This script doesn't work with MySQL

Post by Scottty »

feyd | Please use

Code: Select all

,

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

,

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]
User avatar
zeek
Forum Commoner
Posts: 48
Joined: Mon Feb 27, 2006 7:41 pm

Primary Key

Post 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')";
Scottty
Forum Newbie
Posts: 11
Joined: Fri Nov 17, 2006 4:10 pm

Post 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
Scottty
Forum Newbie
Posts: 11
Joined: Fri Nov 17, 2006 4:10 pm

Post by Scottty »

feyd | Please use

Code: Select all

,

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

,

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]
Scottty
Forum Newbie
Posts: 11
Joined: Fri Nov 17, 2006 4:10 pm

Post 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!
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

Replacing the quotes with an unquoted zero should also fix the problem.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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? :wink:
Scottty
Forum Newbie
Posts: 11
Joined: Fri Nov 17, 2006 4:10 pm

Post 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
User avatar
churt
Forum Commoner
Posts: 39
Joined: Wed Oct 04, 2006 9:59 am

Tutorials

Post 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.
Scottty
Forum Newbie
Posts: 11
Joined: Fri Nov 17, 2006 4:10 pm

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

NULL may garner better results.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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')
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

dibyendrah wrote:'NULL'
as string?
Scottty
Forum Newbie
Posts: 11
Joined: Fri Nov 17, 2006 4:10 pm

Post 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
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post 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')
Post Reply