jimmyoneshot

I think I've solved it the count was the problem. I just used a subquery to get counts

OK sorry about this ha. Thanks for the help. I'll see if I can come up with a better example

Thanks. I modified it to this:- select distinct u1.id as type_1, count(u1.id) as count, u2.id as type_2 from users as u1 left join users as u2 on (u2.type = 2) where u1.type = 1 The problem is this the result is then returned as this in a single row:- type 1 | count | type 2 7 4 8 Whereas I need all users to be returned like this so that I can loop through the result and echo them out on the front end:- id | Name | count | type 7 John 4 type 1 user 8 Paul 4 type 1 user 10 Steve 4 type 1 user 9 Jack 4 type 1 user 6 Jill 2 type 2 user 4 Gary 2 type 2 user I can't see how this can be done with subqueries based on the fact that I need a list of users. My actual where criteria for the queries I have is a bit more complicated as it is actually based on another left join which defines if users are registered but as I say I tried to simplify it here.

OK I modified your query to this:- select distinct users.id as type_1,count(id) as count, (select distinct users.id from users where type = 2) as type_2 FROM users where type = 1 Which resulted in the error:- "#1242 - Subquery returns more than 1 row"

Sorry maybe the example I gave was pretty bad because I tried to simplify it which was a mistake but basically I need to show user types as in I need to show all the user that are type 1 underneath one heading on my site and then all the user's that are type 2 underneath which means using OR wouldn't be relevant so it should look like this like this:- TYPE 1 USERS John Smith Paul Jones Jill Jackson TYPE 2 USERS Gary Stevenson Steve Arnold So maybe this is a better example of why I'm using join which again produces the error of either the top or bottom query returns no results:- SELECT DISTINCT @x:='TYPE 1 USERS' AS heading, COUNT(u1.id) AS u1_count, u1.id FROM users AS u1 WHERE u1.type = 1 UNION ALL SELECT DISTINCT @x:='TYPE 2 USERS' AS heading, COUNT(u1.id) AS u1_count, u1.id FROM users AS u1 WHERE u1.type = 2

Hello

I know this isn't a practical example but it's a simplified version of my problem. Basically if I have this query or something:- SELECT DISTINCT COUNT(u1.id) AS u1_count, u1.id FROM users AS u1 WHERE u1.id = 1 UNION ALL SELECT DISTINCT COUNT(u1.id) AS u1_count, u1.id FROM users AS u1 WHERE u1.id = 997 I get the error #1048 - Column 'id' cannot be null The first query on it's own returns 1 result whereas the second query on it's own returns 0 results but when they are unioned this error get's thrown. Including the COUNT seems to be what causes the problem. The following query however:- SELECT DISTINCT COUNT(u1.id) AS u1_count, u1.id FROM users AS u1 WHERE u1.id = 1 UNION ALL SELECT DISTINCT COUNT(u1.id) AS u1_count, u1.id FROM users AS u1 WHERE u1.id = 2 works fine because the second query in this query returns a row. How can I get rid of this error please?

