A Few Mysql Bugs

[shujjah]

Alright this is how the table looks like and this is the code

//===================================================// 	ARTICLE SUBMIT INSERT INTO THE DATABASE//-----------------------------------------------------------------		$title = str_replace("'", "’", $_POST[title]);		$content = str_replace("'", "’", $_POST[content]);		$rating_1_text = str_replace("'", "’", $_POST[rating_1_text]);		$rating_2_text = str_replace("'", "’", $_POST[rating_2_text]);		$rating_3_text = str_replace("'", "’", $_POST[rating_3_text]);		$rating_4_text = str_replace("'", "’", $_POST[rating_4_text]);		$rating_5_text = str_replace("'", "’", $_POST[rating_5_text]);		$rating_6_text = str_replace("'", "’", $_POST[rating_6_text]);		$rating_overall_text = str_replace("'", "’", $_POST[rating_overall_text]);		if (isset($_POST[title])) { echo "game = ";echo $_POST[game];echo "<br />";echo "rtype = ";echo $_POST[rtype];echo "<br />";	echo "rating 1 = ";echo $_POST[rating_1];echo "<br />";echo "rating 2 = ";echo $_POST[rating_2];echo "<br />";echo "rating 3 = ";echo $_POST[rating_3];echo "<br />";echo "rating 4 = ";echo $_POST[rating_4];echo "<br />";echo "rating 5 = ";echo $_POST[rating_5];echo "<br />";echo "rating 6 = ";echo $_POST[rating_6];echo "<br />"; $sql = "INSERT INTO ccms_article_submit SET title='$title', type='$_POST[submittype]', game='$_POST[game]', cat_id='$_POST[platform]', content='$content', author='$username', tag='user', rtype='$_POST[rtype]', rating_1='$_POST[rating_1]', rating_2='$_POST[rating_2]', rating_3='$_POST[rating_3]', rating_4='$_POST[rating_4]', rating_5='$_POST[rating_5]', rating_6='$_POST[rating_6]', rating_overall='$rating_overall_text', rating_1_text='$rating_1_text', rating_2_text='$rating_2_text', rating_3_text='$rating_3_text', rating_4_text='$rating_4_text', rating_5_text='$rating_5_text', rating_6_text='$rating_6_text', rating_overall_text='$_POST[rating_overall_text]'";			if (@mysql_query($sql)) { echo "	<img src=\"images/spacer2.gif\">	<table width=\"98%\" align=\"center\" $tablereg>	<tr>		<td width=\"100%\" $tdreg3><SPAN class=\"headertitle\"> $lang_success</td>	</tr>	<tr>		<td width=\"100%\" $tdreg><SPAN class=\"content\"><center>$lang_success_submit</td>	</tr>	</table>	<img src=\"images/spacer2.gif\">\n";			} else { 				echo("" . mysql_error() . "</p>");			}		}

the result of the echo are

game = Halo 3rtype =rating 1 =rating 2 =rating 3 =rating 4 =rating 5 =rating 6 =

now the problem is when I choose the game Gears Of War 2 I get this Data too long for column 'game' at row 1although in the table the game field is varchar 250and if I dont choose that game If I choose another game like Halo 3 I dont get that error but instead I get thisIncorrect integer value: '' for column 'rtype' at row 1although the rtype in my table is also varchar .......... If I manually put the value of rtype to 0 then I start getting thisIncorrect integer value: '' for column 'rating_1' at row 1although again rating_1 is varchar in the databasethe full scripthttp://rapidshare.com/files/219580513/s_article.php.htmlOne more thing the ratings fields are only filled when one is submitting a review ... the above error occurs when one is submitting an article Thanks,

[s-p-n]

If you did some indenting and formatting, it'd be much easier for a variety of scriptwriters to work with..look at this for a while; I have a headache

$sql = "INSERT INTO ccms_article_submit 	SET title='$title', 		type='$_POST[submittype]', 		game='$_POST[game]', 		cat_id='$_POST[platform]', 		content='$content', 		author='$username', 		tag='user', 		rtype='$_POST[rtype]', 		rating_1='$_POST[rating_1]', 		rating_2='$_POST[rating_2]', 		rating_3='$_POST[rating_3]', 		rating_4='$_POST[rating_4]', 		rating_5='$_POST[rating_5]', 		rating_6='$_POST[rating_6]', 		rating_overall='$rating_overall_text', 		rating_1_text='$rating_1_text', 		rating_2_text='$rating_2_text', 		rating_3_text='$rating_3_text', 		rating_4_text='$rating_4_text', 		rating_5_text='$rating_5_text', 		rating_6_text='$rating_6_text', 		rating_overall_text='$_POST[rating_overall_text]'";

