php results from form - stuck
 

So, this is a "next step" for me learning how to pass multiple variables from a form to a database and publish the results. I feel that I'm *holds fingers in air* this close, but the actual solution is alluding me.

Goal: To have user select from dropdown list of 4 variables and have the query show results of that query. If there is nothing selected, show all.

Knowledge: Quite used to working and modifying other peoples scripts. First time connecting raw script to a database. I've moderate knowledge of passing variables with GET and POST from form to another page. And have knowledge of some PHP scripting and syntax.

Form Script:

<form action="houseplan_results.php" method="get" name="house plans" id="house plans">

<div class="houseplans">

  Type of House Plan: 

  <select name="houseplans">

    <option selected="selected" value="All Types">All Types</option>

    <option value="Beach/Coastal">Beach/Coastal</option>

    <option value="Bungalow">Bungalow</option>

    <option value="Contemporary">Contemporary</option>

    <option value="Country">Country</option>

    <option value="Craftsman">Craftsman</option>

    <option value="European">European</option>

    <option value="French">French</option>

    <option value="Modern">Modern</option>

    <option value="Northwest">Northwest</option>

    <option value="Ranch">Ranch</option>

    <option value="Victorian">Victorian</option>

    <option value="Commercial">Commercial</option>

  </select>

</div>

<div class="listbox">

Number of Bedrooms: 

<select name="bedrooms">

                                <option selected="selected" value="">Any</option>

                                <option value="1">1</option>



                                <option value="2">2</option>

                                <option value="3">3</option>

                                <option value="4">4</option>

                                <option value="5">5</option>

                                <option value="6">6</option>

                                <option value="7">7</option>

                                <option value="13">13</option>

        </select>

</div>        



<div class="listbox">

Number of Bathrooms:

<select name="bathrooms">

                                <option selected="selected" value="">Any</option>

                                <option value="0">0</option>

                                <option value="1">1</option>



                                <option value="1.5">1.5</option>

                                <option value="2">2</option>

                                <option value="2.5">2.5</option>

                                <option value="3">3</option>

                                <option value="3.5">3.5</option>

                                <option value="4">4</option>



                                <option value="4.5">4.5</option>

                                <option value="4.5 +.5">4.5 +.5</option>

                                <option value="5.5">5.5</option>

                                <option value="7">7</option>

                                <option value="7.5">7.5</option>

                                <option value="12.5">12.5</option>





        </select>

</div>



<div class="listbox">

  Number of Garages: 

  <select name="garages">

    <option selected="selected" value="">Any</option>        

    <option value="0">0</option>

    <option value="1">1</option>

    <option value="2">2</option>

    <option value="3">3</option>

    <option value="4">4</option>

    <option value="5">5</option>

    <option value="6">6</option>

    <option value="7">7</option>

  </select>

</div>                                        

<input name="Search" type="submit" />

</form>

and Result Page/Query Page. (note I do have mysql connection page setup and that is working)

<?php require_once('Connections/plansdb.php'); ?>





<?php

//variables from submit form

$houseplans = $plantypes ;

$bedrooms  = $bed ;

$bathrooms = $bath ;

$garages  = $garage ;



// Query DB

$query = sprintf("SELECT PLANNO, PLANTYPES, DESC, BED, BATH, GARAGE, FLOORS, URL, IMG FROM plans WHERE PLANTYPES='%s' AND BED='%s' AND BATH='%s' AND GARAGE='%s'",

   mysql_real_escape_string($plantypes),

   mysql_real_escape_string($bed),

   mysql_real_escape_string($bath),

   mysql_real_escape_string($garage));



// Perform Query

$result = mysql_query($query);



// Check result

// This shows the actual query sent to MySQL, and the error. Useful for debugging.

if (!$result) {

   $message  = 'Invalid query: ' . mysql_error() . "\n";

   $message .= 'Whole query: ' . $query;

   die($message);

}



// Use result

while ($row = mysql_fetch_assoc($result)) {

   echo $row['PLANNO'];

   echo $row['PLANTYPES'];

   echo $row['DESC'];

   echo $row['BED'];

   echo $row['BATH'];

   echo $row['GARAGE'];

   echo $row['FLOORS'];

   echo $row['URL'];

   echo $row['IMG'];

}



mysql_free_result($result);

?>

resource PHP.net mysql_query()


So - after a good attempt, I'm stuck. I'm thinking I've got my variables all mixed up - but need a tip/direction/solution on how to fix this.

Thanks
Jade

Re: php results from form - stuck
 

I would recommend changing your form method to 'post' instead of 'get' and then you just need to pull the variables in from the $_POST[] array and you should be good to go. I think you want to do this

//variables from submit form

$houseplans = $_POST['houseplans'];

$bedrooms  = $_POST['bedrooms'];

$bathrooms = $$_POST['bathrooms'];

$garages  = $_POST['garages'];

