| |
mysql_connect("localhost", "mysql_user", "mysql_password")
or
die("could not connect");
mysql_select_db("mydb");
$result = mysql_query("SELECT id, name FROM mytable");
while ($row = mysql_fetch_array($result))
{
printf ("ID: %s Name: %s", $row[0], $row["name"]);
}
mysql_free_result($result);
?>
|
| Is there really any need to make my fingers type this kind of gunk so often? Surely there must be an easier way of working with databases than this? And if so, where do I find that kind of information? |
You might say 'abstraction' and bring up names like PEAR and ADOdb, and you might be right (if the only thing important to you is being able to use the same code with different databases). The problem is you still have to type out lots of stuff whenever you want to deal with a database. For example, here is the required code using ADOdb:
|
|
include('adodb.inc.php');
$conn = &ADONewConnection('access');
$conn->PConnect('northwind');
$recordSet = &$conn->Execute('select * from products');
if (!$recordSet)
{
print $conn->ErrorMsg();
}
else
{
while (!$recordSet->EOF)
{
print $recordSet->fields[0].' '.$recordSet->fields[1].'
';
$recordSet->MoveNext();
}
}
$recordSet->Close();
$conn->Close();
?>
|
| Nothing wrong with that, you say. I agree. You can use the same code on lots of different databases, you say. Fair enough, but I want to be even lazier than that. Ah, you say. |
|
| Atomic Operations |
| After extensively scouring the net for examples of how to be a really 'lazy sod' with databases and PHP, I found absolutely nothing that could help me. So like the old adage goes, "it's hard work being lazy", I set about putting some real mental effort into resolving this problem. What I realized was that I should first break down the problem into a list of atomic operations and requirements (for those not in the know, an atomic operation is an operation that does one thing only - and does it well). |
| After all, when you stand back and look at it, what does working with databases using PHP really mean? As far as I can see, the majority of the time you only need four or five atomic operations to do 'most things' that you need to do: |
| |
1) Perform a non-result query such as Insert/Update/Commit
2) Get a single variable from the database
3) Get a single row/column from the database
4) Get a list of results from the database |
| In fact, when I think about it, all the commercial and non-commercial PHP projects that I have ever worked on have never needed any other operation. I'm not even sure you can do any other operation with a database... Before you scream blue murder, I am not talking about SQL queries here, I am talking about the functions that wrap up the SQL queries. Because no matter how complex the SQL query you write, only one set of results will ever be returned -- and as we'll see, that's a good thing. |
| |
| Query Result Sets |
What are query result sets? Good question! I'm not sure I know exactly. But I do have some idea about what 'I think' they are and how they can be useful. Let me try to explain. Imagine that we have a table called users and in that table there are three rows of data like the following:
|
| id | name | email | | 1 | amy | amy@foo.com | | 2 | tyson | tyson@bar.com | | 3 | maggie | magie@simpsons.com | | | |
When we issue the query "SELECT * FROM users" the results we get back are:
|
| id | name | email | | 1 | amy | amy@foo.com | | 2 | tyson | tyson@bar.com | | 3 | maggie | magie@simpsons.com | | | |
| If we then extracted these results into an array, we would be the proud new owners of a query result set. Here is an example: |
| |
$results[0] = Array
(
[id] => 1
[name] => "amy"
[email] => "amy@foo.com"
)
$results[1] = Array
(
[id] => 2
[name] => "tyson"
[email] => "tyson@bar.com"
)
$results[2] = Array
(
[id] => 3
[name] => "maggie"
[email] => "magie@simpsons.com"
)
|
| As you can see, the main array ($results) is a numerical array with an index of array[n], and each element of the main array is an associative array equating to one row of results. This is useful because we can do things like print out the second field of each row simply by doing this: |
foreach ($results as $result)
{
echo $result['name'];
}
|
| Another useful type of result set is as an indexed numerical array. The above results would then be expressed as: |
$results[0] = Array
(
[0] => 1
[1] => "amy"
[2] => "amy@foo.com"
)
$results[1] = Array
(
[0] => 2
[1] => "tyson"
[2] => "tyson@bar.com"
)
$results[2] = Array
(
[0] => 3
[1] => "maggie"
[2] => "magie@simpsons.com"
)
|
| The disadvantage of this type of result set is that we no longer have access to column names. The advantage is that we don't need to know the column name in order to get access to a value. For example, we can print the second field of each row simply by coding the following (no matter what the column is called): |
foreach ($results as $result)
{
echo $result[1];
}
|
| Perhaps the most useful result set of all is the one that uses the same overall format, with the exception being each row is an object instead of an array, like so: |
$results[0] = stdClass Object
(
[id] => 1
[name] => "amy"
[email] => "amy@foo.com"
)
$results[1] = stdClass Object
(
[id] => 2
[name] => "tyson"
[email] => "tyson@bar.com"
)
$results[2] = stdClass Object
(
[id] => 3
[name] => "maggie"
[email] => "magie@simpsons.com"
)
|
| To print out values we can use object syntax, which has the advantage of working inside strings without needing any special formatting. So, to print out the second field of each row we could do this: |
foreach ($results as $result)
{
echo $result->name;
}
|
| Here is an example of why it is easier to use object syntax rather than associative array syntax. |
// associative array style
echo "Print this users " . $result['name'] . " and " . $result['email'];
// object style
echo "Print this users $result->name and $result->email";
|
.gif){kind=spacer}
Technical Documents >> 
RELATED ARTICLES
