Binding SQL arguments. A bit complex situation.

[eluser]behnampmdg3[/eluser]
Hey igniters;

Hopefully I find answer to this quesiton here. I wrote some code to dynamically bind data as below:

Code:

public function search_results($data, $record_start=0,$number_of_records=4,$next_prev_sign='>', $order=' ASC ')
{
  $conditions = $values = array();
  foreach($data as $val=>$row)
   {
    $conditions[] = $val." = ?";
   }
  foreach($data as $val=>$row)
   {
    $values[] = $row;
   }
  if ($conditions)
   {
    $this->where = " AND " . implode(" AND ", $conditions);
   }
  $sql = "SELECT q2.id,
      q2.code,
      q2.class,
      q2.category,
      q2.price,
      q2.production_date,
      q4.title AS STATUS
  FROM   (SELECT q1.id,
        q1.status,
        code,
        class,
        category,
        price,
        production_date
    FROM   products_table AS q1
    WHERE  q1.id  ".$next_prev_sign." ".$record_start."
    ".$this->where."
    ORDER  BY q1.id ".$order."
    LIMIT  ".$number_of_records.") AS q2
      INNER JOIN products_table AS q3
        ON q3.id = q2.id
      JOIN products_status AS q4
     ON q4.id = q3.status ";
  $results = $this->db->query($sql, $values);
  return $results->result_array();
}

This produces

Code:

SELECT q2.id,
               q2.code,
               q2.class,
               q2.category,
               q2.price,
               q2.production_date,
               q4.title AS STATUS
        FROM   (SELECT q1.id,
                       q1.status,
                       code,
                       class,
                       category,
                       price,
                       production_date
                FROM   products_table AS q1
                WHERE  q1.id   >  0
                 AND class = '1' AND category = 'C'
                ORDER  BY q1.id ASC
                LIMIT  20) AS q2
               INNER JOIN products_table AS q3
                       ON q3.id = q2.id
               JOIN products_status AS q4
                 ON q4.id = q3.status

Now my problem is I don't want CI to add single quotes to numeric arguments! How can I active that with the code above? For example AND class = '1' will change to AND class = 1

Please tell me it's possible haha great thanks forum.

[eluser]jairoh_[/eluser]
but it doesn't make any difference.

[eluser]behnampmdg3[/eluser]
[quote author="jairoh_" date="1369544940"]but it doesn't make any difference. [/quote]Where we can see that a fast type=const becomes a slow type=index because it can't use the index properly. Instead it has to perform the implicit conversion from CHAR to numeric value for _every_ record in the table to be able to compare the record with the numeric value in the query. Which on a large table causes quite an impact.

So yes my point was more that you should write correct queries to avoid extra work for the database and unnecessary problems.

[eluser]TheFuzzy0ne[/eluser]
Any smart DBMS should see the the column type is an integer, and the query optimiser should cast the string to an integer. This should only occur once, before any tables are even scanned. It shouldn't happen once for each row. That wouldn't be very scalable, would it?

It makes more sense to cast the query parameter type to match the column type than it does to cast each field in every row to match the type of the parameter in the query.

Just out of interest, have you run any tests to see if there are any performance differences? I'd be interested to see the results.

[eluser]behnampmdg3[/eluser]
[quote author="TheFuzzy0ne" date="1369755008"]Just out of interest, have you run any tests to see if there are any performance differences? I'd be interested to see the results. [/quote]Hi hi how are you? Yes I have. See post # 5 excellent, bonus.