if function inside sql query?

Question

function get_tags_by_criteria($gender_id, $country_id, $region_id, $city_id, $day_of_birth=NULL, $tag_id=NULL, $thread_id=NULL) {

    $query = "SELECT tags.*
        FROM tags, thread_tag_map, threads
        WHERE thread_tag_map.thread_id = threads.id
        AND thread_tag_map.tag_id = tags.id

        AND threads.gender_id = $gender_id
        AND threads.country_id = $country_id
        AND threads.region_id = $region_id
        AND threads.city_id = $city_id
        AND tags.id LIKE '%$tag_id%'
        AND threads.id LIKE '%$thread_id%'";
        if(!$day_of_birth)
        {
            $query += "AND threads.min_day_of_birth <= '$day_of_birth AND threads.max_day_of_birth >= '$day_of_birth' ";
        }

        $query += "GROUP BY tags.name";

    $result = $this->do_query($query);
    return $result;
}

if no $day_of_birth is passed as an argument i want the sql to omit the 2 lines inside the if. i used:

$all_tags = $forum_model->get_tags_by_criteria(1, 1, 0, 0);

i wonder why this sql returns a error:

Couldn't execute query: 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 '0' at line 1

+1 to Galen, simply echoing the query would have enlightened you to a lot of the problems people below have pointed out. — Simon
– Simon, Commented Dec 9, 2009 at 16:57

Greg · Accepted Answer · 2009-12-09 16:54:02Z

2

You're using the addition (+=) operator when you should be using the concatenation (.=) operator.

You should be escaping your inputs too, to avoid SQL injection - see mysql_real_escape_string()

answered Dec 9, 2009 at 16:54

Greg

323k55 gold badges378 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

yedpodtrzitko · Accepted Answer · 2009-12-09 16:55:15Z

1

there's missing white space between " and AND in appended string

answered Dec 9, 2009 at 16:55

yedpodtrzitko

9,4292 gold badges48 silver badges47 bronze badges

Comments

SLaks · Accepted Answer · 2009-12-09 17:03:53Z

1

Your problem is that you left out a ' by the date of birth.

Change it to AND threads.min_day_of_birth <= '$day_of_birth' (Note closing ' and opening )
Also, as others have pointed out, you should write $query .= instead of $query += (note .)

You have a SQL Injection vulnerability; you should use parameters.
Remember Bobby Tables!

edited Dec 9, 2009 at 17:03

answered Dec 9, 2009 at 16:55

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

1 Comment

dcp Over a year ago

LOL, that Bobby Tables bit is quite funny! Thanks for the good laugh :).

Simon · Accepted Answer · 2009-12-09 16:53:41Z

0

.= is used for string concatenation in PHP, not +=

answered Dec 9, 2009 at 16:53

Simon

38k2 gold badges37 silver badges27 bronze badges

1 Comment

Galen Over a year ago

also put a space before the and in the if statement

VolkerK · Accepted Answer · 2009-12-09 17:09:06Z

You can also use placeholders in your query. If an option/parameter is set the script sets the contents of the placeholder to the appropriate sql code otherwise the placeholder is empty/null.

e.g.

function get_tags_by_criteria($gender_id, $country_id, $region_id, $city_id, $day_of_birth=NULL, $tag_id=NULL, $thread_id=NULL) {
  if ( !is_null($day_of_birth) ) {
    $day_of_birth = "AND ('$day_of_birth' BETWEEN threads.min_day_of_birth AND threads.max_day_of_birth)"
  }

  $query = "
    SELECT
      tags.*
    FROM
      tags, thread_tag_map, threads
    WHERE
      thread_tag_map.thread_id = threads.id
      AND thread_tag_map.tag_id = tags.id
      AND threads.gender_id = $gender_id
      AND threads.country_id = $country_id
      AND threads.region_id = $region_id
      AND threads.city_id = $city_id
      AND tags.id LIKE '%$tag_id%'
      AND threads.id LIKE '%$thread_id%'
      {$day_of_birth}
    GROUP BY
      tags.name
  ";

  $result = $this->do_query($query);
  return $result;
}

edit: as mentioned before: keep the possible sql injection in mind.

Collectives™ on Stack Overflow

if function inside sql query?

5 Answers 5

Comments

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related