Database Connection using PHP
Open the MySQL database connection:
mysql_connect()
PHP provides mysql_connect() function to open a database connection.
This function takes three parameters .
This function returns the connection on success, or FALSE and an error on failure.
Syntax:-
mysql_connect(“Servername”,“username”, ”password”);
Example:-
mysql_connect(“localhost”, “root”,””);
Program:-
<?php
mysql_connect("localhost","root","");
echo "database connection";
?>
Create a Database using PHP
mysql_query()
PHP uses this function to create or delete a MySQL database.
This function takes two parameters and returns TRUE on success or FALSE on failure.
mysql_query returns a result handle.
Syntax:-
mysql_query(“sql query”,”connection_database”);
where
sql query:-SQL query to create or delete a MySQL database.
connection_database:-if not specified, then the last opened connection by mysql_connect will be used.
Example:-
<?php
$con=mysql_connect("localhost","root","");
echo "database connection";
echo"<br>";
mysql_query("create database student",$con);
echo"database created suucessfully";
?>
If database not connect and also not create database use mysql-error() or die ()
<?php
$con=mysql_connect("localhost","root","");
if(!$con)
{
die('cannot connect'.mysql_error());
}
echo "database connection";
echo"<br>";
$data1=mysql_query("create database student1",$con);
if(!$data1)
{
die('cannot create database'.mysql_error());
}
echo"database created suucessfully";
?>
How to close the connection
After create php database and after close php database use mysql_close();
mysql_close(“connection_name”);
e.g
mysql_close($con);
<?php
$con=mysql_connect("localhost","root","");
if(!$con)
{
die('cannot connect'.mysql_error());
}
echo "database connection";
mysql_close($con);
echo"<br>";
$data1=mysql_query("create database student1",$con);
if(!$data1)
{
die('cannot create database'.mysql_error());
}
echo"database created suucessfully";
?>
Specifying the database you want to open
mysql_select_db():-
PHP provides function mysql_select_db to select a database.
It returns TRUE on success or FALSE on failure.
Syntax:-
mysql_select_db(database name);
Example:-
mysql_select_db($data1);
<?php
$con=mysql_connect("localhost","root","");
if(!$con)
{
die('cannot connect'.mysql_error());
}
echo "database connection";
echo"<br>";
mysql_select_db("student");
echo"database select suucessfully";
?>
Creating a MySQL Table
| id | name | address |
|---|
| 1 | rohit | shrirampur |
| 2 | Rahu | ahemednagar |
| 3 | seema | aurangabad |
The CREATE TABLE statement is used to create a table in MySQL.
The CREATE TABLE Statement is used to create tables to store data.
Integrity Constraints like primary key, unique key, foreign key can be defined
for the columns while creating the table. The integrity constraints can be defined at column level or table level.
Syntax:-
CREATE TABLE table_name(column_name1 datatype,
column_name2 datatype,
.....
.....
column_nameN datatype);
Where
table_name - is the name of the table.
column_name1, column_name2.... - is the name of the columns
datatype - is the datatype for the column like char, date, number etc.
Example:-
create table student11
(id int primary key,
name varchar(10),
address varchar(20));
<?php
$con=mysql_connect("localhost","root","");
if(!$con)
{
die('cannot connect'.mysql_error());
}
echo "database connection";
echo"<br>";
mysql_select_db("student");
echo"database select suucessfully";
echo"<br>";
$table1="create table student11(id int primary key,name varchar(10),address varchar(20))";
if(mysql_query($table1))
{
echo "table created successfully";
}
else
{
echo"cannot create table".mysql_error($con);
}
echo"<br>";
mysql_close($con);
?>
How can Insert data in database table
The INSERT command creates a new row in the table to store data.
The MySQL INSERT statement is used to insert a single record or multiple records into a table in MySQL.
Syntax:-
insert into table_name values(value1, value2, value3, ...valuen);
Example:-
insert into student11 values(1,'satish','pune');
<?php
$con=mysql_connect("localhost","root","");
if(!$con)
{
die('cannot connect'.mysql_error());
}
echo "database connection";
echo"<br>";
mysql_select_db("student");
echo"database select suucessfully";
echo"<br>";
$insert="insert into student11 values(1,'satish','pune')";
if(mysql_query($insert))
{
echo "insert data successfully";
}
else
{
echo"cannot insert data table".mysql_error($con);
}
echo"<br>";
mysql_close($con);
?>
Viewing a whole record
The SQL SELECT statement is used to select the records from database tables.
Syntax:-
SELECT column1_name, column2_name, columnN_name FROM table_name;
OR
Select * from table_name;
Example:-
select * from student11;
Show values from a MySQL database table inside a HTML table on a webpage
I want to retrieve the values from a database table and show them in a html table in a page.
mysql-fetch_array():
Fetch a result row as an associative array, a numeric array, or both.
mysql_fetch_array is actually a PHP function that allows you to access
data stored in the result returned from a successful mysql_query.
Syntax:-
mysql_fetch_array(resource result);
Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
Used table tag
with TH,TR,TD.
Output Display without table tags
<?php
$con=mysql_connect("localhost","root","");
if(!$con)
{
die('cannot connect'.mysql_error());
}
echo "database connection";
echo"<br>";
mysql_select_db("student");
echo"database select suucessfully";
echo"<br>";
$display="select * from student11";
$result=mysql_query($display);
while($row = mysql_fetch_array($result))
{
echo "<tr><td>".$row['id'].
"</td><td>" . $row['name'] .
"</td><td>" . $row['address'] .
"</td><br></tr>";
}
echo"<br>";
echo "</table>";
mysql_close($con);
?>
Output Display with table tags
<?php
$con=mysql_connect("localhost","root","");
if(!$con)
{
die('cannot connect'.mysql_error());
}
echo "database connection";
echo"<br>";
mysql_select_db("student");
echo"database select suucessfully";
echo"<br>";
$display="select * from student11";
$result = mysql_query($display);
echo "<table border=1 cellspacing=2 cellpadding=2>";
while($row = mysql_fetch_array($result))
{
echo "<tr><td>" . $row['id'] .
"</td><td>" . $row['name'] .
"</td><td>" . $row['address'] .
"</td></tr>";
}
echo "</table>";
echo"<br>";
mysql_close($con);
?>
Update data in tables
The MySQL UPDATE query is used to update existing records in a table in a MySQL database.
It can be used to update one or more field at the same time.
It can be used to specify any condition using the WHERE clause.
<?php
$con=mysql_connect("localhost","root","");
if(!$con)
{
die('cannot connect'.mysql_error());
}
echo "database connection";
echo"<br>";
mysql_select_db("student");
echo"database select suucessfully";
echo"<br>";
mysql_query("update student11 set address='mumbai' where id=1");
echo"database update sucessfully";
$display="select * from student11";
$result = mysql_query($display);
echo "<table border=1 cellspacing=2 cellpadding=2>";
while($row = mysql_fetch_array($result))
{
echo "<tr><td>" . $row['id'] .
"</td><td>" . $row['name'] .
"</td><td>" . $row['address'] .
"</td></tr>";
}
echo "</table>";
echo"<br>";
mysql_close($con);
?>
