[SOLVED] Check one table for same value with another

[mattzo]

Hi I just started working with php and mysql. I have tried searching the net and the forums for a solution however all related posts I have trouble understanding.

 

The result I would like is for the page to list all of the dvd titles from the 'dvds' table and bold the title if the the dvd_id matches any of the udvd_id in the 'userdvds' table. Here is an example:

 

 

dvds

dvd_id	dvd_title
1	title 1
2	title 2
3	title 3

 

userdvds

userdvd_id	udvd_id
1	3
2	1

 

The tables above will produce:

 

title 1

title 2

title 3

 

<?php
require_once ('./includes/config.inc.php');
?>

<?php
require_once ('../mysql_connect_local.php'); // Connect to the local db.

	// Count the number of records
$query = "SELECT COUNT(*) FROM dvds ORDER BY dvd_title ASC";
$result = @mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_NUM);
$num_records = $row[0];

// Make the query.
$query = "SELECT dvd_id, dvd_title, udvd_id, userdvd FROM dvds, userdvds ORDER BY dvd_title ASC";
$result = @mysql_query ($query); // Run the query.

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){

if ($row['udvd_id'] == $row['dvd_id']) {
$bold = '<strong>' . $row['dvd_title'] . '</strong>';
} else {
$bold = '' . $row['dvd_title'] . '';
}

echo '<table><tr bgcolor="">
<td align="center">' . $row['dvd_id'] . '</td>
<td align="left">' . $bold . '</td>
</tr></table>';


}

mysql_free_result ($result); // Free up the resources.
mysql_close(); // Close the database connection.
?>

 

This however duplicates the result from the first table depending on how many rows I have in the second table and for some reason the one of the results of each row doesnt become bolded eg:

 

1	title 1
1	title 1
2	title 2
2	title 2

 

Thank you for your patience, I have been stuck on this problem for days.

[GingerRobot]

I would do this with a subquery to select the number of rows where dvd_id = udvd_id. Then bold the records depending on if this is 1 or 0:

 

<?php
include('test_db_connection.php');
$sql = "SELECT `dvd_title`,`dvd_id`, (SELECT COUNT(*) FROM `userdvds` WHERE`udvd_id`=`dvd_id`) as `count` FROM `dvds` ORDER BY `dvd_title` ASC";
$result = mysql_query($sql) or die(mysql_error());
echo '<table><tr><td>DVD ID</td><td>DVD TITLE</td></tr>';
while($row = mysql_fetch_assoc($result)){
	if($row['count'] == 1){
	$id = '<b>'.$row['dvd_id'].'</b>';
	$title = '<b>'.$row['dvd_title'].'</b>';
}else{
	$id = $row['dvd_id'];
	$title = $row['dvd_title'];
}
echo '<tr><td>'.$id.'</td><td>'.$title.'</td></tr>';
}
?>
</table>

 

Produces:

<table><tr><td>DVD ID</td><td>DVD TITLE</td></tr>
<tr><td><b>1</b></td><td><b>title 1</b></td></tr>
<tr><td>2</td><td>title 2</td></tr>
<tr><td><b>3</b></td><td><b>title 3</b></td></tr>
</table>

 

I think thats what you're after.

[mattzo]

thank you very much! That was exactly what I needed