Setting a cookie from a database lookup
Going back to our webpage submitform.php, which inserted the visitor's name into our database, let's add code to look up the USERID number our database automatically assigns to each submitted name, and then send a cookie to the visitor's browser with the value set as the USERID number. But first, let's look at AUTO_INCREMENT. MySQL can be set to assign a number to each new record, starting with "1". The next inserted record gets "2", the next is "3", etc. You can add such a column, in this case called USERID, with this bit of SQL:
ALTER TABLE dbname
ADD COLUMN USERID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT;
The new field USERID is set as an
11-digit integer (allowing nearly 100 billion records); the field is not allowed to be empty (NOT NULL), the database is indexed by this field (PRIMARY KEY), and, lastly, AUTO_INCREMENT is set. To set a cookie in the visitor's browser after he or she inserts his name into your database, with the value of the cookie taken from the USERID, you could do this:
<?php
mysql_connect (localhost, username, password);
mysql_select_db (dbname);
mysql_query ("INSERT INTO tablename (first_name, last_name)
VALUES ('$first_name', '$last_name')
");
setcookie("CookieID", mysql_insert_id(), time()+94608000, "/");
/* expires in 3 years */
?>
The PHP function mysql_insert_id()
returns the AUTO_INCREMENT number assigned in the last INSERT query. No arguments are required, although you can put in a variable which has been assigned the value of the mysql_query. Try it out and then look at your browser's cookie list. You should see "CookieID" listed. Use your terminal emulator to view the contents of your MySQL table and see that the USERID of the last submission is the same as the value of the cookie listed in your browser.
Receiving a cookie
Let's write a PHP script for a webpage like Amazon.com. First, the PHP script checks if the client's browser has sent a cookie. If so, the visitor's name is displayed. If no cookie is found, a form is displayed for the visitor to submit their name, which is then added to the database and a cookie is set in the client's browser. First, let's create a webpage that displays the visitor's cookie:
<?php
print $CookieID;
?>
Save this script as cookiepage.php.
If you save this to your UNIX server, then open the webpage after running the last version of submitform.php, you should get the value of your cookie. You can check it against your browser's cookie list and your MySQL database. Now let's make cookiepage.php welcome me by name:
<?php
mysql_connect (localhost, username, password);
mysql_select_db (dbname);
$selectresult = mysql_query ("SELECT * FROM tablename
WHERE USERID = '$CookieID'
");
$row = mysql_fetch_array($selectresult);
echo "Welcome ", $row[first_name], "!";
?>
PHP 
/ 100 
