May05

MySQL Select

by glenda on May 5th, 2011 at 2:35 pm
Posted In: Tutorials, MySQL, Tutorials, MySQL, MySQL Select, Tutorials



Select Data From a Database Table

The SELECT statement is used to select data from a database.
Syntax:
SELECT column_name(s)
FROM table_name

The next situation is to selects all the data stored in the “registration_form” table.
(The * character selects all the data in the table):

<?php
$con = mysql_connect(“localhost”,”root”,” “);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db(“engr_department”, $con);

$result = mysql_query(“SELECT * FROM registration_form”);

while($row = mysql_fetch_array($result))
{
echo $row[‘FirstName’] . ” ” . $row[‘LastName’] .” ” . $row[‘Age’];
echo “
“;
}

mysql_close($con);
?>

The above information stores the data returned by the mysql_query() function in the $result variable.
It selects data from the table registration_form ,where the selected data were the “firstname“, “lastname“, “age” only. We use the mysql_fetch_array() function to return the first row from the recordset as an array. Each call to mysql_fetch_array() returns the next row in the recordset. The while loop loops through all the records in the recordset. Now, how to print the value of each row is we use the PHP $row variable ($row[‘FirstName’], $row[‘LastName’] and $row[‘Age’]. So, the out put will be.

Ivan Smith 15
Draether Uy 17
Glenn Lee 19

Display the Result in an HTML Table

The following situation below selects the same data as the example above, but will display the data in an HTML table: See how.

<?php
$con = mysql_connect(“localhost”,”root”,” “);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db(“engr_department”, $con);

$result = mysql_query(“SELECT * FROM registration_form”);

echo “<table border=’1’>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Emailadd</th>
<th>Address</th>
</tr>”;

while($row = mysql_fetch_array($result))
{
echo “<tr>”;
echo “<td>” . $row[‘FirstName’] . “</td>”;
echo “<td>” . $row[‘LastName’] . “</td>”;
echo “<td>” . $row[‘Age’] . “</td>”;
echo “<td>” . $row[‘Emailadd’] . “</td>”;
echo “<td>” . $row[‘Address’] . “</td>”;
echo “</tr>”;
}
echo “</table>”;

mysql_close($con);
?>

The output code will be look like this; where the information of the 3 persons (Ivan,Draether & Glenn) will display.

Firstname Lastname Age Emailadd Address
Ivan Smith 15 ivansmith@yahoo.com Los Angeles
Draether Uy 17 draether17@yahoo.com California
Glenn Lee 19 lee19143@yahoo.com South Korea

MySQL Where

The WHERE clause

The WHERE clause is used to extract only those records that fulfill a specified criterion.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator value

The following sample we have below is to selects all rows from the “registration_form” table where the selected data is “Firstname=’Glenn’. So, this will display the information of Glenn Lee.

<?php
$con = mysql_connect(“localhost”,”root”,” “);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db(“engr_department”, $con);

$result = mysql_query(“SELECT * FROM registration_form
WHERE FirstName=’Glenn'”);

while($row = mysql_fetch_array($result))
{
echo $row[‘FirstName’] . ” ” . $row[‘LastName’].” “.$row[‘Age’].” “.$row[‘Emailadd’].” “.$row[‘Address’];
echo “
“;
}
?>

So, the result will be:
Glenn Lee 19 lee19143@yahoo.com South Korea

You may use or command this through SQL MANAGER 2007 for MySQL.
See how I do this through the SQL Editor:

Type select * from registration_form
where Firstname=’Glenn’

Thus, see the image below:

SQL Manager 2007 for MySQL - where clause through SQL Editor

Then, click the EXECUTE command to show the results:

The EXECUTE command to show the result

Finally, this is the result of the where clause command for “Firstname=’Glenn’ which display the details of Glenn Lee.

SQL Editor shows the result for Glenn Lee


└ Tags: