How to concatenate parameter in query

Question

I want to use a parameter in the following query, but I am not really sure how to.

If you can help me, I will be greatful.

$id will be 1, 2 or 3 etc. and I want to select where C.Name is Galleri1, or Galleri2 etc.

This is what I got so far, but I am not sure how to add $id.

function getGallery($id){

$data = array();

 $Q = $this->db->query('SELECT P.*, C.Name AS CatName
     FROM products AS P
     LEFT JOIN categories C
     ON C.id = P.category_id
     WHERE C.Name = "Galleri($id)"
     AND p.status = "active"
     ');

UPDATE: I am using PHP and MySQL

Answer 1

Have a look at this PHP manual.

First you create a string for gallery name. "Galleri1", "Galleri2" etc. Then bind that string as parameter to query.

Something like this:

  $sth = $this->prepare('SELECT P.*, C.Name AS CatName
               FROM products AS P
               LEFT JOIN categories C
               ON C.id = P.category_id
               WHERE C.Name = ?
               AND p.status = "active"
               ');

 $gallery_name = "Galleri$id";
 $sth->bind_param( 1, $gallery_name );

 $q = $sth->execute();

Constructing a query string from parameter like others answerers have suggested may open door for SQL injection attacks if you are not careful. For example, what happens if $id="' OR 1=1 OR C.Name !='" and you concatenate it to query string?

Using parameter binding, like I have showed above, prevents this problem.

Answer 2

I think what you're looking for here is:

function getGallery($id){

$data = array();

 $Q = $this->db->query('SELECT P.*, C.Name AS CatName
                   FROM products AS P
                   LEFT JOIN categories C
                   ON C.id = P.category_id
                   WHERE C.Name = "Gallery' . $id . '"
                   AND p.status = "active"
                   ');

Note: I fixed the spelling of Gallery in the query, as I think it was probably a typo:

Galleri

to:

Gallery

Hope this helps!

EDIT: To prevent SQL injection use instead:

function getGallery($id){

$data = array();

$id = str_replace("'", "''",  $id);

$query = 'SELECT P.*, C.Name AS CatName
                   FROM products AS P
                   LEFT JOIN categories C
                   ON C.id = P.category_id
                   WHERE C.Name = "Gallery' . $id . '"
                   AND p.status = "active"
                   '
 $Q = $this->db->query($query);

Answer 3

I assume you use PHP. Probably something like

function getGallery($id){

    $data = array();

    $Q = $this->db->query("SELECT P.*, C.Name AS CatName
                      FROM products AS P
                      LEFT JOIN categories C
                      ON C.id = P.category_id
                      WHERE C.Name = 'Galleri$id'
                      AND p.status = 'active'
                      ");

You should however be aware of SQL injections as well, so escape it first (if it isn't done in some other place):

function getGallery($id) {
    $id = $this->db->escape($db); // or similar

If your database class support prepared statements, you should use them instead.