Posted by Jerry Stuckle on 09/18/06 21:17

Ike wrote:
> I'm absolutely stuck, hoping someone can illuminate my sitatuation. I have a
> string (a query) where I need to dynamically be able to change one part of
> the the string (the part where .username LIKE\'%\' ), changing the text I am
> looking for .username to be LIKE, to something else that will sent over via
> an HTTP Get as $_GET['username']. THat is, the quey can be ANY query,
> however, if there is a .username LIKE in the query, I need to change the
> value for the LIKE (which is % in this case) to be $_GET['username'].
>
> Can someone please help me out with this? A typical query would be something
> like:
>
>
> $qid = mysql_query(stripslashes("SELECT t0.username AS \"Username\"
> ,COUNT(t1.closed) AS \"Ups Handled\" , AVG(ABS(t1.closed)) AS \"Closing
> Ratio\" FROM associates t0,leads t1 WHERE (t0.branch LIKE \'%\') AND
> (t1.associatekey=t0.id) AND t0.username LIKE\'%\' AND t1.date
> >=\'2006-01-01%\' AND t1.date <=\'2006-12-31%\' GROUP BY
> t0.username ORDER BY t0.username ASC "));
>
> thanks, Ike
>
>

First of all, you should use mysql_real_escape_string() instead on any
data you use. You should also validate the username field before
sending it - since it's a GET parameter, anyone could put almost
anything in there (also true for POST, but only a tiny bit harder).
Then you can just use the username when building your SQL.

Also, you have several other problems in your SQL.

It's probably not a good idea to have a space in the aliases. And
strings are surrounded by single quotes, not double quotes in SQL.

Also, "t0.username like '%'" is meaningless - it will match any non-null
value.

And "t1.date >= '2006-01-01%' won't work. If you're going to use '%'
you must use like. If you're looking for anything >= 1/1/2006, just
compare like that.

Something like this (not checked):

$username = isset($_GET['username']) ? $_GET['username'] : null;

if ($username ... // validation here
$qid = mysql_query("SELECT t0.username AS Username,
COUNT(t1.closed) AS Ups_Handled\" ,
AVG(ABS(t1.closed)) AS Closing_Ratio
FROM associates t0,leads t1
WHERE t0.branch LIKE '".mysql_real_escape_string($username)."%' AND
t1.associatekey=t0.id AND
t1.date >= '2006-01-01' AND
t1.date <= '2006-12-31'
GROUP BY t0.username
ORDER BY t0.username ASC");

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]