SQL select statements are the most widely used SQL statements in PHP. Rightly so, because most of the times you will be displaying records from the database. SQL insert statements are the second most used SQL statements in PHP. Delete is the least used.
Coming to the point, the SQL Select statement in PHP is the most complex, if any, relative to other PHP SQL statements. Here also, we will follow the steps mentioned in the Common Steps to execute any SQL statements in PHP.
Prepare the SQL statement
So we define a string variable “$sql_statement” which contains the actual SQL statement to select records from a mysql table user_info which has two fields userid and password. This is explained as below.
$sql_statement = “Select * from user_info”;
The above SQL statement when executed will return a resultset of all the records available in the table “user_info”.
If we want to retrieve any specific column we can get it by the following PHP statement.
$sql_statement = “Select userid from user_info”;
Here the above PHP statement will return only a portion of table. This will return all the values in userid field. If some null or blank value has been inserted into the table. The resultset will contain those records also.
A PHP statement creating dynamic sql statement will be something like this.
$sql_statement = “Select userid from user_info where userid=’”.$userid .”’ and password=’”.$password.”’”;
The above statement when executed would return a resultset of only those records where the record selection criteria are satisfied.
Notice the use of dot operator and quotes in the above statement.
Get the resultset
After creating the statement, we execute this query using the PHP inbuilt function mysql_query () as explained below.
$resultset = mysql_query ($sql_statement, $conn) or die(“Could not select records”);
As explained earlier, $conn is the database connection string retrieved using the mysql_connect () function.
Unlike other simple PHP statements for SQL updates, inserts etc. here the mysql_query () function returns a resultset. This resultset is stored in a variable.
Get the rows
Now we will fetch the actual data in the form of one dimensional array using the inbuilt php function mysql_fetch_array() as explained below.
$result_rows = mysql_fetch_array($resultset,$conn)
The mysql_fetch_array() function takes two arguments, the resultset and the connection string. It returns the record retrieved from the sql statement in the form of array.
Get the actual data.. ha! ha! ha!
Now all we have to do is to retrieve the data from this array. To retrieve the next record in the form of array we will have to execute this stament again.
When there are no more records or the resultset is empty, the above statement returns false. We use this information to write a loop using PHP while loop as explained below.
while($result_rows = mysql_fetch_array($resultset,$conn))
{
echo $resultrows[0];
echo $resultrows[1];
}
Here I would like to add that the mysql_fetch_array() function returns an associative array whenever the records are selected using column names instead of values. So you can also retrieve the records by the column names also as explained below.
while($result_rows = mysql_fetch_array($resultset,$conn))
{
echo $resultrows[“userid”];
echo $resultrows[“password”];
}
The complete code
Now the complete code to select userid and password from a table userinfo would be as below.
$sql_statement = “Select userid from user_info where userid=’”.$userid .”’ and password=’”.$password.”’”;
$resultset = mysql_query ($sql_statement, $conn) or die(“Could not select records”);
while($result_rows = mysql_fetch_array($resultset,$conn))
{
echo $resultrows[“userid”];
echo $resultrows[“password”];
}
After the execution of this statement, all the records are echoed one by one. In practical situation we generally take the output in the form tables.
Example of MySQL select
Since the tutorial took all the allocated space, the example to select a record from MySQL table has been given on the next page!
