PHP MySQL Select
The MySQL SELECT Statement
In this chapter we will show you how to get data from a database using PHP
In PHP this is done the same way a table is created or data inserted. The MySQL syntax is sent to the server with the mysql_query() function.
The SELECT statement is used to select data from a table.
MySQL Syntax
SELECT column_name(s)
FROM table_name
|
Note: MySQL statements are not case sensitive. SELECT is the same as select.
PHP MySQL SELECT Example
This code gets the data stored in the "Person" table. We use the * character instead of column names because we want to select all from the table:
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
$result = mysql_query("SELECT * FROM person");
while($row = mysql_fetch_array($result)) {
echo $row['FirstName'];
echo "<br />";
echo $row['LastName'];
echo "<br />";
}
?>
|
Peter
Griffin
Glenn
Quagmire
|
How does it work?
- The data is returned by the mysql_query() function is stored in the $result variable
- The mysql_fetch_array function gets the next line in an array from a MySQL result
- We use the while loop to continue to get the next array until there is no next array to get
- To write the result in the way we want we use $row['FirstName'] and $row['LastName'] variable
PHP MySQL Where
The MySQL WHERE clause
In this chapter we will show you how to get specific data from a database using PHP.In PHP this is done the same way as a table is created or data inserted. The MySQL syntax is sent to the server with the mysql_query() function.
To select only some data from a table, a WHERE clause can be added to the SELECT statement.
MySQL Syntax
SELECT column FROM table
WHERE column operator value
|
Operator
|
Description
|
=
|
Equal
|
!=
|
Not equal
|
>
|
Greater than
|
<
|
Less than
|
>=
|
Greater than or equal
|
<=
|
Less than or equal
|
BETWEEN
|
Between an inclusive range
|
LIKE
|
Search for a pattern
|
PHP MySQL WHERE Example
This code will select all rows from the "Person" table, where the first name is Peter.This code gets the data stored in the "Person" table. We use the * character instead of column names because we want to check the entire table:
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
$result = mysql_query("SELECT * FROM person
WHERE FirstName='Peter'");
while($row = mysql_fetch_array($result)) {
echo $row['FirstName'];
echo "<br />";
echo $row['LastName'];
echo "<br />";
}
?>
|
The output of the code above will be:
Peter
Griffin
|
PHP MySQL Order By
The MySQL ORDER BY keyword
In this chapter we will show you how to get data in a specific order. We will also show you how to show the result in a HTML table.MySQL Syntax
SELECT column_name(s)
FROM table_name
ORDER BY column_name
|
PHP MySQL ORDER BY Example
This code gets the data stored in the "Person" table ordered by age:<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
$result = mysql_query("SELECT * FROM person ORDER BY age");
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'];
echo "<br />";
echo $row['LastName'];
echo "<br />";
echo $row['Age'];
echo "<br />";
}
?>
|
The output of the code above will be:
Glenn
Quagmire
33
Peter
Griffin
35
|
Ascending or Descending
It is possible to order by more than one column, or even select if you want to the order to be ascending (Default. 1 before 9 and "a" before "p") or descending (9 before 1 and "p" before "a").
Order by two columns
When ordering by more than one column, the second column is only used if there is several identical values in the first column:
SELECT column_name(s)
FROM table_name
ORDER BY column_name, column_name
|
Ascending or Descending Order
SELECT column_name(s)
FROM table_name
ORDER BY column_name DESC|ASC
|
Display Result in Table
This code gets the same result as the example above, but displays the data in a table:<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
$result = mysql_query("SELECT * FROM person ORDER BY age");
echo "<table>";
echo "<tr> <th>Firstname</th>
<th>Lastname</th>
<th>Age</th> </tr>";
while($row = mysql_fetch_array( $result ))
{
echo "<tr><td>";
echo $row['FirstName'];
echo "</td><td>";
echo $row['LastName'];
echo "</td><td>";
echo $row['Age'];
echo "</td></tr>";
}
echo "</table>";
?>
|
|
You may like:
.jpg)
0 comments:
Post a Comment