Archive for June, 2007

PHP COTW: mysql_fetch_array, while and count

Sunday, June 24th, 2007

This weeks PHP code of the week is mysql_fetch_array, mysql while rows and count which allow you to retrieve a MySQL row from queries performed, loop through all the rows performing a certain action and how to count an array.

mysql_fetch_array can be used to retrieve the row(s) returned from the query and be placed into an associative array. Say if we wanted to retrieve user details such as name, address and suburb by using a username we could do something as shown below.

$sql = "SELECT name, address, suburb FROM userdata WHERE username = 'alex'";

if ( !($result = mysql_query($sql)) ) {
die('Could not query users table');
}

$userdata = mysql_fetch_array($result, MYSQL_ASSOC);

So we have the standard SQL query been performed and then we use mysql_fetch_array with the arguments as our result from the MySQL query and the second argument saying that we want an associative array.

This means that name, address and suburb are in the array of $userdata. An array is a variable which can contain other variables at different ‘addresses’. Arrays are in the format shown below.

$myarray[0] = “Hi”;
$myarray[1] = 234;
$myarray[5] = “Test”;

echo $myarray[0]; // Prints out Hi
echo $myarray[1]; // Prints out 234
echo $myarray[5]; // Prints out Test

The start of an array always begins at 0 and it’s important to remember this as sometimes you may forget and it’s naturally to believe 1 should be the start of the array.

So regarding our $userdata example, we used an associative array and therefore the array is not indexed by numbers but rather words. In this case we always put the word in quotation marks. In this case the words are the columns we have selected our query to return to us.

echo $userdata[‘name’]; // Prints out the user’s name
echo $userdata[‘address’]; // Prints out the user’s address
echo $userdata[‘suburb’]; // Prints out the user’s suburb

While is a function that loops until a certain condition is meet. In our case we might want to get a list of all users’ usernames and their address that have the suburb as “Sunnybank”.

$sql = "SELECT username, address FROM userdata WHERE suburb = 'Sunnybank'";

if ( !($result = mysql_query($sql)) ) {
die('Could not query users table');
}

while ($row = mysql_fetch_array($result)) {
echo $row[‘username’] . “ “ . $row[‘address’];
}

The code above says while there is another row from the result of the MySQL query then print out the username and address. If there are no more rows from the result then $row would be equal to 0 and therefore 0 is false and the while loop would finish.

We could also have something which lets us process the rows at a later time. This could be as a form of an array ($users) as shown below. We left the $users array index empty as while the loop progresses an empty index will assign the current $row to the last available position on the $users array. This means when printing out anything from the $users array we firstly would need a numeric index and then the associative word.

while ($row = mysql_fetch_array($result)) {
$users[] = $row;
}

echo $users[0][‘name’]; // Prints out the name of the user in the first row

count can be used to count the number of items in an array. So if our array $users had 3 items ($users[0], $users[1] and $users[2]) then count would return the number 3.

echo count($users); // Prints out 3

So you should now have an understanding how to use mysql_fetch_array, arrays and the count function. Next week we’ll be covering the implode/explode function.

PHP COTW: mysql_connect, mysql_select_db, mysql_query and mysql_close_db

Sunday, June 17th, 2007

This weeks PHP code of the week is mysql_connect, mysql_select_db and mysql_query which allow you to connect to a MySQL server, select a MySQL database and perform queries on the MySQL database.

mysql_connect allows you to connect to a local or remote MySQL server by the use of our username and password. Below is a common example of what mysql_connect could look like.

$dbconnect = mysql_connect ("localhost", "myuser", "mypass") or die ('I cannot connect to the database because: ' . mysql_error());

The above mysql_connect uses the MySQL server ‘localhost’ with the username ‘myuser’ and the password ‘mypass’. The next part basically says if it can’t connect to the MySQL server for what ever reason it should terminate the script (‘die’) and print out some text along with the specific MySQL error returned (mysql_error).

mysql_select_db allows you to select the database on the MySQL server. If you have more than one MySQL connection, mysql_select_db has an option to allow you to choose which MySQL connection you are referring to.

