I am using php too.
the query I have is this:
$result=mysql_query('select distinct items.* from items,city,category where items.CityID=city.CityID and items.Category=category.CategoryID');
if (!$result) echo mysql_error();
else {
}
Table structure is this:
Table structure for table 'category'
CREATE TABLE category (
Category varchar(100) DEFAULT '' NOT NULL ,
CategoryID bigint(20) DEFAULT '' NOT NULL auto_increment,
PRIMARY KEY (CategoryID)
);
Table structure for table 'city'
CREATE TABLE city (
City varchar(100) DEFAULT '' NOT NULL ,
CityID bigint(20) DEFAULT '' NOT NULL auto_increment,
PRIMARY KEY (CityID)
);
Table structure for table 'items'
CREATE TABLE items (
ItemSKU varchar(25) DEFAULT '' NOT NULL ,
ItemName varchar(100) DEFAULT '' NOT NULL ,
ItemDescription mediumtext DEFAULT '' NOT NULL ,
PostCode varchar(100) DEFAULT '' NOT NULL ,
Category bigint(20) DEFAULT '0' NOT NULL ,
CityID bigint(20) DEFAULT '0' NOT NULL ,
CTelephone varchar(100) DEFAULT '' NOT NULL ,
ItemID bigint(20) DEFAULT '' NOT NULL auto_increment,
PRIMARY KEY (ItemID)
);
The form that is sending the request is this:
<form action='/companies.php' method='POST' name='CompanySearch'>
<input type='hidden' name='Search' value='YES' />
<input type='hidden' name='CompanySearch' value='Simple' />
<input type='text' class='menuForm' name='ANY' value='Keywords'
maxlength='40' size='13' />
<?
require("connection.php");
mysql_connect("$DBHost", "$DBUser", "$DBPass") or
die("could not connect");
mysql_select_db("$DBName");
echo "<select name=\"CityID\" size=\"1\" class='menuForm'>";
$result=mysql_query("SELECT City, CityID FROM city ORDER BY City");
while ($row = mysql_fetch_array($result)) {
echo "<option value=\"$row[1]\"> $row[0] </option>";
}
echo "</select>";
?>
<?
mysql_connect("$DBHost", "$DBUser", "$DBPass") or
die("could not connect");
mysql_select_db("$DBName");
echo "<select name=\"Category\" size=\"1\" class='menuForm'>";
$result=mysql_query("SELECT Category, CategoryID FROM category ORDER BY Category");
while ($row = mysql_fetch_array($result)) {
echo "<option value=\"$row[1]\"> $row[0] </option>";
}
echo "</select>";
?>
</td>
</tr>
<tr class="navTable">
<td class="bgColorMid">
<table cellpadding="0" cellspacing="0" border="0" width="100%"><tr><td valign="bottom"></td>
<td align="right">
<a href="companies.php" class="submitButton">Search</a>
<a href="companies.php" class="submitButton">
<img src="search.gif" border="0" align="absmiddle" vspace="1" hspace="1"></a>
</tr></table>
</td>
</tr>
</form>
This is driving me nuts can anyone see what I have missed please?
Andrew