It’s very common to insert data in multiple tables: a book followed by its author, a customer followed by their purchase, and so on. When doing this with an autoincrement column, you will need to retain the insert ID returned for storing in the related table.
For example, let’s assume that these devices can be “sold” to a person. New device is stored in the device table, we also want to create a key to tie it to the device to a owner. The code to do this is similar to that in, except that the returned insert ID is stored in the variable $insertID and is then used as part of the subsequent query

Now the cat is connected to its “owner” through the devices unique ID, which was created automatically by AUTO_INCREMENT.

Example for insert

When using AUTO_INCREMENT, you cannot know what value has been given to a column before a row is inserted. Instead, if you need to know it, you must ask MySQL afterward using the mysql_insert_id function. This need is common: for instance, when you process a purchase, you might insert a new customer into a Customers table and then refer to the newly created CustId when inserting a purchase into the Purchases table.

Using AUTO_INCREMENT is recommended instead of selecting the
highest ID in the id column and incrementing it by one, because
concurrent queries could change the values in that column after the
highest value has been fetched and before the calculated value is
stored.

Adding data to the devices table and reporting the insert ID

Nippon ( Denon ) has been sold, so it’s time to remove device
from the database.

Changing data that you have already inserted is also quite simple.
Renaming Denon to Nippon Columbia.

Now that some data has been entered into the devices table. Next thing is how you can check that it was correctly inserted.

Retrieving rows from the devices table

This code simply issues the MySQL query SELECT * FROM devices and then displays all the rows returned.

Adding data to the devices table

You may wish to add a couple more items of data by modifying $query as follows and calling up the program in your browser again:

$query = “INSERT INTO devices VALUES(NULL, ‘Minidisc’, ‘Sony’, 2)”;
$query = “INSERT INTO devices VALUES(NULL, ‘Dat’, ‘Sony’, 3)”;

By the way, notice the NULL value passed as the first parameter? This is because the id column is of type AUTO_INCREMENT, and MySQL will decide what value to assign according to the next available number in sequence. So, we simply pass a NULL value, which will be ignored. Of course, the most efficient way to populate MySQL with data is to create an array and insert the data with a single query.

Dropping a table is very easy to do and is therefore very dangerous, so be careful. However, I don’t recommend that you try it until you have been through the other examples!

Dropping the devices table

When you aren’t logged into the MySQL command line, here’s a handy piece of code that you can use to verify that a table has been correctly created from inside a browser. It simply issues the query DESCRIBE devices and then outputs an HTML table with four headings—Column, Type, Null, and Key—underneath which all columns. Within the table are shown. To use it with other tables, simply replace the name devices in the query with that of the new table.

Describing the devices table

The output from the program should look like this:
Column Type Null Key
id smallint(6) NO PRI
family varchar(32) NO
name varchar(32) NO
age tinyint(4) NO

Let’s assume that you are working for a audio store and need to create a database to hold details about all the types of devices in the store. You are told that there are nine families of devices—Amplifier, minidisc, DVD, C-casette, Turntable, DAT, DCC, Turner, and DAB—so you’ll need a column for that. Then each device has been given a name, so that’s another column, and you also want to keep track of their ages, which is another. Of course, you will probably need more columns later, perhaps to hold voltage requirements, and other details, but for now that’s enough to get going. A unique identifier is also needed for each device, so you also decide to create a column for that called id.

The code you might use to create a MySQL table to hold this
data, with the main query assignment in bold text.

Creating a table called devices

As you can see, the MySQL query looks just like what you would type directly at the command line, except without the trailing semicolon.

You are now ready to look at some practical techniques that you can use in PHP to access the MySQL database, including tasks such as creating and dropping tables; inserting, updating, and deleting data; and protecting your database and website from malicious users. Note that the following examples assume that you’ve already created the login.php program discussed earlier in this website.