Jump to content

A Few Mysql Bugs


shujjah
 Share

Recommended Posts

Alright this is how the table looks like tables.jpgand 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, :) Edited by SpEeDyZ
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 Edited by SpEeDyZ
Link to comment
Share on other sites

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, Edited by SpEeDyZ
Link to comment
Share on other sites

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']}', ...

Link to comment
Share on other sites

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,
Link to comment
Share on other sites

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,
Link to comment
Share on other sites

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, Edited by SpEeDyZ
Link to comment
Share on other sites

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`)...

Edited by justsomeguy
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...