insert works on SOME machines, but not on most?! Why?

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

Moderator: General Moderators

User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

insert works on SOME machines, but not on most?! Why?

Post 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.";

	}
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

Regarding all those zeros. Are those zeros being inserted into a VARCHAR field or an INT field?
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

They are INT fields...

Here is what the whole table looks like:

Image
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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
User avatar
swirlee
Forum Newbie
Posts: 7
Joined: Fri Dec 26, 2003 12:08 am

Post 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.
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post 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
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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 :P
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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)
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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')
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post 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?
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
Post Reply