how do i add a conditional to a query?

[Antivirus]

Trying to add a conditional to the following query, but can't quite get it to work... any ideas what i am doing wrong?

PHP Code:

$querycontents = $db->query_read("
                        SELECT erc_content.*, erc_contenttype.contenttype
                        FROM erc_content
                        LEFT JOIN erc_contenttype ON erc_content.contentid = erc_contenttype.contenttypeid
                        ");

    if ($_GET['contenttypeid'])
    {
        $querycontents .= " AND erc_content.contenttypeid = '".$_GET['contenttypeid']."'";
    }    
    $querycontents .= " ORDER BY erc_contenttype.displayorder, erc_content.displayorder"; 


[amykhar]

you're doing the query before you set the conditional. At least it looks that way at first glance.

[Antivirus]

thanks Amy, i still can't figure it out though - until i can, this should be a work around, however i know it should be able to make it happen- just neeed to work on it......

PHP Code:

    if ($_GET['contenttypeid'])
    {
        $querycontents = $db->query_read("
                        SELECT erc_content.*, erc_contenttype.contenttype
                        FROM erc_content
                        LEFT JOIN erc_contenttype ON erc_content.contentid = erc_contenttype.contenttypeid
                        AND erc_content.contenttypeid = '".$_GET['contenttypeid']."'
                        ORDER BY erc_contenttype.displayorder, erc_content.displayorder
                        ");
    }    
    else
    {
        $querycontents = $db->query_read("
                        SELECT erc_content.*, erc_contenttype.contenttype
                        FROM erc_content
                        LEFT JOIN erc_contenttype ON erc_content.contentid = erc_contenttype.contenttypeid
                        ORDER BY erc_contenttype.displayorder, erc_content.displayorder
                        ");
    } 


[Paul M]

Build the query as a variable (like $sql) first, and then run it afterwards.

(BTW, there is nothing wrong with doing it the way you have in your "workaround").

[Alan @ CIT]

Try:

PHP Code:

$query = "SELECT erc_content.*, erc_contenttype.contenttype
    FROM erc_content
    LEFT JOIN erc_contenttype ON erc_content.contentid = erc_contenttype.contenttypeid
    " . ($_GET['contenttypeid'] ? "AND erc_content.contenttypeid = '" . $_GET['contenttypeid'] . "'" : '') . "
    ORDER BY erc_contenttype.displayorder, erc_content.displayorder";

$contents = $db->query_first($query); 


Although... your query looks wrong to me - it doesn't have a WHERE clause in it

Also, you shouldn't use $_GET[] directly within the query, clean it using the input_cleaner class first

Oh, and don't forget table prefixes!

Thanks,
Alan.

[Antivirus]

lol, the AND shoudl have been a WHERE (oops)

PHP Code:

    if ($_GET['contenttypeid'])
    {
        $querycontents = $db->query_read("
                        SELECT erc_content.*, erc_contenttype.contenttype
                        FROM erc_content
                        LEFT JOIN erc_contenttype ON erc_content.contentid = erc_contenttype.contenttypeid
                        WHERE erc_content.contenttypeid = '".$_GET['contenttypeid']."'
                        ORDER BY erc_contenttype.displayorder, erc_content.displayorder
                        ");
    }    
    else
    {
        $querycontents = $db->query_read("
                        SELECT erc_content.*, erc_contenttype.contenttype
                        FROM erc_content
                        LEFT JOIN erc_contenttype ON erc_content.contentid = erc_contenttype.contenttypeid
                        ORDER BY erc_contenttype.displayorder, erc_content.displayorder
                        ");
    } 


As far as cleaning the $_GET thing, and the table prefixes, that's my next thing to get working... will post completed when i finish it. Thanks Alan!

Paul, i am going to try to do what you said, but I am a little confused, didn't i already build the query as the variable $querycontents?