Eaten By Moths

Reason to Give is a charity I volunteer for in Humbolt Park here in Chicago. As they put they “connect people who give a damn with people who need a hand”. There are a few folks on their website you can help out; there are video interviews with them so you can learn about their needs from their own perspective. If you’ve got a sec, check it out and see if you can give a hand to one of these families.

1. Establishing a connection to a MySQL Database Using PHP:

You will want to create a variable to hold a reference to your DSN (Data Source Name). A DSN is a specific data structure used to describe a connection to a database. The structure of a DSN varies from language to language.

In PHP a basic DSN take the following form:

("$database_name","$database_user","$database_password")

This array is passed as arguments to the mysql_connect function as follows:

$dsn = mysql_connect("$server_name","$database_user","$database_password");

Remember a PHP variable always takes the form of $variable and can hold any kind of information: a string, a number, a function return, a boolean value, etc.

2. Selecting the Database you want to work with

Once you have established your MySQL connection, you will want to select the database with which you will be working. This is done through the mysql_select_db function:

mysql_select_db("my_database",$dsn);

The $dsn variable will now point to the database “my_database” on the server “localhost”. All table level queries will be executed against this database unless we change to a different database using the mysql_select_db function.

Notice how the mysql_select_db() statement is formated. This is the standard format for funtion calls in PHP:

function_name($argument1, $argument2,   $argument3, ...$argument_n);

Now we’re ready to begin executing queries against the tables in our selected database.

3. Basic queries:

MySQL queries in PHP are executed using the mysql_query function. The results of the mysql_query function must them be parsed by any of a number of available methods into user-intelligible output. For efficiency’s sake we assign the results of a MySQL command to a variable.

Here’s an example of a basic MySQL query executed from PHP:

$sql = "select Name, SurfaceArea, Population from Country limit 5";
$result = mysql_query($sql);

We can use the $result variable with a number of other commands to examine the data returned by our query.

mysql_num_rows tells us the number of rows of data (individual entries) returned:

$numRows = mysql_num_rows($result);
var_dump($numRows);
// returns int(5)

mysql_fetch_row retrieves the first row of data from our results (this is a more useful command in queries that are expected to only return one row). The row is returned as an array of the data in each of the fields in that row:

$rowValues = mysql_fetch_row($result);
var_dump($rowValues);
// returns
// array(3)
// { [0]=>  string(11) "Afghanistan"
// [1]=>  string(9) "652090.00"
// [2]=>  string(8) "22720000" }

mysql_fetch_assoc is used to retrieve an associative array of data from the table. The array takes the format of an array of rows represented by individual arrays of field values:

while ($assocValues = mysql_fetch_assoc($result))     {
var_dump($assocValues);
echo "<br />";
}
// returns
// array(3)
// { ["Name"]=>  string(11) "Afghanistan"
// ["SurfaceArea"]=>  string(9) "652090.00"
// ["Population"]=>  string(8) "22720000" }
// array(3)
// { ["Name"]=> string(11) "Netherlands"
// ["SurfaceArea"]=> string(8) "41526.00"
// ["Population"]=> string(8) "15864000" }
// array(3)
// { ["Name"]=> string(20) "Netherlands Antilles"
// ["SurfaceArea"]=> string(6) "800.00"
// ["Population"]=> string(6) "217000" }
// array(3)
// { ["Name"]=> string(7) "Albania"
// ["SurfaceArea"]=> string(8) "28748.00"
// ["Population"]=> string(7) "3401200" }
// array(3)
// { ["Name"]=> string(7) "Algeria"
// ["SurfaceArea"]=> string(10) "2381741.00"
// ["Population"]=> string(8) "31471000" }

MySQL queries executed from PHP use a data pointer object to describe the read/write position within the current results. Because of this, whenever we work with array data we will need to move the data pointer through the results by using a loop (as above) or by explicitly pointing to a particular record.

Moving the data pointer within the result dataset is accomplished using the mysql_data_seek function:

mysql_data_seek($result, 3);
$assocValues = mysql_fetch_assoc($result);
var_dump($assocValues);
// returns
// array(3)
// { ["Name"]=> string(7) "Albania"
// ["SurfaceArea"]=> string(8) "28748.00"
// ["Population"]=> string(7) "3401200" }

mysql_fetch_array allows you to retrieve either a numerical or associative array from your results set:

while($arrayValues = mysql_fetch_array($result, MYSQL_NUM)) {
var_dump($arrayValues);
}
// returns
// array(3)
// { [0]=> string(11) "Afghanistan"
// [1]=> string(9) "652090.00"
// [2]=> string(8) "22720000" }
// array(3)
// { [0]=> string(11) "Netherlands"
// [1]=> string(8) "41526.00"
// [2]=> string(8) "15864000" }
// array(3)
// { [0]=> string(20) "Netherlands Antilles"
// [1]=> string(6) "800.00"
// [2]=> string(6) "217000" }
// array(3)
// { [0]=> string(7) "Albania"
// [1]=> string(8) "28748.00"
// [2]=> string(7) "3401200" }
// array(3)
// { [0]=> string(7) "Algeria"
// [1]=> string(10) "2381741.00"
// [2]=> string(8) "31471000"}

while($arrayValues = mysql_fetch_array($result, MYSQL_ASSOC)) {
var_dump($arrayValues);
}
// returns same as mysql_fetch_assoc

while($arrayValues = mysql_fetch_array($result)) {
var_dump($arrayValues);
}
// returns the both a numeric and an     associative array describing the
// result set