and change your sql parameters to use those variables.

If you want to stick with GET for your form method, just change the $_POST refs to $_GET.

Re: php results from form - stuck
 

I'm getting this error.

I also added a check to make sure I was connected to the DB. Like I said, I've been staring at this for so long - I'm probably blind to the obvious. Once I'm shown how to do it, then I'll be golden.

New Code for the Results page

<?php require_once('Connections/plansdb.php');

$db_selected = mysql_select_db( $database_plansdb, $plansdb);

if (!$db_selected) {

   die ('Can\'t use' . $database_plansdb .' : ' . mysql_error());

} ?>





<?php

//variables from submit form

$plantypes = $_POST['houseplans'];

$bed  = $_POST['bedrooms'];

$bath = $_POST['bathrooms'];

$garage  = $_POST['garages'];



// Query DB

$query = sprintf("SELECT PLANNO, PLANTYPES, DESC, BED, BATH, GARAGE, FLOORS, URL, IMG FROM plans WHERE PLANTYPES='%s' AND BED='%s' AND BATH='%s' AND GARAGE='%s'",

   mysql_real_escape_string($plantypes),

   mysql_real_escape_string($bed),

   mysql_real_escape_string($bath),

   mysql_real_escape_string($garage));



// Perform Query

$result = mysql_query($query);



// Check result

// This shows the actual query sent to MySQL, and the error. Useful for debugging.

if (!$result) {

   $message  = 'Invalid query: ' . mysql_error() . "\n";

   $message .= 'Whole query: ' . $query;

   die($message);

}



// Use result

while ($row = mysql_fetch_assoc($result)) {

   echo $row['PLANNO'];

   echo $row['PLANTYPES'];

   echo $row['DESC'];

   echo $row['BED'];

   echo $row['BATH'];

   echo $row['GARAGE'];

   echo $row['FLOORS'];

   echo $row['URL'];

   echo $row['IMG'];

}



mysql_free_result($result);

?>

Re: php results from form - stuck
 

Ah, the syntax error is because DESC is a reserved word, you'll have to escape that with `desc` I think. You will also need to handle the "any" cases as well, perhaps by changing the use of = to LIKE (i.e. BED LIKE '%') and use the % wildcard for the "any value" cases.

Re: php results from form - stuck
 

Changed the Database field name from "DESC" to "DESCRIPT"

Changed the = to LIKE

No errors, and nothing shows up, even on source.

So - I'm going to build a little output script for the variables that are being passed. And see if there's a drop there.

Not sure how to handle the "any" case beyond the change from = to LIKE. I tried changing the '%s' to just '%' and got an error like this.
Usually new things go "click" and "solved" in a small amount of time. This is causing my head to implode.

It's not like I'm new to scripting either, I wrote the fist skinning system for Gallery.sf.net (G1). This wee little thing is about to get a sledge hammer to the computer. ;)

Jade

Re: php results from form - stuck
 

variables are being passed, the "any" part is setup as an empty set. But the error occurs whether I have a selected value or not.

Re: php results from form - stuck
 

You might change the default 'any' value for houseplans on the form to ""

<option selected="selected" value="">All Types</option>

so that they are all consistent and try this

$plantypes = ($_POST['houseplans']) ? $_POST['houseplans']):'%';

$bed  = ($_POST['bedrooms']) ? $_POST['bedrooms'] : '%';

$bath = ($_POST['bathrooms']) ? $_POST['bathrooms'] : '%';

$garage  = ($_POST['garages']) ? $_POST['garages'] : '%';

. This will set the variable to its selected value if it's not null or '%' for the null case. Then change the query to

$query = sprintf("SELECT PLANNO, PLANTYPES, DESC, BED, BATH, GARAGE, FLOORS, URL, IMG FROM plans WHERE PLANTYPES like '%s' AND BED like '%s' AND BATH like '%s' AND GARAGE like '%s'",

   mysql_real_escape_string($plantypes),

   mysql_real_escape_string($bed),

   mysql_real_escape_string($bath),

   mysql_real_escape_string($garage));

The sprintf() function still needs the '%s' for the variable substitution. From the docs, it sounds like the LIKE '%' wildcard should work with numerics, but I have not verified that myself. It it does not, you will have to build the WHERE string dynamically by concatenating those pieces that have a value other than "" (which was "any").

Re: php results from form - stuck
 

You've been really great, unfortunately it's still not outputting. Played with a few things from php.net phpbuilder and off adobe db primer.

I think I'll need to hit the book store tonight and do a little more research. honestly I thought this would be an easy script to do. The client has been waiting for it to go live for a week. I'll pop open my php book - but it's a couple years old and hesitate to use something depreciated. :(

Re: php results from form - stuck
 

woot! I have output!

I was missing a ' after my last edit. But it works! thanks so much for your help. Now for the easy part - formatting the output.

yay!
:D
Jade