SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    164
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Issue with output displaying from a form - SQL statement issue?

    Hi there

    I'm trying to post details from a html form to a php page which will display the results based on the selected criteria. Below is the html and php code I'm using and the sql error that I'm getting. Is it that I have something wrong in my sql statement.

    I'd appreciate any help with this.

    Form.html

    Code:
    <form action="output.php" method="POST">
    <table border = "0" style ="width:300px; height:266px;background-color:#ebb6f1;">
    <tr>
    <td colspan = "2">
    <h3>Search for a Partner:</h3>
    </td>
    </tr>
    <tr>
    <td>Show Me:</td>
    <td>
    <select name="gender" style = "width:200px;">
    <option value="female">female</option>
    <option value="male">male</option>
    </select>
    </td>
    </tr>
    <tr>
    <td>Age Range:</td>
    <td>
    <select name="min_age" style = "width:50px;>
    <option selected="selected" value="18">18</option>
    <option value="19">19</option>
    							<option value="20">20</option>
    							<option value="21">21</option>
    							<option value="22">22</option>
    							<option value="23">23</option>
    							<option value="24">24</option>
    							<option value="25">25</option>
    							<option value="26">26</option>
    							<option value="27">27</option>
    							<option value="28">28</option>
    							<option value="29">29</option>
    							<option value="30">30</option>
    							<option value="31">31</option>
    							<option value="32">32</option>
    							<option value="33">33</option>
    							<option value="34">34</option>
    							<option value="35">35</option>
    							<option value="36">36</option>
    							<option value="37">37</option>
    							<option value="38">38</option>
    							<option value="39">39</option>
    							<option value="40">40</option>
    						</select>
    						
    <label>to</label>
    
    						<select name="max_age" style = "width:50px;>
    							<option value="18">18</option>
    							<option value="19">19</option>
    							<option value="20">20</option>
    							<option value="21">21</option>
    							<option value="22">22</option>
    							<option value="23">23</option>
    							<option value="24">24</option>
    							<option value="25">25</option>
    							<option value="26">26</option>
    							<option value="27">27</option>
    							<option value="28">28</option>
    							<option value="29">29</option>
    							<option selected="selected" value="30">30</option>
    							<option value="31">31</option>
    							<option value="32">32</option>
    							<option value="33">33</option>
    							<option value="34">34</option>
    							<option value="35">35</option>
    							<option value="36">36</option>
    							<option value="37">37</option>
    							<option value="38">38</option>
    							<option value="39">39</option>
    							<option value="40">40</option>
    						</select>
    </td>
    </tr>
    <tr>
    <td>Location:</td>
    <td>
    <select name="region" style = "width:200px;">
    <option value="0">- Select -</option>
    							<option value="England">England</option>
    							<option value="Ireland">Ireland</option>
    							<option value="Scotland">Scotland</option>
    							<option value="Wales">Wales</option>
    							<option value="Other">Other</option>
    						</select>
    					
    </td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    <td><input type="submit" value="Submit" style = "width:200px;"></td>
    </tr>
    </table>
    </form>
    output.php

    Code:
    <?php
    
    $gender = $_POST['gender'];
    $min_age = $_POST['min_age'];
    $max_age = $_POST['max_age'];
    $region = $_POST['region'];
    
    
    $output=mysql_query("SELECT first_name,last_name, address1, gender_desc, age_desc, country_desc FROM tblMember, tblGender, tblAge, tblCountry WHERE
    tblMember.gender_id = tblGender.gender_id AND
    tblMember.age_id = tblAge.age_id AND
    tblMember.county_id = tblCounty.county_id AND
    gender_desc = $gender AND
    age_desc >= $min_age AND
    age_desc <= $max_age AND
    country_desc = $region") or die(mysql_error());
    
    echo "<table>";
    	
    while($row = mysql_fetch_array( $output )) {
    	
    	echo "<tr>";
    	echo "<td>";
    	echo $row['first_name']." ".$row['last_name'];
    	echo "</td>";
    	echo "<td>";
    	echo $row['address1'];
    	echo "</td>";
    	echo "<td>";
    	echo $row['gender_desc'];
    	echo "</td>";
    	echo "<td>";
    	echo $row['age_desc'];
    	echo "</td>";
    	echo "<td>";
    	echo $row['country_desc'];
    	echo "</td>";
    	echo "</tr>";
    	
    } 
    
    echo "</table>";
    
    ?>

    Error being returned:

    Unknown column 'female' in 'where clause'

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes, you forgot to put quotes around the string variables

    allow me to rewrite the query for you using explicit JOIN syntax --
    Code:
    SELECT first_name
         , last_name
         , address1
         , gender_desc
         , age_desc
         , country_desc 
      FROM tblMember
    INNER
      JOIN tblGender
        ON tblGender.gender_id = tblMember.gender_id
       AND tblGender.gender_desc = '$gender'
    INNER
      JOIN tblAge
        ON tblAge.age_id = tblMember.age_id 
       AND tblAge.age_desc >= '$min_age' 
       AND tblAge.age_desc <= '$max_age'
    INNER
      JOIN tblCountry 
        ON tblCounty.county_id  = tblMember.county_id 
       AND tblCounty.country_desc = '$region'
    first, wouldn't you say that this is easier to read and debug?

    notice i've put single quotes around the string variables, this should fix your error

    as a suggestion, i think you could dispense with the gender and age tables, and probably the country table as well, but that might be a topic for a separate discussion
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •