Jump to content

[SOLVED] simple mysql query.. :/


melvincr

Recommended Posts

my problem is that i've a table with pictures, one for attributes and one to connect them. for a search function i would like to select all pictures which have the same attributes.

 

ie:

SELECT * FROM pictures a, attributes b, pic2att c WHERE a.id = c.picid AND c.addi = b.id AND b.id = 10 AND b.id = 12

 

this works for one attribute but not for two..  ???

Link to comment
Share on other sites

Where's the count?

 

One, two IDs, HA HA HA!

 

The subquery will only return picid's having both attributes due to the "HAVING COUNT(*)=n" portion where "n" must equal the number of elements in the IN() set.  So if there are three attributes, IN(12,90,4), COUNT(*) should = 3, etc.  If not all attributes are found for any given picid, then it won't make it out of the subquery because the COUNT(*) won't equal the number of IN elements.

 

There are at least three ways I can think of to do this query.  The above might have been the easiest.  The first that came to mind was to use JOINs, but doing this for an arbitrarily large n of attributes will quickly become unweildy.  The other would be to make either a set or a string with GROUP_CONCAT and search it with FIND_IN_SET or RLIKE, respectively, but I think the above is faster.

Link to comment
Share on other sites

thx fenway that was the next question :)

 

thank you very much!! it's working  ;D

 

another thing happened when i added SELECT DISTINCT SQL_CALC_FOUND_ROWS to count the rows.. it just made the mysqld feel funny.. (meaning nothing worked anymore..)

Link to comment
Share on other sites

What's wrong with PHP's mysql_num_rows() for counting the number of rows returned?

 

(And I should have said it in the earlier post since it may not have been self-evident, but you'll need to make COUNT(*)=??? equal to the number of attributes you will be seeking when you assemble the query.)

Link to comment
Share on other sites

SELECT DISTINCT SQL_CALC_FOUND_ROWS *

FROM art_picture a

JOIN

(SELECT * FROM art_tech2pic WHERE techid IN ( 35, 34, 16) GROUP BY picid HAVING COUNT(*) = 3) AS f ON a.id = f.picid

JOIN art_technique AS g ON g.id = f.techid

LIMIT 0, 10

 

this is working.. the query that killed the server was

SELECT DISTINCT SQL_CALC_FOUND_ROWS *

FROM art_picture a, art_style b, art_technique c, art_tech2pic d, art_user e

WHERE c.id= '35'

AND e.uabout LIKE '%test%'

OR  e.uname LIKE '%test%'

 

i don't get any response from this query..

 

 

 

Link to comment
Share on other sites

So the top one's working?  That means you're okay, right?

 

The bottom one may have had some other error.  The '%test%' conditions should be enclosed in parentheses since AND binds more tightly than OR.  Of course, you should still get results unless those conditions are not meetable.

Link to comment
Share on other sites

Where's the count?

 

One, two IDs, HA HA HA!

 

The subquery will only return picid's having both attributes due to the "HAVING COUNT(*)=n" portion where "n" must equal the number of elements in the IN() set.  So if there are three attributes, IN(12,90,4), COUNT(*) should = 3, etc.  If not all attributes are found for any given picid, then it won't make it out of the subquery because the COUNT(*) won't equal the number of IN elements.

 

There are at least three ways I can think of to do this query.  The above might have been the easiest.  The first that came to mind was to use JOINs, but doing this for an arbitrarily large n of attributes will quickly become unweildy.  The other would be to make either a set or a string with GROUP_CONCAT and search it with FIND_IN_SET or RLIKE, respectively, but I think the above is faster.

 

*SHUDDER*

 

First, asking for * with a GROUP BY for a single column is bad form... especially because you're using picid (which is obviously the same for all aggreated rows) and attid (which may be, but isn't clear to me)... MySQL will let you do this, and shouldn't -- it feels very unstable to me.

 

Second, I can't stand that you can use expressions in the HAVING clause that aren't in the SELECT column list.

 

Not that I haven't used that "having count() = n" trick many times before, I've just written it very differently.

Link to comment
Share on other sites

First, asking for * with a GROUP BY for a single column is bad form... especially because you're using picid (which is obviously the same for all aggreated rows) and attid (which may be, but isn't clear to me)... MySQL will let you do this, and shouldn't -- it feels very unstable to me.

 

I agree.  melvin should only take what he uses.  I left it out because I don't necessarily know what he'll be drawing on.

Link to comment
Share on other sites

what do you mean i should use what i use?

 

We mean that instead of retreiving everything (*) from the database, you should only have the columns you'll actually use in PHP in the column list.  For instance, if you just want the picture id from art_picture and (I'm making this up) the name of the technique from art_technique, you should just use "SELECT DISTINCT SQL_CALC_FOUND_ROWS a.id, g.name FROM...."  But since we don't know what you'll be using, it'll be up to you to make that change.  It's more efficient to only request what you use, and it's also prone to less confusion.  Say you have an "id" column in art_picture and an "id" column in art_technique.  If you get everything and use mysql_fetch_assoc() and use $row['id'], you will get the last column by that name, art_technique.id instead of art_picture.id since the first "id" element will be overwritten by the second in PHP.

 

what's wrong with the HAVING COUNT(*)=n  what can i do else?

 

Nothing's wrong with it in practice.  In "strict" SQL, it's not "proper" to reference something in the HAVING (or GROUP or ORDER) clauses that's not also in the column list.  In other words, we didn't bother using something like "SELECT picid, techid, COUNT(*) AS gcount FROM ..... HAVING gcount=2".  See where we defined COUNT(*) as a column in the SELECT clause and then later referenced it in the HAVING clause?  That's "proper" SQL, as far as I understand it.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  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.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.