PHP and Dreamweaver Recordset Dynamic Dropdown Query and Filter

I have a page for instance:

http://mysite.com/search_results.php

That I have filtered on the recordset such as this:

http://mysite.com/search_results.php?state=NY&zipcode=00501

and it's working fine.  What I want to do is then filter based on category, for instance, "Category1" or "Category2".

Can I use a dynamic drop down for this and how do I apply it to the page so it is still filtering what is above but ALSO the specific category? or should I just add the category to the search string somehow?  But how do I filter that when it changes?

I'm assuming I have the drop down right above the recordset and whatever the user chooses, it will go to that category?

Thanks experts
palmtreeinfotechAsked:
Who is Participating?
 
Jason C. LevineConnect With a Mentor No oneCommented:
>> it's a DW recordset below the dynamic drop down

If you are using a drop-down, how is the drop down choice communicated back to the server?

(hint: it's a form :)

So the form looks a little like this:

<form method="get" action="search_results.php">
(code for dynamic category field here)
(code for submit button here)
</form>

Now, when submitted the page needs to do a few different things:

1) Check to see if the form has been submitted and the variable is present ( if isset() )
2) Filter a second recordset based on the new variable submitted (Basic DW recordset using the static parameters from the first query string)
3) Switch from the initial all-results recordset to a different recordset that uses category as an additional filter. (if isset() or if recordset is not empty )

#3 is the most challenging aspect to this, but the overall flow is pretty simple.  You have two recordsets defined and two repeat regions showing results.  When $_GET['category'] is set, show the second region.

<?php if(isset($_GET['category'])) { ?>

second repeat region code here

<?php } ?>
0
 
Jason C. LevineNo oneCommented:
Hi palmtreeinfotech,

Do you want to add an extra field to the search form or add the field to the results page only?

>> or should I just add the category to the search string somehow?

In either case above, that's how you will do it.  There will be a second form on the page that uses a recordset to build the dynamic dropdown (SELECT DISTINCT table.category FROM table ORDER table.category ASC) and you submit that form with the GET method to add the results to the query string that is filtering the search results.
0
 
palmtreeinfotechAuthor Commented:
Hi Jason!  Let me clarify sir.  I'm may not be using a form to filter results, instead using the

http://mysite.com/search_results.php?state=NY&zipcode=00501

as a direct link.  I'm going to have a usa map for instance that when they click on it, it will filter state and county, but I have a category in there too that I'm using.  Basically I'm trying to use only one php page for every state and county and category instead of having different results pages for each (which would be about 4,000 php pages as that's how many counties there are)..

So I was thinking about using the direct link above, then letting the user filter out the category as well.  Already having the state and county filter, I would just need to filter the category now.  So would I have the dynamic drop down above the recordset somehow to filter?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Jason C. LevineNo oneCommented:
Even if you don't use a form to do the initial search, you will be a using a form on search_results.php to further filter by category.  It's a simple form submit with the get method and that will add a new parameter to the query string.
0
 
palmtreeinfotechAuthor Commented:
I'm thinking (and I may be off here) that it's not actually a form, it's a DW recordset below the dynamic drop down.  How does that work?  How do I tell the search_results.php page to add the dynamic drop down to the search results?
0
 
palmtreeinfotechAuthor Commented:
Hey Jason.  I have attached my code.  Can you please tell me what I"m doing wrong?  In other words, I'm getting this error now on the page:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 1

Not sure what that means.  In DW the query parses okay and it runs.

Am I on track with the recordsets getting the dropdown to work??
<?php
if(isset($_GET["category"])) $mysql_QS.="WHERE ";
if(isset($_GET["category"])) $mysql_QS.="category='".$_GET["category"]."' "; 
if(isset($_GET["state"])) $mysql_QS="WHERE ";
if(isset($_GET["state"])) $mysql_QS.="state='".$_GET["state"]."' ";
if(isset($_GET["zipcode"])) $mysql_QS="WHERE ";
if(isset($_GET["zipcode"])) $mysql_QS.="zipcode='".$_GET["zipcode"]."' ";

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

mysql_select_db($database_connection2, $connection2);
$query_Recordset1 = "SELECT * FROM zipcodes $mysql_QS INNER JOIN job_categories jc on jc.category_id = $mysql_QS.category";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $connection2) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
  $all_Recordset1 = mysql_query($query_Recordset1);
  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$pageNum_Recordset2 = 0;
if (isset($_GET['pageNum_Recordset2'])) {
  $pageNum_Recordset2 = $_GET['pageNum_Recordset2'];
}
$startRow_Recordset2 = $pageNum_Recordset2 * $maxRows_Recordset2;

mysql_select_db($database_connection2, $connection2);
$query_Recordset2 = "SELECT * FROM job_categories ORDER BY category_name ASC";
$Recordset2 = mysql_query($query_Recordset2, $connection2) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
?>
<form id="form1" name="form1" method="get" action="testzipcode2.php">
  <label for="category"></label>
  <select name="category" id="category">
    <?php
