syntax help php mysql

[bennyhill]

im working on a project that will allow users to first enter a serial number then confirm their details.

my database looks like:

database name USERS
Table name uniq

ID (primary key)
ClientID not null
ClientName not null
SerialNo not null
InvoiceNo not null
ReceiveDate not null
ClientVerDate null
ClientVerified null
ClientAddress null

Basically, all the fields not null are known to me and are currently stored in my database.
I have 4 files Index.php , Validate.php , Config.php , Page.php(just my html split up into variables $p1 and $p2)

index.php is a form that asks the user for their serial number(SerialNo)
this info is passed to validate.php where i collect all known data in the database related to SerialNo
Validate.php displays the known data which again is everything from above marked not null.

$serial = $_POST['serial'];

// Get all the data from the "uniq" table and match to serial
$result = mysql_query("SELECT * FROM uniq" . " WHERE SerialNo like \"%$serial%\" ") 
or die(mysql_error());  

echo "$p1";

// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row and allow user to contact if wrong details
	echo "<p>If This is not you, <a href=\"contact.php\">Please click here</a></p>";
	echo "<p>"; 
	echo  'NAME: ' .$row['ClientName'];
	echo "</p><p>"; 
	echo 'ClientID: ' .$row['ClientID'];
	echo "</p><p>"; 
	echo 'INVOICE NUMBER: ' .$row['InvoiceNo'];
	echo "</p><p>";
	echo 'DATE RECEIVED: ' .$row['ReceiveDate'];
	echo "</p><br /><p>";
	echo "<h2>Please enter your address below to verify your warranty</h2></p><p>";
	echo 'ADDRESS: ' .$row['ClientAddress'];
	echo "</p>";
}

So far so good although i am a noob and if there are better ways to do this please feel free to comment.
Now, i want to get the clients address to validate their application. This is where im way off the mark and need

help.

as im currently doind it, it loops through all entries in the database and screws up my records. im going to post

the code below but beware it is wrong.

if ($row['ClientAddress'] == NULL){
echo "

<p><h2>Please enter your address below to verify your warranty</h2></p>

<form method=\"post\" action=\"{$_SERVER['PHP_SELF']}\">
<textarea name=\"address\" rows=\"10\" cols=\"40\"> </textarea>
<p><input type=\"submit\" name=\"submitaddress\" value=\"Verify Warranty\"></p>

</form> "	


;} 

