Page 1 of 2
insert works on SOME machines, but not on most?! Why?
Posted: Tue Dec 30, 2003 3:31 pm
by robster
Hi everyone,
I have some code where my site members are having some real trouble. It seems for most of them this doesn't add the info into the database, but for a lower percentage it does.
The problem is also, both of my test machines it works, so I can't figure it out. Perhaps someone here can help?
Here is a code snippet:
Code: Select all
$connection = mysql_connect($dbhost, $dbusername, $dbpassword);
// if the user filled in all the fields then go ahead
if ($_POST['animator'] AND $_POST['title'] AND $_POST['hardware'] AND $_POST['software'] AND $_POST['creationtime'] AND $_POST['rendertime']
AND $_POST['viewrecommend'] AND $_POST['descriptionofcreation'] AND $_POST['animdescription'] AND $_POST['forum_uid'] AND $_POST['new_id'])
{
$animator = stripslashes($_POST['animator']);
$title = stripslashes($_POST['title']);
$hardware = stripslashes($_POST['hardware']);
$software = stripslashes($_POST['software']);
$creationtime = stripslashes($_POST['creationtime']);
$rendertime = stripslashes($_POST['rendertime']);
$viewrecommend = stripslashes($_POST['viewrecommend']);
$descriptionofcreation = stripslashes($_POST['descriptionofcreation']);
$animdescription = stripslashes($_POST['animdescription']);
$forum_uid = stripslashes($_POST['forum_uid']);
$new_id = stripslashes($_POST['new_id']);
$sound_id = stripslashes($_POST['sound_id']);
$sound_year = stripslashes($_POST['sound_year']);
$sound_month = stripslashes($_POST['sound_month']);
$mpgfilename = "$forum_uid".".mpg";
$jpgfilename = "$forum_uid".".jpg";
echo "$animator<br>";
echo "$title<br>";
echo "$hardware<br>";
echo "$software<br>";
echo "$creationtime<br>";
echo "$rendertime<br>";
echo "$viewrecommend<br>";
echo "$descriptionofcreation<br>";
echo "$animdescription<br>";
echo "$forum_uid<br>";
echo "$new_id<br>";
echo "<br>Mpg filename will be: $mpgfilename";
echo "<br>Jpg filename will be: $jpgfilename<br>";
$Add = mysql_db_query ($dbname, "INSERT INTO current VALUES ('$forum_uid', '$forum_uid', '$sound_year', '$sound_month', '$new_id', '$title', '$animator', '$hardware', '$software', '$creationtime', '$rendertime', '$viewrecommend', '$animdescription', '$descriptionofcreation', '0', '$mpgfilename', '$jpgfilename', '0', '0', '0')");
echo "<br />Your text information has been added to the database, it's time to upload the JPG thumbnail image.<br />";
echo "Click the button to continue to the next page. There you will be able to upload your JPG and edit anything you've already typed in.";
}
Posted: Tue Dec 30, 2003 3:58 pm
by microthick
Regarding all those zeros. Are those zeros being inserted into a VARCHAR field or an INT field?
Posted: Tue Dec 30, 2003 4:07 pm
by robster
They are INT fields...
Here is what the whole table looks like:

