SQL problem

[hello123456789]

hi, i was trying to debug some of my code and it produced the following errors:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /Applications/MAMP/htdocs/web2/submit.php on line 41

 

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 '<458 AND top_plus_height>74 AND top<170 ORDER BY id DESC LIMIT 30' at line 1

 

Here is the code:

 

        
                do
	{
		$position_top = rand(70, 960);
		$position_left = rand(70, 1440);

		$width = 384;
		$fontsize = $width/$numberofchars;
		$height = $fontsize;

		$position_top_plus_width = $position_top+$width;
		$position_top_plus_height = $position_top+$height;

		$top_plus_height = $position_top_plus_height;
		$left_plus_width = $position_left+$width;

		$check_sql = "SELECT * FROM topics WHERE left_plus_width>".$position_left." AND left<".$position_top_plus_width." AND
		top_plus_height>".$position_top." AND top<".$position_top_plus_height." ORDER BY id DESC LIMIT 30;"; 

		$check_result = mysql_query($check_sql);
		$check_num_rows = mysql_num_rows($check_result) or die(mysql_error()); //Line 41
	}
	while($check_num_rows != 0);

 

The error message seems to be referring to the following line (just in case you cannot find it above):

 

$check_sql = "SELECT * FROM topics WHERE left_plus_width>".$position_left." AND left<".$position_top_plus_width." AND
		top_plus_height>".$position_top." AND top<".$position_top_plus_height." ORDER BY id DESC LIMIT 30;"; 

 

I am kind of new to PHP, and I am not quite sure what I am doing wrong. Any help (ASAP) would be greatly appreciated.

Thanks!

 

[Goldeneye]

I believe you need to put spaces between your operands

[Barand]

Can you

 

echo $check_sql;

 

so we can see what the query looks like?

[Barand]

Never mind.

 

You have a column called "left" which is a mysql reserved word.

 

Recommend you change the name. Failing that, enclose it in backticks

 

... AND `left` < ...

 

 

[hello123456789]

Thanks! I think that worked, as I am no longer seeing an error message.

 

HOWEVER, I am getting a blank page. This script is actually part of a form i was writing, however when i submit the form, I am getting a blank page, and the form doesn't seem to do its job.

 

Here is the code right now (sorry if its a bit long):

 

if($_POST['submit'])
{
	$numberofchars = strcspn($_POST['topicname'], "");
	if($numberofchars == 0 || $numberofchars > 30)
	{
		header("Location: ".$config_basedir."/submit.php?error=1");
	}

	do
	{
		$position_top = rand(70, 960);
		$position_left = rand(70, 1440);

		$width = 384;
		$fontsize = $width/$numberofchars; 
		$height = $fontsize;

		$position_top_plus_width = $position_top+$width;
		$position_top_plus_height = $position_top+$height;

		$top_plus_height = $position_top_plus_height;
		$left_plus_width = $position_left+$width;

		$check_sql = "SELECT * FROM topics WHERE left_plus_width>".$position_left." AND position_left<".$position_top_plus_width." AND
		top_plus_height>".$position_top." AND position_top<".$position_top_plus_height." ORDER BY id DESC LIMIT 30;"; // Lets hope this ****ing works

		$check_result = mysql_query($check_sql);
		$check_num_rows = mysql_num_rows($check_result) or die(mysql_error());
	}
	while($check_num_rows != 0);

		$colornumber = rand(1, 10);
		$color;

		//choose a colour depending on the random number generated
		switch($colornumber){
			case 1:
				$color = "FF0000"; // Red
				break;
			case 2:
				$color = "FF9900"; // Orange
				break;
			case 3:
				$color = "FFCC00"; // Yellow
				break;
			case 4:
				$color = "33FF33"; // Light Green
				break;
			case 5:
				$color = "009900"; // Darker Green
				break;
			case 6:
				$color = "3399FF"; // Some kind of lightish blue
				break;
			case 7:
				$color = "0066CC"; // Some kind of darker blue
				break;
			case 8:
				$color = "3300CC"; // Some kind of purple
				break;
			case 9:
				$color = "CCCCCC"; // Light grey
				break;
			case 10:
				$color = "000000"; // Black
				break;

			default:
				$color = "000000"; // Just in case
				break;
		}

		$dateposted = NOW();

		$sql = "INSERT INTO topics(name, body, author, dateposted, position_top, position_left, height, width, color, fontsize, top_plus_height, left_plus_width) VALUES('".$_POST['topicname']."', '".$_POST['body']."', '".$_POST['author']."', '".$position_left.", ".$height.", ".$width.", '".$color."', ".$fontsize.", ".$top_plus_height.", ".$left_plus_width.";";

		mysql_query($sql) or die(mysql_error());
		header("Location: ".$config_basedir."/index.php") or die(mysql_error());
	}

 

