Mysql Dbase Querry?

[Anon]

I am trying to querry a database to return the number of unique items in a row.

eg:

red
red
blue
black
black
black
green

would return

red 2, blue 1, black 3, green 1.

I am trying to do this using the following code, but am not sure if this is the best way. Ideas/Help?

setup SQL statement

$SQL = " SELECT DISTINCT FROM database_table WHERE color LIKE \"$color\";

execute SQL statement

$retid = mysql_db_query($database_name, SQL, $cid);
$numrows = mysql_num_rows($retid);

check for errors

if (!$retid) { echo( mysql_error()); }
else {

display results

while ($row = mysql_fetch_array($retid))
{
$idview = $row["id"];
$compname= $row["compname"];
$gender = $row["gender"];
}
}

Thanks

[kennethl]

To get the number of unique colors in your DB:

select distinct color, count(color) from database_table group by color

[Anon]

Thanks,

I changed the code to this but it's not working...

setup SQL statement

$SQL = "select distinct color, count(color) from database_table group by color";

execute SQL statement

$retid = mysql_db_query($database_name, SQL, $cid);
$numrows = mysql_num_rows($retid);

check for errors

if (!$retid) { echo( mysql_error()); }
else {

display results

while ($row = mysql_fetch_array($retid))
{
$color = $row["color"];
echo color;
}
}

[steved]

Assuming database_table is actually the name of your table, you should be able to use

SQL = "Select color, count(*) as TotalRows FROM database_table Group By color";

[Anon]

how would i echo the count from the array so i get?
red
blue
blue
black
black
black
black
green
-->red 1, blue 2, black 4, green 1.

Steve D wrote:

Assuming database_table is actually the name of your table, you should be able to use

SQL = "Select color, count(*) as TotalRows FROM database_table Group By color";

[mrskhris]

Just like you fetched the color.
$count = $row["TotalRows"];

[Anon]

hmm i don't know if i am explaining this right, or if i am just not getting it....

if i had data in COLUMN color:
blue
blue
black
black
black
black

I want to display
-->blue 2, black 4

I can use the following code to define that blue and black are the two types of data, but how do i display that there are 2 blue datapieces, and 4 black datapieces.

---

SQL = "Select color, count(*) as TotalRows FROM database_table Group By color";

$retid = mysql_db_query($database_name, $SQL, $cid);

if (!$retid) { echo( mysql_error()); } else {while ($row = mysql_fetch_array($retid))

{$color = $row["color"];echo color;}}

[mrskhris]

Ryan, are you sure you want to be a programmer?

SQL = "Select color, count(*) as TotalRows FROM database_table Group By color";

$retid = mysql_db_query($database_name, $SQL, $cid);

if (!$retid) {
echo( mysql_error());
}
else {
while ($row = mysql_fetch_array($retid)) {
$result[]=$row["color"]." ".$row["TotalRows"];
}
$overall_result=join(", ", $result);
echo $overall_result;
}