Searching form SELECT [Archive]

View Full Version : Searching form SELECT

Tainted Kitten

04-02-2005, 02:11 AM

I have this code here:

$query="SELECT * FROM registeredcats WHERE Name LIKE '$Name' AND Owner LIKE '$Owner' AND Breed LIKE '$Breed' AND Colour LIKE '$Colour' AND Sire LIKE '$Sire' AND Dam LIKE '$Dam'";

But the thing is, I don't want it so they have to enter all the search fields, I want it to be so if they enter search into two fields, it will search only where those two fields match, but they don't have to enter all fields to show results. How would I do this? Sorry if my questions seem real noobish, I'm new to all this programming stuff ^_^

Tangerine Dream

04-02-2005, 02:37 AM

Hi, your problem is much like How do I build a multiple choice search form? (http://www.dbforums.com/t1121439.html)

mr_ego

04-02-2005, 02:42 AM

just a simple idea ...

$_SEARCHABLE = Array (
"name",
"owner",
"breed",
"colour",
"sire",
"dam"
);

$sql = "SELECT * FROM registeredcats WHERE 1";
foreach ($_SEARCHABLE AS $id => $_FIELD) {
if (isset ($_POST['search'][$_FIELD]) && !empty ($_POST['search'][$_FIELD])) {
$sql .= " AND {$_FIELD} LIKE '{$_POST['search'][$_FIELD]}'";
}
}

$query = mysql_query($sql);
// or if you're using an OO approach ... something like:
// $query = $_DB->Query ($sql);

// For this example, you will need to use HTML such as:
// <input type="text" name="search[name]" value="">
// <select name="search[breed]"></select>

Hope this will help.

P.S. You may also want to use mysql_escape_string ... but that's if your server doesn't have Magic Quotes pre-installed.

Tainted Kitten

04-02-2005, 02:53 AM

Thanks, so could you tell me if this is right? Cause I'm not getting anything...
Don't laugh if my codeing's all wrong...I really am new to this...

$_SEARCHABLE = Array (
"Name",
"Owner",
"Breed",
"Colour",
"Sire",
"Dam"
);

$sql = "SELECT * FROM registeredcats WHERE 1";
foreach ($_SEARCHABLE AS $ID => $_FIELD) {
if (isset ($_POST['search'][$_FIELD]) && !empty ($_POST['search'][$_FIELD])) {
$sql .= " AND {$_FIELD} LIKE '{$_POST['search'][$_FIELD]}' ";
}
}
$query = mysql_query($sql);

mysql_close();

echo "Feline Registry ";

$i=0;
while ($i < $num) {

$ID=mysql_result($result,$i,"ID");
$Name=mysql_result($result,$i,"Name");
$Origin=mysql_result($result,$i,"Origin");
$Age=mysql_result($result,$i,"Age");
$Colour=mysql_result($result,$i,"Colour");
$Gender=mysql_result($result,$i,"Gender");
$Genotype=mysql_result($result,$i,"Genotype");
$Sire=mysql_result($result,$i,"Sire");
$Dam=mysql_result($result,$i,"Dam");
$Hips=mysql_result($result,$i,"Hips");
$Patellas=mysql_result($result,$i,"Patellas");
$Quality=mysql_result($result,$i,"Quality");
$Altered=mysql_result($result,$i,"Altered");
$Owner=mysql_result($result,$i,"Owner");

echo "<center><table border=\"1\" cellpadding=\"5\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#008000\" bordercolorlight=\"#33CC33\" bordercolordark=\"#008080\" width=\"300px\">
<tr>
<td width=\"50%\" bgcolor=\"#00FF00\">
ID#: $ID</td>
<td width=\"50%\" bgcolor=\"#00FF00\">
Origin: $Origin</td>
</tr> <tr>
<td width=\"100%\" colspan=\"2\">
<img border=\"0\" src=\"images/bullet.gif\" width=\"12\" height=\"12\"> $Name
$Age year old $Colour $Breed $Gender 
Genotype: $Genotype
Parentage: $Sire x $Dam
Hips/Patellas: $Hips/$Patellas
Quality: $Quality
Altered: $Altered 
Owned by: $Owner</td>
</tr>
</table></center> ";

$i++;
}

?>

mr_ego

04-02-2005, 03:12 AM

...

First of all, you have to be very careful. It looks as though the way you have written your database isn't exactly referentially correct. In saying this, I do not really recommend how you've done what you're creating, but this is the information that I think you need:

$_SEARCHABLE = Array (
"Name",
"Owner",
"Breed",
"Colour",
"Sire",
"Dam"
);

$sql = "SELECT * FROM registeredcats WHERE 1";

foreach ($_SEARCHABLE AS $ID => $_FIELD) {
if (isset ($_POST['search'][$_FIELD]) &&
!empty ($_POST['search'][$_FIELD])) {
$sql .= " AND {$_FIELD} LIKE '{$_POST['search'][$_FIELD]}'";
}
}

$query = mysql_query($sql);

echo "";
echo "Feline Registry";

while ($_CAT = mysql_fetch_assoc ($query)) {
echo "<center>";
echo "<table border='1' cellpadding='5' cellspacing='0' ";
echo "style='border-collapse: collapse' bordercolor='#008000' ";
echo "bordercolorlight='#33CC33' bordercolordark='#008080' ";
echo "width='300px'>";

echo "<tr>";
echo "<td width='50%' bgcolor='#00FF00'>";
echo "";
echo "ID#: {$_CAT['ID']}</td>";
echo "<td width='50%' bgcolor='#00FF00'>";
echo "";
echo "Origin: {$_CAT['Origin']}</td>";
echo "</tr>";

echo "<tr>";
echo "<td width='100%' colspan='2'>";

echo "";
echo "<img border='0' src='images/bullet.gif' width='12' height='12'>";
echo "{$_CAT['Name']";
echo "{$_CAT['Age']} year old ";
echo "{$_CAT['Colour']} {$_CAT['Breed']} {$_CAT['Gender']} ";

echo "";
echo "Genotype: {$_CAT['Genotype']}";

echo "";
echo "Parentage: {$_CAT['Sire']} x {$_CAT['Dam']}";

echo "";
echo "Hips/Patellas: {$_CAT['Hips']}/{$_CAT['Patellas']} ";

echo "";
echo "Quality: {$_CAT['Quality']} ";

echo "";
echo "Altered: {$_CAT['Altered']} ";

echo "";
echo "Owned by: {$_CAT['Owner']}</td>";

echo "</tr>";
echo "</table>";
echo "</center> ";

}

Here are some tips and some questions you should ask yourself.

Don't escape quotes, use single quotes instead. This is a lot easier to edit and maintain
What happens if there is more than 1 owner, or previous owner?
What happens if a record is altered more than once?
If you're going to use a "while" approach, don't use $i ... try the method that I have used.
Use mysql_fetch_assoc instead of doing a mysql_result over and over ... If you want to change to a particular row, check out the PHP documentation for mysql_data_seek
I think the whole way that you've set out your database, and how you've set out the design of this script is going to make it VERY difficult to maintain. Nevertheless, best of luck.

I dunno (nor am I guarenteeing) if this script (that I have written) will work ...
Best of luck ... !

Tainted Kitten

04-02-2005, 03:23 AM

Hi,

I've tried your script and it shows up all the records (I searched for the user name "Test", and it came up with everything, even records in which the user name isn't Test). I must say that this code was constructed on tutorials, this whole programming thing does confuse me for than a bit.
You can't have more than one owner, and I don't usually keep track of previous owners.
What does happen is a record is altered more than once?

