You frequently select data in a MySQL table using PHP. Selecting data through a PHP program
using a MySQL command takes four steps:
1. Make a connection to the database.
2. Create a safe query with the command.
3. Run the query.
4. Read the results.
The following code makes the connection and creates a safe query. Rather than just running the
query, use it as the right side of an assignment. This creates a mysqli_result object that you use to
read the results via methods in the result object. This example takes each row, one at a time,
and puts it into an associative array.
<?php
define(“MYSQLUSER”, “php24sql”);
define(“MYSQLPASS”, “hJQV8RTe5t”);
define(“HOSTNAME”, “localhost”);
define(“MYSQLDB”, “test”);
// Make connection to database
$connection = @new mysqli(HOSTNAME, MYSQLUSER, MYSQLPASS, MYSQLDB);
if ($connection->connect_error) {
die(‘Connect Error: ‘ . $connection->connect_error);
} else {
echo ‘Successful connection to MySQL <br />’;
// Set up the query
$query = “SELECT * FROM ‘table1‘ “
. “ WHERE ‘code‘ = 15”
. “ ORDER BY ‘description‘ ASC “
;
// Run the query
$result_obj = ‘’;
$result_obj = $connection->query($query);
// Read the results
// loop through the result object, row by row
// reading each row into an associative array
while($result = $result_obj->fetch_array(MYSQLI_ASSOC)) {
// display the array
print_r($result);
echo ‘<br />’;
}
}
// Read the results
// loop through the results, row by row
// reading each row into an associative array
while($result = $result_obj->fetch_array(MYSQLI_ASSOC)) {
// collect the array
$item[] = $result;
}
// print array when done
echo ‘<pre>’;
print_r($item);
echo ‘</pre>’;
The example prints out the array
fetch_array(MYSQLI_ASSOC): This returns an associative array. Loop through to get all the
rows. Same as fetch_assoc().
‰ fetch_array(MYSQLI_NUM): This returns a numeric array. Loop through to get all the rows.
Same as fetch_row().
‰ fetch_array(MYSQLI_BOTH): This returns both an associative array and a numeric array
with the same data. Loop through to get all the rows. This is the default if no type is
specifi ed.
‰ fetch_all(MYSQLI_ASSOC): This returns all the rows as an associative array.
‰ fetch_all(MYSQLI_NUM): This returns all the rows as a numeric array.
‰ fetch_all(MYSQLI_BOTH): This returns all the rows both as an associative array and a
numeric array with the same data.
‰ fetch_object($class_name): This returns an object of the row. Loop through to get all
the rows. If you give it a class name, it uses that class to create the object. If there is no class
name it will create a stdClass object, which is a predefi ned class.
using a MySQL command takes four steps:
1. Make a connection to the database.
2. Create a safe query with the command.
3. Run the query.
4. Read the results.
The following code makes the connection and creates a safe query. Rather than just running the
query, use it as the right side of an assignment. This creates a mysqli_result object that you use to
read the results via methods in the result object. This example takes each row, one at a time,
and puts it into an associative array.
<?php
define(“MYSQLUSER”, “php24sql”);
define(“MYSQLPASS”, “hJQV8RTe5t”);
define(“HOSTNAME”, “localhost”);
define(“MYSQLDB”, “test”);
// Make connection to database
$connection = @new mysqli(HOSTNAME, MYSQLUSER, MYSQLPASS, MYSQLDB);
if ($connection->connect_error) {
die(‘Connect Error: ‘ . $connection->connect_error);
} else {
echo ‘Successful connection to MySQL <br />’;
// Set up the query
$query = “SELECT * FROM ‘table1‘ “
. “ WHERE ‘code‘ = 15”
. “ ORDER BY ‘description‘ ASC “
;
// Run the query
$result_obj = ‘’;
$result_obj = $connection->query($query);
// Read the results
// loop through the result object, row by row
// reading each row into an associative array
while($result = $result_obj->fetch_array(MYSQLI_ASSOC)) {
// display the array
print_r($result);
echo ‘<br />’;
}
}
// Read the results
// loop through the results, row by row
// reading each row into an associative array
while($result = $result_obj->fetch_array(MYSQLI_ASSOC)) {
// collect the array
$item[] = $result;
}
// print array when done
echo ‘<pre>’;
print_r($item);
echo ‘</pre>’;
The example prints out the array
fetch_array(MYSQLI_ASSOC): This returns an associative array. Loop through to get all the
rows. Same as fetch_assoc().
‰ fetch_array(MYSQLI_NUM): This returns a numeric array. Loop through to get all the rows.
Same as fetch_row().
‰ fetch_array(MYSQLI_BOTH): This returns both an associative array and a numeric array
with the same data. Loop through to get all the rows. This is the default if no type is
specifi ed.
‰ fetch_all(MYSQLI_ASSOC): This returns all the rows as an associative array.
‰ fetch_all(MYSQLI_NUM): This returns all the rows as a numeric array.
‰ fetch_all(MYSQLI_BOTH): This returns all the rows both as an associative array and a
numeric array with the same data.
‰ fetch_object($class_name): This returns an object of the row. Loop through to get all
the rows. If you give it a class name, it uses that class to create the object. If there is no class
name it will create a stdClass object, which is a predefi ned class.