code check

[triangulum]

I have written a simple database to store my customer details. On making an order the details are checked to see if the customer already exists. As I am primarily interested in email addresses my code checks this field, if it finds a match it then updates the customer address and delivery address(just in case they have changed).

The whole script works well except for the UPDATE addresses which doesn't update the database. All variables contain correct values at the point of update and all table names are correct. Can anyone see the problem from the code below and additionally do I need to 'lock' the table in some way as the code increments the id value if it is not an existing customer?

//open a database and store the data in it.

$dbcnx = @mysql_connect('localhost', 'user', 'pw');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}

if (!@mysql_select_db('user')) {
exit('<p>Unable to locate the store ' .
'database at this time.</p>');
}

$emails = @mysql_query('SELECT id, email, FROM customers');
if (!$emails) {
exit('<p> Error generating a customer number from the database<br />' .
'Error: ' . mysql_error() . '</p>');
}

while ($temail = mysql_fetch_array($emails)) {
$id = $temail['id'];
$email = $temail['email'];

if ($email == $strEmail) {
$customerid = $id;
$customer_exists = 1; //for checks if customer exists

//update the addresses in case they have changed
$sql = "UPDATE customers SET
address='$address',
deladdress='$delAddress'
WHERE id='$customerid'";
break;
}
else {
$id++; //if this is a new customer, use the next id number.
$customerid = $id;
}
}

The code then carries on to store all data where it should be with the newly created customer id and this works fine other than the fact I am not sure whether the customer id may get duplicated if multiple users access the database at the same time.

[omoutop]

right below your sql=Update....
you miss something like :
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');

[triangulum]

Thanks OMOUTOP; it works a treat.

It never mentioned that you have to terminate the query in my 'teach yourself to be a webmaster in a jiffy' book :)

Do you happen to know the answer to my other question; Do I need to lock the table in some way? The book doesn't say anything about it but I did think I might get a problem with multiple users accessing the database. Am I right to assume that mysql locks the table automatically while a script is using it?

[coopster]

Actually, you aren't "terminating" the query, you are executing it. You get your statement all ready and then you execute the statement.

As far as unique identifiers, you are best off using AUTO_INCREMENT with your MySQL database. You'll find that you won't get any duplicates that way.

[triangulum]

It does auto increment in the database but I need to retain the id value to pass on to another page. In the above code I could fetch the new value by searching the 'unique' email column but in another table I am using this won't work because none of the columns are unique. Therefore I need to know how mysql handles execution and whether I need to 'lock' the table, I've searched the net and can't find any reference on the subject.

[whoisgregg]

As far as unique identifiers, you are best off using AUTO_INCREMENT with your MySQL database. You'll find that you won't get any duplicates that way.

So long as you never run two separate versions of the same DB that you must someday merge together, auto increment is perfect. Otherwise, you may consider using a randomly generated 32 character alphanumeric string that you check for redundancy. For a very large table, this may not work as well since the redundancy checks eventually get unbearable slow.

[coopster]

To retain the newly created AUTO_INCREMENT id you can SELECT it after the INSERT operation.

SELECT LAST_INSERT_ID()  [dev.mysql.com] AS lastID;

Then you could push it into a session variable, query string, or pass it as a hidden form field, etc. to be used on the next page.