Can I use a variable in SQL? If not, how to avoid this?

Question

Can I pull out data by using variable in SQL?

For example,

In controller

$frontbottom = $this->MProducts -> getFeatureProducts('Front bottom');

In model

//This does not work.
  function getFeatureProducts($catname){
     $data = array();
     $Q = $this->db->query('SELECT P.*, C.Name AS CatName 
                   FROM omc_products AS P
                   LEFT JOIN omc_categories AS C
                   ON C.id = P.category_id
                   WHERE C.Name = $catname
                   AND p.status = "active"
                   ORDER BY RAND()
                   ');
     if ($Q->num_rows() > 0){
       foreach ($Q->result_array() as $row){
         $data[] = $row;
       }
    }
    $Q->free_result();    
    return $data;  

 }

This does not work. Is it because I am using variable?

The following works. In controller

$frontbottom = $this->MProducts -> getFrontbottom();

In model

function getFrontbottom(){
     $data = array();

     $Q = $this->db->query('SELECT P.*, C.Name AS CatName 
                   FROM omc_products AS P
                   LEFT JOIN omc_categories AS C
                   ON C.id = P.category_id
                   WHERE C.Name = "Front bottom"
                   AND p.status = "active"
                   ORDER BY RAND()
                   ');
     if ($Q->num_rows() > 0){
       foreach ($Q->result_array() as $row){
         $data[] = $row;
       }
    }
    $Q->free_result();    
    return $data;  

 }

Can I use variable in SQL?

If not, what is the best way? Do I have make same SQL again and again just changing WHERE clause?

Answer 1

For one thing, your quotes are mixed up. If you want to include a variable in a string, you need to use double quotes rather than single quotes. Also, the quotes around "active" should be single quotes. Try this and I think it should work:

$this->db->query("SELECT P.*, C.Name AS CatName 
                   FROM omc_products AS P
                   LEFT JOIN omc_categories AS C
                   ON C.id = P.category_id
                   WHERE C.Name = '$catname'
                   AND p.status = 'active'
                   ORDER BY RAND()
                   ");

Answer 2

Try this:

  //This does not work. 
  function getFeatureProducts($catname){ 
     $data = array(); 
     $Q = $this->db->query('SELECT P.*, C.Name AS CatName  
                   FROM omc_products AS P 
                   LEFT JOIN omc_categories AS C 
                   ON C.id = P.category_id 
                   WHERE C.Name = "' . $catname . '"
                   AND p.status = "active" 
                   ORDER BY RAND() 
                   '); 
     if ($Q->num_rows() > 0){ 
       foreach ($Q->result_array() as $row){ 
         $data[] = $row; 
       } 
    } 
    $Q->free_result();     
    return $data;   
 } 

The bottom line, is that you need to put the value from the variable into the SQL String. You cannot simply put the name of the variable.

Answer 3

You can use variables in SQL, after all, that's just a normal string you're passing to the query function.You are using single quotes, that is why your variable does not work. All variables inside single quotes are ignored by PHP and will not be parsed. Put the query in double quotes. You should also always properly escape variables in queries to protect yourself from SQL injection attacks. Thus, this would work and be safe:

$Q = $this->db->query("SELECT P.*, C.Name AS CatName 
               FROM omc_products AS P
               LEFT JOIN omc_categories AS C
               ON C.id = P.category_id
               WHERE C.Name = '".mysql_real_escape_string($catname)."',
               AND p.status = 'active'
               ORDER BY RAND()");

If it's still failing, try using echo mysql_error() after the query, and also echo the query to the browser so you'll see whether it's properly formatted.

Note that the database library might have it's own alternatives to mysql_real_escape_string and mysql_error, but this was just an example anyways.

Answer 4

you need to expand the variable before yo make the sql query.

sql_query1 = 'SELECT P.*, C.Name AS CatName FROM omc_products AS P LEFT JOIN omc_categories AS C ON C.id = P.category_id WHERE C.Name = "'

sql_query2 = '" AND p.status = "active" ORDER BY RAND() '

finally ,

sqlquery = sqlquery1+ $catname + sqlquery2

• on your actual program don't use sqlquery1,2 etc .. this is a very cumbersome way to do this.. it is only to make it clear to understand