MYSQL Problem?

Pinkmischief

Hi guys i am having a bit of a problem as i have a dynamic dropdown that populates it self from a database where user post messages and the thing is when a user posts more than one message it shows multiple instances of the same user in the dropdown menu so the basics working of it is as a admin user of site you can click the dropdown and then click submit to view all posts from that user, i know the DISTINCT command in mysql will only work on one field where as this table has 3 fields which all need to be unique the fields are title,firstname,lastname so the question is how do i do it so that MYSQL checks all 3 are unique and dont already exist in the dropdown so Mr Whoever Wherever does not appear 100 times if he posts 100 times,??? This is the code i currently have i have tried placing DISTINCT inbetween the query but it throws a error

$sql = "select Id,title,firstname,lastname from comments";

Here is the code i tried.

$sql = "select Id,title,firstname,DISTINCT (lastname)) from comments";

Any help would be greatly appreciatted Many Thanks Pinky.

Houdini

Try removing the extra )

 $sql = "select Id,title,firstname,DISTINCT (lastname)) from comments";

$sql = "select Id,title,firstname,DISTINCT (lastname) from comments";

Pinkmischief

Hi thanks for thje reply i tried that and it told me this

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT (lastname) from comments' at line 1select Id,title,firstname,DISTINCT (lastname) from comments

if you have any more suggestions would be appreciated

Thanks Again 🙂

Houdini

Here is how to query with your criteria but do not be suprized at the results, mostly because most last names are DISTINCT

SELECT DISTINCT (lastname), id, title, firstname FROM comments LIMIT 0 , 30

Pinkmischief

Im not sure i explained quite properly so i shall post the code here

function usercomments(){
	$sql = "select Id,title,firstname,lastname from comments";
	$query = mysql_query($sql) or die (mysql_error()."". $sql);
	echo "<form id='form1' name='form1' method='post' action=''>";
	echo "<select name='selectuser'>";
	while ($result = mysql_fetch_array($query)){
                //$result[1] = Mr $result[2] = Harry $result[3] = Houdini
	echo " <option value='".$result[0]."'>".$result[1]." ".$result[2]." ".$result[3]."</option>";
		}
    echo "</select>
 			 <label>
  				<input type='submit' name='Submit' value='View Comments By This User' />
  				</label>
			</form>";

}

This is the code i have for the dropdown but.... this is where the tricky part is the dropdown population is working fine as it fills the dropdown with the users names But say for example Mr Harry Houdini posts 2 comments on seperate occasions it puts another Mr Harry Houdini in the dropdown menu so that is why i need to do it so Harry Houdini will only appear once in the dropdown menu even if he has posted 100 times.

Hope this explains a bit better 🙂
Regards Pinky

Pinkmischief

Also would like to add there is 3 fields Title which would contain the first part ie Mr firstname contains Harry and lastname contains Houdini so basically i need to DISTINCT the 3 fields to make sure that Mr Harry Houdini only appears once in the DD menu.