SQL Query Naming / Standards

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

SQL Query Naming / Standards

Post by SidewinderX »

Well, in my short career of programming in php, I've noticed a few `junk` variables people use when working with sql queries.

Code: Select all

$q = "QUERY HERE";
$sql = mysql_query($q);
$rows = mysql_fetch_assoc($sql)
Generally 9 times out of 10, if you see a snippet of code, it is more than likely you will see those variables [q, sql, rows] used, and as a result, thats what I use in my code. My question is, what do you use when you have more than one query? Currently I have 3 queries, and I've just added a 2 and 3 to the variables, but it just seems messy and not what you would expect in `good` code.

This is what I currently have:

Code: Select all

$sql = mysql_query("SELECT * FROM `".$db_prefix."tl_stats` WHERE `cid` = '$randcid'") or die(mysql_error());
$sql2 = mysql_query("SELECT * FROM `".$db_prefix."tl_hits` WHERE `cid` = '$randcid'") or die(mysql_error());
$sql3 = mysql_query("SELECT * FROM `".$db_prefix."tl_users` WHERE `cid` = '$randcid'") or die(mysql_error());
$rows = mysql_fetch_assoc($sql);
$rows2 = mysql_fetch_assoc($sql2);
$rows3 = mysql_fetch_assoc($sql3);
I was looking into UNION to possibly make it one query, would that be recommended? Other than that, are there any general naming standards for such cases as this?
User avatar
dude81
Forum Regular
Posts: 509
Joined: Mon Aug 29, 2005 6:26 am
Location: Pearls City

Post by dude81 »

You use joins to get all the result in one query. Do a search for SQL Join tutorials.

In Your case

Code: Select all

$stats = mysql_query("SELECT * FROM `".$db_prefix."tl_stats` as A,`".$db_prefix."tl_hits` as B ,`".$db_prefix."tl_users`  as C where A.cid=B.cid and A.cid=C.cid and  A.`cid` = '$randcid'") or die(mysql_error());
Use proper naming conventions on variables instead of q or sql user, stats_query which improves the readability of code
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

I don't much use any MySQL functions directly since I wrote my database class, but back when I did, I avoided the common "$sql," "$result," and "$rows" variables because they aren't descriptive. Variable names can be as long as we'd like them to be, and shouldn't be ambiguous.
Post Reply