Welcome to WebmasterWorld Guest from 54.226.146.15

Forum Moderators: open

Message Too Old, No Replies

mysql count matching rows

     

jessica22

8:28 pm on Jul 14, 2013 (gmt 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.

Dijkgraaf

10:58 pm on Jul 14, 2013 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.

jessica22

1:57 am on Jul 15, 2013 (gmt 0)



Plain English? What part of my English do you not understand?

brotherhood of LAN

2:10 am on Jul 15, 2013 (gmt 0)

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



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.

Dijkgraaf

4:01 am on Jul 15, 2013 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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'")

jessica22

3:30 pm on Jul 15, 2013 (gmt 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.

Demaestro

9:38 pm on Jul 15, 2013 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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]

Dijkgraaf

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

WebmasterWorld Senior Member 10+ Year Member



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'

Dijkgraaf

4:19 am on Jul 17, 2013 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.

Dijkgraaf

11:11 pm on Jul 22, 2013 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Did this resolve your problem?
 

Featured Threads

Hot Threads This Week

Hot Threads This Month