Cant order by in PHP via SQL Database?

Question

Im trying to order By "ItemLevel" in shops in a game I'm currently developing. it should be correct as because this code

ini_set('display_errors', 'On');
error_reporting(E_ALL | E_STRICT)

Displays no errors. Heres the ORDER BY ItemLevel line.

$item = mysql_query("SELECT * FROM knightG_{$shop["ItemCategory"]}s WHERE  
ItemId='{$shop["ItemId"]}' ORDER BY ItemLevel ASC") or die (mysql_error());

I can give anyone more information if requested. Thanks.

`{$shop["ItemCategory"]}` must have single quotes not a double — insanebits, Aug 04 '14 at 11:26
Besides, mysql error will not be displayed even with these error_reporting-related settings. — Cthulhu, Aug 04 '14 at 11:27
your code is vulnerable to mysql-injection [see this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php).plus `mysql_` functions in PHP are deprecated and should not be used anymore: [PHP manual](http://php.net/manual/en/function.mysql-query.php). — low_rents, Aug 04 '14 at 11:28
Unfortunately setting `display_errors` and `error_reporting` as you have done still will not show parser errors, which is what you have here (@VMai's answer should solve your problem). Parser errors do show up in your PHP error log however (or Apache error log if you're using Apache and don't have a separate PHP error log), or if you try to run the code from the command line. Better yet, use an IDE such as Aptana Studio or NetBeans which will catch parser errors for you. — Matt Browne, Aug 04 '14 at 11:33
Take this seriously: use prepared statements and parameterised queries. Really. Now! — david.pfx, Aug 04 '14 at 11:58

VMai · Answer 1 · 2014-08-04T11:36:57.280

2

It should be

$item = mysql_query("SELECT * FROM knightG_{$shop['ItemCategory']}s WHERE  
ItemId='{$shop['ItemId']}' ORDER BY ItemLevel ASC") or die (mysql_error());

instead. Inside of double string variable interpolation you must obmit the quotes around array indexes.

This is not valid if using braces surrounding arrays within strings allows constants, so you've got to use single quotes in your case. It may seem odd, but it's valid.

Better would be to move from the deprecated mysql_* functions to PDO or mysqli and use prepared statements with placeholders to bind inut values to. This will not take care of the problem of input parameters in identifiers for the names of columns or tables (the first input substitution here).

edited Aug 04 '14 at 11:36

answered Aug 04 '14 at 11:27

VMai

10,156
9
25
34

One must not _omit_ quotes, but use a different kind (single). – Cthulhu Aug 04 '14 at 11:29
@Cthulhu actually the answer is correct...quotes are optional for array values within double-quoted strings (this makes it more convenient to use array values within strings). – Matt Browne Aug 04 '14 at 11:30
1

I believe they are only optional without curly brackets. As is, the code should produce warning (undefined constant). – Cthulhu Aug 04 '14 at 11:34
@Cthulhu Yes I saw them a bit late and was just editing my answer. – VMai Aug 04 '14 at 11:37
Unfortunatly that didn't work. – Aug 04 '14 at 15:12

score 0 · Answer 2 · answered Aug 04 '14 at 11:44

0

$sql = "SELECT * FROM knightG_{$shop['ItemCategory']}s";
$sql.= " WHERE  ItemId='".$shop["ItemId"]."'";
$sql.= " ORDER BY ItemLevel ASC";
$item = mysql_query($sql) or die (mysql_error());

You should make sure though that your variables are safe from mysql injections.

Also I would advice to use PDO instead of the mysql extension. It is deprecated.

answered Aug 04 '14 at 11:44

Max

368
3
10

Why not MYSQLI? (I'm not saying its better or worse, just a question) – Aug 04 '14 at 12:18
Well I just like it more :) mysqli is a good choice as well if you are sure you will always use mysql. – Max Aug 04 '14 at 12:28

Cant order by in PHP via SQL Database?

2 Answers2