PHP Forum

**willemsem** · 08-02-2004, 07:25 AM

Hello, I'm having a problem with a php/mysql application that I'm creating, I always get this error:

Quote:

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 'ssl, ssh, dnscontrol) VALUES ('2', '177', 'WEBHOSTING_SHARED_m'

Below is the php code where I created the database table with:

PHP Code:


			
<?php

$host ="****";

$dbname ="****";

$username ="****";

$password ="****";

$db = mysql_connect($host,$username,$password);

mysql_select_db($dbname,$db) or die(mysql_error());





mysql_query("CREATE TABLE `allepakketdb` (

  `pakketid` int(11) NOT NULL auto_increment,

  `klantid` int(11) NOT NULL default '0',

  `referid` int(11) NOT NULL default '0',

  `itemname` text NOT NULL,

  `username` text NOT NULL,

  `email` text NOT NULL,

  `passwd` text NOT NULL,

  `passwd2` text NOT NULL,

  `domain` text NOT NULL,

  `ip` text NOT NULL,

  `notify` text NOT NULL,

  `paymonth` text NOT NULL,

  `a3` text NOT NULL,

  `p3` text NOT NULL,

  `t3` text NOT NULL,

  `quota` int(11) NOT NULL default '0',

  `uquota` text NOT NULL,

  `bandwidth` int(11) NOT NULL default '0',

  `ubandwidth` text NOT NULL ,

  `mysql` int(11) NOT NULL default '0',

  `umysql` text NOT NULL,

  `nemails` int(11) NOT NULL default '0',

  `unemails` text NOT NULL,

  `nemailf` int(11) NOT NULL default '0',

  `unemailf` text NOT NULL,

  `nemailr` int(11) NOT NULL default '0',

  `unemailr` text NOT NULL,

  `nemailml` int(11) NOT NULL default '0',

  `unemailml` text NOT NULL,

  `vdomains` int(11) NOT NULL default '0',

  `uvdomains` text NOT NULL,

  `nsubdomains` int(11) NOT NULL default '0',

  `unsubdomains` text NOT NULL,

  `domainptr` int(11) NOT NULL default '0',

  `udomainptr` text NOT NULL,

  `ftp` int(11) NOT NULL default '0',

  `uftp` text NOT NULL,

  `aftp` text NOT NULL,

  `cgi` text NOT NULL,

  `ssl` text NOT NULL,

  `ssh` text NOT NULL,

  `dnscontrol` text NOT NULL,

  PRIMARY KEY  (`pakketid`)

) TYPE=MyISAM AUTO_INCREMENT=1");

?>

And now folows the code where the error is:

PHP Code:


			
<?php include("logincheck.php"); 



?>

<html>

<head>

<title>Order enterded in database, please choose payment method</title>

</head>

<body>

<?php include("return1_command.php"); ?>

<h2>The order has been entered in the database</h2>

<p>&nbsp;</p>

<p>You currently have two options to activate the webhosting:</p>

<p>1) The fully automated payment by using the paypal subscription method.(recommended)</p>

<?php

include("connection.php");



