Server Side Validation [MySQL client information table help]

[Robert W.]

hi,
Iv been looking for a great installer and I came across Advanced Installer and I like it, it has the server side validation but I am having issues with it

I saw on another post this code:

<?php

// server response codes
define('LICENSE_VALID', '601');
define('LICENSE_INVALID', '602');

// database connection parameters
$db_host = 'localhost:3306';
$db_user = 'root';
$db_pass = 'root123';
$db_name = 'test_db';

// client information table
$clients_tbl_name = 'clients';
$sn_tbl_col = 'serial_no';

/**
* Server HTTP response to the query issued by Advanced Installer serial validation tool.
*/
function ServerResponse($is_valid, $posted_serial = '', $lang_id = 1033)
{
$msg_sep = "\n";

// load error messages from your database, using "$lang_id" for localization (optional)

if($posted_serial == '')
return LICENSE_INVALID . $msg_sep . "Missing Serial Number !";

if($is_valid == true)
return LICENSE_VALID;
else
return LICENSE_INVALID . $msg_sep . "Serial Number: " . $posted_serial . ' is invalid !';
}

// Variables POSTed by Advanced Installer serial validation tool to this web page: "sn", "languageid".
if(isset($_POST['sn']) && trim($_POST['sn']) != '')
{
// get the serial number entered by the installing user in the "UserRegistrationDlg" dialog
$sn = trim($_POST['sn']);

// get the system language ID of the user's machine
// (you can use this parameter to display a localized error message taken from your database)
$languageid = (int) $_POST['languageid'];

// connect to database
$db_conn = @mysql_connect($host, $db_user, $db_pass);
if(!$db_conn)
{
// issue error response
echo ServerResponse(false, $sn, $languageid);
die();
}

// select target database
$db_selected = @mysql_select_db($db_name, $db_conn);
if(!$db_selected)
{
// issue error response
echo ServerResponse(false, $sn, $languageid);
die();
}

// prepare SQL statement
$sn_query = "SELECT `". $sn_tbl_col ."` FROM `". $clients_tbl_name ."` WHERE `". $sn_tbl_col ."` = '" . mysql_escape_string($sn) . "'";

// execute query
$result = @mysql_query($sn_query, $db_conn);

// get result set size
if(@mysql_num_rows($result) == 0)
{
// serial number NOT found in database => issue error response
echo ServerResponse(false, $sn, $languageid);
die();
}
else
{
// serial number was found in database => issue SUCCESS response
echo ServerResponse(true, $sn, $languageid);
die();
}
}
else
{
// issue error response
echo ServerResponse(false);
die();
}

?>

And I cannot get it to work, i have editing the MySQL connect setting but I dont know what

// client information table
$clients_tbl_name = 'clients';
$sn_tbl_col = 'serial_no';

is for what is the query I should execute?
This is what i have tried

Code: Select all

create table serial_no (			
	serial_no	smallint not null,

But it wont work, I get

#1064 - 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 2

What should I do?

Could some one give me a sample for clients and serial_no?

[Robert W.]

Could someone PLEASE tell me what sql table should be made!
a sample would be very appreciated.

[Cosmin]

Hi,

Here is an example for creating the database:

Code: Select all

-- --------------------------------------------------------
--
-- Database: `test_db`
--

CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------
--
-- Table structure for table `clients`
--

CREATE TABLE IF NOT EXISTS `clients` (
  `client_id` int(10) unsigned NOT NULL auto_increment,
  `first_name` varchar(255) collate utf8_bin NOT NULL,
  `last_name` varchar(255) collate utf8_bin NOT NULL,
  `email` varchar(255) collate utf8_bin NOT NULL,
  `serial_no` varchar(255) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`client_id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `serial` (`serial_no`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Our company clients' AUTO_INCREMENT=3 ;

--
-- Dumping data for table `clients`
--

INSERT INTO `clients` (`client_id`, `first_name`, `last_name`, `email`, `serial_no`) VALUES
(1, 'John', 'Doe', 'johnd@domain.com', '233-421-752-325'),
(2, 'FirstName', 'LastName', 'email@domain.com', '444-555-222-243');

This example also includes two entries in the database.

Regards,
Cosmin