Page 1 of 1
Possible to query an option value query?
Posted: Sun Apr 11, 2004 2:40 pm
by cdickson
My database is comprised of 500 businesses and 280 categories. Each business can choose to belong to as many as 3 categories.
Based on the category selection that a visitor makes, I want to generate a query that will then produce a list of businesses that are in that category.
I have been able to do this on individual PHP pages that are specific to certain categories (i.e. Accommodations or Dining). But this list is so long, I don't really want to create a separate page for each category.
The code that successfully creates the list menu (I know it's DreamweaverMX code - I'll fix it later) is:
Code: Select all
<select name="businessCategory">
<?php
do {
?>
<option value="<?php echo $row_rsCategories['cat_desc1']?>"><?php echo $row_rsCategories['cat_desc1']?></option>
<?php
} while ($row_rsCategories = mysql_fetch_assoc($rsCategories));
$rows = mysql_num_rows($rsCategories);
if($rows > 0) {
mysql_data_seek($rsCategories, 0);
$row_rsCategories = mysql_fetch_assoc($rsCategories);
}
?>
</select>
Is what I am trying to do possible?
Can anyone help me with the code?
Posted: Sun Apr 11, 2004 3:10 pm
by Steveo31
Here's similar code that works:
Code: Select all
<?php
mysql_connect('localhost', 'root') or die(mysql_error());
mysql_select_db('test') or die(mysql_error());
?>
<select name='Choose a company...'>
<?php
$query = mysql_query('SELECT * FROM colours');
while($row = mysql_fetch_assoc($query)){
echo '<option value='.$row['colour_name'].'>'.$row['colour_name'].'</option>';
}
?>
</select>
Maybe the do...while loop is messing up. A while.. does the same thing for the most part.
Posted: Sun Apr 11, 2004 3:21 pm
by cdickson
Thanks, Steveo, but perhaps I didn't make my issue clear.
My list is coming up great. All 280 or so of the categories appear with no problem.
But because the list is generated with a query, what I don't know how to do is query within the query.
For example, one of my categories is 175 - Restaurants. When you are viewing the page and click on the down arrow that generates the list, when someone chooses "Restaurants", how do I get that selection to generate the query (generally speaking)
Code: Select all
$sql="SELECT * FROM Members WHERE cat_id1 = '175' ORDER BY member_name";
Posted: Mon Apr 12, 2004 12:55 pm
by JAM
If I did understand (if not, ignore this post

) , you are likely using something similiar to the code we havve seen sofar:
Code: Select all
while (<your statements here>) {
echo '<option value='.$row['cat_id1'].'>'.$row['cat_name'].'</option>';
}
...a loop echoing the above.
What we do see, is that the above cat_name (ie. Restaurants) is whats being seen, and whats being sendt when posting the form in fact it's cat_id (here; 175).
So after $_POST'ing the form you would recieve the information you need to deal with a 'query within a query'.
You are not thinking about dealing with on-the-fly changes are you? As in that the users selects Restaurants, and something else happens somewhere else?
Posted: Mon Apr 12, 2004 4:14 pm
by cdickson
Apparently I had brain fade and was thinking competely in the wrong direction.
You have me on the right track now

, but I need to work on my results page query.
Thanks!
Posted: Mon Apr 12, 2004 4:30 pm
by JAM
cdickson wrote:Apparently I had brain fade and was thinking competely in the wrong direction. :roll:
Oh yah, I
know that feeling.
cdickson wrote:You have me on the right track now :idea:
Glad to hear that...
cdickson wrote:but I need to work on my results page query.
...as in "JAM, you didn't answer my question right" or "I'll be back with a debriefing"?
cdickson wrote:Thanks!
Welcome...
Posted: Tue Apr 13, 2004 7:38 am
by cdickson
I'm not sure of the answer to your last question
...as in "JAM, you didn't answer my question right" or "I'll be back with a debriefing"?
because I don't know enough yet to know the answer!
In all honesty, I think I may have a different question, but first I need to analyze my tables and the results that I'm looking for so that I can structure the query appropriately.
I may be back...!
Thanks for the welcome.
Posted: Tue Apr 13, 2004 9:36 am
by JAM
Yah well, we're not going anywhere

