[SOLVED] SQL syntax

HSM · December 17, 2007

Greetings!!

PHP Ver 5.2.0

MySQL Ver 5.0.26

PHP-Nuke Ver 8.1 (patched)

The code I have posted below does only 1 function, it updates the data collected from a standard form and posts it into the db. This is the error I receive when it is run:

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

I have tried multiples of different strings for the UPDATE string (which is Line 1 refered to in the error statement). Any assistance or further direction would be very appreciated at this point.

Kind Regards,

HSM

<?PHP

$pagetitle = _JCSVTVALIDATEEDIT;
include ("header.php");
title($pagetitle);

	if ($modneedpw=='Yes') {
		$modneedpw='1';
	} else {
		$modneedpw='0';
	}

	$query = ("UPDATE ".$prefix."_jcsvt_server SET host='$host', port='$port', spasswd='$spasswd', vtpasswd='$vtpasswd', sname='$sname', vname='$vname', auth='$auth', modneedpw='$modneedpw', clientcount='$clientcount', maxclients='$maxclients', platform='$platform', version='$version', lastquery='$lastquery', homepage='$homepage' WHERE sid=$evid");
	$result = mysql_query($query) or die(mysql_error()); 	

	$address="admin.php?op=jcsvtserverdetail&evid&evid=".$evid;
	Header("Location: $address");

    include("footer.php");
?>

revraz · December 17, 2007

Try changing

".$prefix."

to

'.$prefix.'

HSM · December 17, 2007

No go - returns the same error, except it displays the changes made to the file.

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 ''.xxx.'_jcsvt_server SET host='Admin Test', port='9911', spasswd='holycow', vtpa' at line 1

Note: xxx = where the actual prefix normally is, I removed the real prefix name and input 'xxx' for posting purposes only.

PHPNewbie55 · December 17, 2007

I don't see where you have the prefix...

but you might try this:

