Append ORDER BY syntax to php MySQL query

Question

I am trying to add a ORDER BY syntax to a MySQL join query I am performing in PHP. I am using an already defined session variable to dynamically select criteria.

Works fine:

$query_icons = sprintf("SELECT i.id, i.name, e.EmpNo FROM gbl_icons i LEFT JOIN gbl_empicons e ON e.IconId = i.id AND e.EmpNo = %s", GetSQLValueString($ParamEmpNo_WADAgbl_qemplisting, "text"));

When I try to append ORDER BY it throws a 500 Internal Server Error:

$query_icons = sprintf("SELECT i.id, i.name, e.EmpNo FROM gbl_icons i LEFT JOIN gbl_empicons e ON e.IconId = i.id AND e.EmpNo = %s", GetSQLValueString($ParamEmpNo_WADAgbl_qemplisting, "text")ORDER BY i.id ASC);

How do I properly escape the dynamic session value properly?

ITroubs · Accepted Answer · 2013-03-20 17:20:13Z

2

do this:

$query_icons = sprintf("SELECT i.id, i.name, e.EmpNo FROM gbl_icons i LEFT JOIN gbl_empicons e ON e.IconId = i.id AND e.EmpNo = %s", GetSQLValueString($ParamEmpNo_WADAgbl_qemplisting, "text")."ORDER BY i.id ASC");

you forgot to enclode the ORDER BY i.id ASC in " so that you got a serious syntax error

as mentiond in the comment this would work too:

$query_icons = sprintf("SELECT i.id, i.name, e.EmpNo FROM gbl_icons i LEFT JOIN gbl_empicons e ON e.IconId = i.id AND e.EmpNo = %s ORDER BY i.id ASC", GetSQLValueString($ParamEmpNo_WADAgbl_qemplisting, "text"));

answered Mar 20, 2013 at 17:20

ITroubs

11.2k4 gold badges30 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jared Farrish Over a year ago

Why not just put it in the primary string?

Jared Farrish Over a year ago

Are you sure that first part will work with sprintf()?

ITroubs Over a year ago

@JaredFarrish Yes since "%s" means string

Jared Farrish Over a year ago

Right. So the first part in all likelihood doesn't resolve the problem past the parse error.

ITroubs Over a year ago

@JaredFarrish what problem didn't it resolve? he said that the server gave a 500 error which was caused by the parse error. Any logical or other Error is not part of the question..

|

dweeves · Accepted Answer · 2013-03-20 17:23:12Z

2

put the ORDER BY inside the string , after the "%s".

as an addition, it's very unsafe to forge SQL queries into a string directly, try using PDO or other data access layer to create safe parameterized requests.

$query_icons = sprintf("SELECT i.id, i.name, e.EmpNo FROM gbl_icons i LEFT JOIN gbl_empicons e ON e.IconId = i.id AND e.EmpNo = %s ORDER BY i.id ASC", GetSQLValueString($ParamEmpNo_WADAgbl_qemplisting, "text"));

answered Mar 20, 2013 at 17:23

dweeves

5,64524 silver badges28 bronze badges

Collectives™ on Stack Overflow

Append ORDER BY syntax to php MySQL query

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related