query help

[23style]

i need to return a list from a database and check whether or not an element in that list occurs more than once.

if it occurs more than once, id like to add that element to an array.

what would be the best way to do this?

so far i have this:

$query = "SELECT DISTINCT bid FROM videomail_broadcast_flv WHERE id = '$id'";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

for ($i=0; $i<$num_results; $i++) {
	$row = mysql_fetch_array($result);
	$bid = $row['bid'];

$bidArray[count($bidArray)] = $bid;
}


for ($j=0; $j<count($bidArray); $j++) {
	$query = "SELECT * FROM videomail_broadcast_flv WHERE bid = '$bidArray[$j]' AND id = '$id'";
	$result = mysql_query($query);
	$num_results = mysql_num_rows($result);

echo "j = $j - ";
if ($num_results > 1) {
	$duplicateArray[$j] = $bidArray[$j];
}

echo "$duplicateArray[$j]<br />";
}

the only problem with this is that it will skip over spots in the array creating blank spaces in the array.

any help is much appreciated!

thanks guys 😃

[johnassis]

I don't quite understand your logic, if you are selecting distinct where id = $id. because usually the id column in a table will always be unique, I assume yours does not comply with this.

Also how does it skip over spots when you are telling it to print only when the value in the num_rows is greater than 1, wouldn't that mean that it is printing 0s as well?

However i would suggest you try this

$query ="SELECT_DISTINCT bid FROM videomail_broadcast_flv_WHERE id ='$id'"; 
$result = mysql_query($query); 
$num_results = mysql_num_rows($result);

if($result):
print"<center>$num_results unique bids retrieved<br>";
else:
print"No bid for that id";
exit;
endif;

    WHILE($row = mysql_fetch_array($result)):
$tocheck ="$row[bid]";

$sql ="SELECT
                *
                FROM videomail_broadcast_flv
                WHERE bid ='$tocheck'
                ORDER BY id ASC";

$result = mysql_query($sql);
$count_again = mysql_num_rows($result);
$row_body =mysql_fetch_array($result);


    if ($count_again > 1):
    $id ="$row_body[id]";
    $bid ="$row_body[bid]";

print"$bid found $count_again times<br>";
    else:
    /*do nothing*/
    endif;
    ENDWHILE;

[drawmack]

how about only grabbing the ones that match your condition in the first place?

$sql  = "SELECT <COLUMN 1>,<COLUMN 2>,count(<COLUMN 3>)  as limiter";
$sql .= "FROM <TABLE> ";
$sql .= "WHERE limiter > 1 ";
$sql .= "GROUP BY <COLUMN 3> ";
$sql .= "ORDER BY <COLUMN 1>,<COLUMN 2>,limiter";

[johnassis]

Drawmack, very true and much faster, however; I think that your mysql group by and order by have a problem and the query would not work.

I tried this code below and it worked.

$sql ="SELECT field_one, field_two, field_so_on, COUNT(field_two) AS sum
	FROM videomail_broadcast_flv
        GROUP BY field_two
        HAVING sum >1
	";


$result = mysql_query($sql);

	if(!$result):
	print"<center>no data</center>";
                            exit;
	else:

$row_body = mysql_fetch_array($result);

print"<center>Here are the dupes</center>";


WHILE($row_body = mysql_fetch_array($result)):

$var0 ="$row_body[0]";
$var1 ="$row_body[1]";
$var2 ="$row_body[2]";
$sum ="$row_body[3]";

print"For bid $var1 I found $sum occurrances, starting at $var0<br>\n";


ENDWHILE;
print"<b> end of report.</b>\n";

	endif;