All of those rating fields seem a bit redundant, and can be done with 1-3 fields (in a table like this) which would make it compatible with an infinite number of ratings.Also, the way you handle your $_POST requests isn't too hot. In PHP there are allot of different ways to get things to work, but not half of them are the right ways. The first rule in programming it to properly define your variables. When scripting PHP, the same rules apply, but it's easier. If your making a new variable that holds text (in C++), you would call: [ string text_holder = "Text Held In Here"; ] The text is always put in quotes. If you're making a new variable that holds a number (or integer, in C++), you would call [ int num_holder = 1; ] The numbers aren't ever in quotes.PHP is sort of the same way, except it's ALLOT less strict, and tends to guess allot when mistakes are made. That enables PHP scriptwriters to put numbers in quotes, and text outside quotes. That will work (sometimes..), but it's not the right way to do it.Always hold your text inside quotes; Stop! in PHP, use apostrophes ('). As you seem to know, PHP quotes have the power to execute numbers and variables. That's unique to PHP, don't get used to it. The downside to this, is that every set of quotes in PHP takes about 5x longer to load then apostrophes (apostrophes -> single quotes to many PHP techs).Back to your $_POST variables -I noticed you use $_POST[text here]; instead of $_POST['text here'];Stop that. It can only lead to future problems. (if not technical problems, others.)Back to your problem, is the rtype and rating stuff supposed to be empty, or is that okay?Also, did you try to echo the $_POST['game'] variables to make sure MySQL wasn't telling you lies and really did have a problem?

[shujjah]

I did echo game and I get the game I choose ( i.e Gears Of War 2 )but it still gives the same error the results of the echo

game = Gears Of War 2rtype = 1rating 1 = 0.1rating 2 = 0.1rating 3 = 0.1rating 4 = 0.1rating 5 = 0.1rating 6 = 0.1Data too long for column 'game' at row 1

( yes I have managed to fix that rtype and ratings issue but that game issue still remains )Data too long for column 'game' at row 1although I have checked again that game field is varchar 250 whereas the above value is only like 20 charactersnow I did some googling and I found out that most of the errors are caused by something being wrong with the table like the encoding or something like that ? Also does it have to do anything with PHP strict mode ?Thanks in advance for helping me and Spence thanks for the brief explanation

[justsomeguy]

Print out the SQL query. You're not escaping quotes in the values you're using in the query, so if there are any single quotes in the data it will break the query. Print the query to see what it looks like.

[shujjah]

this is the code

if ( $_REQUEST[ratingid] == 1 ) {$sql = "INSERT INTO ccms_article_submit SET title='$title', type='$_POST[submittype]', game=\"$_POST[game]\", cat_id='$_POST[platform]', content='$content', author='$username', tag='user', rtype='$_POST[rtype]', rating_1='$_POST[rating_1]', rating_2='$_POST[rating_2]', rating_3='$_POST[rating_3]', rating_4='$_POST[rating_4]', rating_5='$_POST[rating_5]', rating_6='$_POST[rating_6]', rating_overall='$_POST[rating_overall]', rating_1_text='$rating_1_text', rating_2_text='$rating_2_text', rating_3_text='$rating_3_text', rating_4_text='$rating_4_text', rating_5_text='$rating_5_text', rating_6_text='$rating_6_text', rating_overall_text='$_POST[rating_overall_text]'";echo "INSERT INTO ccms_article_submit SET title='$title', type='$_POST[submittype]', game=\"$_POST[game]\", cat_id='$_POST[platform]', content='$content', author='$username', tag='user', rtype='$_POST[rtype]', rating_1='$_POST[rating_1]', rating_2='$_POST[rating_2]', rating_3='$_POST[rating_3]', rating_4='$_POST[rating_4]', rating_5='$_POST[rating_5]', rating_6='$_POST[rating_6]', rating_overall='$_POST[rating_overall]', rating_1_text='$rating_1_text', rating_2_text='$rating_2_text', rating_3_text='$rating_3_text', rating_4_text='$rating_4_text', rating_5_text='$rating_5_text', rating_6_text='$rating_6_text', rating_overall_text='$_POST[rating_overall_text]'";}else  {$sql = "INSERT INTO ccms_article_submit SET title='$title', type='$_POST[submittype]', game=\"$_POST[game]\", cat_id='$_POST[platform]', content='$content', author='$username', tag='user'";echo "INSERT INTO ccms_article_submit SET title='$title', type='$_POST[submittype]', game=\"$_POST[game]\", cat_id='$_POST[platform]', content='$content', author='$username', tag='user'";}

and I get this ( when the ratingid is not 1 but in either case I get the game too long error )

INSERT INTO ccms_article_submit SET title='Testing muhahah', type='Article', game="Gears Of War 2", cat_id='ps3', content='OK so this is just a test', author='SpEeDyZ', tag='user'

and if instead of game=\"$_POST[game]\"I use game=\"$_POST['game']\" in the query then I get this Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/speedyz/public_html/CCMS/pages/s_article.php on line 95thanks,

[justsomeguy]

If you run that query that you printed do you get the same error?When you have an array element inside double quotes you should use brackets around it:$sql = "INSERT INTO ccms_article_submit SET title='$title', type='{$_POST['submittype']}', ...

[shujjah]

If you run that query that you printed do you get the same error?When you have an array element inside double quotes you should use brackets around it:$sql = "INSERT INTO ccms_article_submit SET title='$title', type='{$_POST['submittype']}', ...

No I dont get the same error , it gets inserted into the db Inserted rows: 1Inserted row id: 10 (Query took 0.0002 sec)SQL query:INSERT INTO ccms_article_submitSET title = 'Testing muhahah',TYPE = 'Article',game = "Gears Of War 2",cat_id = 'ps3',content = ' OK so this is just a test ',author = 'SpEeDyZ',tag = 'user'So what does this mean ? I am confused.Thanks,

[justsomeguy]

Do you get the error on the PHP page when you submit that data? We're trying to get the error to show up and also print out the query that caused it.

[shujjah]

yes yes its on the same page this is the code

//===================================================// 	ARTICLE SUBMIT INSERT INTO THE DATABASE//-----------------------------------------------------------------		$title = str_replace("'", "’", $_POST[title]);		$content = str_replace("'", "’", $_POST[content]);		$rating_1_text = str_replace("'", "’", $_POST[rating_1_text]);		$rating_2_text = str_replace("'", "’", $_POST[rating_2_text]);		$rating_3_text = str_replace("'", "’", $_POST[rating_3_text]);		$rating_4_text = str_replace("'", "’", $_POST[rating_4_text]);		$rating_5_text = str_replace("'", "’", $_POST[rating_5_text]);		$rating_6_text = str_replace("'", "’", $_POST[rating_6_text]);		$rating_overall_text = str_replace("'", "’", $_POST[rating_overall_text]);		if (isset($_POST[title])) { echo "game = ";echo $_POST[game];echo "<br />";echo "rtype = ";echo $_POST[rtype];echo "<br />";	echo "rating 1 = ";echo $_POST[rating_1];echo "<br />";echo "rating 2 = ";echo $_POST[rating_2];echo "<br />";echo "rating 3 = ";echo $_POST[rating_3];echo "<br />";echo "rating 4 = ";echo $_POST[rating_4];echo "<br />";echo "rating 5 = ";echo $_POST[rating_5];echo "<br />";echo "rating 6 = ";echo $_POST[rating_6];echo "<br />";	if ( $_REQUEST[ratingid] == 1 ) {$sql = "INSERT INTO ccms_article_submit SET title='$title', type='$_POST[submittype]', game='{$_POST['game']}', cat_id='$_POST[platform]', content='$content', author='$username', tag='user', rtype='$_POST[rtype]', rating_1='$_POST[rating_1]', rating_2='$_POST[rating_2]', rating_3='$_POST[rating_3]', rating_4='$_POST[rating_4]', rating_5='$_POST[rating_5]', rating_6='$_POST[rating_6]', rating_overall='$_POST[rating_overall]', rating_1_text='$rating_1_text', rating_2_text='$rating_2_text', rating_3_text='$rating_3_text', rating_4_text='$rating_4_text', rating_5_text='$rating_5_text', rating_6_text='$rating_6_text', rating_overall_text='$_POST[rating_overall_text]'";echo "INSERT INTO ccms_article_submit SET title='$title', type='$_POST[submittype]', game='{$_POST['game']}', cat_id='$_POST[platform]', content='$content', author='$username', tag='user', rtype='$_POST[rtype]', rating_1='$_POST[rating_1]', rating_2='$_POST[rating_2]', rating_3='$_POST[rating_3]', rating_4='$_POST[rating_4]', rating_5='$_POST[rating_5]', rating_6='$_POST[rating_6]', rating_overall='$_POST[rating_overall]', rating_1_text='$rating_1_text', rating_2_text='$rating_2_text', rating_3_text='$rating_3_text', rating_4_text='$rating_4_text', rating_5_text='$rating_5_text', rating_6_text='$rating_6_text', rating_overall_text='$_POST[rating_overall_text]'";}else  {$sql = "INSERT INTO ccms_article_submit SET title='$title', type='$_POST[submittype]', game='{$_POST['game']}', cat_id='$_POST[platform]', content='$content', author='$username', tag='user'";echo "INSERT INTO ccms_article_submit SET title='$title', type='$_POST[submittype]', game='{$_POST['game']}', cat_id='$_POST[platform]', content='$content', author='$username', tag='user'";}			if (@mysql_query($sql)) { echo "	<img src=\"images/spacer2.gif\">	<table width=\"98%\" align=\"center\" $tablereg>	<tr>		<td width=\"100%\" $tdreg3><SPAN class=\"headertitle\"> $lang_success</td>	</tr>	<tr>		<td width=\"100%\" $tdreg><SPAN class=\"content\"><center>$lang_success_submit</td>	</tr>	</table>	<img src=\"images/spacer2.gif\">\n";			} else { 				echo("" . mysql_error() . "</p>");			}		}}

and this is what I get

game = Gears Of War 2rtype = 1rating 1 = 0.1rating 2 = 0.1rating 3 = 0.1rating 4 = 0.1rating 5 = 0.1rating 6 = 0.1INSERT INTO ccms_article_submit SET title='Testing', type='Article', game='Gears Of War 2', cat_id='x360', content='just a test ', author='SpEeDyZ', tag='user'Data too long for column 'game' at row 1

I get the same error whether ratingid is 1 or notthe full script if you wanna see it http://rapidshare.com/files/221325085/s_article.php.htmlThanks,

[justsomeguy]

I don't see a reason why that query would cause that error, is that the only query running? Could another query be causing the error? The only thing I can think to try is the normal insert syntax instead of insert..set.

[shujjah]

there are no queries before that .. but there are after it .. but I doubt they can cause a problem since the script gets stopped when it gets that error ..... anyways I will try the normal insert syntax now

[shujjah]

Ok so heres the deal .. its a bit confusing but I hope that you'll understand since you are the only one who can help me :)this is the code

//===================================================// ARTICLE SUBMIT INSERT INTO THE DATABASE//----------------------------------------------------------------- $title = str_replace("'", "’", $_POST[title]); $content = str_replace("'", "’", $_POST[content]); $rating_1_text = str_replace("'", "’", $_POST[rating_1_text]); $rating_2_text = str_replace("'", "’", $_POST[rating_2_text]); $rating_3_text = str_replace("'", "’", $_POST[rating_3_text]); $rating_4_text = str_replace("'", "’", $_POST[rating_4_text]); $rating_5_text = str_replace("'", "’", $_POST[rating_5_text]); $rating_6_text = str_replace("'", "’", $_POST[rating_6_text]); $rating_overall_text = str_replace("'", "’", $_POST[rating_overall_text]); if (isset($_POST[title])) { echo "Rating ID ="; echo $_REQUEST[ratingid] ; echo "<br />";echo "game = ";echo $_POST[game] ;echo "<br />";echo "rtype = ";echo $_POST[rtype] ;echo "<br />"; echo "rating 1 = ";echo $_POST[rating_1] ;echo "<br />";echo "rating 2 = ";echo $_POST[rating_2] ;echo "<br />";echo "rating 3 = ";echo $_POST[rating_3] ;echo "<br />";echo "rating 4 = ";echo $_POST[rating_4] ;echo "<br />";echo "rating 5 = ";echo $_POST[rating_5] ;echo "<br />";echo "rating 6 = ";echo $_POST[rating_6] ;echo "<br />"; if ( $_REQUEST[ratingid] == 1 ) {$sql = mysql_query("INSERT INTO ccms_article_submit (title, type, game, cat_id, content, author, tag, rtype, rating_1, rating_2, rating_3, rating_4, rating_5, rating_6, rating_overall, rating_1_text, rating_2_text, rating_3_text, rating_4_text, rating_5_text, rating_6_text, rating_overall_text) VALUES ('$title', '$_POST[submittype]', '{$_POST['game']}','$_POST[platform]','$content','$username','user','$_POST[rtype]','$_POST[rating_1]', '$_POST[rating_2]', '$_POST[rating_3]', '$_POST[rating_4]', '$_POST[rating_5]', '$_POST[rating_6]', '$_POST[rating_overall]', '$rating_1_text', '$rating_2_text', '$rating_3_text', '$rating_4_text', '$rating_5_text', '$rating_6_text', '$_POST[rating_overall_text]')");echo "INSERT INTO ccms_article_submit (title, type, game, cat_id, content, author, tag, rtype, rating_1, rating_2, rating_3, rating_4, rating_5, rating_6, rating_overall, rating_1_text, rating_2_text, rating_3_text, rating_4_text, rating_5_text, rating_6_text, rating_overall_text) VALUES ('$title', '$_POST[submittype]', '{$_POST['game']}','$_POST[platform]','$content','$username','user','$_POST[rtype]','$_POST[rating_1]', '$_POST[rating_2]', '$_POST[rating_3]', '$_POST[rating_4]', '$_POST[rating_5]', '$_POST[rating_6]', '$_POST[rating_overall]', '$rating_1_text', '$rating_2_text', '$rating_3_text', '$rating_4_text', '$rating_5_text', '$rating_6_text', '$_POST[rating_overall_text]')";if (!$sql) { die('Line ' . __LINE__ . ': Error: ' . mysql_error()); }}else {$sql = mysql_query("INSERT INTO ccms_article_submit (title, type, game, cat_id, content, author, tag) VALUES ('$title', '$_POST[submittype]', '{$_POST['game']}','$_POST[platform]','$content','$username','user')");echo "INSERT INTO ccms_article_submit (title, type, game, cat_id, content, author, tag) VALUES ('$title', '$_POST[submittype]', '{$_POST['game']}','$_POST[platform]','$content','$username','user')";if (!$sql) { die('Line ' . __LINE__ . ': Error: ' . mysql_error()); }} if (@mysql_query($sql)) { echo " <img src=\"images/spacer2.gif\"> <table width=\"98%\" align=\"center\" $tablereg> <tr> <td width=\"100%\" $tdreg3><SPAN class=\"headertitle\"> $lang_success</td> </tr> <tr> <td width=\"100%\" $tdreg><SPAN class=\"content\"><center>$lang_success_submit</td> </tr> </table> <img src=\"images/spacer2.gif\">\n"; } else { echo("" .mysql_error(). "</p>"); } }}

Alright so heres the deal when the Red code is in use i.e when ratingid == 1I get this

Rating ID =1game = Gears Of War 2rtype = 1rating 1 = 0.1rating 2 = 0.1rating 3 = 0.1rating 4 = 0.1rating 5 = 0.1rating 6 = 0.1INSERT INTO ccms_article_submit (title, type, game, cat_id, content, author, tag, rtype, rating_1, rating_2, rating_3, rating_4, rating_5, rating_6, rating_overall, rating_1_text, rating_2_text, rating_3_text, rating_4_text, rating_5_text, rating_6_text, rating_overall_text) VALUES ('WTH', 'Article', 'Gears Of War 2','ds','','SpEeDyZ','user','1','0.1', '0.1', '0.1', '0.1', '0.1', '0.1', '0.1', '', '', '', '', '', '', '')Line 99: Error: Data too long for column 'game' at row 1

whereas if I insert the query directly into phpmyadmin it worksif the blue code is in use this is what I get

Rating ID =game = Gears Of War 2rtype =rating 1 =rating 2 =rating 3 =rating 4 =rating 5 =rating 6 =INSERT INTO ccms_article_submit (title, type, game, cat_id, content, author, tag) VALUES ('Testinggg', 'Preview', 'Gears Of War 2','xbox','','SpEeDyZ','user')Line 110: Error: Data too long for column 'game' at row 1

again If I insert the query directly it worksso basically even after the change of syntax it is giving me the same error in both the cases full script if you wanna see it http://rapidshare.com/files/221354333/s_article.php.htmlThanks,

[justsomeguy]

I've never seen MySQL respond differently to queries sent through PHP vs phpMyAdmin (keep in mind that queries run through phpMyAdmin are also being run through PHP). Are you sure you're running this on the same database and table both times? It doesn't make sense. Maybe try using backquotes around the field names, but that doesn't sound like the right error.INSERT INTO ccms_article_submit (`title`, `type`, `game`, `cat_id`, `content`, `author`, `tag`)...