Search & replace

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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  

$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  
( AND  t0.username LIKE'%'  AND  
 >='2006-01-01%'  AND <='2006-12-31%'       GROUP BY  
t0.username   ORDER BY t0.username  ASC "));

thanks, Ike  

Re: Search & replace

Ike wrote:
Quoted text here. Click to load it

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  

And " >= '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 AND >= '2006-01-01'  AND <= '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.

Site Timeline