SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Help on how to get the right stuff from MySQL

    I have a website with a couple of coverbands.
    They update their own info about their band and one field is called style.
    The can choose what kind of style they play (jazz, soul, pop, rock, and so on).
    Some of them pick more than one alternative and in mySql the choices are marked as 1 or 0.

    Now I'm building a page where people can search for a specific band.
    They can search for bands by price, origin and style.

    So I have used SELECT * from table_bands WHERE place LIKE $place AND price LIKE $price AND pop LIKE $pop AND jaz LIKE $jazz and so on...

    People who search have a dropdown where they select the laternatives and the can choose different styles or all styles (which generates a %%).

    So I made something like

    PHP Code:
    if ($style=='jazz') {
    $jazz='1';
    $pop='0';
    $soul='0';
    $rock='0';
    $other='0';
    }
    if (
    $style =='pop') {
    $jazz='0';
    $pop='1';
    $soul='0';
    $rock='0';
    $other='0';

    This works fine if you're looking for, let's say, a band that plays jazz.
    But the problem is if you look for a jazz band and a band have marked both jazz and soul. Then I've set the search to include only those who have marked only jazz as 1.

    I just can't figure out how to do this properly without changing all the fields in my db. Can anyone give me a clue on what to do here?
    My brain is not working like it should now. I'm just blocked here...

  2. #2
    Dinah-Moe Humm mudshark's Avatar
    Join Date
    Dec 2003
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like this perhaps:
    PHP Code:
    <?php
    if(!empty($_POST)){
        
    $sql "SELECT * FROM bands WHERE ";
        foreach(
    $_POST['type'] as $k => $v){
            if(
    $v != 0){
                
    $sql .= "type = " $v " OR ";
            }
        }
        echo 
    rtrim($sql" OR ");
    }
    ?>
    <form method="post" action="">
        <select name="type[]">
            <option value="0" selected="selected">Choose...</option>
            <option value="1">jazz</option>
            <option value="2">pop</option>
            <option value="3">rock</option>
        </select>
        <select name="type[]">
            <option value="0" selected="selected">Choose...</option>
            <option value="1">jazz</option>
            <option value="2">pop</option>
            <option value="3">rock</option>
        </select>
        <select name="type[]">
            <option value="0" selected="selected">Choose...</option>
            <option value="1">jazz</option>
            <option value="2">pop</option>
            <option value="3">rock</option>
        </select>
        <input type="submit" value="go" />
    </form>
    (barebones, but you get the idea)

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Didn't understand that. I'm a newbie almost...

  4. #4
    Dinah-Moe Humm mudshark's Avatar
    Join Date
    Dec 2003
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I could try 'n explain what it does, but I think you'll understand better if you just copy/paste and execute the above. I'm only echo'ing out the $sql query string there, but obviously, you'd use $sql to query the database.
    Post some code if you'd like to know how to incorporate it.

  5. #5
    SitePoint Enthusiast fredanthony's Avatar
    Join Date
    Aug 2006
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also, you might want to use a switch instead:
    PHP Code:
    <?php

    switch ($style) {
        case 
    "jazz":
             
    $jazz 1;
                 
    $pop 0;
                 
    $soul 0;
                 
    $rock 0;
                 
    $other 0;
        break;    

            case 
    "pop":
                 
    $jazz 0;
                 
    $pop 1;
                 
    $soul 0;
                 
    $rock 0;
                 
    $other =0
            break;

           }  

    ?>

  6. #6
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    But what fredanthony wrote. What will happen if a band have both jazz and soul?
    Then if a viewer searching for jazz won't see that band, since their field with soul will be looking for one with a zero in that field or am I wrong?

  7. #7
    is_empty(2); foofoonet's Avatar
    Join Date
    Mar 2006
    Posts
    1,000
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think its called Funk (or maybe that's Phunk?)
    Upgrading to Mysql 5? Auto-increment fields now strict
    use NULL
    Or zero or leave the field name out completely.

  8. #8
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I just came up with a solution this morning that I hope will work.
    I use it in other fields where it's a string. I people pick all choices then I make it %% (which is used as a wild card).
    So if I set all styles to wild cards and only change the one they pick to 1, then it will find the right ones.
    But "%%" didn't work as a wild card here. Maybe that's because the field is set as a mediumint? Is there another sign to use as a wild card then?

  9. #9
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Sorry, it worked. I just forgot to put ' around the $style.
    So, finally my brain started working. But guess this isn't the best solution but I think it will work. Now I will try it with all kinds of combinations.

  10. #10
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Nope it din't work as I wanted. But very close.

    If they pick a style, then it works great. But if they pick the wildcard to list all styles, then the query won't work.

    I have written
    PHP Code:
    jaz='$jaz' 
    which is working fine for picking a style but it won't work with a wildcard. Then it has to be
    PHP Code:
    jaz LIKE '$jaz' 
    But how do I get both of them to work? If the choose ALL from the dropdown, then I'm posting %%. If the choose jazz then I post 'jaz'.


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
  •