do {  
?>
    <option value="<?php echo $row_Recordset2['category_id']?>"><?php echo $row_Recordset2['category_name']?></option>
    <?php
} while ($row_Recordset2 = mysql_fetch_assoc($Recordset2));
  $rows = mysql_num_rows($Recordset2);
  if($rows > 0) {
      mysql_data_seek($Recordset2, 0);
	  $row_Recordset2 = mysql_fetch_assoc($Recordset2);
  }
?>
  </select>
  <input type="submit" name="button" id="button" value="Submit" /> 
  Select Category
</form>
<?php if ($totalRows_Recordset2 > 0) { // Show if recordset not empty ?>
  recordset 1<br />
  <table width="917" border="1" cellpadding="2" cellspacing="2">
    <tr>
      <td width="182">zipcode</td>
      <td width="148">city</td>
      <td width="135">state</td>
      <td width="70">cat</td>
      <td width="132">latitude</td>
      <td width="198">longitude</td>
    </tr>
    <?php do { ?>
      <tr>
        <td><?php echo $row_Recordset1['zipcode']; ?></td>
        <td><?php echo $row_Recordset1['city']; ?></td>
        <td><?php echo $row_Recordset1['state']; ?></td>
        <td><?php echo $row_Recordset1['category_name']; ?></td>
        <td><?php echo $row_Recordset1['latitude']; ?></td>
        <td><?php echo $row_Recordset1['longitude']; ?></td>
      </tr>
      <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
  </table>
  <?php } // Show if recordset not empty ?>
<p>recordset2</p>
<table width="917" border="1" cellpadding="2" cellspacing="2">
    <tr>
      <td width="182">zipcode</td>
      <td width="148">city</td>
      <td width="135">state</td>
      <td width="70">cat</td>
      <td width="132">latitude</td>
      <td width="198">longitude</td>
  </tr>
    <?php do { ?>
    <tr>
      <td><?php echo $row_Recordset1['zipcode']; ?></td>
      <td><?php echo $row_Recordset1['city']; ?></td>
      <td><?php echo $row_Recordset1['state']; ?></td>
      <td><?php echo $row_Recordset1['category_name']; ?></td>
      <td><?php echo $row_Recordset1['latitude']; ?></td>
      <td><?php echo $row_Recordset1['longitude']; ?></td>
    </tr>
    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
<p>
  <?php
//if(isset($_GET["state"])) $mysql_QS="WHERE ";
//if(isset($_GET["state"])) $mysql_QS.="state='".$_GET["state"]."' ";

//.... all other criterias 

//Your queryies now should be like 
//$resultset="SELECT * FROM zipcodes $mysql_QS";

mysql_free_result($Recordset1);

mysql_free_result($Recordset2);
?>
</p>

Open in new window

0
 
palmtreeinfotechAuthor Commented:
Jason messed around a little bit while waiting on your response and I think I got it working now, the dropdown that is.  But when I use the drop-down it removes the other filters on there ?state=NY, etc.

Still having a JOIN SQL error.  Recordset1 doesn't like the inner join or join much on the php page.
0
 
Jason C. LevineNo oneCommented:
>> But when I use the drop-down it removes the other filters on there ?state=NY, etc.

So you need to add some code that saves the initial variables from the link and uses those in the recordsets instead of relying on the URL to never change...

if(isset($_GET['state'])) { $state = $_GET[state]; }

>> SQL

INNER JOIN job_categories jc

job_categories space jc ?
0
 
palmtreeinfotechAuthor Commented:
OK I will give it a shot..

jc is the table alias but it may not like the alias part.  I will try to use the table name there.
0
 
palmtreeinfotechAuthor Commented:
The page is not liking anything with a join there.  Any suggestions?
0
 
Jason C. LevineNo oneCommented:
Hard to say without seeing a fully evaluated query.  I'm pretty sure your syntax is off.
0
 
palmtreeinfotechAuthor Commented:
That's weird because it parses okay in DW

Look at line 48 that's my query.
0
 
Jason C. LevineNo oneCommented:
No, I see that line.  But you have a couple of variables being added to the query so I don't really know how it ends up.

DW probably isn't fully evaluating it either, so it lets things go.
0
 
palmtreeinfotechAuthor Commented:
Gotcha about DW.  Yeah I'm getting a syntax error when looking at the php page from the browser.   Query just not liking anything with a join.

That "$' part of the query before the join for the variable, DW put that in there as it's referencing the isset at the top.
0
 
Jason C. LevineNo oneCommented:
What you may want to do is make a copy of this page for backup and then comment out lines 49-59

In the body, do echo $query_Recordset1 to get the actual SQL being parsed.
0
 
palmtreeinfotechAuthor Commented:
OK Jason, I am down to one recordset really.  Just need help on a couple more things if you have time!

I don't think the dropdown worked because well I had no WHERE statement.  However the WHERE statement and form variable isn't working...NOW I figured that out.  I was using $POST_ instead of $GET in the param box.  So that's working.

Just have a question regarding saving the variables in the page so the $GET doesn't overwrite itself.

For instance like above search.php?state=NY&county=Cook&category=3&button=Submit

When I change the category, it's leaving the state and county off the filter.

I think this will do it after this answer sir!
0
 
palmtreeinfotechAuthor Commented:
Here is the code that I did so still waiting on how to change the filters and keep them!
<?php require_once('Connections/connection2.php'); ?>
<?php
//if(isset($_GET["category"])) $mysql_QS.="WHERE ";
//if(isset($_GET["category"])) $mysql_QS.="category='".$_GET["category"]."' "; 
if(isset($_GET["state"])) $mysql_QS="WHERE ";
if(isset($_GET["state"])) $mysql_QS.="state='".$_GET["state"]."' ";
if(isset($_GET['state'])) { $state = $_GET['state']; }
if(isset($_GET["zipcode"])) $mysql_QS="WHERE ";
if(isset($_GET["zipcode"])) $mysql_QS.="zipcode='".$_GET["zipcode"]."' ";

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

mysql_select_db($database_connection2, $connection2);
$query_Recordset1 = "SELECT * FROM zipcodes $mysql_QS";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $connection2) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
 $all_Recordset1 = mysql_query($query_Recordset1);
 $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}

