MySQLi Tutorial: Selecting Multiple Entries

MySQLi Tutorial: Selecting Multiple Entries

Hey again, I’ll be showing you how to handle multiple rows from a query, using MySQLi, for example:

SELECT * FROM `people`;

The table I’ll be using is the same table from this tutorial. Since you probably already have that table ready, let’s get started!

<?php
/* make our connection, remember the order:
host, username, password, database */
$sql = new mysqli('localhost','username','password','database');
 
/* let's write our query, we'll select everyone, showing the oldest first. */
$query = "SELECT * FROM `people` ORDER BY `age` DESC;";
 
/* set our result information */
$result = $sql->query($query);
 
/* now that we have our result information, we can cycle through the
database rows much like we used to in the procedural days */
echo 'Display using objects.<br />';
while ( $row = $result->fetch_object() ) {
    echo "{$row->name} is {$row->age} years old.<br />";
}
 
/* if you prefer to use associative arrays, you can do the following */
echo 'Display using an associative array.<br />';
while ( $row = $result->fetch_assoc() ) {
    echo "{$row['name']} is {$row['age']} years old.<br />";
}
 
/* ok we're done here, close our connection */
$sql->close();
?>

Running the above code should yield the following results:

Display using objects.
Mike is 26 years old.
Angela is 21 years old.
Joe is 19 years old.
Stephanie is 18 years old.
Display using an associative array.
Mike is 26 years old.
Angela is 21 years old.
Joe is 19 years old.
Stephanie is 18 years old.

You may wish to check that there are actually results before trying to display them, this is often a good idea. You can do this by check the number of rows returned by your query, you can do this with the following:

<?php
/* so again, make your connection */
$sql = new mysqli('localhost','username','password','database');
 
/* write our query */
$query = "SELECT * FROM `people` ORDER BY `age` DESC;";
 
/* set our result information */
$result = $sql->query($query);
 
if ( $result->num_rows > 0 ) {
    echo "We found {$result->num_rows} people:<br />";
    while ( $row = $result->fetch_object() ) {
        echo "{$row->name} is {$row->age} years old.<br />";
    }
} else {
    echo 'There are no results to display.';
}
 
/* and close up */
$sql->close();
?>

In our case, you should get:

We found 4 people:
Mike is 26 years old.
Angela is 21 years old.
Joe is 19 years old.
Stephanie is 18 years old.

And there you have it, a quick and painless way to display information from a query using the MySQLi class. Hope you found it useful.

By Mike on March 27, 2011 | MySQLi, Tutorials
Tags: , ,

PHP Tutorial: Arrays mapping your mind in PHP

According to the PHP manual:

An array in PHP is actually an ordered map. A map is a type that associates values to keys. This type is optimized for several different uses; it can be treated as an array, list (vector), hash table (an implementation of a map), dictionary, collection, stack, queue, and probably more. As array values can be other arrays, trees and multidimensional arrays are also possible.

Uh…. ok, so what does that all mean? Well I’ll start with the part where it said “associates values to keys.” As you should already know all variables in PHP must have a value, whether it is NULL, empty, an array, an integer, whatever. So in the case of an array inside the array there are keys, these keys are like identifiers for the values contained in the array. Here is an example:

<?php
$variable = array('key'=>'value');
?>

So to explain the above code: the variable named $variable is an array, it has one key=>value pair, the key is key and the value is value. So, to use this new value we would simply make a call like this:

echo $variable['key'];

The names of keys can be integers, or strings. If you do not set a key, PHP will set one for you. For example:

<?php
/* PHP will automatically set the key for the value Apples to 0 */
$fruit = array('Apples');
 
/* another way to assign values into an array is like this: */
$veggies[] = 'Lettuce';
$veggies[1] = 'Broccoli';
 
/* so $veggies contains two key=>value pairs, one is 0=>'Lettuce', 
                                    and the other is 1=>'Broccoli' 
To use these values, we can simply do an echo, like this: */
echo "I like to eat {$veggies[0]} and {$veggies[1]} with every meal.";
?>

Now, keys don’t always have to be one or the other, you and mix and match all you’d like:

<?php
$breads[1] = 'Whole Wheat';
$breads['one'] = 'Light Rye';
?>

So there you have it, a simple and straight-forward look at arrays in PHP. I hope you found it useful!

By Mike on March 26, 2011 | PHP, Tutorials
Tags: , , , ,

PHP Tutorial: The function of functions

Functions in PHP, are wonderful tools. They allow you to go with the DRY (don’t repeat yourself) style of writing code.
Functions are good for when you want to run some code often, perhaps a small block. We’ll make a function called “tax()”, you create a function like this:

<?php
/* our function will have two parameters:
$amount which will be put in by you when you call the function, and
$rate which, by default, will be 0.05, but you can change it anytime */
function tax( $amount, $rate = 0.05 ) {
/* now we need to find out how much the tax will be
 $amount is set when the function is called */
$tax = $rate*$amount;
/* now return the value, for use in your script */
return $tax;
} 
/* now you simply call the function: 
the following code will echo:  5 */
echo tax(100);
 
/* adding a new tax rate 
the following code will echo 8 */
echo tax(100,0.08);
?>

Variables outside a function cannot be used inside a function except for three cases: (to my knowledge)

  • The variable is a super global, such as $_POST, $_GET, $_SESSION, $_ENV etc…
  • The variable is not a variable, but a constant, defined earlier in the execution.
  • The variable is defined in the function using the global ; option.

Using a variable inside a function by definition of global ;

<?php
$fruit = 'Apple';
function fruit() {
    global $fruit;
    echo $fruit;
} 
fruit();
/* However */
function rotten() {
    echo "Rotten $fruit";
} 
/* This will only produce the word Rotten, not Rotten Apple. 
in fact you will get an error! something like this: 
Notice: Undefined variable: fruit in yourfilename.php on line 16 */
rotten();
?>

So that’s the basics of functions, I hope that helps!

By Mike on March 25, 2011 | PHP, Tutorials
Tags: , , ,