Search Problems with MySQL Database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
aelaron
Forum Newbie
Posts: 4
Joined: Sun Dec 29, 2002 5:35 pm

Search Problems with MySQL Database

Post by aelaron »

Hi All,

I will try to be as clear as possible and hopefully ya'll can assist!!!

I have a MySQL with a table called audio, this database contains files that I have uploaded to my server so I can catalog them for future use.

The Problem!!!!!
I want to search by keywords I assigned to the file, as well as individually search by filename and other parameters. I can get the individual searches to work just fine but not my keyword search. I have 4 keywords fields in my table keywords1, keywords2, keywords3, keywords4. The below select statement is what I am using:

$keywords is my search box name: <input type "text" name="keywords">

$results12=mysql_query("select * from audio where keywords1='$keywords'");
if (mysql_num_rows ($results12)<1)
{
?>
error if nothing found
<?
}

This will return only keywords1 result but not search keywords2,3,and 4

I almost need it written like this:

$results12=mysql_query("select * from audio where keywords1,keywords2,keywords3,keywords4='$keywords'");
if (mysql_num_rows ($results12)<1)
{

When a keyword is found I am doing this:

if (mysql_num_rows ($results12)>0)
{

$results12=mysql_query("select keywords1,keywords2,keywords3,keywords4,filename,format,date,rate,size,comments from audio where keywords1 || keywords2='$keywords'");
while (list($keywords1,$keywords2,$keywords3,$keywords4,$filename,$format,$date,$rate,$size,$comments)=mysql_fetch_row($results12))
{
This echo's to the screen all my data I have assigned to the file
}

Can anyone please help? I am ok with php so please be very precise and submit the code exactly the way it should. Much thanks in advance!!!!


Aelaron
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Keywords

Post by Jade »

Hey,

is it just me, or if you do something like this:

$keywords is my search box name: <input type "text" name="keywords">

$results12=mysql_query("select * from audio where keywords1='$keywords'");
if (mysql_num_rows ($results12)<1)
{
?>
error if nothing found
<?
}

then you database will only return things from Keywords1 because you are only searching from the keywords in that one spot. For your keyword search to work you'd have to search all your keywords spots. You might want to use a flag to do this.

For instance:

$found = 1;

$results12=mysql_query("select * from audio where keywords1='$keywords' and keyword2='$keywords'");
if (mysql_num_rows ($results12)<1)
{
$found = 0;
}
else
{
?>
display results
<?php
}
$results12=mysql_query("select * from audio where keywords3='$keywords' and keyword4='$keywords'");
if (mysql_num_rows ($results12)<1)
{
$found = 0;
}
else
{
?>
display results
<?php
}
if ($found == 0)
{
echo "No results were found";
}
?>


I dunno, just a suggestion.
Jade
Post Reply