How can I add a search filter to my current query without changing the context?

General PHP Help
#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 usually 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
}

But simply adding this statement to the first one throws an error. (Error: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given)

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.

Basically a user should be able to search (via an html5 form) the table for anything that matches the cuid or ipaddress columns. Is my statement incorrectly constructed, or am I just missing something small?

#2

Getting a boolean back means the query failed. And using anything that can come from the user is a bad thing waiting to happen.

You need to fetch the error the database is giving to remedy the problem.

::::EDIT::::
My guess is, WHERE ((cuid LIKE ‘$inShort’) should be c.username, because that alias doesn’t exist in that context.

#3

Alright, give me a moment to check the apache error log (I think it resides there?).

I will be escaping the user input, yes. For now I am only worrying about functionality before security :slight_smile:

#4

Strange, the error log doesn’t give any additional info?

I have thought of that as well, and tried it again now. The error still persists.

May I ask, is this where I get the detailed error logs on localhost? C:\xampp\apache\logs

#5

Are you using mysqli?

#6

Yep. This is how I execute it:

$result = mysqli_query($conn, $sql);

Note that the program is working perfectly without the where/like clause. I am only trying to find a way to add a search filter into the working query. When I do, it fails and throws the error.

#7 
$result = mysqli_query($conn, $sql) or die(mysqli_error($conn));
#8

Gee, thanks for showing me that. I followed along the errors it was displaying, and then changed the alias back to c.username and voila.

I feel ashamed now, but thanks for showing me how to debug that way, couldn’t have done it without your help! :slight_smile:

Sponsor our Newsletter | Privacy Policy | Terms of Service