wierd error. please help... again :D

helz

so i had a manage.php which basically had edit/del/add/view code inside it. even tho edit and add are seperate you get where im coming from. since i had a fully working script i have added more fields into the areas and now there is an error where as usual it seems alright.
please have a scan of the code to see if you can help me. i think its only a simple mistake but im not sure.

here is the error:
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 '' at line 1

here it is:

<link href="style.css" rel="stylesheet" type="text/css"> 
<?
require ("../../../cgi-bin/connect2test.php");

#---------------------------------------------------------------------------------------------------------------#
# // \\ // \\ // \\ // \\ // \\ // \\ // \\ // DELETE RECORD \\ // \\ // \\ // \\ // \\ // \\ // \\ // \\       #
#---------------------------------------------------------------------------------------------------------------#
    if ($task=="del") {

    $SQL = " DELETE FROM ".$tablename." ";
    $SQL = $SQL . " WHERE id = $id ";
    mysql_db_query($db, "$SQL", $cid);
	echo "Record Deleted<br>
		  <- <a href='$PHP_SELF?task=ed-del-menu'>Back to the Edit / Delete Menu</a><br>
		  ".$back."";
} elseif ($task=="ed-del-menu") {
#===============================================================================================================#

#---------------------------------------------------------------------------------------------------------------#
# // \\ // \\ // \\ // \\ // \\ // \\ // \\ EDIT - DELETE MENU // \\ // \\ // \\ // \\ // \\ // \\ // \\ //     #
#---------------------------------------------------------------------------------------------------------------#
    $SQL = " SELECT * FROM ".$tablename." ";
    $retid = mysql_db_query($db, "$SQL", $cid);
    if (!$retid) { echo( mysql_error()); }
    else {
        echo ("<ul>Record Edit/Delete Menu\n");
        while ($row = mysql_fetch_array($retid)) {
			  $name = $row["name"];  

			  $id = $row["id"];  

			  $position = $row["position"];
			  $email = $row["email"];
			  $location = $row["location"];
			  $skills = $row["skills"];
			  $resume = $row["resume"]; 

        echo ("<li>ID Number: <b>".$id."</b>. Name: <b>".$name."</b> ( <A HREF='edit.php?id=$id'>Edit</A> / <A HREF='$PHP_SELF?id=$id&task=del'>Delete</A> )</li>");
    }
    echo ("</ul><br>".$back."");
#===============================================================================================================#
    }
#---------------------------------------------------------------------------------------------------------------#
# // \\ // \\ // \\ // \\ // \\ // \\ // \\ VIEW ALL RECORDS // \\ // \\ // \\ // \\ // \\ // \\ // \\ // \\    #
#---------------------------------------------------------------------------------------------------------------#

	} elseif ($task=="view") {
# setup SQL statement
$SQL = " SELECT * FROM ".$tablename." ";

# execute SQL statement
$retid = mysql_db_query($db, "$SQL", $cid);

# check for errors
if (!$retid) { echo( mysql_error()); }
else {

    # display results
    echo ("All Data Records<br><table width='100%' border=1 cellspacing=0 cellpadding=0><tr><td>");

	while ($row = mysql_fetch_array($retid)) {
			  $name = $row["name"];  
			  $id = $row["id"];  
			  $position = $row["position"];
			  $email = $row["email"];
			  $location = $row["location"];
			  $skills = $row["skills"];
			  $resume = $row["resume"]; 

        echo ("<table border=1 cellspacing=1 cellpadding=1>
				<tr><td colspan=5>ID: ".$id."</td></tr>
				<tr><td>Name:</td><td>Skills:</td></tr>
				<tr><td>".$name."</td><td rowspan=7>".$skills."</td></tr>
				<tr><td>Position:</td></tr><tr><td>".$position."</td></tr>
				<tr><td>Location:</td></tr><tr><td>".$location."</td></tr>
				<tr><td>Email Address:</td></tr><tr><td><a href='mailto:".$email."'>".$email."</a></td></tr>
				</table>");
    }
    echo ("</td></tr></table>");
}

#===============================================================================================================#

#---------------------------------------------------------------------------------------------------------------#
# // \\ // \\ // \\ // \\ // \\ // \\ // \\ // \\ MAIN MENU // \\ // \\ // \\ // \\ // \\ // \\ // \\ \\ //     #
#---------------------------------------------------------------------------------------------------------------#
    } else {
	echo "This is the main menu where you can add / edit or delete records and upload an image.<br>
		  Add a Record: <a href='add.php'>Click Here</a><br>
		  Edit or Delete a Record: <a href='$PHP_SELF?task=ed-del-menu'>Click Here</a><br>
		  View the Records: <a href='$PHP_SELF?task=view'>Click Here</a><br>
		  Upload an Image: <a href='upload.php'>Click Here</a>";
}
?>

cgraz

        $SQL = " DELETE FROM ".$tablename." "; 
        $SQL = $SQL . " WHERE id = $id ";

Why do you have a space after $tablename, then another space before WHERE? You only need once space...

Anyways, your error is most likely caused because you don't have single quotes around $id.

I'd rewrite your code like this

        $SQL = "DELETE FROM ".$tablename; 
        $SQL .= " WHERE id='$id'";

Cgraz

YappyDog

I'd start by printing out the value of $SQL before you execute it. It doesn't look like you are setting the $tablename variable anywhere.

cgraz

I'm guessing the value for $tablename is in require ("../../../cgi-bin/connect2test.php");

If that were the problem, would mysql_error() print that it was a syntax error, or would it say table [blank] doesn't exist. I could be wrong, but I've usually found the single quotes will cause that error.

Cgraz

helz

yes the tablename is in the connect file. and with the changes you suggested the error still applies, you are correct in saying that if $tablename is wrong then the table not found would have shown.
nothing like a problem from me to get the little nano-bots in our brains to start working

oh and the error shows in parts: add/edit-delete menu/view

to view the php script in action go to:
HERE

YappyDog

Okay, so I'll ask again. What does $SQL contain before you execute it?

helz

what do you mean by "contain" ? its just a variable used to define what sql query to execute. the first time it worked on the unedited version with just a few test fields the table to add/edit view was typed in manually but i thought whether it would work with a variable, easier to change when it goes from beta to final release

YappyDog

add a statement that says:

echo "SQL = $SQL<br>";

That way you can see exactly what sql is being executed.

helz

this is echo'ed before the erro:

SQL = SELECT * FROM

which instantly says that the table is not being inserted properly but the table does exist...

i tried it again with a static tablename and it works. so i can either just do static. or does anyone have any ideas to make it work?

YappyDog

Change your logon info and then show us the code where you are trying to set $tablename.

helz

<?
    $usr = "[enterloginhere]";
    $pwd = "[enterpasswordhere]";
    $db = "[enterdbhere]";
    $host = "localhost";

# connect to database
$cid = mysql_connect($host,$usr,$pwd);
if (!$cid) { echo("ERROR: " . mysql_error() . "\n");    }

mysql_select_db ($db); 

$ftp = "ftp://$usr:$pwd@ftp.hellrazer.net/www/";
$url = "http://www.hellrazer.net/";
$copyright = "Version 1.0 by <a href='mailto:helz@hellrazer.net'>Ian Helsby</a>";
$back = "<--<a href='manage.php'>Back to the main menu</a>";

$tablename = "ddg";

?>

YappyDog

Well, if that is the code within your

require ("../../../cgi-bin/connect2test.php");

Then right after that I'd add a statement to show you the contents of $tablename.

require ("../../../cgi-bin/connect2test.php");
echo "tablename = $tablename<br>";

helz

it says that theres nothing in $tablename

wierd since the other variables inside connect.php work

YappyDog

Well, about the only other thing I can think of to try would be to move the following statement

echo "tablename = $tablename<br>";

to within your require file so that it is right after the spot where the variable is set. That'll confirm whether or not you are using the file you think you are.

helz

i think the scary thing is: we just changed servers so i think its still the old connect2test.php thats on the server.

uploading now....
Confirming embarrasing situation....

god damnit lol.

sorry for puttin you guys through this lol. damn upload thing. cos im either still a noobie or i like the look of the pretty colours i use dreamweaver mx to do my php coding. lets face it i like the colours. so i set up a site thing on there so it uploads the files as i save em. guess it got a bit screwed up there. thanks for the trouble you guys put into this. ill try and not let it happen again 😃