homepage Welcome to WebmasterWorld Guest from 54.226.93.128
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
mysql count matching rows
jessica22




msg:4593074
 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




msg:4593115
 10:58 pm on Jul 14, 2013 (gmt 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.

jessica22




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

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

brotherhood of LAN




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

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




msg:4593147
 4:01 am on Jul 15, 2013 (gmt 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'")

jessica22




msg:4593269
 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




msg:4593351
 9:38 pm on Jul 15, 2013 (gmt 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]

Dijkgraaf




msg:4593352
 9:41 pm on Jul 15, 2013 (gmt 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'

Dijkgraaf




msg:4593771
 4:19 am on Jul 17, 2013 (gmt 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.

Dijkgraaf




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

Did this resolve your problem?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved