Adding a search filter (WHERE column_name LIKE %search%) to a complicated statement...?

Adding a search filter (WHERE column_name LIKE %search%) to a complicated statement...?
0

#1

Hello :slight_smile:

I’m creating an admin dashboard with a list of user details. I have built the query as such:

$sql = "SELECT *, 
c.username cuid 
FROM customers c 
LEFT JOIN (SELECT * FROM radius.radacct f1 
           INNER JOIN 
	        (SELECT MAX(radacctid) id 
              FROM radius.radacct 
              GROUP BY username) f2 
           ON f1.radacctid = f2.id) f3 
ON c.username = f3.username";

It’s selecting all the rows from two different tables, from two different databases, inner joining the second table to get a filtered row, then left joining the two tables based on that filtered value.
The query is working like a charm, and there’s a reason it’s constructed that way, so if it’s possible I will prefer no changes to it.
Now, I am trying to add a search filter to the above as well, but having a hard time doing so. This is how I would ususally create a search filter:

if (isset($_GET['item'])) {
	$inShort = 	"%".$_GET['item']."%"; 
	$sql .= " WHERE ((cuid LIKE '$inShort') OR (ipaddress LIKE '$inShort')";
	//concatenate the search filter to the query
}

And if I type a search item to the URL, the query throws an error. I tried other varieties of using the WHERE and LIKE clauses with my first query, but for the life of me I can not get it to work. Please advise.


#2

The solution is to change cuid to c.username because the alias cuid doesn’t exist in that context.