("UPDATE $prefix_jcsvt

trq · December 17, 2007

What does....

$result = mysql_query($query) or die(mysql_error()."<br />$query");

produce?

PHP_PhREEEk · December 17, 2007

Remove the parens...

<?php
$query = "UPDATE ".$prefix."_jcsvt_server SET host='$host', port='$port', spasswd='$spasswd', vtpasswd='$vtpasswd', sname='$sname', vname='$vname', auth='$auth', modneedpw='$modneedpw', clientcount='$clientcount', maxclients='$maxclients', platform='$platform', version='$version', lastquery='$lastquery', homepage='$homepage' WHERE sid=$evid";

PhREEEk

PHP_PhREEEk · December 17, 2007

While we're at it, please use back ticks around ALL field and table names, and single quotes around ALL values. Formatting also helps with long querys.

<?php
$query = "UPDATE `".$prefix."_jcsvt_server`
          SET `host` = '$host', `port`= '$port', `spasswd` = '$spasswd', `vtpasswd` = '$vtpasswd',
              `sname` = '$sname', `vname`= '$vname', `auth` = '$auth', `modneedpw = '$modneedpw',
              `clientcount` = '$clientcount', `maxclients` = '$maxclients', `platform` = '$platform',
              `version` = '$version', `lastquery` = '$lastquery', `homepage` = '$homepage'
          WHERE `sid` = '$evid'";

PhREEEk

HSM · December 17, 2007

Thorpe, your output was:

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

UPDATE xxx_jcsvt_server SET host='Admin Test', port='9911', spasswd='holycow', vtpasswd='', sname='ADMIN TESTING CODES', vname='Admin', auth='No', modneedpw='0', clientcount='', maxclients='', platform='', version='', lastquery='', homepage='http://fake.1' WHERE sid=

HSM · December 17, 2007

Phreeek, your output was:

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 'clientcount` = '', `maxclients` = '', `platform` = '', `version` ' at line 3

PHP_PhREEEk · December 17, 2007

Was that error given for the second version I posted?

PhREEEk

lemmin · December 17, 2007

Don't put in '' for NULL values, just leave the column out completely. If NULL is allowed, it will automatically nullify any columns that aren't specified.

PHP_PhREEEk · December 17, 2007

Missed a back tick, sorry.. re-try:

<?php
$query = "UPDATE `".$prefix."_jcsvt_server`
          SET `host` = '$host', `port`= '$port', `spasswd` = '$spasswd', `vtpasswd` = '$vtpasswd',
              `sname` = '$sname', `vname`= '$vname', `auth` = '$auth', `modneedpw` = '$modneedpw',
              `clientcount` = '$clientcount', `maxclients` = '$maxclients', `platform` = '$platform',
              `version` = '$version', `lastquery` = '$lastquery', `homepage` = '$homepage'
          WHERE `sid` = '$evid'";

PhREEEk

HSM · December 17, 2007

Yes, Phreeek - it was.

PHP_PhREEEk · December 17, 2007

Yes, Phreeek - it was.

If you missed it while posting a response, please try the revised code posted...

PhREEEk

HSM · December 17, 2007

Phreeek, that last suggestion from you - displays the page output correctly, except without any data. In other words, the layout is correct, no errors are presented, no data either though.

This is using your latest code snippet.

PHP_PhREEEk · December 17, 2007

Ok, well the SQL errors are gone, that's a good start. The variables are provided by your script elsewhere... so I guess we need to see the code where the variables are coming from...

PhREEEk

HSM · December 17, 2007

This is the EDIT form that is being called; it in turn calls the "validateedit" form (the first code snippet) once the submit is done.

<?php

if (!eregi($admin_file.".php", $_SERVER['SCRIPT_NAME']) AND !eregi($admin_file.".php", $_SERVER['PHP_SELF'])) { die ("Access Denied"); }

$pagetitle = _JCSVA_SERVERDETAIL;
include ("header.php");
if ($showGrafikAdmin == 1) {GraphicAdmin();}
title($pagetitle);
navigation();

$colorbg1 = get_jcsvt_config('colorbg1');
$colorbg2 = get_jcsvt_config('colorbg2');
$colorfg1 = get_jcsvt_config('colorfg1');
$colorfg2 = get_jcsvt_config('colorfg2');

$result = sql_query("select * from ".$prefix."_jcsvt_server where sid='$evid'", $dbi);

list($sid, $uid, $host, $port, $spasswd, $vpasswd, $sname, $vname, $auth, $modneedpw, $clientcount, $maxclients, $platform, $version, $lastquery, $homepage) = sql_fetch_row($result, $dbi);
$adm_name = get_jcsvt_user($uid);

echo "<br>";
OpenTable();

echo "<CENTER>\n<TABLE BORDER='0'>\n  <TR>\n";
echo "    <TD ALIGN='center' COLSPAN='2' BGCOLOR='".$colorbg1."'><FONT COLOR='".$colorfg1."'><STRONG> "._JCSV_SERVERINF." </STRONG></FONT></TD>";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg1."'><STRONG> "._JCSV_HostName.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$host." </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";	
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$textcolor2."'><STRONG> "._JCSV_Port.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$port." </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$textcolor2."'><STRONG> "._JCSVA_STATUSPW.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$spasswd." </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$textcolor2."'><STRONG> "._JCSV_VTPASSWORD.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$vtpasswd." </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$textcolor2."'><STRONG> "._JCSV_ServerName.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$sname." </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$textcolor2."'><STRONG> "._JCSV_VentriloName.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$vname." </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$textcolor2."'><STRONG> "._JCSVA_AUTH.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> \n";
if ($auth==1) {
echo _JCSV_YES."\n";	
} else {
echo _JCSV_NO."\n";
}
echo " </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg1."'><STRONG> "._JCSV_MODNEEDSPW.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> \n";
if ($modneedpw==1) {
echo _JCSV_YES."\n";	
} else {
echo _JCSV_NO."\n";
}
echo " </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg1."'><STRONG> "._JCSV_CLIENTCOUNT.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$clientcount." </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg1."'><STRONG> "._JCSV_maxClients.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$maxclients." </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg1."'><STRONG> "._JCSV_Platform.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$platform." </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg1."'><STRONG> "._JCSV_Version.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$version." </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg1."'><STRONG> "._JCSV_LastQuery.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$lastquery." </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg1."'><STRONG> "._JCSV_HOMEPAGE.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$homepage." </FONT></TD>\n";
echo "  </tr>\n  <tr>\n";
echo "    <TD ALIGN='right' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg1."'><STRONG> "._JCSVA_CREATEDBY.": </STRONG></FONT></TD>\n";
echo "    <TD ALIGN='left' BGCOLOR='".$colorbg2."'><FONT COLOR='".$colorfg2."'> ".$uid." (<A HREF='".$upath.$adm_name."' TITLE='"._JCSVA_CLICKUSERDETAILS."' TARGET='_blank'>".$adm_name."</A>) </FONT></TD>\n";
echo "  </tr>\n</table><BR>\n";
echo "<STRONG><A HREF='admin.php?op=jcsvtserveredit&evid=".$evid."'>"._JCSVA_MIDIFYTHISSERVER."</A></STRONG>\n</center>\n";
CloseTable();	

include("footer.php");

?>

HSM · December 17, 2007

Whoops!! Sorry, that is the wrong code segment. I have now posted the correct segement. This is the EDIT form that is being called; it in turn calls the "validateedit" form (the first code snippet) once the submit is done.

Okay I have been coding now for 15+ hrs today... and it shows... this code in this segment allows the user input, it is then passed to the validate code (1st one posted), it is then passed to the serverdetails page - code previous to this post.

<?PHP

$pagetitle = _JCSVTSERVEREDIT;
include ("header.php");
title($pagetitle);

$result = sql_query("select * from ".$prefix."_jcsvt_server where sid='$evid'", $dbi);
list($sid, $uid, $host, $port, $spasswd, $vpasswd, $sname, $vname, $auth, $modneedpw, $clientcount, $maxclients, $platform, $version, $lastquery, $homepage) = sql_fetch_row($result, $dbi);

navigation();
echo "<br>";
OpenTable();
echo "<form action=\"admin.php?op=jcsvtvalidateedit\" method=\"POST\">\n";
echo "  <table width=\"100%\" ALIGN=\"center\" CELLSPACING=\"1\">\n";
echo "    <tr>\n";
echo "      <td align=\"center\" colspan=\"2\" height=\"30\" bgcolor=\"$colbg2\"><b><font size=\"3\" face=\"Verdana\" COLOR=\"$coltxt2\">Server Details</font></b>\n";
echo "      <input type=\"hidden\" name=\"sid\"></td>\n";
echo "    </tr>\n";
echo "    <tr>\n";
echo "      <td width=\"200\" align=\"right\" height=\"22\" BGCOLOR=\"$colbg2\"><font size=\"2\" face=\"Verdana\" COLOR=\"$coltxt2\">Host Name</font><font face=\"Verdana\" size=\"2\"> </font></td>\n";
echo "      <td align=\"left\" height=\"22\" BGCOLOR=\"$colbg3\"><input type=\"text\" name=\"host\" value=\"$host\" size=\"100\" onblur=\"window.status=window.defaultStatus; return true;\" onfocus=\"window.status=\'Host name MUST be entered\'; return true;\" title=\"Host name MUST be entered\"></td>\n";
echo "    </tr>\n";
echo "    <tr>\n";
echo "      <td width=\"200\" align=\"right\" height=\"22\" BGCOLOR=\"$colbg2\"><font face=\"Verdana\" size=\"2\" COLOR=\"$coltxt2\">Port</font></td>\n";
echo "      <td align=\"left\" height=\"22\" BGCOLOR=\"$colbg3\"><input type=\"text\" name=\"port\" value=\"$port\" size=\"6\" onblur=\"window.status=window.defaultStatus; return true;\" onfocus=\"window.status=\'Valid PORT # is between 0001 to 9999\'; return true;\" title=\"Valid PORT # is between 0001 to 9999\"></td>\n";
echo "    </tr>\n";
echo "    <tr>\n";
echo "      <td width=\"200\" align=\"right\" height=\"22\" BGCOLOR=\"$colbg2\"><font size=\"2\" face=\"Verdana\" COLOR=\"$coltxt2\">Status Password</font></td>\n";
echo "      <td align=\"left\" height=\"22\" BGCOLOR=\"$colbg3\"><input type=\"text\" name=\"spasswd\" value=\"$spasswd\" size=\"20\" onblur=\"window.status=window.defaultStatus; return true;\" onfocus=\"window.status=\'Note: Passwords are case sensitive\'; return true;\" title=\"Note: Passwords are case sensitive\"></td>\n";
echo "    </tr>\n";
echo "    <tr>\n";
echo "      <td width=\"200\" align=\"right\" height=\"22\" BGCOLOR=\"$colbg2\"><font size=\"2\" face=\"Verdana\" COLOR=\"$coltxt2\">VT Password</font></td>\n";
echo "      <td align=\"left\" height=\"22\" BGCOLOR=\"$colbg3\"><input type=\"text\" name=\"vtpasswd\" value=\"$vtpasswd\" size=\"20\" onblur=\"window.status=window.defaultStatus; return true;\" onfocus=\"window.status=\'Note: Passwords are case sensitive\'; return true;\" title=\"Note: Passwords are case sensitive\"></td>\n";
echo "    </tr>\n";
echo "    <tr>\n";
echo "      <td width=\"200\" align=\"right\" height=\"22\" BGCOLOR=\"$colbg2\"><font face=\"Verdana\" size=\"2\" COLOR=\"$coltxt2\">Server Name</font></td>\n";
echo "      <td align=\"left\" height=\"22\" BGCOLOR=\"$colbg3\"><input type=\"text\" name=\"sname\" value=\"$sname\" size=\"100\" onblur=\"window.status=window.defaultStatus; return true;\" onfocus=\"window.status=\'Name that is veiwable by public for clickable link\'; return true;\" title=\"Name that is veiwable by public for clickable link\"></td>\n";
echo "    </tr>\n";
echo "    <tr>\n";
echo "      <td width=\"200\" align=\"right\" height=\"22\" BGCOLOR=\"$colbg2\"><font size=\"2\" face=\"Verdana\" COLOR=\"$coltxt2\">VT Name</font></td>\n";
echo "      <td align=\"left\" height=\"22\" BGCOLOR=\"$colbg3\"><input type=\"text\" name=\"vname\" value=\"$vname\" size=\"100\" onblur=\"window.status=window.defaultStatus; return true;\" onfocus=\"window.status=\'Name that appears at the top of your VT chat\'; return true;\" title=\"Name that appears at the top of your VT chat\"></td>\n";
echo "    </tr>\n";
echo "    <tr>\n";
echo "      <td width=\"200\" align=\"right\" height=\"22\" VALIGN=\"top\" BGCOLOR=\"$colbg2\"><font size=\"2\" face=\"Verdana\" COLOR=\"$coltxt2\">Authentication</font></td>\n";
echo "      <td align=\"left\" height=\"22\" BGCOLOR=\"$colbg3\"><select size=\"1\" name=\"auth\">\n";
if ($status=='1') {
	echo "	    <option selected value=\"Yes\">Yes</option>\n";
	echo "      <option value=\"No\">No</option>\n";
} else {
	echo "	    <option value=\"Yes\">Yes</option>\n";
	echo "      <option selected value=\"No\">No</option>\n";
}
echo "      </select></td>\n";
echo "    </tr>\n";
echo "    <tr>\n";
echo "      <td width=\"200\" align=\"right\" height=\"22\" BGCOLOR=\"$colbg2\"><font size=\"2\" face=\"Verdana\" COLOR=\"$coltxt2\">Homepage</font></td>\n";
echo "      <td align=\"left\" height=\"22\" BGCOLOR=\"$colbg3\"><input type=\"text\" name=\"homepage\" value=\"$homepage\" size=\"100\" onblur=\"window.status=window.defaultStatus; return true;\" onfocus=\"window.status=\'Valid HOMEPAGE must be entered to permit password links to function\'; return true;\" title=\"Valid HOMEPAGE must be entered to permit password links to function\"></td>\n";
echo "    </tr>\n";			
echo "    <tr>\n";
echo "<tr>\n";
echo "<td align=\"center\" bgcolor=\"$colbg2\" height=\"26\" COLSPAN=\"2\"><font size=\"2\" COLOR=\"$coltxt2\"><input type=\"submit\" value=\"VALIDATE CHANGES\" name=\"submit\"><br> <a href=\"admin.php?op=jcsvtserverdetail&evid=$evid\">GO BACK</a></font></td>\n"; #$id_event
echo "</tr>\n";
echo "  </table>\n";
echo "</form>\n";
echo "<BR>";
CloseTable();
    include("footer.php");
?>

HSM · December 17, 2007

(And) Thanks for the speedy help here folks. It is VERY appreciated.

HSM · December 19, 2007

Thx everyone, my coding partner came up with a solution which works perfectly. Very much appreciate the fast replies by all online here.

Sign In

[SOLVED] SQL syntax

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information