I have no idea as to what I am doing wrong, any help would be greatly appreciated. Thanks again!

 

[Barand]

you should be getting an error message. You insert query list 12 columns and only 10 values (dateposted an position_top have no values)

 

<?php

$sql = "INSERT INTO topics(name, body, author, 
dateposted, position_top, 
position_left, height, width, 
color, fontsize, top_plus_height, 
left_plus_width) 
VALUES('".$_POST['topicname']."', '".$_POST['body']."', '".$_POST['author']."', 
'".$position_left.", ".$height.", ".$width.", 
'".$color."', ".$fontsize.", ".$top_plus_height.", 
".$left_plus_width.";";

?>

[hello123456789]

Thanks, I can't believe I didn't see that! Well, I fixed the code, and I'm still getting a blank screen. Any idea why?

 

Just in case, here's the changed part of the code:

 

<?php
$sql = "INSERT INTO topics(name, body, author, dateposted, position_top, position_left, height, width, color, fontsize, top_plus_height, left_plus_width) VALUES('".$_POST['topicname']."', '".$_POST['body']."', '".$_POST['author']."', '".$dateposted."', ".$position_top.", ".$position_left.", ".$height.", ".$width.", '".$color."', ".$fontsize.", ".$top_plus_height.", ".$top_plus_width.";";

?>

 

Thank you very much for your patience.

[Barand]

What do expect to see? I can't see any code to output anything (unless there is an error). Just queries then a redirect to index.php

[hello123456789]

The queries don't go through at all (the database does not get updated/the INSERT thing doesn't work), and the page does NOT redirect to index.php . I checked the address bar of the blank screen and it still says "submit.php", which is the name of the current script.

[hello123456789]

hi, I went through my code again and realized that i was missing a bracket in my SQL statement. However, I am still getting a blank page when i submit the form, none of the information in the form gets processed, and the database does not update. I am starting to wonder if it is because there is something wrong with the way i set up the do while loop, or the switch statement, but I'm not sure.

 

If anyone can help me with this problem, a few tips and pointers would be greatly appreciated.

[unkwntech]

add this to the TOP of the script

ini_set('display_errors', '1');
error_reporting(E_ALL);

To see if there are any PHP errors.

[hello123456789]

Thanks!

 

I added those lines to the top of my script, but nothing has changed.

 

I forgot to mention earlier though, that I have been getting the following messages (before and after adding the 2 lines to the top of the code):

 

Notice: Undefined index: submit in /Applications/MAMP/htdocs/web2/submit.php on line 5

 

Notice: Undefined index: error in /Applications/MAMP/htdocs/web2/submit.php on line 117

 

Line 5 is " if($_POST['submit']){ //etc" and line 117 is "if($_GET['error']==1){ //etc". I ignored this because it said "Notice" instead of "error on line x", but I'm not entirely sure if this is a problem or not.

 

I still do not know what is wrong with my script. As always, any help would be greatly appreciated.

Thanks for your patience!