Pass a php variable to mysql query

Question

Hai guys,

What is wrong in this code i get an empty array. i am passing a php variable to the query it didnt work, when i give a hardcoded value the query returns result.

echo $sub1 = $examSubject[$i];
$subType = $examType[$i];
$query = $this->db->query("select dSubject_id from tbl_subject_details where dSubjectCode='$sub1'");
print_r($query->result_array());

Answer 1

google sql injection. not familiar with this->db->query, what db driver are you using? the syntax for escaping variables varies from driver to driver.

here is a PDO example:

$preqry = "INSERT INTO mytable (id,name) VALUES (23,?)";
$stmt = $pdo->prepare($preqry);

$stmt->bindparam(1,$name);
$stmt->execute();

Answer 2

failing to see what you database abstraction layer ($this->db) does, here's the adjusted code from example1 from the mysql_fetch_assoc documentation

<?php
  // replace as you see fit
  $sub1 = 'CS1';

  // replace localhost, mysql_user & mysql_password with the proper details
  $conn = mysql_connect("localhost", "mysql_user", "mysql_password");
  if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
  }

  if (!mysql_select_db("mydbname")) {
    echo "Unable to select mydbname: " . mysql_error();
    exit;
  }

  $sql = 'SELECT `dSubject_id` ';
  $sql .= 'FROM `tbl_subject_details` ';
  $sql .= "WHERE `dSubjectCode` ='$sub1';";

  $result = mysql_query($sql);

  if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
  }

  if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
  }

  while ($row = mysql_fetch_assoc($result)) {
    echo $row['dSubject_id'];
  }

  mysql_free_result($result);

?>

Let me know what the output is, I'm guessing it will say: 6

Answer 3

Is it CodeIgniter framework you're using (from the $this->db->query statement). If so, why don't you try:

$this->db->where('dSubjectCode',$sub1);
$query = $this->db->get('tbl_subject_details');

If this doesn't work, you've got an error earlier in the code and $sub1 isn't what you expect it to be.