PHP MySQL SELECT

Display data using mysql_fetch_array( ) function

mysql_fetch_array() return the rows from the number of records available in the database as an associative array or numeric array.

at a time it return only the first row as an associative array or numeric array. if we want to retrieve all the records of the table then we must put this function inside the while loop.

Syntax

1 2 3

mysql_fetch_array(data)

Display the records whose email is devesh@gmail.com using mysql_fetch_array()

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

<?php //database connectivity $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //select  values from empInfo table $sql = "SELECT * from empInfo WHERE email='devesh@gmail.com'"; $result = mysql_query($sql); print_r(mysql_fetch_array($result)); mysql_close($con); ?>

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Output :   Array( [0] =>1 [emp_id] => [1] => devesh [Name] => devesh [2] => devesh@gmail.com [email] =>devesh@gmail.com [3] => 9910099100 [mobile] => 9910099100 )

In the above example first connection to the database is created after that the result provided by mysql_query() is passed to mysql_fetch_array() and the data of the associative array or numeric array is displayed .

Display all records from empInfo table usingmysql_fetch_array( ) function.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

<?php //connect database $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //select all values from empInfo table $data="SELECT * FROM empInfo"; $val=mysql_query($data); while($r=mysql_fetch_array($val)) { echo $r['emp_id']." ".$r['name']." ".$r['email']." ".$r['mobile']."<br/>"; } ?>

In the above example first connection to the database is created after that the result provided by mysql_query() is passed to mysql_fetch_array() and array returned is stored in ‘r’ variable and it put in while loop to fetch all records . after that the data is displayed in tabular form.

Mysql_fetch_assoc

Fetch data using use of mysql_fetch_assoc( ) function

mysql_fetch_assoc() return the rows from the number of records available in the database as an associative array.

at a time it return only the first row as an associative array. if we want to retrieve all the records of the table then we must put this function inside the while loop.

Syntax

1 2 3 4

mysql_fetch_assoc(data)

Display the records whose email is devesh@gmail.com using mysql_fetch_assoc( )

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

<?php $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //select values from empInfo table $sql = "SELECT * from empInfo WHERE email='devesh@gmail.com'"; $result = mysql_query($sql); print_r(mysql_fetch_assoc($result)); mysql_close($con); ?>

1 2 3 4 5 6 7 8 9

Output : Array( [emp_id] =>1 [Name] => devesh [email] =>devesh@gmail.com [mobile] => 22 )

In the above example first connection to the database is created after that the result provided by mysql_query() is passed to mysql_fetch_assoc() and the data of the associative array is displayed .

Display all records from empInfo table usingmysql_fetch_assoc( ) function.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

<?php //connect database $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //select all values from empInfo table $data="SELECT * FROM empInfo"; $val=mysql_query($data); while($r=mysql_fetch_assoc($val)) { echo $r['emp_id']." ".$r['name']." ".$r['email']." ".$r['mobile']."<br/>"; } ?>

Mysql_fetch_row

Fetch data using mysql_fetch_row( ) function

The mysql_fetch_row() function returns a row from a recordset as a numeric array.

mysql_fetch_row() return a single row from the number of records available in the database.
at a time it return only the first row of the result set. if we want to retrieve all the rows of the table then we must put this function inside the while loop.
Syntax

1 2 3

mysql_fetch_row(data)

Display the records whose email is devesh@gmail.com using mysql_fetch_row( )

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

<?php //connect database $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //select values from empInfo table $sql = "SELECT * from empInfo WHERE email='devesh@gmail.com'"; $result = mysql_query($sql); print_r(mysql_fetch_row($result)); mysql_close($con); ?>

1 2 3 4 5 6 7 8 9 10

Output : Array( [0] =>1 [1] => devesh [2] => devesh@gmail.com [3] => 9910099100 )

In the above example first connection to the database is created after that the result provided by mysql_query() is passed to mysql_fetch_row() and the data of the row is displayed using print_r() function.

Retrieve all records from empInfo table usingmysql_fetch_row( ) function.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

<?php //connect database $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //select all values from empInfo table $data="SELECT * FROM empInfo"; $val=mysql_query($data); while($r=mysql_fetch_row($val)) { echo $r[0]." ".$r[1]." ".$r[2]." ".$r[3]."<br/>"; } ?>

In the above example first connection to the database is created after that the result provided by mysql_query() is passed to mysql_fetch_row() and row returned is stored in ‘r’ variable and it put in while loop to fetch all records . after that the data is displayed in tabular form.

Mysql_fetch_object

Fetch data using mysql_fetch_object( ) function

The mysql_fetch_object() function returns a row from a recordset as an object.