6. I think the whole way that you've set out your database, and how you've set out the design of this script is going to make it VERY difficult to maintain.

I don't know what I'm doing wrong or not. My b/f doesn't really have time to teach me and throws me at a whole lot of tutorials, so I'm doing the best I can by myself! LOL.

Thanks for your help BTW, it's much appreciated.

mr_ego

04-02-2005, 03:34 AM

Ohh I forgot ... make sure you enter % as wildcars in your "LIKE" statements.

EG:

SELECT * FROM table WHERE name LIKE '%{$name}%'

or if you want an exact match

SELECT * FROM table WHERE name='{$name}'

Tainted Kitten

04-02-2005, 03:42 AM

Okay, now I'm really confused. LOL.
So is it still like this?
$sql = "SELECT * FROM registeredcats WHERE Name='{$Name}' AND Owner='{$Owner}' AND Breed='{$Breed}' AND Colour='{$Colour}' AND Sire='{$Sire}' AND Dam='{$Dam}'";
If so, we're back to where we started with this thread. Say someone searches for Owner AND Name, I want the form to show those records which match both owner and name, but they shouldn't have to fill out the other fields of the search form to get them.
I'm trying to make sense, I have difficulty explaining these things. LOL

mr_ego

04-02-2005, 04:06 AM

ok well ...
The query that you wrote doesn't really exist anymore..

Say we're not actually searching for the "name" of a cat, then we don't need to put it into the SQL statement.

So the SQL statement would turn out like this:

SELECT * FROM cats WHERE breed LIKE 'Persian'

Now (not knowing much about cats)... if you want to match something like:

"Persian Gulf Cat" in the Breed:

SELECT * FROM cats WHERE breed LIKE '%Persian%'
Would match it. Hence why you need the % signs.

Tainted Kitten

04-02-2005, 05:46 AM

Oh okay, so how can I get my search to work exactly?

mr_ego

04-02-2005, 09:02 AM

In your search form ... make sure you have:

<input type="text" name="search[breed]">
or
<select name="search[breed]"></select>

and ETC...

if you don't use the search[something] method, the script won't work ...