Advance Search

Home | Register now. Already a member? Sign In |

Technical Documents >> Database Posted:  9 January 2003 Rating:  [N/A] PHP and Working with Databases (Part III) Getting Even Lazier 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: RELATED ARTICLES Add/Drop Foreign Key Values Scripts Troubleshooting SQL Server Backup/Restore Problems PHP and Working with Databases (Part II) SEARCH MAGAZINE Select a Category... All Title Article Body Author Reference Article ID   Type in your search  Search Now    MAGAZINE LINKS News Highlights Spotlight News Technical Documents <?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. Conclusion In this article we have spoken about the benefits of atomic operations, database abstraction and the use of classes. atomic operations - We have converted the usual gunk of working with databases into a small number of key functions that do no less and no more than needed. database abstraction - We have created a new set of functions that are entirely database independent (simply by switching one include file). Even better, these new functions require much less code than traditional methods of working with databases (due to our atomic operation implementation). classes - We have wrapped all of the above up into a nice, easy to use, class/object. This is handy because it allows us to do things like easily manage two concurrent server connections (should we so wish) as well as cache results and offers us neat functions that won't get confused with other global scope functions. Final Note If you would like to see more examples of how to work with the class I've written (ezSQL) and how it can make your life easier, just go and have a look at http://php.justinvincent.com/. For a full-blown example of a busy Website built using all the above concepts and the ezSQL class, visit http://vip.channel.ac/. It is a collaborative environment where about 2700 users post News, Pictures, Links and FAQs. It also has busy Forums and Live chat. It took about two months to build from scratch (no external scripts or software were used). It gets approximately 95,000 hits to the mySQL database per day (all via ezSQL) and it runs like a charm. If you would like to help by porting ezSQL to another database platform, please feel free to do so and mail me the code (justin_at_visunet_dot_ie). I will include it in any future distributions. As stated, the only functions you need to change are $db->db, $db->select and $db->query. At the time of writing this article, ezSQL is available for mySQL and Oracle8. Reference: www.internet.com Article ID: 475

	About Us  | Alliances  | Terms of Use  |  Privacy Statement  |  Contact Us  | Site Info Mission Statement  |  © 2002 IT Misr.com.  All rights reserved.