Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Drill down through recordset. 1

Status
Not open for further replies.
petrosky

petrosky

Technical User
Aug 1, 2001
512
AU
Hi there,

I am trying to produce a form that POSTs to itself and limits the recordset used to display the page. Here is what is working thus far...

Code: 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html>
<head>
<title>Product Enquiry Emulator</title>
</head>
<body>
<table width="50%" border="1">
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  <tr>
    <td>Product Search:</td>
    <td></td>
  </tr>
  <tr>
    <td><label>Search
        <input type="text" name="prodSearch" id="prodSearch" />
    </label></td>

    <td>
      <input type="submit" name="submit" id="submit" value="Submit" />
    </td>
  </tr>
  </form>
</table>

<?php
// This page will be a product search emulator.

include('conn/connect.php');
include('conn/database.class.php');

if (isset($_POST['prodSearch'])){
    $prodSearch = $_POST['prodSearch'];
}

// My Conntection values.
$db = new Database($config['server'], $config['user'], $config['pass'], $config['database'], $config['tablePrefix']);
// connect to the server
$db->connect();

$sql = "select * from d3_import WHERE rsv_desc LIKE '%$prodSearch%'";

$result = mysql_query($sql) or die (mysql_error());

while ($row = mysql_fetch_row($result)){
    echo $row[0]. ' ' . $row[1] . '<br/>';
}

mysql_free_result($result);
?>

</body>
</html>

This works great for one submission...then of course the next time the page is submitted the value of $prodSearch is the last entered value.

What I would like is a method of allowing the users to "drill down" into the recordset/s.

Sample Table Data

Code: 
rsv_desc     cost    inv
Canon EOS 5  100.00  10
Pentax X     200.00   5
Canon EOS 10 500.00   1
Canon IXUS    49.00   20

In my ideal world...the use would be able to type in "Canon" submit, then type in EOS and the recordset would limit down to only the two CANON EOS Products in the example.

So...my SQL would look something like the following.

Code: 
$sql = "select * from d3_import WHERE rsv_desc LIKE '%$prodSearch%' AND LIKE '%$othervariable%'";

My question is how can I go about storing the other variable/s and tacking it on to the end of my select...session variables? Hidden fields? Javascript?

As you may tell, I am new to PHP/MYSQL and I would appreciate any ideas on this.

Thanks in advance.

Peter.

Remember- It's nice to be important,
but it's important to be nice :)
 
Sort by date Sort by votes
either by creating forms or links, are the traditional methods. If the variables are posted they will be accessible in the $_POST superglobal, if they are query vars: $_GET

for example

Code: 
Canon EOS 5  100.00  10 <form action="someplace" method="post"><input type="hidden" name="somevariable" value="somevalue"/><input type="submit" name="x_submit" value="Drill Down"/></form>

or
Code: 
Canon EOS 5  100.00  10  <a href="somepage?action=drilldown&variablename=variablevalue">Drill Down</a>
 
Upvote 0 Downvote
I would use sessions to store the searched terms, that way you can use them to drill down.

Basically each time you get a search term you save it into your session var. Then you offer a new button to "Search these results" . This makes it simple to reset the search or keep using it. As you can check which button was used.

The sessions can keep all your search terms until the new search button is clicked or until it gets destroyed by leaving the page.





----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
For this kind of thing I use JS, AJAX and session variables - Depending on needs. One a project I am working on, I have set a filter which is sensitive to the module you are in. So, if you click on the filter option, a layer is brought to the front with a number of fields to use as filters. Values are retained since I keep them in session variables.

Content of layer changes based on module ... Data shown on views are automatically limited to whatever the filters are set.

All that being said, I think that the combination of some basic JS + AJAX will get the job done. Use the onkeyup element to trigger a JS. The JS in turn will create a variable by concatenating the strings into one or simply pass them all as individual parameters.

In the PHP side, the script which will be called through AJAX will accept these parameters, run the query and return the results.

I use XAJAX (a PHP classs) and I can place the results on a layer right from my PHP code.

The returned results is then placed on a holding layer and the layer property must be changed to display="block"

The layer, now showing a nicely set grid, should be visible and the user now has the option to choose from this grid a match to his/her interest.

It is important that the grid has a trigger (onclick="...") so that when the option is clicked on, a JS is triggered to manipulate passed information, parse it and populate fields as needed.

Makes sense? Some months ago I posted code here to dynamically populate (change) combo box based on options chosen from other boxes using this very method.

Hope this helps!




--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.
 
Upvote 0 Downvote
Hi there,

Thank you all for your valuable help. I followed vacunita's advice and used a session variable which stored an array of the search items as entered.

Then I included a reset button to unset the session array variable.

Code: 
if (isset($_POST['reset'])){
    unset($_SESSION['search']);
}

if (!isset($_SESSION['search'])){
    $_SESSION["search"]= array();
}
if (isset($_POST['prodSearch']) &&!empty($_POST['prodSearch'])) {
 $product = $_POST['prodSearch'];
 $_SESSION['search'][] = $product;
}

Then my SQL statement became this.
Code: 
$sql = "select * from d3_import WHERE rsv_desc LIKE '%" . $_SESSION['search'][0]. "%' AND rsv_desc LIKE '%" . $_SESSION['search'][1]. "%' AND rsv_desc LIKE '%" . $_SESSION['search'][2]. "%' AND rsv_desc LIKE '%" . $_SESSION['search'][3]. "%'";

Which gives users 4 drill down levels.

It seems a bit of a slap together in the end but does what I need it to do.

Can anyone see any way to improve the above?

Thanks again,

Peter.


Remember- It's nice to be important,
but it's important to be nice :)
 
Upvote 0 Downvote
Other than automating the actual query it looks fine try something like:

Code: 
$sql = "select * from d3_import WHERE ";
$count=0;
foreach ($_SESSION['search'] as $term){
if($count>0){ $sql.=" AND "; }
$sql.= "rsv_desc LIKE '%" . $term . "%'";
}

That way you can have as many drill down levels as you need, without needing to code them all explicitly:

Which by the way in the event you had less than all 4 session vars defined, in your example you should be getting at the very least notices about it from PHP.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
Hi vacunita,

Thanks again for your help and guidance.

I seemed to have to add a catch all in for when the user first lands on the page.

Code: 
if (empty($_POST['prodSearch'])){
$sql = "SELECT * FROM d3_import";
}
elseif (!empty($_POST['prodSearch'])){
$count = 0;
$sql = "SELECT * FROM d3_import WHERE ";
foreach ($_SESSION['search'] as $term){
if($count>0){ $sql.=" AND "; }
$sql.= " rsv_desc LIKE '%" . $term . "%'";
$count++;
}
}

Now there is no limit to the levels of drill downs.

Thank you.

Peter.

Remember- It's nice to be important,
but it's important to be nice :)
 
Upvote 0 Downvote
Glad I could help.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
I seem to have totally misunderstood OP. I was under the impression that OP meant that data of each subsequent element will be dependent on the data entered on previous elements.

Glad to see vacunita was on target and OP was able to get problem solved!


--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.
 
Upvote 0 Downvote
Hi southbeach,

It is an instore product catalogue with only approx 15K records. Our sales guys can log in and then search quickly for their product.

It is emulating a PICK system which does pretty much what the above does.

I did look at your recommended thread but opted to use session variable (for my first time!)

Thanks again.

Peter.

Remember- It's nice to be important,
but it's important to be nice :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Pudsters
  • Locked
  • Question
Shorten code to loop through array? 1
Replies
4
Views
190
Pudsters
Pudsters
pc3957
  • Locked
  • Question
Any ideas how I can download a file from a web site that requires login mimicking clicks
Replies
1
Views
130
spamjim
spamjim
someoneneedshelps
  • Locked
  • Question
Fall through switch statement
Replies
1
Views
74
someoneneedshelps
someoneneedshelps
mikeba1
  • Locked
  • Question
drop down list
Replies
4
Views
110
southbeach
southbeach
PCHomepage
  • Locked
  • Question
Looping Through Data, Replacing String
Replies
1
Views
84
PCHomepage
PCHomepage

Part and Inventory Search

Sponsor

Back
Top