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 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Searching a music database

    I can't wrap my head around this. I am trying to come up with a way to search my music database. However, I am occuring multiple problems

    One, my GET data wants to resolve to a, or some other alphanumeric characters, and two, the big one, is my SQL data wants to mess up (returns bands that shouldn't be in the search, returns multiple results, etc) because the the PHP variables used to search it. Those are the two biggest ones.

    PHP Code:
                {
                
    $for $_POST['for'];
                
    $query $_POST['query'];

                    if ( (
    strlen($query) <= 0) || (strlen($for) <= 0) || !isset($search[$for]))
                        {
                        print 
    "<form action=\"lyrics3.php?action=search\" method=\"post\">\n";
                        print 
    "<table align=\"center\">\n";
                        print 
    "<tr bgcolor=\"#666666\"><th colspan=\"3\">What For?</th</tr>\n";
                        print 
    "<tr bgcolor=\"#666666\"><td>\n";
                                print 
    "<select name=\"for\">\n";
                                foreach (
    $search as $k=>$v)
                                    { 
                                    print 
    '<option value="' $k .'">' $v '</option>\n';
                                     }
                                print 
    "</select>\n";
                        echo 
    "</td>\n";
                        echo 
    "<td>\n";            
                                print 
    "<input type=/\text\" name=\"query\" />";
                        echo 
    "</td>";
                        echo 
    "<td>";
                                print 
    "<input type=\"submit\" title=\"Change\" value=\"Change\" />";
                        print 
    "</td>\n</tr>\n";
                        print 
    '</table>' "\n\r";
                        print 
    "</form>\n";
                        echo 
    '</div>';
                        echo 
    '<br />';
                        }
                        
                    elseif ( (
    strlen($query) > 0) && (strlen($for) > 0) && isset($search[$for]))
                        {
                        echo 
    '<div class="content">' "\n\r";
                        
    $sql "SELECT s.id AS s_id, s.song AS song, s.hits AS hits, a.name AS a_name, a.id AS a_id FROM gb_songs s, gb_artists a WHERE " $for " ILIKE " "'%"$query "%' AND gb_songs.artist = gb_artists.id ORDER BY " $for " LIMIT " $limit " OFFSET " $offset;
                        
    $sql pg_query($sql);
                        if (!
    pg_num_rows($sql))
                            { 
                            echo 
    '<span>Error</span>' "\n\r";
                            echo 
    '<p>No results</p>' "\n\r"
                            } 
                            
                        else 
                            {
                            
    $x=0;
                            while(
    $row pg_fetch_array($sql,NULL,PGSQL_ASSOC))
                                {
                                
    $class = ($x 2) ? 'con1' 'con2';
                                
    $query[1] = array("action" => "band""id" => $row['a_id']);
                                
    $query[2] = array("action" => "song""id" => $row['s_id']); 
                                echo 
    '<p class="'$class '">' make_link(append_query_string("lyrics3.php"$query), $row['a_name']) . ' :: ' make_link(append_query_string("lyrics3.php"$query[2]), $row['song']). '( ' $row['hits'] . ' )</p>' "\n\r";
                                
    $x++;
                                }
                            }
                        echo 
    '</div>' "\n\r"
    $limit, and $offset are handled else where in the script.

    Here is what $search looks like.
    PHP Code:
    $search = array('s.song' => 'Song Name''a.name' => 'Artist Name''s.lyrics' => 'Lyrics'); 
    http://www.greenbomber.com/lyrics3.php?action=search

    Any help will be greatly apperciated.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Raf should be able to answer this better, as it seems more of a mySQL based problem. However, you should left join those tables in your query, as that may be what is causing the multiple names to show up.
    I've never seen ILIKE used before either, so I'm assuming it works exactly as mySQL's LIKE but for postgres instead.
    Does the records you don't want displayed contain the same words as used in the query? Cause that my be where that one comes up.
    As for the GET data, I don't understand the problem. I don't see where you are using any at all, so I'm not certain as to what this may have to do with effecting it.

  • #3
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts
    ILIKE is a case insensitive LIKE, SIMILAR might actually be better.

    I still cna't think of what might cause this problem, other than having something like s.song in the WHERE statement. Maybe if I read over the array, and if it is a certain item (like song), I'll switch it to gb_songs.song.

    I'll try that and post on how it works.

    EDIT: Ok, that didn't work, and now it likes to say attribute g not found (there is no attribute g! >_<).

    PHP Code:
                    switch($for)
                            {
                            case 
    "song":
                            case 
    "lyrics":
                                
    $for['where'] = 'gb_song.' $for;
                                
    $for['order'] = $for;
                            break;
                        
                            case 
    "artist":
                                
    $for['where'] = 'gb_artists.' $for;
                                
    $for['order'] = $for;
                            break;
                        
                            default:
                                
    $for['where'] = 'gb_song.song';
                                
    $for['order'] = 'song';
                            break;
                            }
                        
                        
    $query clean_query($query);
                                            
                        echo 
    '<div class="content">' "\n\r";
                        
    $sql "SELECT s.id AS s_id, s.song AS song, s.hits AS hits, a.name AS a_name, a.id AS a_id FROM gb_songs s, gb_artists a WHERE " $for['where'] . " ~~* '%"$query"%' AND gb_songs.artist = gb_artists.id ORDER BY " $for['order'] . " LIMIT " $limit ." OFFSET " $offset
    That's the new code, clean_query just runs trim and then htmlspecialchars (and addslashes if magic_quotes are off) on $query. And ~~* is shorthand for ILIKE, and no SIMILAR TO didn't work.
    Last edited by Kurashu; 01-16-2005 at 10:04 PM.

  • #4
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts
    ...OK, this is crap. I messed with the SQL some, and got it to work. However, the GET data I appened to the link (that's maybe why you didn't think I was using any) still wanted to be some letter. So, I see how I did it before, and copy that. Appearently, my solution for appending data onto URLs doesn't like reading an array from an array (that and I had it set to two different variables).

    So yeah...the lesson learned here, SQL can be easily fixed with soem debugging and append_query_string only likes 1D arrays, even if you tell it to read from $array[1]['query'] (or something similar).


  •  

    Posting Permissions

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