switch($_POST["package"]){

case "s": 

    $quota = 50;

    $uquota = "OFF";

    $bandwidth = 1000;

    $ubandwidth = "OFF";

    $mysql = 1;

    $umysql = "OFF";

    $nemails = 20;

    $unemails = "OFF";

    $nemailf = 10;

    $unemailf = "OFF";

    $nemailr = 5;

    $unemailr = "OFF";

    $nemailml = 2;

    $unemailml = "OFF";

    $vdomains = 1;

    $uvdomains = "OFF";

    $nsubdomains = 10;

    $unsubdomains = "OFF";

    $domainptr = 5;

    $udomainptr = "OFF";

    $ftp = 2;

    $uftp = "OFF";

    $aftp = "OFF";

    $cgi = "ON";

    $ssl = "ON";

    $ssh = "ON";

    $dnscontrol = "ON";

    $itemnameb = "SHARED_";

break;

case "m":

    $quota = 100;

    $uquota = "OFF";

    $bandwidth = 2000;

    $ubandwidth = "OFF";

    $mysql = 2;

    $umysql = "OFF";

    $nemails = 40;

    $unemails = "OFF";

    $nemailf = 40;

    $unemailf = "OFF";

    $nemailr = 10;

    $unemailr = "OFF";

    $nemailml = 4;

    $unemailml = "OFF";

    $vdomains = 1;

    $uvdomains = "OFF";

    $nsubdomains = 4;

    $unsubdomains = "ON";

    $domainptr = 10;

    $udomainptr = "OFF";

    $ftp = 4;

    $uftp = "OFF";

    $aftp = "OFF";

    $cgi = "ON";

    $ssl = "ON";

    $ssh = "ON";

    $dnscontrol = "ON";

    $itemnameb = "SHARED_";

break;

case "l":

    $quota = 200;

    $uquota = "OFF";

    $bandwidth = 4000;

    $ubandwidth = "OFF";

    $mysql = 4;

    $umysql = "OFF";

    $nemails = 80;

    $unemails = "OFF";

    $nemailf = 80;

    $unemailf = "OFF";

    $nemailr = 20;

    $unemailr = "OFF";

    $nemailml = 6;

    $unemailml = "OFF";

    $vdomains = 1;

    $uvdomains = "OFF";

    $nsubdomains = 8;

    $unsubdomains = "ON";

    $domainptr = 20;

    $udomainptr = "OFF";

    $ftp = 6;

    $uftp = "OFF";

    $aftp = "OFF";

    $cgi = "ON";

    $ssl = "ON";

    $ssh = "ON";

    $dnscontrol = "ON";

    $itemnameb = "SHARED_";

break;

case "xl":

    $quota = 500;

    $uquota = "OFF";

    $bandwidth = 10000;

    $ubandwidth = "OFF";

    $mysql = 8;

    $umysql = "OFF";

    $nemails = 200;

    $unemails = "OFF";

    $nemailf = 200;

    $unemailf = "OFF";

    $nemailr = 50;

    $unemailr = "OFF";

    $nemailml = 10;

    $unemailml = "OFF";

    $vdomains = 1;

    $uvdomains = "OFF";

    $nsubdomains = 20;

    $unsubdomains = "ON";

    $domainptr = 50;

    $udomainptr = "OFF";

    $ftp = 10;

    $uftp = "OFF";

    $aftp = "OFF";

    $cgi = "ON";

    $ssl = "ON";

    $ssh = "ON";

    $dnscontrol = "ON";

    $itemnameb = "SHARED_";

break;

case "b":

    $quota = 1000;

    $uquota = "OFF";

    $bandwidth = 20000;

    $ubandwidth = "OFF";

    $mysql = 40;

    $umysql = "OFF";

    $nemails = 40;

    $unemails = "ON";

    $nemailf = 40;

    $unemailf = "ON";

    $nemailr = 10;

    $unemailr = "ON";

    $nemailml = 4;

    $unemailml = "ON";

    $vdomains = 20;

    $uvdomains = "OFF";

    $nsubdomains = 4;

    $unsubdomains = "ON";

    $domainptr = 10;

    $udomainptr = "ON";

    $ftp = 4;

    $uftp = "ON";

    $aftp = "OFF";

    $cgi = "ON";

    $ssl = "ON";

    $ssh = "ON";

    $dnscontrol = "ON";

    $itemnameb = "RESELLER_";

break;

case "c":

    $quota = 2000;

    $uquota = "OFF";

    $bandwidth = 40000;

    $ubandwidth = "OFF";

    $mysql = 80;

    $umysql = "OFF";

    $nemails = 80;

    $unemails = "ON";

    $nemailf = 80;

    $unemailf = "ON";

    $nemailr = 20;

    $unemailr = "ON";

    $nemailml = 6;

    $unemailml = "ON";

    $vdomains = 40;

    $uvdomains = "OFF";

    $nsubdomains = 8;

    $unsubdomains = "ON";

    $domainptr = 20;

    $udomainptr = "ON";

    $ftp = 8;

    $uftp = "ON";

    $aftp = "OFF";

    $cgi = "ON";

    $ssl = "ON";

    $ssh = "ON";

    $dnscontrol = "ON";

    $itemnameb = "SHARED_";

break;

case "e":

    $quota = 50000;

    $uquota = "OFF";

    $bandwidth = 100000;

    $ubandwidth = "OFF";

    $mysql = 200;

    $umysql = "OFF";

    $nemails = 200;

    $unemails = "ON";

    $nemailf = 200;

    $unemailf = "ON";

    $nemailr = 50;

    $unemailr = "ON";

    $nemailml = 10;

    $unemailml = "ON";

    $vdomains = 1;

    $uvdomains = "ON";

    $nsubdomains = 20;

    $unsubdomains = "ON";

    $domainptr = 50;

    $udomainptr = "ON";

    $ftp = 20;

    $uftp = "ON";

    $aftp = "OFF";

    $cgi = "ON";

    $ssl = "ON";

    $ssh = "ON";

    $dnscontrol = "ON";

    $itemnameb = "RESELLER_";

break;

}

