PHP RSS PHP RSS

php results from form - stuck

Thread Solved
Reply

•
•
Join Date: Aug 2007
Posts: 8
Reputation: JadeaDragon is an unknown quantity at this point 
Solved Threads: 0
JadeaDragon JadeaDragon is offline Offline
Newbie Poster

php results from form - stuck

 
0
  #1
Aug 7th, 2007
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:
PHP Syntax (Toggle Plain Text) 
  1. <form action="houseplan_results.php" method="get" name="house plans" id="house plans">
  2. <div class="houseplans">
  3.   Type of House Plan: 
  4.   <select name="houseplans">
  5.     <option selected="selected" value="All Types">All Types</option>
  6.     <option value="Beach/Coastal">Beach/Coastal</option>
  7.     <option value="Bungalow">Bungalow</option>
  8.     <option value="Contemporary">Contemporary</option>
  9.     <option value="Country">Country</option>
  10.     <option value="Craftsman">Craftsman</option>
  11.     <option value="European">European</option>
  12.     <option value="French">French</option>
  13.     <option value="Modern">Modern</option>
  14.     <option value="Northwest">Northwest</option>
  15.     <option value="Ranch">Ranch</option>
  16.     <option value="Victorian">Victorian</option>
  17.     <option value="Commercial">Commercial</option>
  18.   </select>
  19. </div>
  20. <div class="listbox">
  21. Number of Bedrooms: 
  22. <select name="bedrooms">
  23. 				<option selected="selected" value="">Any</option>
  24. 				<option value="1">1</option>
  25.  
  26. 				<option value="2">2</option>
  27. 				<option value="3">3</option>
  28. 				<option value="4">4</option>
  29. 				<option value="5">5</option>
  30. 				<option value="6">6</option>
  31. 				<option value="7">7</option>
  32. 				<option value="13">13</option>
  33. 	</select>
  34. </div>	
  35.  
  36. <div class="listbox">
  37. Number of Bathrooms:
  38. <select name="bathrooms">
  39. 				<option selected="selected" value="">Any</option>
  40. 				<option value="0">0</option>
  41. 				<option value="1">1</option>
  42.  
  43. 				<option value="1.5">1.5</option>
  44. 				<option value="2">2</option>
  45. 				<option value="2.5">2.5</option>
  46. 				<option value="3">3</option>
  47. 				<option value="3.5">3.5</option>
  48. 				<option value="4">4</option>
  49.  
  50. 				<option value="4.5">4.5</option>
  51. 				<option value="4.5 +.5">4.5 +.5</option>
  52. 				<option value="5.5">5.5</option>
  53. 				<option value="7">7</option>
  54. 				<option value="7.5">7.5</option>
  55. 				<option value="12.5">12.5</option>
  56.  
  57.  
  58. 	</select>
  59. </div>
  60.  
  61. <div class="listbox">
  62.   Number of Garages: 
  63.   <select name="garages">
  64.     <option selected="selected" value="">Any</option>	
  65.     <option value="0">0</option>
  66.     <option value="1">1</option>
  67.     <option value="2">2</option>
  68.     <option value="3">3</option>
  69.     <option value="4">4</option>
  70.     <option value="5">5</option>
  71.     <option value="6">6</option>
  72.     <option value="7">7</option>
  73.   </select>
  74. </div>					
  75. <input name="Search" type="submit" />
  76. </form>

and Result Page/Query Page. (note I do have mysql connection page setup and that is working)
PHP Syntax (Toggle Plain Text) 
  1. <?php require_once('Connections/plansdb.php'); ?>
  2.  
  3.  
  4. <?php
  5. //variables from submit form
  6. $houseplans = $plantypes ;
  7. $bedrooms  = $bed ;
  8. $bathrooms = $bath ;
  9. $garages  = $garage ;
  10.  
  11. // Query DB
  12. $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'",
  13.    mysql_real_escape_string($plantypes),
  14.    mysql_real_escape_string($bed),
  15.    mysql_real_escape_string($bath),
  16.    mysql_real_escape_string($garage));
  17.  
  18. // Perform Query
  19. $result = mysql_query($query);
  20.  
  21. // Check result
  22. // This shows the actual query sent to MySQL, and the error. Useful for debugging.
  23. if (!$result) {
  24.    $message  = 'Invalid query: ' . mysql_error() . "\n";
  25.    $message .= 'Whole query: ' . $query;
  26.    die($message);
  27. }
  28.  
  29. // Use result
  30. while ($row = mysql_fetch_assoc($result)) {
  31.    echo $row['PLANNO'];
  32.    echo $row['PLANTYPES'];
  33.    echo $row['DESC'];
  34.    echo $row['BED'];
  35.    echo $row['BATH'];
  36.    echo $row['GARAGE'];
  37.    echo $row['FLOORS'];
  38.    echo $row['URL'];
  39.    echo $row['IMG'];
  40. }
  41.  
  42. mysql_free_result($result);
  43. ?>
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
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2007
Posts: 4,346
Reputation: Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of 
Solved Threads: 498
Moderator
Featured Poster
Ezzaral's Avatar
Ezzaral Ezzaral is offline Offline
Industrious Poster

Re: php results from form - stuck

 
0
  #2
Aug 7th, 2007
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
PHP Syntax (Toggle Plain Text) 
  1. //variables from submit form
  2. $houseplans = $_POST['houseplans'];
  3. $bedrooms  = $_POST['bedrooms'];
  4. $bathrooms = $$_POST['bathrooms'];
  5. $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.
