Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

PHP

Posted Oct 22, 2002

PHP and Working with Databases (for the Lazy Sod) - Page 5

By DatabaseJournal.com Staff


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:
<?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.


Page 6: Conclusion




PHP Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.