[Resolved] Best way to reuse queries

bpat1434

I'm kind of at a loss here. I am trying to set up a website so that all the queries are consolidated in a single document. What i want to be able to do is to just call a variable as the query string.

So I tried:
define("Query_Name", "mySQL_Query", true);

When I go to check my page, I get a lot of errors because variables are missing that are used to help finish the queries. I.e.:
I have a query that will select a row based on a username input by a form. The form only appears on one page, and I only need to use the query when someone wants to login.

So, what is the best way to set up queries that can be re-used over and over, with different variable values? Here's a snippet of what I had that doesn't work:

define("Request_UserPass", "SELECT * FROM `users` WHERE Username = '$username' LIMIT 1", true);

// Front-Page queries
define("Request_Main_Stories", "SELECT * FROM `stories` ORDER BY id DESC LIMIT 5", true);
define("Request_Events_Sidebar", "SELECT * FROM `events` ORDER BY eDate DESC LIMIT 3", true);
define("Request_News_Sidebar", "SELECT * FROM `news` ORDER BY id DESC LIMIT 3", true);
define("Request_Calls_Sidebar", "SELECT * FROM `majorcalls` ORDER BY Date DESC LIMIT 3", true);

// Reading Queries
define("Read_Story", "SELECT * FROM `stories` WHERE id = '".$read_id."' LIMIT 1", true);
define("Read_News", "SELECT * FROM `news` WHERE id = '".$read_id."' LIMIT 1", true);

I only need some to run at certain points. What would be the best way to set this up? Is it possible to actually do what I want, or do I have to write out each query individually when I want to run it?

~Brett

devinemke

as with any code you want to re-use, create a fucntion:

function run_query($table, $where, $order_by, $limit)
{
	$sql = "SELECT * FROM $table";
	if ($where) {$sql .= " WHERE $where";}
	if ($order_by) {$sql .= " ORDER BY $order_by";}
	if ($limit) {$sql .= " LIMIT $limit";}

$result = mysql_query($sql) or exit(mysql_error());
return $result;
}

$result = run_query('users', "Username = '$username'", '', 1);
$result = run_query('stories', '', 'id DESC', 5);
$result = run_query('events', '', 'eDate DESC', 3);
$result = run_query('news', '', 'id DESC', 3);

bpat1434

Thanks Devin.

Always a life-saver.

~Brett

AstroTeg

Look into [man]sprintf[/man]. I think you can run with your current idea, but instead of dropping in the variables when you define your query, you drop in something like %s or %d or whatever variable type that you expect to have at that moment of time. Then you do sprintf([your query string],

); With sprintf, you can even reuse a sinle variable from the list. The only problem you may have is order IS important with sprintf. You can't be mixing up the list of variables otherwise it'll botch up your query and have data going to the wrong field. Because of this, you'll find yourself looking back to the original query pretty often (unless you have a great memory). A workaround to this might be to setup a function to accept the variables and line them up in the right order. Just ideas...