$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
echo $query_Recordset1;

$pageNum_Recordset2 = 0;
if (isset($_GET['pageNum_Recordset2'])) {
  $pageNum_Recordset2 = $_GET['pageNum_Recordset2'];
}
$startRow_Recordset2 = $pageNum_Recordset2 * $maxRows_Recordset2;

mysql_select_db($database_connection2, $connection2);
$query_Recordset2 = "SELECT * FROM job_categories ORDER BY category_name ASC";
$Recordset2 = mysql_query($query_Recordset2, $connection2) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

$colname_Recordset1 = "-1";
if (isset($_GET['category'])) {
  $colname_Recordset1 = $_GET['category'];
}
mysql_select_db($database_connection2, $connection2);
$query_Recordset1 = sprintf("SELECT * FROM zipcodes $mysql_QS INNER JOIN job_categories jc on jc.category_id = category WHERE category = %s ", GetSQLValueString($colname_Recordset1, "int"));
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $connection2) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
  $all_Recordset1 = mysql_query($query_Recordset1);
  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
echo $query_Recordset1
?>
<form id="form1" name="form1" method="get" action="testzipcode2.php">
  <label for="category"></label>
  <select name="category" id="category">
    <?php
do {  
?>
    <option value="<?php echo $row_Recordset2['category_id']?>"><?php echo $row_Recordset2['category_name']?></option>
    <?php
} while ($row_Recordset2 = mysql_fetch_assoc($Recordset2));
  $rows = mysql_num_rows($Recordset2);
  if($rows > 0) {
      mysql_data_seek($Recordset2, 0);
	  $row_Recordset2 = mysql_fetch_assoc($Recordset2);
  }
?>
  </select>
  <input type="submit" name="button" id="button" value="Submit" /> 
  Select Category
</form>

<p>recordset 1<br />
</p>
<table border="1" cellpadding="2" cellspacing="2">
  <tr>
    <td>zipcode</td>
    <td>city</td>
    <td>state</td>
    <td>latitude</td>
    <td>longitude</td>
    <td>category</td>
  </tr>
  <?php do { ?>
    <tr>
      <td><?php echo $row_Recordset1['zipcode']; ?></td>
      <td><?php echo $row_Recordset1['city']; ?></td>
      <td><?php echo $row_Recordset1['state']; ?></td>
      <td><?php echo $row_Recordset1['latitude']; ?></td>
      <td><?php echo $row_Recordset1['longitude']; ?></td>
      <td><?php echo $row_Recordset1['category_name']; ?></td>
    </tr>
    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
<p>&nbsp;
  Records <?php echo ($startRow_Recordset1 + 1) ?> to <?php echo min($startRow_Recordset1 + $maxRows_Recordset1, $totalRows_Recordset1) ?> of <?php echo $totalRows_Recordset1 ?> </p>
<p>
  <?php
//if(isset($_GET["state"])) $mysql_QS="WHERE ";
//if(isset($_GET["state"])) $mysql_QS.="state='".$_GET["state"]."' ";

//.... all other criterias 

//Your queryies now should be like 
//$resultset="SELECT * FROM zipcodes $mysql_QS";

mysql_free_result($Recordset1);

mysql_free_result($Recordset2);
?>
</p>

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.