Home arrow MySQL arrow Page 5 - PHP and Databases for the Lazy Sod
MYSQL

PHP and Databases for the Lazy Sod


In this article Justin explains his ezSQL utility, which reduces code and development time for database operations in PHP. This is great for the lazy sod...and everyone else.

Author Info:
By: Justin Vincent
Rating: 4 stars4 stars4 stars4 stars4 stars / 20
January 19, 2003
TABLE OF CONTENTS:
  1. · PHP and Databases for the Lazy Sod
  2. · Atomic Operations
  3. · Use PHP Functions not DB Functions!
  4. · Abstraction
  5. · Getting Even Lazier
  6. · Conclusion

print this article
SEARCH DEVARTICLES

PHP and Databases for the Lazy Sod - Getting Even Lazier
(Page 5 of 6 )

Have you ever tried to include the query results from one query inside another query? It can get quite hairy. For example, say we wanted to select a random user from the "our users" table.

First, we would have to count how many user there were in the users table (query 1) and then using this value as our random max, we would select a random user (query 2). Here's one way of doing it using traditional methods:

<?php
mysql_connect("localhost", "mysql_user", "mysql_password")
  or
die("could not connect");

mysql_select_db("mydb");

$result = mysql_query("SELECT count(*) FROM users");

$row = mysql_fetch_array($result);

$num_users = $row[0];

mysql_free_result($result);

$result = mysql_query("SELECT name FROM users LIMIT rand(0, $num_users-1) ,1");

$row = mysql_fetch_object($result);

mysql_free_result($result);

echo $row->name;

?>


Now, let's do exactly the same thing using our new class:

<?php

include_once "ez_sql.php";

$name = $db->get_var("SELECT name FROM users LIMIT ".rand(0,$db->get_var("SELECT count(*) FROM users")-1).",1");

echo $name;

?>


You've got to admit, if you're a lazy sod, this class stuff makes a lot of sense.
Another neat function that I use regularly is $db->get_col.

This is useful because it returns the contents of one column as a one-dimensional numerical array. I use it for things like product lists that are stored in the database.

If you really want to be lazy and you are positive that the query will always return results, you can even include the function directly inside your "for each" brackets (which you can do with any of the functions that return result sets):

foreach ( $db->get_col("SELECT name FROM products") as $name)
{
  echo $name;
}


Most times you will want to display something different depending on whether you have any results or not. The good news is that since we are using single functions to get our result, we can now include our query directly within an “if” statement, once again meaning that we need much less code.

Here is an example:

if ( $users = $db->get_results("SELECT name, email FROM users") )
{
  foreach($users as $user)
  {
    echo $user->name;
    echo $user->email;
  }
}
else
{
  echo "No users!";
}


Within the above “if” statement we are assigning a value to $users (the result set) and then evaluating if any value was assigned, all in the same line of code.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials