Page 1 of 1

Query Problems using a drop down PHP Form

Posted: Tue Feb 25, 2003 8:04 am
by meandrew
I have a query that is extracting records from a MySQL DB at the moment the query is just pulling 'all' records and not the selected records from a drop down :(

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

Posted: Tue Feb 25, 2003 9:59 am
by volka
<a href="companies.php" class="submitButton">Search</a>
that will not submit the form (with its values). Afaik you need some kind of "submit" element such as <input type="submit" />

You query string does not contain any clause that includes the submitted values.

Query Problems using a drop down PHP Form

Posted: Tue Feb 25, 2003 10:22 am
by meandrew
well blow me down with a feather - all this high level proramming and one of the main problems it appears ifs that the sumit value is out of sync

Volka you are a star :)

ok now that some values are actually being passed now can we solve this drop down serch please? :)

Andrew

Posted: Tue Feb 25, 2003 5:11 pm
by bionicdonkey