Normal Query Syntax Not Working, '' For Numeric

[Pikachu2000]

Shift the goalposts, declare yourself the winner and run off. The mark of a true professional.

[DavidAM]

You clearly have never written an application that uses abstraction and base classes. This is basic stuff.

 

Now that's chest-thumping!

 

 

So the answer is .... quoted columns in the query string. A standard feature in DB engines.

 

Only in databases that are designed to support loosely-typed languages (like mySql). I have worked with multiple enterprise database engines, that do not support quoting of numeric data-types. Numeric data-types should never be quoted.

 

As Jessica pointed out, have a look at prepared statements. In order to bind a variable to the statement, you have to specify the data-type. It is trivial to add an additional field to whatever method call you have in order to pass the data types along.

 

 $fields = array('Name' => 'I. M. Noob', 'Age' => 15);
 $types = array('s', 'i');
 $sql = $db->buildQuery($fields, $types);


public function buildQuery($fields, $types) {
 $sql = 'UPDATE myTable SET';
 $ind = 0;
 $sets = array();
 foreach ($fields as $column => $value) {
   switch($types[$ind++]) {
     case 's': $sets[] = $column . '="' . $value . '"'; break;
     case 'i': $sets[] = $column . '=' . $value; break;
     default: trigger_error('Unknown datatype', E_USER_ERROR); return false;
   }
 }
 $sql .= implode(', ' $sets);

 return $sql
}

 

That's not from my library. I quit using generically generated queries a long time ago. A data model (class) that is specific to a particular table knows what columns exist and what their datatypes are.

[fenway]

It is quite common to do this, and not wrong at all. If the application is building queries and doesn't know the column types, this is how to do it.

 

This is *not* how to do it -- don't pretend that it is.

 

It doesn't work "as expected" -- it works by accident.

 

It's trivial to have your DB wrapper consult your schema once.