mysql_fetch_object() return the rows from the number of records available in the database as an object.
at a time it return only the first row as an object. if we want to retrieve all the records of the table
then we must put this function inside the while loop.

Syntax

1 2 3

mysql_fetch_object(data)

Display the records whose email is devesh@gmail.com using mysql_fetch_object()

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

<?php //connect database $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con); //select table $sql = "SELECT * from empInfo WHERE email='devesh@gmail.com'"; $result = mysql_query($sql); $row=mysql_fetch_object($result); echo $row->name; mysql_close($con); ?>

1 2 3

Output : Devesh

In the above example first connection to the database is created after that the result provided by mysql_query() is passed to mysql_fetch_object() and object returned is stored in row variable. the data of the object is displayed .

Retrieve all records from empInfo table usingmysql_fetch_object( ) function.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

<?php //connect database $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //select all values from empInfo table $data="SELECT * FROM empInfo"; $val=mysql_query($data); while($row=mysql_fetch_object($val)) { echo $row->emp_id." ".$row->name." ".$row->email." ".$row->mobile."<br/>"; } mysql_close($con); ?>

Output :

Emp_id	Name	Email	Mobile
1	devesh	devesh@gmail.com	9910099100
2	deepak	deepak@gmail.com	9210053520
3	ravi	ravi@gmail.com	9810098100

In the above example first connection to the database is created after that the result provided by mysql_query() is passed to mysql_fetch_object() and object returned is stored in row variable and it put in while loop to fetch all records . after that the data is displayed in tabular form.

MySQL Where Clause

Use Of MySQL Where Clause

The WHERE clause is used to extract only those records that fulfill a specified condition.

where clause is a part of select command . it is used to fetch data from a table . we basically use
this clause to filter out our results. by using where clause we can select particular set of records based
specified condition.

Syntax

1 2 3

SELECT column_name(s) FROM table_name WHERE column_name = value

The following example selects all data from the “empInfo” table where email=’devesh@gmail.com’

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

<?php //connect database $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //select values from empInfo table where column=value $data="SELECT * FROM empInfo where emp_id='devesh@gmail.com'"; $val=mysql_query($data); echo "<table border='1'>"; echo "<tr><th>Emp_id</th><th>Name</th><th>Email</th><th>Mobile</th></tr>"; while(list($id,$name,$eid,$mob) =  mysql_fetch_array($val)) { echo "<tr>"; echo "<td>".$id."</td>"; echo "<td>".$name."</td>"; echo "<td>".$eid."</td>"; echo "<td>".$mob."</td>"; echo "</tr>"; } echo "</table>"; ?>

Output :

Emp_id	Name	Email	Mobile
1	devesh	devesh@gmail.com	9910099100

In above example first of all we make connection with the database after that select query is used to search various records and a where clause is given according to which , wherever emp_id matches with devesh@gmail.com all the fields of that particular record are shown in tabular form.

MySQL Order By

How to use MySQL Order By

The ORDER BY is a keyword which is used to sort the results based on one or more columns.
by default it sorts the result set in ascending order . if we want to sort the results in
descending order we use DESC keyword. for ascending order we use ASC keyword.

Syntax

1 2 3

SELECT * FROM table_name ORDER BY column_name ASC|DESC

Retrieve all records from empInfo table in ascending order.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

<?php //connect database $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //select all values from empInfo table $data="SELECT * FROM empInfo ORDER BY emp_id  ASC"; $val=mysql_query($data); while($r=mysql_fetch_array($val)) { echo $r['emp_id']." ".$r['name']." ".$r['email']." ".$r['mobile']."<br/>"; } ?>

Output :

Emp_id	Name	Email	Mobile
1	devesh	devesh@gmail.com	9910099100
2	deepak	deepak@gmail.com	9210053520
3	ravi	ravi@gmail.com	9810098100

In the above example there is a empInfo table the data is retrived in ascending order based on emp_id
the fetched data is stored in the val variable and results are displayed in tabular form.

Retrieve all records from empInfo table in descending order.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

<?php //connect database $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //select all values from empInfo table $data="SELECT * FROM empInfo ORDER BY emp_id  desc"; $val=mysql_query($data); while($r=mysql_fetch_array($val)) { echo $r['emp_id']." ".$r['name']." ".$r['email']." ".$r['mobile']."<br/>"; } ?>

Output :

Emp_id	Name	Email	Mobile
3	ravi	ravi@gmail.com	9810098100
2	deepak	deepak@gmail.com	9210053520
1	devesh	devesh@gmail.com	9910099100

In the above example first we create connection with the database and data is retrived in descending order by using DESC keyword the fetched data is stored in the val variable and results are displayed in tabular form.

Select Insert Update Delete In Php MySQL Example