$itemnamea ="WEBHOSTING_";

$itemnamec = $itemnamea . $itemnamec;

$itemname =$itemnamea . $itemnameb . $_POST["package"];

echo $loguser;

echo $email;

echo $pakket;

$query = "INSERT INTO allepakketdb( klantid, referid, itemname, username, email, passwd, passwd2, domain, ip, notify, paymonth, a3, p3, t3, quota, uquota, bandwidth, ubandwidth, mysql, umysql, nemails, unemails, nemailf, unemailf, nemailr, unemailr, nemailml, unemailml, vdomains, uvdomains, nsubdomains, unsubdomains, domainptr, udomainptr, ftp, uftp, aftp, cgi, ssl, ssh, dnscontrol) ";

$query .="VALUES ('";

$query .= $_POST["klantid"] ."', '";

$query .= $_POST["referid"] ."', '";

$query .= $itemname ."', '";

$query .= $_POST["username"] ."', '";

$query .= $_POST["email"] ."', '";

$query .= $_POST["passwd"] ."', '";

$query .= $_POST["passwd2"] ."', '";

$query .= $_POST["domain"] ."', '";

$query .= $_POST["ip"] ."', '";

$query .= $_POST["notify"] ."', '";

$query .= $_POST["paymonth"] ."', '";

$query .= $_POST["a3"] ."', '";

$query .= $_POST["p3"] ."', '";

$query .= $_POST["t3"] ."', '";

$query .= $quota ."', '";

$query .= $uquota ."', '";

$query .= $bandwidth ."', '";

$query .= $ubandwidth ."', '";

$query .= $mysql ."', '";

$query .= $umysql ."', '";

$query .= $nemails ."', '";

$query .= $unemails ."', '";

$query .= $nemailf ."', '";

$query .= $unemailf ."', '";

$query .= $nemailr ."', '";

$query .= $unemailr ."', '";

$query .= $nemailml ."', '";

$query .= $unemailml ."', '";

$query .= $vdomains ."', '";

$query .= $uvdomains ."', '";

$query .= $nsubdomains ."', '";

$query .= $unsubdomains ."', '";

$query .= $domainptr ."', '";

$query .= $udomainptr ."', '";

$query .= $ftp ."', '";

$query .= $uftp ."', '";

$query .= $aftp ."', '";

$query .= $cgi ."', '";

$query .= $ssl ."', '";

$query .= $ssh ."', '";

$query .= $dnscontrol ."')";

$result = mysql_query($query) or die(mysql_error());

