PHP SQL Select Query and Getting rows

The SQL query statement is uset to access the database. It can be used to read the database or write to the database. This page discusses the use of the query statement to read the database. The PHP MySQL introduction page explains some query call types and what they are used forhere.

PHP SQL Query Statement Syntax

There are two query statements which may be used, they are mysql_db_query and mysql_query. The statement mysql_db_query is depreciated and it is recommend that the mysql_query statement is used. The syntax for both statements is shown below.

resource mysql_db_query ( string database, string query [, resource link_identifier])

resource mysql_query ( string query [, resource link_identifier])

PHP SQL Query Statement Example

The below example performs a query looking for an idname called "Home" in the database table "sections".

$dbuser="username";

$dbpass="password";

$dbname="mydata";  //the name of the database

$chandle = mysql_connect("localhost", $dbuser, $dbpass) 

    or die("Connection Failure to Database");

mysql_select_db($dbname, $chandle) or die ($dbname . " Database not found. " . $dbuser);

$mainsection="sections"; //The name of the table

$query1="select * from " . $mainsection . " as t1 where t1.idname = " .  "\"Home\"";  //select the home section

$result = mysql_db_query($dbname, $query1) or die("Failed Query of " . $query1);  //do the query

$thisrow=mysql_fetch_row($result);

if ($thisrow)  //if the results of the query are not null

{

  echo "The Home section was found.<br>";

}

else

{

  echo "The Home section was not found.<br>";

}

When a query is done, the results can be placed in a specific order. The example below shows how to do this using the order by phrase. When the query is done with a "order by" sort, the results will be displayed in the order requested.

$dbuser="username";

$dbpass="password";

$dbname="mydata";  //the name of the database

$chandle = mysql_connect("localhost", $dbuser, $dbpass) 

    or die("Connection Failure to Database");

mysql_select_db($dbname, $chandle) or die ($dbname . " Database not found. " . $dbuser);

$mainsection="links"; //The name of the table where web links are stored

$query1="select * from " . $mainsection . " as t1 where ((t1.active and t1.approved)

  and (t1.cat1 = \"" . $section . "\"  or t1.cat2 = \"" . $section . "\" or t1.cat3 = \"" . $section . "\"))

  order by t1.score DESC, t1.hits DESC";  //select all approved links that belong to the current category

$result = mysql_db_query($dbname, $query1) or die("Failed Query of " . $query1);  //do the query

while($thisrow=mysql_fetch_row($result))

{

  $i=0;

  while ($i < mysql_num_fields($result))

  {

    $field_name=mysql_fetch_field($result, $i);

    echo $thisrow[$i] . " ";  //Display all the fields on one line

    $i++;

  }

echo <br>";  //put a break after each database entry

}