At the moment I have the following markup:- <script type="text/javascript"> function save(x) { id=x.form.getAttribute("data-id"); //Do stuff here to save the photo based on the above id } </script> <style> .photo button{ display:none; } .photo:hover button{ display:block; } .photo:hover img{ width:200px; height:200px; position:absolute; top:-8px; left:-8px; } </style> <form data-id="1"> <a class="photo"> <img/> <button onclick="save(this);return false;"></button> </a> </form> Basically this is one item within an image gallery. And I have my css setup so that when a user hovers over the anchor tag the image enlarges and the button's display is set to block so that it can be clicked upon which the photo will be saved by the user. As you can see I need to use the button as the clickable element because in my save function I use x.form.getAttribute("data-id") and the .form is only applicable to form elements. Problem is this won't validate in HTML5. Are there any alternatives which will validate?

I've been working on this for a while but basically I want to be able to get a list of items (photos) with the first 3 comments on each item displayed beneath each item. At the moment I have the following:- SELECT DISTINCT @rowtype:='1' AS rowType, p.id AS rowId, p.id AS id, p.authorId FROM z_photos AS p UNION SELECT @rowtype:='2' AS rowType, c.id2 AS rowId, c.id, c.authorId FROM ( SELECT id2, COALESCE( ( SELECT id FROM z_comments li WHERE li.id2 = dlo.id2 ORDER BY li.id2, li.id LIMIT 2, 1 ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid FROM ( SELECT DISTINCT id2 FROM z_comments dl ) dlo ) lo, z_comments c WHERE c.id2 >= lo.id2 AND c.id2 <= lo.id2 AND c.id <= lo.mid ORDER BY rowId DESC, rowType ASC, date DESC But I now want to do this without the union by combining both queries into one. The reason being I want to be able to apply an overall WHERE statement at the end such as "WHERE p.authorId = 7" which will get all photos by that user and the comments on those as the above query will obviously return all comments. Is this possible without the above union or will I have to append such a where statement to the photos query and the comments query?

I have this at the moment:- <?php $postId=0;?> <?php while($row = mysql_fetch_assoc($result)){?> <?php if($row['rowId']!=$postId && $row['rowType']==1){ if ($postId !== 0){ echo 'POST END</br>'; } echo 'POST START</br>'; echo $row['body'].'</br>'; $postId = $row['rowId']; }else if($row['rowId']==$postId && $row['rowType']==2){ echo $row['body'].'</br>'; } ?> <?php }echo 'POST END</br>'//while($row = mysql_fetch_assoc($result))?>

Starting to realize that this is hard as hell

Cheers for the suggestions guys. It's one of those things which I thought would be easy but now seems complex mainly due to the fact that comments need to be grouped inside their items in the output but they are obviously not this way inside the query result. Good idea about checking the previous id aswell.

At the moment I have an sql query which retrieves an item and it's latest comments and it works well but I am now struggling to get it to output properly. My query is as follows:- SELECT DISTINCT @rowtype:='1' AS rowType, //Type of row post or comment s.id AS rowId, //used as the 'grouper' to group posts and their comments s.id, s.body, //text of the post s.date, s.aboutItemId, //id of the attached item u1.id AS u1_id, u1.username AS u1_username, u1.name AS u1_name, u1.surname AS u1_surname FROM z_posts AS s LEFT JOIN z_users AS u1 ON s.id1 = u1.id UNION SELECT @rowtype:='2' AS rowType, l.id2 AS rowId, l.id, l.body, l.date, l.id1 AS u1_id, NULL AS u1_username, NULL AS u1_name, NULL AS u1_surname FROM ( SELECT id2, COALESCE( ( SELECT id FROM z_postComments li WHERE li.id2 = dlo.id2 ORDER BY li.id2, li.id LIMIT 14, 1 ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid FROM ( SELECT DISTINCT id2 FROM z_postComments dl ) dlo ) lo, z_postComments l WHERE l.id2 >= lo.id2 AND l.id2 <= lo.id2 AND l.id <= lo.mid ORDER BY rowId DESC, rowType ASC, date DESC What I want is to output the posts as lis in a ul with the comments for each item in their own ul within each post li so it looks like this:- <ul> <li>Post2 <ul> <li>Comment 2 on post 2</li> <li>Comment 1 on post 2</li> </ul> </li> <li>Post1 <ul> <li>Comment 3 on post 1</li> <li>Comment 2 on post 1</li> <li>Comment 1 on post 1</li> </ul> </li> </ul> How can I create this this via php?

Me neither. Mainly because when a user adds an object the system would need to:- 1. insert an object 2. get the id of the last inserted object 3. insert the object extra details into the extra detail table rather than just inserting everything in one single query I do see a benefit in this case though as it would save me coalescing all of the common object details as you can see I do in my current query. This may not mean much though I suppose it depends on how detrimental coalesce is to the speed of a query, which I'm not sure on

Cheers Fenway. I'll see how it goes. Do you think I'm going along the right lines by coalescing the attachment details like I have? I'm trying to get the query to work as fast as possible. The only other option on that part is to have an objects table acting as a base table for all objects and have seperate tables for any extra fields specfic to any type of object which I think you advised me against a little while back.