Posted: Tue Dec 30, 2003 4:14 pm
by microthick
A far as I know, you shouldn't be quoting integers. In most cases it works (like on my machine), but it might not on machines of other OS's or older versions of MySQL.
Also, why not change your insert statement syntax to:
insert into tablename (col1, col2, col3) values (col1value, col2value, col3value)
rather than how you're currently doing it. If the user creates the columns in a different order than your insert query expects, then the insert will fail.
Posted: Fri Jan 02, 2004 10:52 pm
by robster
Thanks so much for your reply, sorry it took so long for me to respond (and try it out) as I had to split for a bit doing the new years christmas thing
What I've done is made the INT fields default to '0' rather than telling the 0 to be placed in their manually.
I've also replaced my above code to this:
Code: Select all
$Add = mysql_db_query ($dbname, "INSERT INTO current (id,uid,year,month,number,title,animator,hardware,software,creationtime,rendertime,viewrecommend,animdescription,descriptionofcreation,popularity,mpg,jpg,rating,ratingafter,winner)
VALUES ('$forum_uid', '$forum_uid', '$sound_year', '$sound_month', '$new_id', '$title', '$animator', '$hardware', '$software', '$creationtime', '$rendertime', '$viewrecommend', '$animdescription', '$descriptionofcreation', '', '$mpgfilename', '$jpgfilename', '', '', '')");
I've tested it on my machine and on the server and it works. BUT, it always did on my machine.
I asked a friend to test it on his (he was one of the few it didn't work for in the past) and it still didn't work for him. We've both flushed our caches also.
Sorry to ask again, but anyone, do you have any ideas?
I really appreciate the help
Rob
Posted: Sat Jan 03, 2004 5:11 am
by swirlee
microthick already answered your question:
microthick wrote:you shouldn't be quoting integers
Give it a try, then come back if it still doesn't work.
Posted: Sat Jan 03, 2004 5:48 am
by robster
Sorry, perhaps I misunderstood. I thought I'd done that by simply putting '','', etc etc for the values that are integers.... ahhhh hold on!
Are you saying anything at all that's an integer?!
How will I get the $year and $month values into the fields if I can't quote them?
That sounds really restricting. But I'm open to try anything if it'll work.
Also, it seems strange that it will work on a some peoples machines but not others. I just don't understand that, I mean, it's the one server that they are all using (all using the same web form on the same site). How can that be?
So yes please, explain how I can get the $year, $month etc into the integer fields!
Thanks SO much!
Rob
Posted: Sat Jan 03, 2004 5:59 am
by Nay
Not really relating to your question but instead of:
Code: Select all
$animator = stripslashes($_POST['animator']);
$title = stripslashes($_POST['title']);
$hardware = stripslashes($_POST['hardware']);
$software = stripslashes($_POST['software']);
$creationtime = stripslashes($_POST['creationtime']);
$rendertime = stripslashes($_POST['rendertime']);
$viewrecommend = stripslashes($_POST['viewrecommend']);
$descriptionofcreation = stripslashes($_POST['descriptionofcreation']);
$animdescription = stripslashes($_POST['animdescription']);
$forum_uid = stripslashes($_POST['forum_uid']);
$new_id = stripslashes($_POST['new_id']);
$sound_id = stripslashes($_POST['sound_id']);
$sound_year = stripslashes($_POST['sound_year']);
$sound_month = stripslashes($_POST['sound_month']);
you can do:
Code: Select all
foreach($_POST as $arrayKey => $arrayValue) {
$arrayValue = stripslashes($arrayValue);
${$arrayKey} = $arrayValue;
}
-Nay
Posted: Sat Jan 03, 2004 8:10 am
by robster
That looks quite good, thank you
I don't suppose you (or anyone) knows how to get my data into the INT fields? It seems like a strange strange thing to me. I'm sure it's not hard, once you know how
Rob
Posted: Sat Jan 03, 2004 11:25 am
by infolock
i'm just curios, but why are you splitting the month, year, and day up? Why not just use a date field and put them all into one column?
and then, if you need just the month, or the year, or the day for that matter, just use a preg_split and assign whichever one you need to that variable (or array).
edit : also, you said that the others are having problems. What is the exact problem they are having? are they getting an error? is the query not working? If the query isn't working, why not add some debug code to help you figure it out.
ie : add this to the end of your query :
or die(mysql_error());
Code: Select all
$Add = mysql_db_query ($dbname, "INSERT INTO current (id,uid,year,month,number,title,animator,hardware,software,creationtime,rendertime,viewrecommend,animdescription,descriptionofcreation,popularity,mpg,jpg,rating,ratingafter,winner)
VALUES ('$forum_uid', '$forum_uid', '$sound_year', '$sound_month', '$new_id', '$title', '$animator', '$hardware', '$software', '$creationtime', '$rendertime', '$viewrecommend', '$animdescription', '$descriptionofcreation', '', '$mpgfilename', '$jpgfilename', '', '', '')") or die(Mysql_Error());
then, you can post the error messaage they are getting and we will have even more to go by and help you out with

Posted: Sat Jan 03, 2004 3:32 pm
by microthick
For example, to insert years and other integers, proper syntax would be like this:
INSERT INTO mytable (year, month, day) VALUES (2004, 1, 4)
Posted: Sun Jan 04, 2004 4:10 pm
by robster
Wow, thanks for all the feedback!
Here are the results of your suggestions and my trial and errors:
Microthick... you say:
For example, to insert years and other integers, proper syntax would be like this:
INSERT INTO mytable (year, month, day) VALUES (2004, 1, 4)
The problem with this is I need to enter variables into there... for year i need to enter $year, for id I need to enter $id etc. I just can't see how I can get around that.
Infolock, the reason I use individual month and year etc is because I'm actually pulling them from another table in my database at an earlier stage. So the whole extraction process has already happened, I've just passed them to this page via forms etc...
RE: the error message. I can't BELIEVE I didn't put it in!

Here is the error message:
Code: Select all
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'fast cable modem', 'mozilla!', 'a few mins download',
Please note that 'fast cable modem', 'mozilla!', 'a few mins download', are test data that was placed in the fields "hardware","software","creationtime". Also, I'm not sure if the error message is truncating itself or if they are the ONLY fields that are erroring...
So again, thank you so much everyone for your help. I hope my new data and questions can help.. well.. ME!

LOL
Honestly, I just don't know what I'd do without this forum. I've learned so much, but this one is just stumping me.
Rob
Posted: Sun Jan 04, 2004 5:12 pm
by microthick
robster wrote:Microthick... you say:
For example, to insert years and other integers, proper syntax would be like this:
INSERT INTO mytable (year, month, day) VALUES (2004, 1, 4)
The problem with this is I need to enter variables into there... for year i need to enter $year, for id I need to enter $id etc. I just can't see how I can get around that.
Then just go:
INSERT INTO mytable (year, month, day) VALUES ($year, $month, $day)
assuming that year, month and day are integer fields.
If they are varchars, then you'd have to go:
INSERT INTO mytable (year, month, day) VALUES ('$year', '$month', '$day')
Posted: Sun Jan 04, 2004 6:02 pm
by d3ad1ysp0rk
if you're not sure what they are, and can't/dont have time to check, can you just use no single quotes for the query? or is it necessary to use single quotes for varchar and none for ints?
Posted: Sun Jan 04, 2004 6:09 pm
by microthick
LiLpunkSkateR wrote:if you're not sure what they are, and can't/dont have time to check, can you just use no single quotes for the query? or is it necessary to use single quotes for varchar and none for ints?
I know on my machine (PHP, MySQL), I can single quote integers. But this might not be the case for all versions of PHP/MySQL/OS.
I also know that at work (ColdFusion, MS SQL), I cannot single quote integers.
I also know that at school (C++, Oracle), I cannot single quote integers.
I know that you must quote varchars for everything.