Jump to content

some help with a sql query


Aap

Recommended Posts

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

Link to comment
Share on other sites

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 boge.png

 

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.

 

c8f64520f268bcecce87d27f20f6a78b.png

Link to comment
Share on other sites

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.

 

c8f64520f268bcecce87d27f20f6a78b.png

 

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 by FrostBug
Link to comment
Share on other sites

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 smile.png

 

 

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 by Explv
Link to comment
Share on other sites

 

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.

 

e847dc59fc8b9eb75ad9fd69c112952e.png

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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')
Link to comment
Share on other sites

 

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 		  
		  ";

b5f9b9a75602c6bb0f4d99f454116e3e.png 

 

thats why i thought adding OR for proxy and category will made it that it work also when i only search on 1 option.

 

 

Link to comment
Share on other sites

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 		  
		  ";

b5f9b9a75602c6bb0f4d99f454116e3e.png

 

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Explv
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...