Page 1 of 1

IF Statement Inside a Query - Stupid Noob Question

Posted: Mon Feb 21, 2011 2:21 pm
by DedMousie
In Coldfusion, I can put an "if" statement INSIDE a query. I'm not finding anything in Google showing me a way to do this in PHP though.

Below, I'm feeding URL variables into a query.
Basically, if a specific date isn't defined, then I want all the events for that month/year.
If a specific date IS defined, just the events for that one date.

So, sorta like this:

$getEVENTS = mysql_query( "
SELECT
cal_occurrences.startdate,
cal_occurrences.eid,
cal_events.subject,
cal_events.eventCOLOR
FROM
cal_events
Inner Join cal_occurrences ON cal_occurrences.eid = cal_events.eid
WHERE month(startdate) = $urlMONTH
AND year(startdate) = $urlYEAR
if(isset($_GET["uDAY"])) {
AND day(startdate) = $urlDAY }
" )
or die(mysql_error());


What do I have wrong?

Re: IF Statement Inside a Query - Stupid Noob Question

Posted: Mon Feb 21, 2011 3:09 pm
by crazycoders
God you have to love ColdFusion... seriously, i hate it, but you gotta come from somewhere...

Here is what you don't understand: The world of coding php is one with types, if you want to insert PHP code inside of SQL code, forget that, i know coldfusion does cool things by embedding all sorts of functionality inside each other, but only coldfusion does that... Some kind of messaging feature i guess... Welcome to PHP and MySQL :)

Your solution is to see your problem as units of work!

You are trying to send a query to mysql, a query is a string, you need to build your string before sending it (or you can do it inline), you can't place PHP code inside of a text string, it won't be executed, instead it will be understood as simple text that you want to send to MySQL.

The way to do it is varied but here is how i'd do it: (It's called inline query building)

Code: Select all

$getEVENTS = mysql_query( "
SELECT
cal_occurrences.startdate,
cal_occurrences.eid,
cal_events.subject,
cal_events.eventCOLOR
FROM
cal_events
Inner Join cal_occurrences ON cal_occurrences.eid = cal_events.eid
WHERE month(startdate) = ".$urlMONTH." 
AND year(startdate) = ".$urlYEAR." 
".(isset($_GET["uDAY"]) ? ' AND day(startdate) = '.$urlDAY : '')."
")
or die(mysql_error());
Note that your way of doing can be dangerous, i suspect that URLMONTH and URLYEAR and URLDAY come from the URL of a page or the "$_GET" array. If you don't type check the content, you could run into serious hacking issues and compromise your server. Looking into "mysql_real_escape_string" for more information or look on the web for mysql best security practices.

Note: magic_quotes_gpc is not a good way to learn, neither is magic_quotes_mysql...

Re: IF Statement Inside a Query - Stupid Noob Question

Posted: Mon Feb 21, 2011 3:37 pm
by DedMousie
crazycoders wrote: you need to build your string before sending it
Wow - big extra chunk of code having to basically write out the entire query for each part of an "if" or "switch" statement. Was tearing my hair out trying to get it to work the other way! Yup, gotta love Coldfusion!
crazycoders wrote:If you don't type check the content, you could run into serious hacking issues
Have noticed that PHP doesn't have anything like "cfqueryparam" either. Darned!

SELECT id, foo
FROM mytable
WHERE foo = <cfqueryparam value="#trim(form.foo)#" cfsqltype="CF_SQL_VARCHAR" maxlength="15" />

How easy is that?

I'm definitely having to learn to look at some things in a different way in order to get code to work the way I want. Trying not to tear too much hair out! However, I like logic puzzles, so it's been fun.

Anyway, no worries! I'm checking datatypes further up the page before it ever gets to the query.

Thanks very much for your assistance.