Aap Posted May 18, 2016 Share Posted May 18, 2016 Hey all, i know its the scripting section, but it looks like the best section to post this. i am currently busy with a database. want to show al bot info on a table( this is working) but now i want, to display the table based on 1 or 2 inputs. so if i search on proxy. all account with this proxy will showed. or if i search on category all accounts of this category will be showed. and last but not least is i search on a proxy and category i want to show alle account with selected proxy and category the problem is i only get 1 of the 3 worked in 1 query. so only searching on account/proxy. i guess the problem is using AND and OR wrong, but cant find the solution. so maybe anyone here can help me with it. 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 INNER JOIN subcategory c ON a.category_id=c.subcat_id WHERE proxy_id = $proxy AND category_id = $sub_category OR category_id = $sub_category OR proxy_id = $proxy thank for the time jappa Quote Link to comment Share on other sites More sharing options...
FrostBug Posted May 18, 2016 Share Posted May 18, 2016 (edited) Lacking some info here; are these PHP variables? Do they hold default values when "unspecified" ? And yes, the WHERE clause here doesn't make sense Edited May 18, 2016 by FrostBug Quote Link to comment Share on other sites More sharing options...
Aap Posted May 18, 2016 Author Share Posted May 18, 2016 Lacking some info here; are these PHP variables? Do they hold default values when "unspecified" ? And yes, the WHERE clause here doesn't make sense Yes these are php variables and hold default values. if the Where clause doesn't make sense, what make sense then. with this query, its only searching on proxy, even when i select a category. Quote Link to comment Share on other sites More sharing options...
FrostBug Posted May 18, 2016 Share Posted May 18, 2016 (edited) Yes these are php variables and hold default values. if the Where clause doesn't make sense, what make sense then. with this query, its only searching on proxy, even when i select a category. Assuming the default values aren't valid proxies/categories, try this: 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 p.proxy='$proxy' INNER JOIN subcategory c ON a.category_id=c.subcat_id AND c.subcategory='$sub_category' Edited May 18, 2016 by FrostBug Quote Link to comment Share on other sites More sharing options...
Explv Posted May 18, 2016 Share Posted May 18, 2016 (edited) Hey all, i know its the scripting section, but it looks like the best section to post this. i am currently busy with a database. want to show al bot info on a table( this is working) but now i want, to display the table based on 1 or 2 inputs. so if i search on proxy. all account with this proxy will showed. or if i search on category all accounts of this category will be showed. and last but not least is i search on a proxy and category i want to show alle account with selected proxy and category the problem is i only get 1 of the 3 worked in 1 query. so only searching on account/proxy. i guess the problem is using AND and OR wrong, but cant find the solution. so maybe anyone here can help me with it. 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 INNER JOIN subcategory c ON a.category_id=c.subcat_id WHERE proxy_id = $proxy AND category_id = $sub_category OR category_id = $sub_category OR proxy_id = $proxy thank for the time jappa Your logic sounds quite strange. If I understand correctly, it sounds like you want 1 query that does: Select all rows where: the row's proxy_id = $proxyId or the row's category_id = $categoryId or the row's proxy_id = $proxyId AND the row's category_id = $categoryId Well isn't that the exact same as: Select all rows where: the row's proxy_id = $proxyId or the row's category_id = $categoryId Because if the row matches both category id and proxy id, it will still satisfy the above condition. However what your query currently does is: Select all rows where: the row's proxy_id = $proxyId AND the row's category_id = $categoryId Why? Well because you are doing an Inner join on both proxy id and category id. So the resulting values only satisfy this condition. I (think) this would work instead: SELECT a.id, a.rslogin, a.rswachtwoord, a.gebruikersnaam, p.proxy, c.subcategory FROM bots a FULL OUTER JOIN proxy p ON a.proxy_id=p.id FULL OUTER JOIN subcategory c ON a.category_id=c.subcat_id WHERE p.proxy = $proxy OR c.subcat_id = $sub_category; An explanation of what this does: Select all the values we want from The bots table Joined with the proxy table on proxy id (keeping all values from both tables) Joined with the subcategory table on category id (Keeping all values from both tables) Where the proxy value matches or the category matches Edited May 18, 2016 by Explv Quote Link to comment Share on other sites More sharing options...
Aap Posted May 18, 2016 Author Share Posted May 18, 2016 Assuming the default values aren't valid proxies/categories, try this: 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 p.proxy='$proxy' INNER JOIN subcategory c ON a.category_id=c.subcat_id AND c.subcategory='$sub_category' your right the default calues aren't valid proxies/categories the query you suggest is not showing anything. Quote Link to comment Share on other sites More sharing options...
FrostBug Posted May 18, 2016 Share Posted May 18, 2016 your right the default calues aren't valid proxies/categories the query you suggest is not showing anything. made a last minute edit to that query, did you grab the right one? Quote Link to comment Share on other sites More sharing options...
Aap Posted May 18, 2016 Author Share Posted May 18, 2016 Your logic sounds quite strange. If I understand correctly, it sounds like you want 1 query that does: Select all rows where: the row's proxy_id = $proxyId or the row's category_id = $categoryId or the row's proxy_id = $proxyId AND the row's category_id = $categoryId Well isn't that the exact same as: Select all rows where: the row's proxy_id = $proxyId or the row's category_id = $categoryId Because if the row matches both category id and proxy id, it will still satisfy the above condition. However what your query currently does is: Select all rows where: the row's proxy_id = $proxyId AND the row's category_id = $categoryId Why? Well because you are doing an Inner join on both proxy id and category id. So the resulting values only satisfy this condition. I (think) this would work instead: SELECT a.id, a.rslogin, a.rswachtwoord, a.gebruikersnaam, p.proxy, c.subcategory FROM bots a FULL OUTER JOIN proxy p ON a.proxy_id=p.id FULL OUTER JOIN subcategory c ON a.category_id=c.subcat_id WHERE p.proxy = $proxy OR c.subcat_id = $sub_category; An explanation of what this does: Select all the values we want from The bots table Joined with the proxy table on proxy id (keeping all values from both tables) Joined with the subcategory table on category id (Keeping all values from both tables) Where the proxy value matches or the category matches If you are looking to return results where BOTH proxy id matches AND category id matches, then you will need to use Frostbug's snippet posted above. However, you cannot achieve both in one query. Hey thx for you suggestion, i know the logic showed here is not right, but was tired of trying, cant found the problem. also your almost right on what i want. want also get the results if both are selected to get all account with proxy/category. but also only proxy or category. also why FULL OUTER JOIN? but your query, isnt showing any results made a last minute edit to that query, did you grab the right one? yes got the right one, still no results. offcourse i can make a extra query and just check what option is selected. but it will be more code, and i am sure this can and must be done in a single query Quote Link to comment Share on other sites More sharing options...
FrostBug Posted May 18, 2016 Share Posted May 18, 2016 well, maybe like this, then? If we want to include results if either values have the "unspecified" value. Assuming that the unspecified value is literally "unspecified", this might do the trick: 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 (p.proxy='$proxy' OR p.proxy='unspecified') INNER JOIN subcategory c ON a.category_id=c.subcat_id AND (c.subcategory='$sub_category' OR c.subcategory='unspecified') Quote Link to comment Share on other sites More sharing options...
Aap Posted May 18, 2016 Author Share Posted May 18, 2016 well, maybe like this, then? If we want to include results if either values have the "unspecified" value. Assuming that the unspecified value is literally "unspecified", this might do the trick: 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 (p.proxy='$proxy' OR p.proxy='unspecified') INNER JOIN subcategory c ON a.category_id=c.subcat_id AND (c.subcategory='$sub_category' OR c.subcategory='unspecified') no still give no results. no matter on what way i search. with this query i can search on proxy and category. but not only proxy or category. $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 INNER JOIN subcategory c ON a.category_id=c.subcat_id WHERE a.proxy_id = $proxy AND a.category_id = $sub_category "; thats why i thought adding OR for proxy and category will made it that it work also when i only search on 1 option. Quote Link to comment Share on other sites More sharing options...
FrostBug Posted May 18, 2016 Share Posted May 18, 2016 no still give no results. no matter on what way i search. with this query i can search on proxy and category. but not only proxy or category. $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 INNER JOIN subcategory c ON a.category_id=c.subcat_id WHERE a.proxy_id = $proxy AND a.category_id = $sub_category "; thats why i thought adding OR for proxy and category will made it that it work also when i only search on 1 option. We haven't seen your schema or code, so its up to you to correct the little mistakes in here. By the looks of your query, either a.category_id is a varchar, or $sub_category is an integer. So whatever the case, fix the comparison clause in the subcategory inner join Quote Link to comment Share on other sites More sharing options...
Aap Posted May 18, 2016 Author Share Posted May 18, 2016 We haven't seen your schema or code, so its up to you to correct the little mistakes in here. By the looks of your query, either a.category_id is a varchar, or $sub_category is an integer. So whatever the case, fix the comparison clause in the subcategory inner join both are integer, but will check futher into it. Quote Link to comment Share on other sites More sharing options...
FrostBug Posted May 18, 2016 Share Posted May 18, 2016 both are integer, but will check futher into it. I assumed both were strings try this, then. 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 (p.proxy='$proxy' OR '$proxy'='unspecified') INNER JOIN subcategory c ON a.category_id=c.subcat_id AND (c.subcat_id=$sub_category OR $sub_category=UNSPECIFIED_ID) If $proxy is also an integer, change 'unspecified' string to the unspecified id Quote Link to comment Share on other sites More sharing options...
Explv Posted May 18, 2016 Share Posted May 18, 2016 (edited) no still give no results. no matter on what way i search. with this query i can search on proxy and category. but not only proxy or category. So let me get this straight, you want a query that does 3 things in one. 1) Lets you search for a match on proxy 2) Lets you search for a match on category 3) Lets you search for a match on both Well, why don't you just use three different god damn queries? You could have written them in the time it took you to make this post. Query 1: SELECT values FROM bot table INNER JOIN proxy table ON bot.proxy_id = proxy.proxy_id AND proxy.proxy = $proxyValue INNER JOIN category table ON bot.category_id = category.category_id Query 2: SELECT values FROM bot table INNER JOIN category table ON bot.category_id = category.category_id AND category .category = $categoryValue INNER JOIN proxy table ON bot.proxy_id = proxy.proxy_id Query 3: SELECT values FROM bot table INNER JOIN proxy table ON bot.proxy_id = proxy.proxy_id AND proxy.proxy = $proxyValue INNER JOIN category table ON bot.category_id = category.category_id AND category .category = $categoryValue And then in PHP: If category is undefined: Execute query 1 Else if proxy is undefined: Execute query 2 Else Execute query 3 Edited May 18, 2016 by Explv Quote Link to comment Share on other sites More sharing options...
Aap Posted May 18, 2016 Author Share Posted May 18, 2016 So let me get this straight, you want a query that does 3 things in one. 1) Lets you search for a match on proxy 2) Lets you search for a match on category 3) Lets you search for a match on both Well, why don't you just use three different god damn queries? You could have written them in the time it took you to make this post. Query 1: SELECT values FROM bot table INNER JOIN proxy table ON bot.proxy_id = proxy.proxy_id AND proxy.proxy = $proxyValue INNER JOIN category table ON bot.category_id = category.category_id Query 2: SELECT values FROM bot table INNER JOIN category table ON bot.category_id = category.category_id AND category .category = $categoryValue INNER JOIN proxy table ON bot.proxy_id = proxy.proxy_id Query 3: SELECT values FROM bot table INNER JOIN proxy table ON bot.proxy_id = proxy.proxy_id AND proxy.proxy = $proxyValue INNER JOIN category table ON bot.category_id = category.category_id AND category .category = $categoryValue And then in PHP: If category is undefined: Execute query 1 Else if proxy is undefined: Execute query 2 Else Execute query 3 thats what i said before, but it give more code. and i thought that it was possible to do with 1 query Quote Link to comment Share on other sites More sharing options...