if (isset($_POST['submitaddress'])) {
	$address = $_POST['address'];
	$sql = ("UPDATE uniq SET 
	ClientAddress='$address',
	ClientVerDate=CURDATE(),
	ClientVerified="true" 
	WHERE SerialNo like \"%$serial%\" 
	");
	if (@mysql_query($sql)) {
		echo ('<p>thank you, you are verified</p>');
	} else {
		echo ('<p>error adding address:' .mysql_error().'</p>');
	}
}

I hope someone can make sense of what im trying to do here and point me in the right direction. What i want to

happen is, the user enters their address and hits submit. The script then adds the address to the Database field

ClientAddress, adds the current date to ClientVerDate and adds the value 'true' to the field ClientVerified. The

submitted information obviously has to apply to the correct user as identified by the serial they enter. I then

want to display a thank you type of message when the data has been added to the database. what is the correct way

to do this?

 

[bennyhill]

update

[noticed a typo in my first post so corrected]

Ok, so ive spent time on this today and have made progress but im still stuck on a somewhat new problem.

picking up from above, on my validate.php page i have the returned values relating to whatever serial number the user entered. I know that ClientAddress is empty so i give the user a form to enter it

if ($row['ClientAddress'] == NULL){
echo "

<p><h2>Please enter your address below to verify your warranty</h2></p>

<form method=\"post\" action=\"finished.php?identity=$serial\">
<textarea name=\"address\" rows=\"10\" cols=\"40\"> </textarea>
<p><input type=\"submit\" name=\"submitaddress\" value=\"Verify Warranty\"></p>


</form> "	


;}

i then pass this to the page finished.php with the urlquery passing the serial number so i can keep track of it. the user enters their address and hits submit and finished.php loads. This is where i am now stuck,

finished.php looks like this

//this is the serial number passed from the previous page
$user = $_REQUEST['identity']; 
//this is the address passed from the form
$address = $_POST['address'];

//this is the query to update the database
$sql=("INSERT INTO uniq SET ClientAddress='$address', ClientVerified='YES', ClientVerdate='CURDATE()' "." WHERE SerialNo=$user ");

if (isset($address)){
if (@mysql_query($sql)) {
	echo "$p1
	Your Warranty Has Been Validated
	$p2";
} else {
	die ("$p1 <p>Error Validating:" . mysql_error(). "</p>$p2");
}
}

So now my problem is, this page displays 'Error Validating: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 'WHERE SerialNo=5S53Y2J' at line 1' (the SerialNo value a random value i entered for testing) and so the database does not get updated and the user gets an ugly error. what is the correct way to enter this information into my database?

 

[louie]

did you try adding quote in the where clause:
also are you updating the table or new records insertion because the sql is wrong.

//insert sql
$sql = "Insert into `table_name` (`table_field_name`) VALUES ('value')";
 
//update sql
$sql = "Update `table_name` SET `field_name`='value' ";

//this is the serial number passed from the previous page 
$user = $_REQUEST['identity'];  
//this is the address passed from the form 
$address = $_POST['address']; 

//this is the query to update the database 
$sql=("INSERT INTO uniq SET ClientAddress='$address', ClientVerified='YES', ClientVerdate='CURDATE()' WHERE SerialNo='$user'"); 
//echo $sql;//uncomment both lines to see the query before execution
//die();
if (isset($address)){ 
if (@mysql_query($sql)) { 
    echo "$p1 
    Your Warranty Has Been Validated 
    $p2"; 
} else { 
    die ("$p1 <p>Error Validating:" . mysql_error(). "</p>$p2"); 
} 
}

</SPAN></SPAN>

 

[bennyhill]

Hi Louie

i have tried to add quote but no joy. i have also tried the following as i seen it somewhere in my searching to find the solution

$sql=("INSERT INTO table_name SET column_name='value', column_name='value', Column_name='value' " . " WHERE SerialNo='value' ");

note the concatenation

As it is, my command is in the following format, ive been searching the web and this 'seems' to be the correct syntax but it is not working

$sql=("INSERT INTO table_name SET column_name='value', column_name='value', column_name='value'  WHERE column_name='value' ");

each of these values is empty until this script is run so i presume i need to use insert as opposed to update? again, my script is below.

maybe the isset condition cecking for the address is wrong? im beginning to feel a bit lost, anybody got any pointers?

require("config.php"); // my database connection
include("page.php"); // html elements
 
//this is the serial number passed from the previous page
$user = $_REQUEST['identity']; 
//this is the address passed from the form
$address = $_POST['address'];

 

$sql=("INSERT INTO uniq SET ClientAddress='$address', ClientVerified='YES', ClientVerdate='CURDATE()' WHERE SerialNo='$user'");


if (isset($address)){
if (@mysql_query($sql)) {
	echo "$p1
	Your Warranty Has Been Validated
	$p2";
} else {
	die ("$p1 <p>Error Validating:" . mysql_error(). "</p>$p2");
}
}

my error is

Error Validating: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 'WHERE SerialNo='FHX3Y2J''' at line 1

 

[louie]

did you read my post?
the insert command doesn't need the "SET", that's for updating a record.
change the "INSERT INTO" to "UPDATE" as the "WHERE" clause is not required only if records is updating.

 

[bennyhill]

Hi Louie

I read but misunderstood, Many thanks for taking the time to help me, your advice is truly appreciated.

the correct command as you pointed out is

$sql=("UPDATE tablename SET column='value', column='value', column='value' WHERE SerialNo LIKE 'value' ");

my database is now accepting the data - THANK YOU!!!

 

[louie]

when you use the "where" clause you are trying to update a record that already exist and has a field value = to the "where" statement, which you can not use it in the insert statement as the record doesn't exist yet.

glad I could help.