Last edited by Ezzaral; Aug 7th, 2007 at 12:46 pm.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 8
Reputation: JadeaDragon is an unknown quantity at this point 
Solved Threads: 0
JadeaDragon JadeaDragon is offline Offline
Newbie Poster

Re: php results from form - stuck

 
0
  #3
Aug 7th, 2007
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 Syntax (Toggle Plain Text) 
  1. <?php require_once('Connections/plansdb.php');
  2. $db_selected = mysql_select_db( $database_plansdb, $plansdb);
  3. if (!$db_selected) {
  4.    die ('Can\'t use' . $database_plansdb .' : ' . mysql_error());
  5. } ?>
  6.  
  7.  
  8. <?php
  9. //variables from submit form
  10. $plantypes = $_POST['houseplans'];
  11. $bed  = $_POST['bedrooms'];
  12. $bath = $_POST['bathrooms'];
  13. $garage  = $_POST['garages'];
  14.  
  15. // Query DB
  16. $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'",
  17.    mysql_real_escape_string($plantypes),
  18.    mysql_real_escape_string($bed),
  19.    mysql_real_escape_string($bath),
  20.    mysql_real_escape_string($garage));
  21.  
  22. // Perform Query
  23. $result = mysql_query($query);
  24.  
  25. // Check result
  26. // This shows the actual query sent to MySQL, and the error. Useful for debugging.
  27. if (!$result) {
  28.    $message  = 'Invalid query: ' . mysql_error() . "\n";
  29.    $message .= 'Whole query: ' . $query;
  30.    die($message);
  31. }
  32.  
  33. // Use result
  34. while ($row = mysql_fetch_assoc($result)) {
  35.    echo $row['PLANNO'];
  36.    echo $row['PLANTYPES'];
  37.    echo $row['DESC'];
  38.    echo $row['BED'];
  39.    echo $row['BATH'];
  40.    echo $row['GARAGE'];
  41.    echo $row['FLOORS'];
  42.    echo $row['URL'];
  43.    echo $row['IMG'];
  44. }
  45.  
  46. mysql_free_result($result);
  47. ?>
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2007
Posts: 4,346
Reputation: Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of 
Solved Threads: 498
Moderator
Featured Poster
Ezzaral's Avatar
Ezzaral Ezzaral is offline Offline
Industrious Poster

Re: php results from form - stuck

 
0
  #4
Aug 7th, 2007
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 8
Reputation: JadeaDragon is an unknown quantity at this point 
Solved Threads: 0
JadeaDragon JadeaDragon is offline Offline
Newbie Poster

Re: php results from form - stuck

 
0
  #5
Aug 7th, 2007
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 8
Reputation: JadeaDragon is an unknown quantity at this point 
Solved Threads: 0
JadeaDragon JadeaDragon is offline Offline
Newbie Poster

Re: php results from form - stuck

 
0
  #6
Aug 7th, 2007
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2007
Posts: 4,346
Reputation: Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of Ezzaral has much to be proud of 
Solved Threads: 498
Moderator
Featured Poster
Ezzaral's Avatar
Ezzaral Ezzaral is offline Offline
Industrious Poster

Re: php results from form - stuck

 
0
  #7
Aug 7th, 2007
You might change the default 'any' value for houseplans on the form to ""
PHP Syntax (Toggle Plain Text) 
  1. <option selected="selected" value="">All Types</option>
so that they are all consistent and try this
PHP Syntax (Toggle Plain Text) 
  1. $plantypes = ($_POST['houseplans']) ? $_POST['houseplans']):'%';
  2. $bed  = ($_POST['bedrooms']) ? $_POST['bedrooms'] : '%';
  3. $bath = ($_POST['bathrooms']) ? $_POST['bathrooms'] : '%';
  4. $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
PHP Syntax (Toggle Plain Text) 
  1. $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'",
  2.    mysql_real_escape_string($plantypes),
  3.    mysql_real_escape_string($bed),
  4.    mysql_real_escape_string($bath),
  5.    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").
Last edited by Ezzaral; Aug 7th, 2007 at 3:31 pm.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 8
Reputation: JadeaDragon is an unknown quantity at this point 
Solved Threads: 0
JadeaDragon JadeaDragon is offline Offline
Newbie Poster

Re: php results from form - stuck

 
0
  #8
Aug 7th, 2007
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 8
Reputation: JadeaDragon is an unknown quantity at this point 
Solved Threads: 0
JadeaDragon JadeaDragon is offline Offline
Newbie Poster

Re: php results from form - stuck

 
0
  #9
Aug 7th, 2007
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!

Jade
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the PHP Forum
Thread Tools Search this Thread



apache api array basic beginner broken cache cakephp class cms code computing confirm countingeverycharactersfromastring cron curl customizableitems database date delete dynamic echo email error fcc file filter folder form forms forum freelancing function functions gc_maxlifetime google header headmethod howtowriteathesis href htaccess html iframe image include incode ip javascript joomla limit link login malfunction match memmory memory menu method mod_rewrite multiple mysql navigation neutrality oop pagerank parsing paypal pdf php phpmysql query question random recursiveloop root script search select server sessions sms snippet soap source space sql support! system table template thesishelp trouble tutorial upload url variable video web window.onbeforeunload=closeme; youtube
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC