mySQL query - How to select names with out duplicates

[mazman13]

I want to select names from a database

ie:

Dog

Dog

Dog

Cat

Bird

Bird

 

I want something where I can select >Dog, Cat, Bird instead of the three dogs and two birds.

 

Can I do that?

[recklessgeneral]

Hi,

 

You can use the SELECT DISTINCT keywords instead of just SELECT in your query. Everything else would be the same and that should get you what you're after.

 

Cheers,

Darren.

[mazman13]

I have two things that should show up. Laszlo and Dusty. When I add the 2nd query it only adds the 1st one on the table. Laszlo. Is it conflicting with each other? Any way to get the same result with something else?

 

	
 $query = "SELECT DISTINCT writer FROM data";
$result = mysql_query($query)
	or die ("Can't do anything with the query!");
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
extract($row);
	echo"$writer<br>";
$query = "SELECT COUNT(quality) AS twriter FROM data WHERE writer='$writer' AND date BETWEEN '$year-$month-00' AND 			'$year-$month-32' ";
$result = mysql_query($query)or die(mysql_error());
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
	{
extract($row);
echo"$writer - $twriter";
	}
}

[rajivgonsalves]

code should be

 

$query = "SELECT DISTINCT writer FROM data";
$result = mysql_query($query) or die ("Can't do anything with the query!");
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
extract($row);
echo "$writer<br>";
$query1 = "SELECT count(quality) AS twriter1 FROM data WHERE writer='$writer' AND date BETWEEN '$year-$month-00' AND 			'$year-$month-32' ";
$result1 = mysql_query($query1) or die(mysql_error());
while ($row1 = mysql_fetch_array($result,MYSQL_ASSOC))
{
	extract($row1);
	echo "$writer - $twriter";
}
}

or do the same thing with

 

$query = "SELECT count(*) as twriter, writer FROM data group by twriter";
$result = mysql_query($query) or die ("Can't do anything with the query!");
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
	extract($row);
	echo "$writer - $twriter";
}

[aschk]

Seems to me like you have a non-normalised database layout. You have duplicate data there that should not be.

Secondly do NOT do a query inside a query while still using mysql_... functions. They overwrite each other...

$sql1 = "select * from ...";
$res1 = mysql_query($sql1,$conn);
while($row1 = mysql_fetch_assoc($res1){
  
   $sql2 = "select * from ...";
   $res2 = mysql_query($sql1,$conn);
   while($row2 = mysql_fetch_assoc($res2){
        /// BLAM!!!!
        /// WE JUST OVERWROTE OUR OUTER LOOP...
   }
}

 

So you're probably wondering what you should do about this. Use arrays...

 

$sql1 = "select * from ...";
$res1 = mysql_query($sql1,$conn);
$data1 = array();
while($row1 = mysql_fetch_assoc($res1){
   $data1[] = $row1;
}

$sql2 = "select * from ...";
$res2 = mysql_query($sql2,$conn);
while($row2 = mysql_fetch_assoc($res2){
   // Use data1 array or do something else.  
}

 

If you don't understand why the first loop is being messed then you best read up on mysql_fetch...