PHP & MySQL
phpmyadmin
Type in your browser address bar
Localhost/phpmyadmin
Click database
Create database
Type the name of your database, in the create
new database.
library
Create a library database, leave collation in
the pull down menu then click create.
Create a table
Click the just created database “library” from
the list of database.
the database has no tables
book 5
Type a table name and number of fields then
click go.
Create a “book” table, with 5 fields.
Setup table fields
Add the following fields to table “books”
Bid:numric (integer),
auto increment
Title:string(varchar)
Author:string(varchar)
PublisherId:numric(integer)
Price:numric(decimal) with
attribuate unsigned
For (varchar) data type length must be specified.
To create primary key, choose primary from index
Choose Bid to be primary key and Pid (foreign key) to be
indexed
Choose Bid to be autoincrement
Click save
Create another table and Setup table fields
Create table publisher with 2 fields.
Add the following fields to table “publisher”
Pid:numric(integer)
address:
string(varchar)50
Choose Pid as a primary key, select primary
from index
Click save
Insert data in table
To insert data in table click insert.
Browse: to view data in tables
Structure: to display field setup
Search: to search data in table.
Empty: to delete table content.
Type data under the value heading, then click
go.
1
java
John
Create a Relation
Select your database (library)
Click more and choose designer
Click create relation
Create a Relation
Select the referenced key
(primary key) in the one side.
Select the foreign key
Select your referential
integrity 1
2
3
Create a Relation
To delete the relation click
The link the many side.
To edit the relation select the
book table, structure then click
The relation view. 1
2
Export Database
Select export
Click custom then
select your database.
Click go
Import Database
Under localhost
Click import
Choose file
Click go.
Using mysql console
Open XAMPP control panel
Click shell
Type mysql –h localhost –u root
to create database type
Create database database_name;
To open database type
Use database_name;
To create table type
Create table table_name;
To view table description type
Desc table_name;
To insert type
Insert into table_name values (‘ ‘,’ ‘,…);
Access mysql database using php
Setup connection to mysql database.
Select the database.
Query the database.
Fetch query results.
Close connection.
connection to mysql database.
Mysqli _connect(server,
username, password)
connects to a MySQL server through a port
the default is the string "localhost:3306“
username is a string for the user name
password is a string for the password
returns FALSE on failure
Example
$db_link =
mysqli_connect("localhost:3306“,"test",
"123");
Select the database.
mysqli_select_db(link ,name
)
select a database given by the string name
the link specifies the open link value such
as $db_link returned by a connect
statement.
returns TRUE on success else FALSE
Example
mysqli_select_db($db_link,"web_db");
Query the database.
mysqli_query(link,query)
make a select query
query is a string for the MySQL query
Don't end the query with a semi-colon
Return value is a resource identifier or FALSE
if the query is SELECT, SHOW or DESCRIBE
Example (select all rows of books table)
$query = "SELECT * FROM books";
$result = mysqli_query($db_link,,$query);
Fetch query results.
mysqli_fetch_row(result)
each call returns the next row as an indexed
array where result is a resource returned
from a call to mysqli_query (FALSE if no more
rows)
Example:
$query = "SELECT * FROM books";
$result = mysqli_query($db_link,$query);
$row = mysqli_fetch_row($result); // row 0
$isbn = $row[0]; // isbn for row 0
close connection
mysqli_close(link);
Example
Using Table “Books” In ”Library” Database
Make PHP code where:
1. Show all data in books table in a
table .
2. Add a textbox and a submit
button when user enter Book ID
in textbox and click on submit
the program showPid all data
price Autho Title Bi
about this book r d
1 250.0 John Java 1
0
2 200.0 Tom PHP 2
0
1 150.0 Smith Mysql 3
resourse
http://www.roseindia.net/tutorial/php/phpda
tabase/PHP-Create-Database.html
http://www.homeandlearn.co.uk/php/
php12p2.html
