Forums

better SQL query

Permalink
Normally, an SQL-Query should look like this:
$rs = $db->Execute('SELECT * FROM table WHERE val=?', array('10'));


I have the following code and do not know, how to do this in the propper way. Could some one please help me?

$clients = $controller->getClientsFromBlock();
$i = 1;
foreach($clients as $client){
   $queryClientPart .= "client='" . $client."'";
   if($i != count($controller->getClientsFromBlock())){
      $queryClientPart .= " OR ";
   }$i++;
}
$query = 'SELECT * FROM btSteffTravelsTravelModes WHERE (' . $queryClientPart . ') ORDER BY sortOrder';
$tmp = $db->getArray($query);
foreach($tmp as $row){
   array_push($rows[] = $row);
}


thanks
Steff
 
Mainio replied on at Permalink Reply
Mainio
You should put the values into their own array that you pass to the query. The backend (adodb) handles SQL injections etc. already, so that should be used.

Otherwise, well there isn't actually that much better way to do that in c5 because of its poor support for SQL layer hiding. Other c5 developers seem to be quite passionate about using SQL and SQL being the best possible database iteraction layer. I slightly disagree but can also see why some people think that way. The thing I disagree is just with these kinds of cases that you have. With some simpler abstraction layer, that could be done with few lines of code.

There is the class DatabaseItemList in the core that can handle some of the SQL but for example your "OR" statements are not supported in that class. If you want to check that out, just open /concrete/models/item_list.php.


Antti
Mainio replied on at Permalink Reply
Mainio
But one thing you could also do there is a subquery but I don't know if it's any better. Subqueries are also usually not very efficient.