$query2 = "SELECT pakketid FROM allepakketdb WHERE klantid = '".$klantid."' AND username = '".$_POST["username"]."'";

$result = mysql_query($query2) or die(mysql_error());

list($itemnumber) = mysql_fetch_row($result);

?>



<form action="https://www.paypal.com/cgi-bin/webscr" method="post">

<input type="image" src="order_paypal.gif" border="0" name="submit" alt="Order the webhosting package with paypal, and start editing your website instantly!">

<input type="hidden" name="cmd" value="_xclick-subscriptions">

<input type="hidden" name="business" value="****">

<input type="hidden" name="item_name" value="<?php echo $itemname; ?>">

<input type="hidden" name="item_number" value="<?php echo $itemnummer; ?>">

<input type="hidden" name="return" value="http://www.servehost.net/sicas/payment_ok.php">

<input type="hidden" name="rm" value="2">

<input type="hidden" name="notify_url" value="http://www.servehost.net/sicas/subscription_ipn.php">

<input type="hidden" name="cancel_return" value="http://www.servehost.net/sicas/payment_cancel.htm">

<input type="hidden" name="no_shipping" value="1">

<input type="hidden" name="no_note" value="1">

<input type="hidden" name="currency_code" value="EUR">

<input type="hidden" name="a3" value="<?php echo $_POST["a3"]; ?>">

<input type="hidden" name="p3" value="<?php echo $_POST["p3"]; ?>">

<input type="hidden" name="t3" value="<?php echo $_POST["t3"]; ?>">

<input type="hidden" name="src" value="1">

<input type="hidden" name="sra" value="1">

<input type="hidden" name="modify" value="0">

</form>

&nbsp;

<p>2) Pay using bank transfer.(alternative) If you decide to pay using bank transfer please note that the account creation can take up to one week.<br>

Recommended: As the payments are on a regular base, you might like to domicile the payments.

</p>



<form action="http://www.servehost.net/SICAS/banktransfer.php" method="post">

<input type="image" src="order_bank.gif">

<input type="hidden" name="item_name" value="<?php echo $itemname; ?>">

<input type="hidden" name="item_number" value="<?php echo $itemnummer; ?>">

</form>

</body>

</HTML> 

</body>

</html>

Thanks a lot.

Michael Willemse

**Kyrnt** · 08-02-2004, 04:30 PM

Michael,

I took a quick look at your code and you are trying to store numerical values as strings. Take bandwidth, for example. It is defined as an integer in the database, but then in the SQL you surround the value with quotes -- it looks like you are surrounding all your integer values in quotes. That would be causing your problem and is consistent with your error. Take the quotes off those values.

One warning about the way that you are building your SQL. You are including the quotes that end your current value and begin your next value at the end of each line, such as in:

PHP Code:


			
$query .= $unemails ."', '";

I wouldn't do it that way - it makes it very confusing when you need to put in a new field or take quotes off a field (as you need to now to solve your problem).

It may be uglier, but I would recommend doing it this way:

PHP Code:


			
// For strings

$query .= "'${unemails}', ";



// For integers

$query .= $bandwidth . ", ";

**willemsem** · 08-02-2004, 08:54 PM

Hello Kyrnt, I've just found the solution

Quote:

I took a quick look at your code and you are trying to store numerical values as strings. Take bandwidth, for example. It is defined as an integer in the database, but then in the SQL you surround the value with quotes -- it looks like you are surrounding all your integer values in quotes. That would be causing your problem and is consistent with your error. Take the quotes off those values.

It has nothing to do with the integer/string difference, it actually doesn't matters, but you're right, there should be a difference.

The "bug" was that SSL is a reserved word in sql. So for developers, especially webhosters (like me) who want to automate the signup/upgrade/suspend of webhosting, don't use SSL, use something like SSLenabled instead.

Still thanks a lot Kyrnt for replying

**Kyrnt** · 08-03-2004, 12:38 AM

willem,

Glad that my unhelpful help actually helped without helping....