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.


{
$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.
$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.

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.

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! >_<).

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.

...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).