943,859 Members | Top Members by Rank

Web Development > PHP > php results from form - stuck
Ad:
  • PHP Discussion Thread
  • Marked Solved
  • Views: 1292
  • PHP RSS
Permalink
Aug 7th, 2007
0

php results from form - stuck

Expand Post »
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
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
JadeaDragon is offline Offline
8 posts
since Aug 2007
Permalink
Aug 7th, 2007
0

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
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.
Moderator
Featured Poster
Reputation Points: 3239
Solved Threads: 839
Posting Genius
Ezzaral is offline Offline
6,761 posts
since May 2007
Permalink
Aug 7th, 2007
0

Re: php results from form - stuck

I'm getting this error.

Quote ...
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. ?>
Reputation Points: 10
Solved Threads: 0
Newbie Poster
JadeaDragon is offline Offline
8 posts
since Aug 2007
Permalink
Aug 7th, 2007
0

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.
Moderator
Featured Poster
Reputation Points: 3239
Solved Threads: 839
Posting Genius
Ezzaral is offline Offline
6,761 posts
since May 2007
Permalink
Aug 7th, 2007
0

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.
Quote ...
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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
JadeaDragon is offline Offline
8 posts
since Aug 2007
Permalink
Aug 7th, 2007
0

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.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
JadeaDragon is offline Offline
8 posts
since Aug 2007
Permalink
Aug 7th, 2007
0

Re: php results from form - stuck

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.
Moderator
Featured Poster
Reputation Points: 3239
Solved Threads: 839
Posting Genius
Ezzaral is offline Offline
6,761 posts
since May 2007
Permalink
Aug 7th, 2007
0

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.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
JadeaDragon is offline Offline
8 posts
since Aug 2007
Permalink
Aug 7th, 2007
0

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!

Jade
Reputation Points: 10
Solved Threads: 0
Newbie Poster
JadeaDragon is offline Offline
8 posts
since Aug 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in PHP Forum Timeline: phpinfo page has array errors. What for?
Next Thread in PHP Forum Timeline: Next Link Help!!





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC