Using Sort and Order in Select Queries

[GoogleCodeExporter]

How can I use Sort and Order in select queries? The code samples show nothing 
like that

Original issue reported on code.google.com by maroun.b...@gmail.com on 16 Apr 2012 at 2:49

[GoogleCodeExporter]

$db->select("MyTable", "MyField = 'MyValue' ORDER BY MyOrderField");

That's similar to...
SELECT * FROM MyTable WHERE MyField = 'MyValue' ORDER BY MyOrderField;

Does that make sense?  The second parameter for the select method is the WHERE 
clause - where the ORDER BY is placed.

- Andrew

Original comment by ajporterfield@gmail.com on 16 Apr 2012 at 6:38

[GoogleCodeExporter]

Hello,

I don't know this project is still active or not but after looking at PDO 
wrapper class I noticed that this class is using where statement in select 
method just like normal queries i.e. $sql .= " WHERE " . $where;

It is not using placeholders for select method so I want to know is it safe to 
use parameters in prepare statement just like that without using placeholders 
in the query? Did these kinds of queries are SQL Injection safe?

It would be great is someone reply on that.

Original comment by sarfraz....@gmail.com on 26 Sep 2012 at 7:15

[GoogleCodeExporter]

The where clause should be passed in using a prepared statement - i.e:

$db->select("table", "field1 = :someValue", $someValue);

Or for multiple values something like:

$db->select("table", "field1 = :someValue, field2 = :anotherValue", 
array("someValue"=>$someValue,"anotherValue"=>$anotherValue));

The bind array is then applied by the execute function in run().

Using prepared statements certainly helps protect against SQL injection but it 
is not the be all and end all. There are an abundance of articles online about 
SQL injection though.

Original comment by rjbou...@gmail.com on 20 Nov 2012 at 11:45

[GoogleCodeExporter]

Does anyone know if you can actually use order by or even limit for that matter 
in a select statement. I have even tried using the run function with no luck.

Here is an example of a query I am trying to accomplish.

$sql = "select product_sku, product_desc, product_price from `products` ORDER 
BY round(product_price) desc limit 0,10";

Any help is appreciated!

Original comment by Bari...@gmail.com on 25 Feb 2013 at 7:33

[GoogleCodeExporter]

Hello Bari just change this in class.php

 public function select($table, $where="", $bind="", $fields="*", $order) {
                $sql = "SELECT " . $fields . " FROM " . $table;
                if(!empty($where))
                        $sql .= " WHERE " . $where;
                if(!empty($order))
                        $sql .= ' '.$order;
                $sql .= ";";
                return $this->run($sql, $bind);
        }

and also edit your quer as follow.

$result = $db->select($TBLname,'','','*',"LIMIT $startRow, $maxRows");

Br.

Umar Draz

Original comment by umar.d...@penguinitcs.com on 10 Mar 2013 at 12:27

[GoogleCodeExporter]

Thank you umar.draz. Thank worked like a charm! One note I did have to set 
$this->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); within the constructor.

Original comment by Bari...@gmail.com on 30 Dec 2013 at 6:19

[GoogleCodeExporter]

I didn't have any problems running a similar SQL query with an ORDER BY and 
LIMIT using the $db->run() function. Here's my example:

$sSql = "SELECT *column_name, column_name2 FROM mytable ORDER BY column_name 
DESC LIMIT 0,10";
$aResults = $db->run($sSql);

I tried a query with both MySQL and SQLite connections and both returned the 
first 10 results.

The $db->select() function could be modified to handle ORDER BY and LIMIT, but 
I don't think this is wise to modify this function. $db->run() with a LIMIT 
statement or $db->select() with some PHP code to loop and limit the returned 
results can both be instead. However, I will switch this ticket to an 
enhancement and review adding it into the code base.

Original comment by ayd...@gmail.com on 19 Jun 2014 at 6:12

• Changed state: Accepted
• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

Original comment by ayd...@gmail.com on 19 Jun 2014 at 6:12