php results from form - stuck

[JadeaDragon]

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

[Ezzaral]

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.

[JadeaDragon]

I'm getting this error.

Invalid query: 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 'DESC, BED, BATH, GARAGE, FLOORS, URL, IMG FROM plans WHERE PLANTYPES='All Types'' at line 1 Whole query: SELECT PLANNO, PLANTYPES, DESC, BED, BATH, GARAGE, FLOORS, URL, IMG FROM plans WHERE PLANTYPES='All Types' AND BED='' AND BATH='' AND GARAGE=''

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);
?>

[Ezzaral]

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.

[JadeaDragon]

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.

Invalid query: 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 'ND BATH LIKE '0' AND GARAGE LIKE '0.00000e-1' at line 1 Whole query: SELECT PLANNO, PLANTYPES, DESCRIPT, BED, BATH, GARAGE, FLOORS, URL, IMG FROM plans WHERE PLANTYPES LIKE 'ND BED LIKE 'ND BATH LIKE '0' AND GARAGE LIKE '0.00000e-1

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

[JadeaDragon]

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.

[Ezzaral]

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").

[JadeaDragon]

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. :(

[JadeaDragon]

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