How to Use PHP to Interact With MySQL

PHP is a programming language used with HTML to design websites. MySQL is a database program that is efficient and reliable. To properly use PHP and MySQL you need to have both programs downloaded onto your computer.

Instructions

1. Login or create a user name and password for MySQL. To create a new user name, login to MySQL as "Root."

2. Type in this statement once you are logged in:CREATE DATABASE db_test;
   USE db_test;CREATE TABLE tbl_phonebook( phone_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, phone_name VARCHAR(40), phone_number VARCHAR(40), INDEX phone_name_idx (phone_name), INDEX phone_number_idx (phone_number); GRANT SELECT,UPDATE,DELETE,INSERT TO db_test.* TO 'test_user' IDENTIFIED BY 'test_user'. After this is entered, MySQL creates a web application and titles it "tbl_phonebook."

3. Connect to your MySQL database. To do so, type: ?php$link=mysql_connect('localhost','test_user','test_user');$persist_link=mysql_pconnect('localhost','test_user','test_user');?. This automatically creates a connection to the database. If there is already a connection it uses the open one.

4. Pick your database. Type in: ?php$link=mysql_connect('localhost','test_user','test_user');mysql_select_db('db_test',$link);?. This allows you to select the database you want to use.

5. To insert data, use this template: ?php mysql_connect('localhost','test_user','test_user');
   mysql_select_db('db_test');$sql="INSERT INTO tbl_phonebook
   (phone_name,phone_number)
   VALUES('john doe' ,'[phone]') ";
   mysql_query($sql);?

6. Retrieve data from the MySQL database use, by typing: ?php
   mysql_connect('localhost','test_user','test_user');
   mysql_select_db('db_test');$sql="SELECT * FROM tbl_phonebook";
   mysql_query($sql);
   ?

7. Take data off the database with this template: mysql_connect('localhost','test_user','test_user');
   mysql_select_db('db_test');
   $sql="DELETE FROM tbl_phonebook WHERE phone_id=1";
   $res=mysql_query($sql);
   if ($res){print('Phone ID =1 deleted');
   } else print('Unable to delete');
   ?>

8. Close the connection to the database by typing: ?php$link=mysql_connect('localhost','test_user','test_user');mysql_select_db('db_test',$link);
   $sql="SELECT * FROM tbl_phonebook";$res=mysql_query($sql,$link);
   if ($res){while ($data=mysql_fetch_object($res)){
   print("

   Name : ".$data->phone_name);print("Phone : ".$data->phone_number."

   ");} mysql_free_result($res);} mysql_close($link)

Tips & Warnings

• Everywhere you see test_user, insert your user name.

Resources

• Find out everything you need to know about MySQL