IF statement in Query

Hi

I have a query / recordset that?would be looking at 12000 rows in a database and 10 different variables and potential filters chosen by end users.

Should I put 10 wild card / url Where statements in my recordset query or should I put IF statements in my query.

ie.

If URL colname then '',and BetType= ''xzz'''' .

Is that the most efficient way to run my queries.

I will be running about 10 recordsets on my page all looking at these url variables so it will be a busy page.

If that is the answer can someone please tell me how to insert the if statement - i've tried allsorts but it wont work.

$colname_Recordset4 = ''%'';
if (isset($_GET['colname'])) {
?$colname_Recordset4 = $_GET['colname'];
}
mysql_select_db($database_racing_analysis, $racing_analysis);
$query_Recordset4 = sprintf(''SELECT BetType, sum(if(season='2006-2007', Bet, 0)) AS '2006-2007',?sum(if(season='2007-2008', Bet, 0)) AS '2007-2008',?sum(if(season='2008-2009', Bet, 0)) AS '2008-2009' FROM dataextract WHERE BetType Like %s and TrackID = 1 and Distance = 1000 and Class = 1 GROUP BY BetType'', GetSQLValueString($colname_Recordset4, ''text''));
$Recordset4 = mysql_query($query_Recordset4, $racing_analysis) or die(mysql_error());
$row_Recordset4 = mysql_fetch_assoc($Recordset4);
$totalRows_Recordset4 = mysql_num_rows($Recordset4);

hope someone can help.

Simon

IF statement in Query

I'm trying to test it using something like this:

?if (isset($_GET['colname'])) { and 1=1;}

I put this in my Where statement but still no good

IF statement in Query

Im still cant figure out what the return values u want from this code sum(if(season='2006-2007', Bet, 0)) AS '2006-2007',?sum(if(season='2007-2008', Bet, 0)) AS '2007-2008',?sum(if(season='2008-2009', Bet, 0)) AS '2008-2009'

That part of the query cross tabs my data into three columns - not intended to confuse the issue. I'd have the same problem with a basic query.

So Here is a very basic version:

I want an IF statement to go around: ''WHERE Distance = 1000'' and ''AND Class = 1''

That I believe will reduce the effort on the MYSQL Server as it wouldn't be running a bunch of wild card queries and would only run if there is a URL paramter delivered.??

I just don't get how to put the IF statements in PHP.

thanks

$maxRows_Recordset3 = 5;
$pageNum_Recordset3 = 0;
if (isset($_GET['pageNum_Recordset3'])) {
?$pageNum_Recordset3 = $_GET['pageNum_Recordset3'];
}
$startRow_Recordset3 = $pageNum_Recordset3 * $maxRows_Recordset3;

mysql_select_db($database_racing_analysis, $racing_analysis);
$query_Recordset3 = ''SELECT * FROM dataextract WHERE Distance = 1000 AND Class = 1'';
$query_limit_Recordset3 = sprintf(''%s LIMIT %d, %d'', $query_Recordset3, $startRow_Recordset3, $maxRows_Recordset3);
$Recordset3 = mysql_query($query_limit_Recordset3, $racing_analysis) or die(mysql_error());
$row_Recordset3 = mysql_fetch_assoc($Recordset3);

if (isset($_GET['totalRows_Recordset3'])) {
?$totalRows_Recordset3 = $_GET['totalRows_Recordset3'];
} else {
?$all_Recordset3 = mysql_query($query_Recordset3);
?$totalRows_Recordset3 = mysql_num_rows($all_Recordset3);
}
$totalPages_Recordset3 = ceil($totalRows_Recordset3/$maxRows_Recordset3)-1;

Hi

I have got this to work ...almost

mysql_select_db($database_racing_analysis, $racing_analysis);
$query_Recordset5 = sprintf(''SELECT dataextract.BetType, dataextract.Bet FROM dataextract'');
if (isset($_GET['colname'])) {$query_Recordset5 = sprintf(''WHERE BetType Like %s'', GetSQLValueString($colname_Recordset5, ''text''));
}
$query_limit_Recordset5 = sprintf(''%s LIMIT %d, %d'', $query_Recordset5, $startRow_Recordset5, $maxRows_Recordset5);
$Recordset5 = mysql_query($query_limit_Recordset5, $racing_analysis) or die(mysql_error());
$row_Recordset5 = mysql_fetch_assoc($Recordset5);

OK the IF statement works if a url parameter is not chosen bythe user. So the IF statement is in action. However when it is chosen and the IF statement includes the where statement I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE BetType Like 'double' LIMIT 0, 10' at line 1

Does that mean anything to anyone??

Change this one

if (isset($_GET['colname'])) {$query_Recordset5 = sprintf(''WHERE BetType Like %s'', GetSQLValueString($colname_Recordset5, ''text''));
}

to this

if (isset($_GET['colname'])) {

$query_Recordset5 .= sprintf('' WHERE BetType Like %s'', GetSQLValueString($colname_Recordset5, ''text''));
}

You must add (.) before equal(=) sign to combine both code and put space before WHERE. So it will be

FROM table WHERE

instead of

FROM tableWHERE %26lt;-mysql syntax error

many thanks

simon