Parameter value not passed correctly to a query

[sv1cec]

Gentlemen,

I am trying to use various parameters in an ORDER BY clause in a query, and I just came up with an issue I can't overpass.

Suppose we have the followings:


$s11t='w.warned_time ASC';
$s12t='w.warned_time DESC';


These variables are then assigned to another variable, depending on the user's selection, in other words:


if ($selection==1)
{
$orderby=$s11t;
}
if ($selection==2)
{
$orderby=$s12t;
}


and the query which gets run after these lines, is:


$get_warns=$DB_site->query("
select w.*,u.username as wusername,u.userid as wuserid,wt.*,wuser.username
as warned_username_v,wuser.userid as warned_userid_v, ruser.username as
removed_by_v from
".TABLE_PREFIX."warnings w
left join ".TABLE_PREFIX."user wuser on(wuser.userid=w.warned_user)
left join ".TABLE_PREFIX."user u on(u.userid=w.warned_by)
left join ".TABLE_PREFIX."user ruser on(ruser.userid=w.removed_by)
left join ".TABLE_PREFIX."warning_types wt on(wt.tid=w.warned_warning_id) ORDER BY ".$orderby." LIMIT $startat, $perpage");


These work fine.

Now, I am trying to sort on a field, which does not exist as a column in any of the tables, but is calculated as below:


$s14t='(w.warned_time+wt.warn_maturity*24*60*60) DESC';


Now, if I put the line above, right below the two other lines, and run the program, I get the following error:


Database error in vBulletin 3.0.3:

Invalid SQL: select w.*,u.username as wusername,u.userid as
wuserid,wt.*,wuser.username as warned_username_v,wuser.userid as
warned_userid_v, ruser.username as removed_by_v from
warnings w
left join user wuser on(wuser.userid=w.warned_user)
left join user u on(u.userid=w.warned_by)
left join user ruser on(ruser.userid=w.removed_by)
left join warning_types wt on(wt.tid=w.warned_warning_id)
ORDER BY (w.warned_time wt.warn_maturity*24*60*60) DESC
LIMIT 0, 15
mysql error: You have an error in your SQL syntax near
'wt.warn_maturity*24*60*60) DESC LIMIT 0, 15' at line 6

mysql error number: 1064

Date: Saturday 25th of December 2004 01:07:49 PM
Script: http://xxxxx.xxxx.com/admincp/admin_warn.php?act=viewlogs
&script=/admincp/admin_warn.php&perpage=15
&orderby=(w.warned_time+wt.warn_maturity*24*60*60)% 20DESC&page=1
Referer: http://xxxxx.xxx.com/admincp/admin_warn.php?act=viewlogs
Username: John
IP Address: 55.55.55.55


Note the underlined section in this error, there should be a plus sign between the w.warned_time and wt.warn_maturity*24*60*60. As it shows, that plus sign is not passed to the query. Now, if I run this query directly from AdminCP, replacing the $orderby with the string defined in $s14t, it works just fine, so I have to assume that the problem has something to do with the way vB is handling the replacement of the variable in the query.

I tried escaping the plus sign using a \, I tried enclosing the variable in double quotes, and then use " . $orderby .", I tried whatever I could think of, but admitedly I am not good at this, so I end up asking for your help here.

Any ideas? They will be greatly appreciated.

Merry Christmas to all

[Dean C]

I've added some php and code tags to your post to make it a little more readable. I'm going to bed now and will take a look in the morning if I get a chance :)

Edit:

Ok i see your error already, you have to define the calculated field within your first batch of SQL. So add this to the first part of the sql:


(w.warned_time+wt.warn_maturity*24*60*60) AS calculated_field


Then add this clause:


ORDER BY calculated_field DESC

[sv1cec]

(w.warned_time+wt.warn_maturity*24*60*60) AS

Dean, much apprecaited, you helped me avoid adding another column in that table.

Thanks a lot. I got it working now.

Sorry I missed those php tags, I should have known better, but I was so frustrated when I posted that message.

Again tnx and Rgds

[Dean C]

No problem, good luck with whatever you're coding ;)

[sv1cec]

No problem, good luck with whatever you're coding ;)
I am coming up with the latest version of Advanced Warning System (that's my hack) and I wanted to give the user the ability to sort a list, by various columns.

Thanks for the tip Dean, sincerely appreciated.

Happy New Year to you and yours.