Monday, June 4, 2012

Retrieving table data in the result set of MySQL using PHP

Data retrieval from a MySQL table is an essential thing for every developer. But the question here is how to retrieve the data and from where we actually need to retrieve the data and where the data will be stored.


Let us see in how many ways we can retrieve the data from a MySQL table. As per my knowledge in PHP, we can retrieve the data from a MySQL database table is with 4 ways. Let us see what are those 4 ways.

Method 1: 
We can retrieve the data with the help of a php pre-defined function called mysql_fetch_row(). Lets see what's the use of this function.

mysql_fetch_row() : The mysql_fetch_row() function returns a row from a resultset as a numeric array. This function gets a row from the mysql_query() function and returns an array on success, or FALSE on failure or when there are no more rows. mysql_fetch_row() fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0.

Demo : Retrieving or fetching one row with mysql_fetch_row()
Create a table called 'websites' in a database called 'test'. I have used test database and websites table for my demo. So for better understanding of this task you too adopt these things.

CREATE TABLE IF NOT EXISTS `websites` (
  `id` smallint(4) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `visits` decimal(10,0) NOT NULL DEFAULT '0',
  `about` text NOT NULL,
  `type` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `name` (`name`,`id`)
) ENGINE=MyISAM;

PHP Code: 


<?php
$con = mysql_connect("localhost", "root", "");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$db_selected = mysql_select_db("test",$con);
$sql = "SELECT * FROM websites";
$result = mysql_query($sql,$con);
$row=print_r(mysql_fetch_row($result));
echo $row[0];
mysql_close($con);
?>

Output:
Array ( [0] => 1 [1] => www.youtube.com [2] => 1246 [3] => its a videos website... [4] => videos )


Method 2:
We can retrieve the table data with the help of a php pre-defined function called mysql_fetch_array(). Lets see what's the use of this function.

mysql_fetch_array(): The mysql_fetch_array() function fetches a result row as an associative array or a numeric array or using both the ways.
This function gets a row from the mysql_query() function and returns an array on success, or FALSE on failure or when there are no more rows. 
Note : What If two or more columns of the result have the same field names In mysql_fetch_array(), what action is taken?
Answer : If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you must use the numeric index of the column or make an alias for the column. For aliased columns, you cannot access the contents with the original column name.

Demo : Retrieving or fetching one row with mysql_fetch_array()
PHP Code :
<?php


$con = mysql_connect("localhost", "root", "");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$db_selected = mysql_select_db("test",$con);
$sql = "SELECT * FROM websites";
$result = mysql_query($sql,$con);
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    printf("id: %s  name: %s visits: %s", $row[0], $row[1], $row[2]); 
}
/* 
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    printf("id: %s  name: %s visits: %s", $row['id'], $row['name'], $row['visits']); 
}
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
    printf("id: %s  name: %s visits: %s", $row['id'], $row[1], $row['visits']); 
}*/
mysql_free_result($result);


?>
Output:
Array ( [0] => www.youtube.com [name] => www.youtube.com [1] => videos [type] => videos [2] => 1246 [visits] => 1246 )


Method 3 :

We can retrieve the table data with the help of a php pre-defined function called mysql_fetch_object(). Lets see what's the use of this function.
mysql_fetch_object(): The mysql_fetch_object() function fetches a result row as an object.
This function gets a row from the mysql_query() function and returns an object on success, or FALSE on failure or when there are no more rows.

Note:
mysql_fetch_object() is similar to mysql_fetch_array(), with one difference - an object is returned, instead of an array. Indirectly, that means that you can only access the data by the field names, and not by their offsets (numbers are illegal property names).

Demo: Retrieving or fetching one row with mysql_fetch_object()
PHP Code: 
<?php
$con = mysql_connect("localhost", "root", "");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$db_selected = mysql_select_db("test",$con);
$sql = "SELECT * FROM websites";
$result = mysql_query($sql,$con);
while ($row = mysql_fetch_object($result)) {
    echo $row->name;
    echo $row->type;
}
mysql_close($con);
?>
Output:
www.youtube.comvideoswww.w3schools.comelearningwww.facebook.comsocialnetworkingwww.orkut.comsocialnetworkingwww.playtoongames.comgameswww.gmail.comemailwww.linkedin.comsocialnetworking

Method 4 :

We can retrieve the table data with the help of a php pre-defined function called mysql_fetch_assoc(). Lets see what's the use of this function.
mysql_fetch_assoc(): The mysql_fetch_assoc() function fetches a result row as an associative array.
This function gets a row from the mysql_query() function and returns an array on success, or FALSE on failure or when there are no more rows.

Demo : Retrieving or fetching one row with mysql_fetch_assoc()
PHP Code: 
<?php
$con = mysql_connect("localhost", "root", "");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$db_selected = mysql_select_db("test",$con);
$sql = "SELECT * FROM websites";
$result = mysql_query($sql,$con);


while ($row = mysql_fetch_assoc($result)) {
    echo $row["id"];
    echo $row["name"];
    echo $row["visits"];
}
mysql_free_result($result);
?>

Huh..that's all for now about data retrieval from a mysql table using different ways...for more updates keep visiting this blog..till then bye..

Did You Enjoy this Article ?

If yes, Then enter your email below to get

more such great articles in your inbox

For FREE !

No comments:

Post a Comment

Thank you for reading and commenting...