HELP with SQL "Order By " query

hi everyone

Sorry for this question but i am still new to SQL/PHP.

i have a database of multiple registered members, i now wish to display the ‘member profiles’ on a gallary page. However, i wish to display the profiles of the members who have uploaded pictures first .

when a picture is uploaded to the database an entry is made in the mysql database with the ID of the pictures. consequently, members who have not uploaded picutres will not have an entry in that database

how do i now programme my "Order by " query to first check if there is an entry in the photo gallery and to then show them first

it might help if i show you my current sql command



<?php

	$select = " SELECT  u. user_id ,
,                                 p.  photonames "; 
	 
	$from   = " FROM 
	             users u  LEFT OUTER JOIN phototable p
	             ON u.user_id =  p.user_id
	              LEFT  OUTER JOIN phototable p  ";                 

       $where      = " WHERE 
	                u.users = 'active'  "; 

        $order=  "ORDER BY u.user_id ASC LIMIT $start, $display ";    
	
?>

the above command retrieves the data but does not bring it out in the required order. i tried to amend the command below to first display from the phototable ( that contains a postive value in the photnames column and then by the user IDs.


<?php
 $order=  "ORDER BY phototable ( WHERE  p.photonames != NULL),  u.user_id    ASC LIMIT 10, 30 "; 

?>


Can someone please tell me where i am going wrong and how the SQL command should be drafted.

thank you for your kind help

warm regards

Andreea

if a user can have at most one picture in the gallery, then your query will work okay, but if there can be more than one picture, your query returns as many rows as there are pictures, which means each user can be in the result set multiple times, which is kinda messy if what you wanted was “check if there is ~an~ entry in the photo gallery”

if there are multiple pictures for a given user, do you want your query to return any of them so that you can include it in the member profile?

this leads to the question… which (single) picture would you lke to return? the biggest? the earliest? the one flagged with the “show in profile” flag?

Semi-OT:
Rudy, if the OP COUNT’d on photonames while GROUP BY’ing on user_id, and the group contained no pictures, would it return 0 or NULL? (and further, if it returns NULL, what happens when you ORDER BY on said column?)

why don’t you test it and you tell us?

CREATE TABLE users
( user_id INTEGER 
, user_name VARCHAR(9)
);
INSERT INTO users VALUES
 ( 1 , 'curly' )
,( 2 , 'larry' )
,( 3 , 'moe' )
;
CREATE TABLE phototable 
( user_id INTEGER 
, photo_name VARCHAR(37)
);
INSERT INTO phototable VALUES
 ( 1, 'picture of moe in pants' )
,( 1, 'picture of moe in pyjamas' )
,( 3, 'picture of curly' )
;
SELECT u.user_id
     , COUNT(p.photo_name) AS count_pictures
  FROM users AS u
LEFT OUTER
  JOIN phototable AS p
    ON p.user_id = u.user_id
GROUP
    BY u.user_id    
ORDER
    BY count_pictures
;            

:slight_smile: