[Resolved] query form variable, can you help?

rhock

In the query below... the word Dentists is a column (category) in a database... how do I make a form that will create a dropdown menu populated by said database column?...i.e.,so when the submit button is hit the query will execute and the results will be returned for the "category" that was selected?

Here is the query...

mysql_query("SELECT `name` , `address` , `city` , `phone` , `www` ,`subcatid` FROM `bus_list` WHERE 1 AND `category` LIKE '%Dentists%'  ORDER BY name ASC LIMIT $page, $limit");

TIA

Installer

You could do it something like this:

if (!isset($_POST['submit'])) {
    echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="post">' .
         '<select name="dentists">' .
         '<option value="prosthodontist">Prosthodontist</option>' .
         '<option value="orthodontist">Orthodontist</option>' .
         '<option value="retrodontist">Retrodontist</option>' .
         '<input type="submit" name="submit" value="submit" />' .
         '</select></form>';
} else {
    $query = ("SELECT `name` , `address` , `city` , `phone` , `www` ,`subcatid` FROM `bus_list` WHERE 1 AND `category` LIKE '$_POST[dentists]'  ORDER BY name ASC LIMIT $page, $limit");
    $result = msql_query($query);
    while ($row = mysql_fetch_array($result) {
        echo $row['name'];
        // etc.
    }
}

rhock

Originally posted by Installer
You could do it something like this:

if (!isset($_POST['submit'])) {
    echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="post">' .
         '<select name="dentists">' .
         '<option value="prosthodontist">Prosthodontist</option>' .
         '<option value="orthodontist">Orthodontist</option>' .
         '<option value="retrodontist">Retrodontist</option>' .
         '<input type="submit" name="submit" value="submit" />' .
         '</select></form>';
} else {
    $query = ("SELECT `name` , `address` , `city` , `phone` , `www` ,`subcatid` FROM `bus_list` WHERE 1 AND `category` LIKE '$_POST[dentists]'  ORDER BY name ASC LIMIT $page, $limit");
    $result = msql_query($query);
    while ($row = mysql_fetch_array($result) {
        echo $row['name'];
        // etc.
    }
}

[/B]

Thanks for the reply...
This code is still using a hard coded query "Dentists"...I am looking for a form that will choose a variable and pass it (variable) to the query...????

sorry if I am missing something???

here is the form:

I removed this form code b/c it would not wrap

leatherback

Hi,

Yes. you are missing something. Not how he used $_POST['dentists'] in the query.

This means that in the form you have a field called denstists, and whatever value was selected there, will be in the variable $_POST['dentists'] after the form has been submitted. That way it has become a non-fixed query. Of course you should check the variable first, and see that there is no coding in the variable.

As for the form.. Please realize that the correct form for an option in a drop-down menu is:
<option value = "" > blabla </option>

You might want to strcuture your coding somewhat...

Installer

OK, I think I get it now. I was confused when you said "Dentists is a column...".
I had figured that out, but not completely. You meant that "Dentists"
is an example of a piece of data in the column "category", right? And "Accountants",
"Adoption Agencies", etc. would be other examples? If so, then just put your form
inside the "if" block of my code, and, in the else block, change
"LIKE '$POST[dentists]'" to "LIKE '$POST[category]'".

[Lines broken up for easier reading (less horizontal scrolling).]

rhock

Sorry but I am lost...

I thought there would be an easy way to pass a selection from the form (dropdown menu) I posted...into the query that is part of my script...

Is there not a way to pass the selection to the script and have it execute the script/query accordingly?

rhock

OK, I think I get it now. I was confused when you said "Dentists is a column...".

Exactly...so:

category LIKE '$_POST[dentists]'

should be : ???

rhock

Thanks for the efforts...

I am still lost... the suggestion given seems to be part of a script action? below is more of my existing script:

}

if (!($limit)){
$limit = 10;} // Default results per-page.
if (!($page)){
$page = 0;} // Default page value.
$numresults = mysql_query("SELECT `name` , `address` , `city` , `phone` , `www` ,`subcatid` FROM `bus_list` WHERE 1 AND `category` LIKE '%Dentists%' "); // the query.
$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
if ($numrows == 0){
echo("No results found matching your query - $query"); 
exit();}

I am looking for a seperate (html) form that will send the menu selection to the script (seperate document) is this possible?

Installer

Just like I said in my last post: [QUOTE}...change "LIKE '$POST[dentists]'" to "LIKE '$POST[category]'".[/QUOTE]("category" also the name you gave to your form "select" block, right?)

leatherback

Hi rhock,

As I mentioned before:

In your form you have a field, say category
After submitting this form the selected value will be in $_POST['category']
So in the query you can:

select * from MYTABLE where CATEGORY_COLUM = $_POST['category']

Installer

so when the submit button is hit the query will execute and the results will be returned for the "category" that was selected?

The code I posted is example code, just to show you how it might be done. I think it does that, now you need to substitute your own form data and query result code (such as what you just posted; that's really immaterial to the nuts and bolts of how to do it.)

Installer

leatherback said:

select * from MYTABLE where CATEGORY_COLUM = $_POST['category']

That would be a better query, but it comes down to the way you do it. And like we've been saying is the way to do it.

rhock

Originally posted by Installer
leatherback said:That would be a better query, but it comes down to the way you do it. And like we've been saying is the way to do it.

what makes it a "better" query? faster?

Thanks again for the efforts...I have it working thanks to the all the help I got on here...

rhock

I was wrong...I thought I had it working...

the first query works but whatever I did messed up the pagination action... when you click the link for the next page it does not display anything...

evidently it is not retaining the value previously chosen???

leatherback

Nope,

It is not retaining the previously chosen link.

An easy way to deal with this, is writing a small function which will try to retrieve the varible from both the $POST and the $GET array. Then you parse it into your query.

In the link you then also have to include the category, like:
link.php?category=$category