Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Apr 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MYSQL Query Issue with returning data

    Hi Guys

    Probably an easy one for an expert this, I have a dynamic drop down menu and i run on query based on a users selection, the query seems to be returning two of the three Where Clauses, but the Subcategory is no returning data, i will paste the Query and the offending piece of HTML/Javascript and see if someone can save me a little headache. Its probably something to do with the HTML or Javascript but I wanted to ask the opinion of a MYSQL expert as i am quite new to PHP/MYSQL. Thanks alot for any assistance.

    [CODE]
    The Query
    $category=$_POST['Category'];
    $subcategory=$_POST['Subcategory'];
    $destination=$_POST['Destination'];

    // Retrieve all the data from the "travel" table
    $result = mysql_query("SELECT * FROM travel WHERE Category='$category' AND Subcategory='$subcategory' AND Destination='$destination'")
    or die(mysql_error());

    // store the record of the "example" table into $row
    $row = mysql_fetch_array( $result ) ;

    The HTML/Javascript

    <td align="right" valign="middle">Sub Category :
    </td>
    <td align="left" valign="middle"><script type="text/javascript" language="JavaScript">
    document.write('<select name="subcategory"><option value="">Select Sub-Category</option></select>')
    </script>
    <noscript><select name="subcategory" id="subcategory" >
    <option value="">Select Sub-Category</option>
    </select>
    </noscript>

    [CODE]

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    Well, first of all, there seems no point in your <script> and <noscript> stuff, as both ways you end up with identically the same thing in the browser.

    But secondly, you have ZERO PHP code in there that would ever cause any list of subcategories to show, so why do you expect them to show?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Apr 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Old Penant, that was extremely helpful... there is actually quite a lot of a point of having the script as this calls another function that makes my drop down menu change according to the users action, second of all, I dont claim to be an expert with PHP or MYSQL, hence the reason why I asked for help! I do actually get data returned from 2 of my 3 where clauses so, i dont expect that there is too many problems with the code, maybe a small tweak, perhaps if you made a suggestion on how an expert like you may resolve it or an explanation of what i have done wrong instead of your previous comment then people like me, might actually learn how to improve.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    Huh?
    Code:
    document.write('<select name="subcategory"><option value="">
    where is the onchange handler that "calls another function that makes my drop down menu change according to the users action"???

    If you add that dynamically during page init, then why does it make any difference if the <select> is created via JS or not?

    Maybe I'm blind, but I don't see even one character of difference in there excepting for the addition of the id to the non-JS version:
    Code:
    ...
    document.write('
    <select name="subcategory">
        <option value="">Select Sub-Category</option>
    </select>
    ')
    </script>
    <noscript>
    <select name="subcategory" id="subcategory" >
        <option value="">Select Sub-Category</option>
    </select>
    ...
    I would think that, if anything, the id would be more useful in the JS version than in the non-JS version.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    Anyway, I think I see your problem:
    Code:
    <select name="subcategory">
    vs.
    Code:
    $subcategory=$_POST['Subcategory'];
    I don't use PHP, but I believe that PHP is case sensitive when it comes to form field names. (ASP/ASP.NET/JSP, all of which I use, are not, so it was easy for me to miss this first time.)

    EDIT: Yes, I just confirmed that:
    http://phpsense.com/2006/php-form-handling/
    See the last "Note" on that page, almost at bottom.

    ********

    Oh...and FWIW, I completely misread the intent of your original post. I thought you were asking how you could use PHP to put the list of subcategories in place under the <select>. Since you didn't list any <option>s that had any non-blank values, I just assumed you were trying to find a way to get the right options in place there.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    One more thing:

    With your code as written, if the user does *NOT* choose a subcategory, then your $subcategory will be blank.

    You may or may not want to provide for that. If you do:
    Code:
    $category=$_POST['Category'];
    $subcategory=$_POST['subcategory']; // MATCHING CASE!
    $destination=$_POST['Destination'];
    
    // Retrieve all the data from the "travel" table
    $sql = "SELECT * FROM travel WHERE Category='$category' ";
    if ( isset($subcategory) && $subcategory != "" )
    {
        $sql .= " AND Subcategory='$subcategory' ";
    }
    $sql .= " AND Destination='$destination'");
    
    // comment out next line after things start working:
    echo "<hr/>DEBUG SQL: " . $sql . "<hr/>\n";
    
    $result = mysql_query( $sql ) or die(mysql_error());
    ...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

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