Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP and Dreamweaver Recordset Dynamic Dropdown Query and Filter

Posted on 2011-04-23
17
Medium Priority
?
796 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:palmtreeinfotech
  • 10
  • 7
17 Comments
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35455186
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
 

Author Comment

by:palmtreeinfotech
ID: 35457297
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35457459
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:palmtreeinfotech
ID: 35457616
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
 
LVL 70

Accepted Solution

by:
Jason C. Levine earned 2000 total points
ID: 35457686
>> 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
 

Author Comment

by:palmtreeinfotech
ID: 35457950
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
 

Author Comment

by:palmtreeinfotech
ID: 35458017
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35458022
>> 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
 

Author Comment

by:palmtreeinfotech
ID: 35458035
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
 

Author Comment

by:palmtreeinfotech
ID: 35458038
The page is not liking anything with a join there.  Any suggestions?
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35458119
Hard to say without seeing a fully evaluated query.  I'm pretty sure your syntax is off.
0
 

Author Comment

by:palmtreeinfotech
ID: 35458122
That's weird because it parses okay in DW

Look at line 48 that's my query.
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35458126
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
 

Author Comment

by:palmtreeinfotech
ID: 35458128
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35458135
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
 

Author Comment

by:palmtreeinfotech
ID: 35458720
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
 

Author Comment

by:palmtreeinfotech
ID: 35458738
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month21 days, 6 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question