search multiple tables

[russlock]

Hope someone can help as this is now getting me down.

I am using Dreamweaver 8

I have a search form with 2 dropdown tables (categories & type), and they are both showing the correct records when i use them, which are drawn from their own seperate recordsets.

when i search using the "type" dropdown, i get the correct records on the result page, but when i use the category drop-down i get all the records, multiple times, even if they are not in that category.

here is the code for the form.

<form id="searchform" name="searchform" method="get" action="catalog.php?">
<table width="100%" border="2" cellspacing="2" cellpadding="2">
<tr>
<td>SEARCH</td>
</tr>
<tr>
<td>Type:
<label>
<select name="listtype" id="listtype">
<option value=""></option>
<?php
do {

?>
<option value="<?php echo $row_rsType['tID']?>"><?php echo $row_rsType['tname']?></option>
<?php
} while ($row_rsType = mysql_fetch_assoc($rsType));
$rows = mysql_num_rows($rsType);
if($rows > 0) {
mysql_data_seek($rsType, 0);
$row_rsType = mysql_fetch_assoc($rsType);
}
?>
</select>
</label></td>
</tr>
<tr>
<td>Category:
<label>
<select name="listcat" id="listcat">
<option value="" <?php if (!(strcmp("", $row_rsMaster['catname']))) {echo "selected=\"selected\"";} ?>></option>
<?php
do {

?><option value="<?php echo $row_rsCategory['catID']?>"<?php if (!(strcmp($row_rsCategory['catID'], $row_rsMaster['catname']))) {echo "selected=\"selected\"";} ?>><?php echo $row_rsCategory['catname']?></option>
<?php
} while ($row_rsCategory = mysql_fetch_assoc($rsCategory));
$rows = mysql_num_rows($rsCategory);
if($rows > 0) {
mysql_data_seek($rsCategory, 0);
$row_rsCategory = mysql_fetch_assoc($rsCategory);
}
?>
</select>
</label></td>
</tr>
<tr>
<td>Keywords:
<label>
<input name="keywords" type="text" id="keywords" />
</label></td>
</tr>
<tr>
<td><label>
<input type="submit" value="Search" />
</label></td>
</tr>
</table>
</form>

and here is the recordsets code.

<?php
$listcat_rsMaster = "-1";
if (isset($GET['listcat'])) {
$listcat_rsMaster = (get_magic_quotes_gpc()) ? $GET['listcat'] : addslashes($GET['listcat']);
}
$listtype_rsMaster = "-1";
if (isset($GET['listtype'])) {
$listtype_rsMaster = (get_magic_quotes_gpc()) ? $GET['listtype'] : addslashes($GET['listtype']);
}
mysql_select_db($database_mytemplates, $mytemplates);
$query_rsMaster = sprintf("SELECT * FROM templates, type, category WHERE templates.catID = '%s' OR templates.tID = '%s' AND category.catID = templates.catID AND type.tID = templates.tID", $listcat_rsMaster,$listtype_rsMaster);
$rsMaster = mysql_query($query_rsMaster, $mytemplates) or die(mysql_error());
$row_rsMaster = mysql_fetch_assoc($rsMaster);
$totalRows_rsMaster = mysql_num_rows($rsMaster);

mysql_select_db($database_mytemplates, $mytemplates);
$query_rsCategory = "SELECT * FROM category";
$rsCategory = mysql_query($query_rsCategory, $mytemplates) or die(mysql_error());
$row_rsCategory = mysql_fetch_assoc($rsCategory);
$totalRows_rsCategory = mysql_num_rows($rsCategory);

mysql_select_db($database_mytemplates, $mytemplates);
$query_rsType = "SELECT * FROM type";
$rsType = mysql_query($query_rsType, $mytemplates) or die(mysql_error());
$row_rsType = mysql_fetch_assoc($rsType);
$totalRows_rsType = mysql_num_rows($rsType);
?>

any help would be great.

Thanks

[Yegg]

well at first, this query not right:

$query_rsMaster = sprintf("SELECT * FROM templates, type, category WHERE templates.catID = '%s' OR templates.tID = '%s' AND category.catID = templates.catID AND type.tID = templates.tID", $listcat_rsMaster,$listtype_rsMaster);

if you have templates.catID = '%s' DEFINED, so it's trues, so it's enough for OR and other will not done, so your table will not JOINED BY id, to do so you need to use brackets:

$query_rsMaster = sprintf("SELECT * FROM templates, type, category WHERE (templates.catID = '%s' OR templates.tID = '%s') AND category.catID = templates.catID AND type.tID = templates.tID", $listcat_rsMaster,$listtype_rsMaster);

[russlock]

PERFECT, Exactly what I wanted, and so simple to resolve. Thanks ever so much.

Russ