mysql_select_db ("mydatabase");

To refer to a specific connection:

mysql_select_db ("mydatabase", $dbconnect2);

mysql_query enables you to perform SQL commands on the MySQL database which also allows you to perform the query on a specific MySQL connection.

mysql_query("INSERT INTO users (username, password)
VALUES ('$form_username','$form_password')");

For specific connections do the same as in mysql_select_db:

mysql_query("INSERT INTO users (username, password)
VALUES ('$form_username','$form_password')", $dbconnect2);

If you would like to know if your mysql_query was performed or not you can do something similar to:

$sql = "INSERT INTO users (username, password)
VALUES ('$form_username','$form_password')";

if ( !($result = mysql_query($sql)) ) {
die('Could not insert into users table');
}

So above if it isn’t successful in the query it will terminate the script and print out the message configured. Generally you shouldn’t print out the mysql_error as you are giving away too much information, more than the average user needs to know.

mysql_close closes the MySQL connection which we have active. Usually this is not needed as all MySQL connections are closed at the end of the file

mysql_close($dbconnect2);

So there you have the basic functionality of connecting/disconnecting, selecting databases and querying a MySQL Server. Next week we’ll cover fetching rows, looping through rows using while, arrays and count.

PHP COTW: isset and empty

Sunday, June 10th, 2007

The third PHP code of the week is isset and empty which allow you to determine if a variable has a value or not.

isset checks to see if the variable has a value. This function is mostly used for POST and GET variables. If used with POST or GET, it allows you to not have to do the processing for the variables unless they have a value present.

empty is basically the opposite of isset, it checks to see that the variable is empty.

Below are some examples:

if ( isset ($HTTP_POST_VARS['username']) ) {
// execute code if POST var is set
}

if ( empty ($HTTP_POST_VARS['username']) ) {
// execute code if POST var is not set
}

The next PHP code of the week we’ll cover mysql_connect, mysql_select_db, mysql_query and mysql_close.

PHP COTW: htmlspecialchars, intval and str_replace

Saturday, June 2nd, 2007

The second PHP code of the week are htmlspecialchars, intval and str_replace which can all be used to process user form input before you use that input in your code.

htmlspecialchars basically converts the HTML characters like single and double quotes, less than, more than and ampersand signs to entities; which means that using this function we can prevent the users text from containing HTML characters.

echo htmlspecialchars(“<a href=’test’>Test</a>”);

This example will output in the browsers source code:

&lt;a href='test'&gt;Test&lt;/a&gt;

intval is a way we can convert anything to an integer since we are expecting an integer. This means we can covert a string containing a number to a integer. We can be sure that we will only receive a number and nothing else.

echo intval('035');

The example will output 35. If the string contains any other characters other than digits it will return 0 (unless the digits are at the start of the string).

A full example of htmlspecialchars and intval would be:

$sort_order = htmlspecialchars($HTTP_POST_VARS['order']);
$form_status = intval($HTTP_POST_VARS['status']);

str_replace is used to protect us from SQL injections into our database. If you aren’t protected from SQL injections, this means a user could perform any SQL query that you have access to.

For example, you could be updating a users email address in your database and the user types into the email address field.

'; DROP some_table; some_fake_query_here('

The original query is below.

UPDATE userdata SET email_address = '$form_email' WHERE user_id = '$user_id';

So what is going on you say? Below is what the resulting SQL statement looks like after the form has been processed.

UPDATE userdata SET email_address = ''; DROP some_table; some_fake_query_here('' WHERE user_id = '5234';

So what happens is the first query is processed we try not to break this query or all other queries would then fail. The second query to drop the table is successful and the table is therefore dropped and the last query fails.

So if we want to protect ourselfs from an SQL injection we use this code below.

str_replace("\'", "''", $variable_name);

If form fields that a user submits contain a quote they are automatically re-written as \’. What our code will do is make it database friendly. It converts \’ to ” which for the database means it will be represented as a single quote. We have therefore solved the SQL injection issue.

On the next PHP code of the week we will cover isset and empty which are other functions which should be considered when processing forms.