PHP and Database

Select Statement:


• So far, we have created a new table and inserted data into that table. In this lesson we will cover the most common MySQL Query that is used to retrieve information from a database.

• The SELECT statement is used to select data from a database.

• Syntax: SELECT column_name(s)FROM table_name

• Example: The following example selects all the data stored in the "Persons" table created in previous lesson.

<?php

$con = mysql_connect("localhost","root","abc123");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

while($row = mysql_fetch_array($result))

{

echo $row['FirstName'] . " " . $row['LastName'] ] . " " . $row['Age'];

echo "<br />";

}

mysql_close($con);

?>


• Below is a step-by-step walkthrough of the code.

• '$result = mysql_query("SELECT * FROM Persons")'

• When you perform a SELECT query on the database it will return a MySQL Resource that holds everything from your MySQL table, "Persons". We want to use this Resource in our PHP code, so we need to store it in a variable, $result.

• 'SELECT * FROM Persons'

• In English, this line of code reads "Select every entry from the table Persons". The asterisk is the wild card in MySQL which just tells MySQL to include every single column for that table.

• '$row = mysql_fetch_array( $result );'

• $result is now a MySQL Resource containing data from your MySQL table, "Persons". Data is being stored in $result, but it is not yet visible to visitors of your website. When we pass $result into the mysql_fetch_array function -- mysql_fetch_array($result) -- an associative array (Firstname, Lastname,Age) is returned.

• In our MySQL table "Persons," there are three fields that we care about: Firstname, Lastname and Age. These names are the keys to extracting the data from our associative array. To get the firstname we use $row['Firstname'], to get the lastname we use $row['Lastname'] and to get the age we use $row['Age'].

• PHP is case sensitive when you reference MySQL column names, so be sure to use capitalization in your PHP code that matches the MySQL column names!

• By putting it in a while loop it will continue to fetch the next array until there is no next array to fetch. This function can be called as many times as you want, but it will return FALSE when the last associative array has already been returned.

More about mysql_fetch_array() function:

• This function gets a row from the mysql_query() function and returns an array (an associative array, a numeric array, or both ) on success, or FALSE on failure or when there are no more rows.

• Syntax:array mysql_fetch_array(data,array_type)

• data: Required. Specifies which data pointer to use. The data pointer is the result from the mysql_query() function.

• array_type: Optional. Specifies what kind of array to return.

Possible values of this is:

MYSQL_ASSOC - Associative array

MYSQL_NUM - Numeric array

MYSQL_BOTH - Default. Both associative and numeric array

• Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows. The type of returned array depends on how result_type is defined. By using MYSQL_BOTH (default), you'll get an array with both associative and number indices. Using MYSQL_ASSOC, you only get associative indices (as mysql_fetch_assoc() works), using MYSQL_NUM, you only get number indices (as mysql_fetch_row() works).

• 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.

• Example: mysql_fetch_array() with MYSQL_NUM

<?php

$con = mysql_connect("localhost","root","abc123");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

while($row = mysql_fetch_array($result, MYSQL_NUM))

{

echo $row[0] . " " . $row[1] ] . " " . $row[2];

echo "<br />";

}

mysql_close($con);

?>



• Example: mysql_fetch_array() with MYSQL_ASSOC

• <?php

$con = mysql_connect("localhost","root","abc123");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

while($row = mysql_fetch_array($result, MYSQL_ASSOC))

{

echo $row['FirstName'] . " " . $row['LastName'] ] . " " . $row['Age'];

echo "<br />";

}

mysql_close($con);

?>



•Example: mysql_fetch_array() with MYSQL_BOTH

<?php

$con = mysql_connect("localhost","root","abc123");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

while($row = mysql_fetch_array($result, MYSQL_BOTH))

{

echo $row[0] . " " . $row['LastName'] ] . " " . $row[‘Age’];

echo "<br />";

}

mysql_close($con);

?>



Display the Result in an HTML Table

• The following example selects the same data as the example above, but will display the data in an HTML 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 Persons");

echo "<table border='1'>

<tr>

<th>Firstname</th>

<th>Lastname</th>

<th>Age</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 "</tr>";

}

echo "</table>";

mysql_close($con);

?>