Posted: Tue Apr 13, 2004 11:28 am
by cdickson
I'm missing something that I can't seem to "logic out" to write the correct query. And I may be making this more complicated than it needs to be.
This is for a group of about 500 members. Each member can select 3 of approximately 280 categories in which to be included.
Here is the background:
2 Tables
- Members
long list of fields that don't matter, plus:
cat_id1
cat_id2
cat_id3
- Category1 (only 2 fields)
cat_id1
cat_desc1
- Category2
cat_id2
cat_desc2
- Category3
cat_id3
cat_desc3
The 3 category tables are identical (I may not need to do this, but thought I did at first).
What I'm trying to do:
- ::Search page - query pulls up list of categories
::Based on the category that the visitor selects on the search page, result should show a list of members that have selected the chosen category as one of their 3
I can generate the list, but I can't get the results right. I have tried several things that don't work. Do I even need the 3 category tables?
Posted: Tue Apr 13, 2004 11:39 am
by kettle_drum
You can just use 1 category table:
cat_id
cat_desc
Then, from what i understand, you want to show all the other members that are a member of the selected category. To do this you of course need the users input, once youve got that you can just do:
Code: Select all
SELECT * FROM members WHERE cat_1 = '$id' OR cat_2 = '$id' OR cat_3 = '$id';
Where $is is the cat_id of what the user selected.
You could also clean up the database by in members have only one field for the cats and just seperate the cat_id by a comma or pipe |. And then in the sql query use "WHERE cat LIKE '%|$id|%'" -for that you would of course need the field to be like "|id1|id2|id3|".
Posted: Tue Apr 13, 2004 2:15 pm
by cdickson
I'm having a heck of a time getting one file to "talk" to the other. If my first page (nameSearch.php) query is:
Code: Select all
<?php require_once('../Connections/hschamber.php'); ?>
<?php error_reporting(E_ALL); ?>
<?php
foreach ($_GET as $key => $value) {
$_GET[$key] = trim($value);
}
mysql_select_db($database_hschamber, $hschamber);
$query_rsCategories = "SELECT * FROM Category1 ORDER BY cat_desc1";
$rsCategories = mysql_query($query_rsCategories, $hschamber) or die(mysql_error());
$row_rsCategories = mysql_fetch_assoc($rsCategories);
$totalRows_rsCategories = mysql_num_rows($rsCategories);
?>
---------and---------
<select name="searchCategory">
<?php
do {
?>
<option value="<?php echo $row_rsCategories['cat_desc1']?>"><?php echo $row_rsCategories['cat_desc1']?></option>
<?php
} while ($row_rsCategories = mysql_fetch_assoc($rsCategories));
$rows = mysql_num_rows($rsCategories);
if($rows > 0) {
mysql_data_seek($rsCategories, 0);
$row_rsCategories = mysql_fetch_assoc($rsCategories);
}
?>
</select>
then which variable do I use in the results page query for the
$id? I thought my query results were defined as $rsCategories, but in desperation I also tried using $row_Categories. The code for my results page is:
Code: Select all
<?php //require_once('../Connections/hschamber.php'); ?>
<?php
$maxRows_rsCategorySearch = 100;
$pageNum_rsCategorySearch = 0;
if (isset($_GET['pageNum_rsCategorySearch'])) {
$pageNum_rsCategorySearch = $_GET['pageNum_rsCategorySearch'];
}
$startRow_rsCategorySearch = $pageNum_rsCategorySearch * $maxRows_rsCategorySearch;
mysql_select_db($database_hschamber, $hschamber);
$query_rsCategorySearch = "SELECT * FROM Members WHERE cat_id1 = '$id' OR cat_id2 = '$id' OR cat_id3 = '$id';";
$query_limit_rsCategorySearch = sprintf("%s LIMIT %d, %d", $query_rsCategorySearch, $startRow_rsCategorySearch, $maxRows_rsCategorySearch);
$rsCategorySearch = mysql_query($query_limit_rsCategorySearch, $hschamber) or die(mysql_error());
$row_rsCategorySearch = mysql_fetch_assoc($rsCategorySearch);
if (isset($_GET['totalRows_rsCategorySearch'])) {
$totalRows_rsCategorySearch = $_GET['totalRows_rsCategorySearch'];
} else {
$all_rsCategorySearch = mysql_query($query_rsCategorySearch);
$totalRows_rsCategorySearch = mysql_num_rows($all_rsCategorySearch);
}
$totalPages_rsCategorySearch = ceil($totalRows_rsCategorySearch/$maxRows_rsCategorySearch)-1;
?>
Where am I goofing?

Posted: Tue Apr 13, 2004 6:11 pm
by JAM
Shouldn't that be used somewhere?
Btw, try to stick to the orignal post. I get the feeling that we now are drifting away abit on the original subject and if so, do make a new post with the new question.
Not flaming you, but it makes it easier to follow and understand (especially for the beginners trying to learn).
Posted: Wed Apr 14, 2004 3:46 pm
by cdickson
Thanks, JAM!
I finally got it - had a couple more changes to make to get the results that I wanted.
Sorry for veering from the subject of the original post.

I will be sure to keep that in mind for future reference.