Welcome to WebmasterWorld Guest from 54.146.201.80

Forum Moderators: open

Message Too Old, No Replies

mysql count matching rows

     
8:28 pm on Jul 14, 2013 (gmt 0)

New User

joined:July 14, 2013
posts:3
votes: 0


I'm trying to get a total $rowcnt for this query. I've made several attempts but so far, no luck. The following , which isn't attempting to count the rows but actually retrieve the data, results in a #1242(?)
subquery returns more than one row'

error.

$results = mysql_query("SELECT `user_answer_id` FROM `user_answers` WHERE `user_name` = '$var5' LIKE (SELECT `user_answer_id` FROM `user_answers` WHERE `user_name` = 
'$profile')")



I've tried like this.

$results = mysql_query("SELECT COUNT(`user_answer_id`) FROM `user_answers` WHERE `user_name` = '$var5' LIKE (SELECT `user_answer_id` FROM `user_answers` WHERE `user_name` = 
'$profile')")


This returns nothing. Any help would be much appreciated.
10:58 pm on July 14, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 0


Not quite sure what you are trying to do as your SQL does not make sense, you have
'user_name' = '$var5'
'user_name' = '$profile'
Unless $var5 is equal to $profile you won't get any results. Also you are using a LIKE statement in a very strange way.

Please explain in plain English what you are trying to achieve and someone might be able to help.
1:57 am on July 15, 2013 (gmt 0)

New User

joined:July 14, 2013
posts:3
votes: 0


Plain English? What part of my English do you not understand?
2:10 am on July 15, 2013 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4842
votes: 1


FWIW (it seems you know this already), the error is telling you that it expects one row returned from the subquery, but it returns more than one.

It's apparent you want the row count, but as stated the query you have does seem to be unusually constructed.

My general impression is that you don't need a JOIN/subquery at all.
4:01 am on July 15, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 0


Plain English in as in ...
"I'm trying to get a row count from table user_answers where it matches X conditions" explaining what the parameters $profile and $var5 mean, rather than the badly written SQL code that does not work and has logic errors in it.

I suspect that what you want is below, but without that explanation I'm just guessing as I don't know what you are trying to do with $var5 and the LIKE clause.

$results = mysql_query("SELECT COUNT(`user_answer_id`) FROM `user_answers` WHERE `user_name` = '$profile'")
3:30 pm on July 15, 2013 (gmt 0)

New User

joined:July 14, 2013
posts:3
votes: 0


Ok, I'll put it a different way.
I have a table named user_answers with columns named user_answer_id and user_name. Each user_name/user_answer_id has 25 rows per user. The user_answer_id can be different. What I'm trying to do is compare/match user Jake user_answer_id to user Betty user_answer_id. And get a count of those that match. I'll take the variables out in this example for easier interpretation.

$results = mysql_query("SELECT COUNT (*) `user_answer_id` FROM `user_answers` WHERE `user_name` = 'Jake' LIKE (SELECT `user_answer_id` FROM `user_answers` WHERE `user_name` = 'Betty')")

I tried substituting LIKE with IN.
9:38 pm on July 15, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


What is returned when you run this query?

SELECT `user_answer_id` FROM `user_answers` WHERE `user_name` = 'Betty'

Also your query is malformed.

You have:
SELECT COUNT (*) `user_answer_id` FROM `user_answers` WHERE `user_name` = 'Jake' LIKE (SELECT `user_answer_id` FROM `user_answers` WHERE `user_name` = 'Betty')

But you aren't defining what is supposed to be LIKE the sub query.

It should be something like:
where user_name = 'Jake' and user_answer_id in (select ....)

[edited by: Demaestro at 9:44 pm (utc) on Jul 15, 2013]

9:41 pm on July 15, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 0


Try
SELECT COUNT (*) AS Cnt
FROM `user_answers` AS a1
INNER JOIN `user_answers` AS a2
ON a1.`user_answer_id` = a2.`user_answer_id`
WHERE a1.`user_name` = 'Jake'
AND a2.`user_name` = 'Betty'
4:19 am on July 17, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 0


Demaestro is also correct and you could change your query to use IN as below
SELECT COUNT (*) `user_answer_id` FROM `user_answers` WHERE `user_name` = 'Jake' AND user_answer_id IN (SELECT `user_answer_id` FROM `user_answers` WHERE `user_name` = 'Betty')

However using the JOIN is usually a better way of doing it rather than sub queries for optimisation/speed.
11:11 pm on July 22, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 0


Did this resolve your problem?
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members