PHP SAMPLE SCRIPTS FOR BASIC MYSQL OPERATIONS
We are going to create a table called "contacts" in this exercise.
This table is consist of the following four fields:
1. id （Primary Key) 2. first（First Name) 3. last （Last Name) 4. phone （Telephone Number)
Creates a table in the database:
After connecting to the database, execute a SQL command for creating a table.
<?php

# Connects to the mysql database server.
$conn_id = mysql_connect("db_server_name", "user_name", "password") or die;

# Selects the database.
mysql_select_db("db_name", $conn_id) or die;

# Creates a table.
# Here, we designate id as a primary key.
$query="CREATE TABLE contacts(id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL, last varchar(15) NOT NULL,phone varchar(20) NOT NULL, PRIMARY KEY(id))";
mysql_query($query);

# Disconnects from the mysql database server.
mysql_close($conn_id);

?>
Inserts a row of data into the table:
After connecting to the database, execute a SQL command for inserting a row.
<?php

$conn_id = mysql_connect("db_server_name", "user_name", "password") or die;
mysql_select_db("db_name", $conn_id) or die;

# Inserts a row into the table.
# Here, the value for id is empty since it is automatically set by the database.
$query="INSERT INTO contacts VALUES('', 'John', 'Smith', '425.123.4567')"; mysql_query($query);

mysql_close($conn_id);

?>
Updates the data in the table:
After connecting to the database, execute a SQL command for updating the data.
<?php

$conn_id = mysql_connect("db_server_name", "user_name", "password") or die;
mysql_select_db("db_name", $conn_id) or die;

# Updates the data in the table.
# Here, the telephone number for John Smith is updated.
$query="UPDATE contacts SET phone='425.111.2222' WHERE first='John' and last='Smith'";
mysql_query($query);

mysql_close($conn_id);

?>
Retrieves data from the table:
After connecting to the database, execute a SQL command for retrieving data.
<?php

$conn_id = mysql_connect("db_server_name", "user_name", "password") or die;
mysql_select_db("db_name", $conn_id) or die;

# Retrieves data from the table.
# Here, the telephone number for John Smith is retrieved.
$query="SELECT phone FROM contacts WHERE first='John' and last='Smith'";
$result=mysql_query($query);

mysql_close($conn_id);

# Displays the data.
$num=mysql_numrows($result);
$i=0;
while ($i < $num) {
$phone=mysql_result($result, $i, "phone");
echo "$phone<br>";
$i++;
}

?>
Deletes a row of data from the table:
After connecting to the database, execute a SQL command for deleting a row of data.
<?php

$conn_id = mysql_connect("db_server_name", "user_name", "password") or die;
mysql_select_db("db_name", $conn_id) or die;

# Deletes a row of data from the table.
# Here, a row of the data associated with John Smith is deleted.
$query="DELETE FROM contacts WHERE first='John' and last='Smith'";
mysql_query($query);

mysql_close($conn_id);

?>
Deletes a table from the database:
After connecting to the database, execute a SQL command for deleting a table.
<?php

$conn_id = mysql_connect("db_server_name", "user_name", "password") or die;
mysql_select_db("db_name", $conn_id) or die;

# Deletes a table named "contacts" from the database.
$query="DROP TABLE contacts";
mysql_query($query);

mysql_close($conn_id);

?>