some help with a sql query

[Explv]

thats what i said before, but it give more code. and i thought that it was possible to do with 1 query

 

 

Why do you care if it's more code? Less code does not necessarily mean better code.

 

You're trying to solve a simple problem, so why not use a simple solution.

[Aap]

Why do you care if it's more code? Less code does not necessarily mean better code.

 

You're trying to solve a simple problem, so why not use a simple solution.

 

Thats true, got it working now with just a different query for each part.

 

but like if you want use group now its already 3 extra lines of code(not much) but extra work.

 

so i still want to figure out how to get it in 1 query.

 

also a question to you why you use full outer join?

[liverare]

Pseudo code:

	...
	
	WHERE ($proxy IS NULL OR proxy_id = $proxy)
	  AND ($sub_category IS NULL OR category_id = $sub_category)

Your WHERE logic may be wrong. I haven't done PHP in a while, but here's my understanding:

 

If A is valid, then we'll filter results by comparing A to B. Otherwise, if A is invalid, we're not filtering results for B, so display results.

 

That's pretty much the logic being used for both proxy and sub category. It should work.

Edited May 19, 2016 by liverare

[Explv]

 

so i still want to figure out how to get it in 1 query.

 

 

 

I think this would probably work as a single query:

SELECT a.id, a.rslogin, a.rswachtwoord, a.gebruikersnaam, p.proxy, c.subcategory
FROM bots a
INNER JOIN proxy p ON a.proxy_id=p.id AND ('$proxy' = 'noproxy' OR p.proxy = '$proxy')
INNER JOIN subcategory c ON a.category_id=c.subcat_id AND ('$sub_category' = 'nocat' OR c.subcategory = '$sub_category')

The logic behind it is as follows:

 

If you pass a valid proxy then the query would join bots and proxy only for values where p.proxy = '$proxy'.

 

If you pass the proxy value as 'noproxy' then the query would join bots and proxy for all matching ids ignoring the p.proxy value because '$proxy' = 'noproxy' will be true for all rows.

 

The same logic applies for the subcategory part of the query.

Edited May 19, 2016 by Explv