[SOLVED] Mass Search Issue

[Zergman]

There is one column in the database that holds the employee ID's.

 

Im trying to build a mass search page so I can drop in a bunch if ID's and get all the records in one shot.

 

I threw together a quick page with 20 simple text fields, named them flagentid1 to flagentid20.

 

Its my sql statement that im having issues with.  Not sure how to search 1 column with 20 boxes.

 

I would post what I got so far, but im assuming im not even close.

 

Any help would be greatly appreciated 

[Maq]

Do you mean something like this where 1, 2, and 3 are substituted with your 20 text field values?

 

SELECT * 
FROM tablename 
WHERE 
fieldname IN (1, 2, 3);

 

 

[Zergman]

I think so.

 

This is by no means accurate, but shows what im trying to do

 

Text fields

<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><input name="frontlineagentfield1" type="text" class="inputbox" id="frontlineagentfield1" /></td>
<td><input name="frontlineagentfield2" type="text" class="inputbox" id="frontlineagentfield2" /></td>
<td><input name="frontlineagentfield3" type="text" class="inputbox" id="frontlineagentfield3" /></td>
<td><input name="frontlineagentfield4" type="text" class="inputbox" id="frontlineagentfield4" /></td>
<td><input name="frontlineagentfield5" type="text" class="inputbox" id="frontlineagentfield5" /></td>
</tr>
<tr>
<td><input name="frontlineagentfield6" type="text" class="inputbox" id="frontlineagentfield6" /></td>
<td><input name="frontlineagentfield7" type="text" class="inputbox" id="frontlineagentfield7" /></td>
<td><input name="frontlineagentfield8" type="text" class="inputbox" id="frontlineagentfield8" /></td>
<td><input name="frontlineagentfield9" type="text" class="inputbox" id="frontlineagentfield9" /></td>
<td><input name="frontlineagentfield10" type="text" class="inputbox" id="frontlineagentfield10" /></td>
</tr>
<tr>
<td><input name="frontlineagentfield11" type="text" class="inputbox" id="frontlineagentfield11" /></td>
<td><input name="frontlineagentfield12" type="text" class="inputbox" id="frontlineagentfield12" /></td>
<td><input name="frontlineagentfield13" type="text" class="inputbox" id="frontlineagentfield13" /></td>
<td><input name="frontlineagentfield14" type="text" class="inputbox" id="frontlineagentfield14" /></td>
<td><input name="frontlineagentfield15" type="text" class="inputbox" id="frontlineagentfield15" /></td>
</tr>
<tr>
<td><input name="frontlineagentfield16" type="text" class="inputbox" id="frontlineagentfield16" /></td>
<td><input name="frontlineagentfield17" type="text" class="inputbox" id="frontlineagentfield17" /></td>
<td><input name="frontlineagentfield18" type="text" class="inputbox" id="frontlineagentfield18" /></td>
<td><input name="frontlineagentfield19" type="text" class="inputbox" id="frontlineagentfield19" /></td>
<td><input name="frontlineagentfield20" type="text" class="inputbox" id="frontlineagentfield20" /></td>
</tr>
</table>

 

$flagent1 = $_GET['frontlineagentfield1'];
$flagent2 = $_GET['frontlineagentfield2'];
$flagent3 = $_GET['frontlineagentfield3'];
$flagent4 = $_GET['frontlineagentfield4'];
$flagent5 = $_GET['frontlineagentfield5'];
$flagent6 = $_GET['frontlineagentfield6'];
$flagent7 = $_GET['frontlineagentfield7'];
$flagent8 = $_GET['frontlineagentfield8'];
$flagent9 = $_GET['frontlineagentfield9'];
$flagent10 = $_GET['frontlineagentfield10'];
$flagent11 = $_GET['frontlineagentfield11'];
$flagent12 = $_GET['frontlineagentfield12'];
$flagent13 = $_GET['frontlineagentfield13'];
$flagent14 = $_GET['frontlineagentfield14'];
$flagent15 = $_GET['frontlineagentfield15'];
$flagent16 = $_GET['frontlineagentfield16'];
$flagent17 = $_GET['frontlineagentfield17'];
$flagent18 = $_GET['frontlineagentfield18'];
$flagent19 = $_GET['frontlineagentfield19'];
$flagent20 = $_GET['frontlineagentfield20'];

 

So by your example, this would work?

Select * FROM data WHERE flagentTID IN ($flagent1, $flagent2, $flagent3, $flagent4, etc...;

 

 

[Mchl]

Not really, because it will require you to always input all 20 $flagents.

I think it'd be better to just have one field, and input the IDs separated by commas.

[Zergman]

I already have that field on my main search page.  Anything special I would have to do to modify it to allow multiple entries?.

 

<input name="frontlineagentfield" type="text" class="inputbox" id="frontlineagentfield" />

 

Or would I have to implode the field into comma separated?

[Mchl]

No. Just ask whoever is going to enter the values, to enter them separated by commas.

The idea with implode is not bat either. You could have 20 fields as you have now, and then implode them into a single string to be passed to a query.

 

NB: Remember to sanitize all user-input data before passing it to query, to avoid SQL injections.

[Zergman]

Good stuff, thanks for the help Mchl

[Maq]

Try this, haven't tested it.

 

$x=0;
foreach ($_POST as $key => $value) {
     if($x==0) {$sql_string = $value;}    
     else { $sql_string .= ", ".$value;}
     $x++;
}

SELECT * FROM data WHERE flagentTID IN ('$sql_string')

 

 

 

 

[xtopolis]

Try this, haven't tested it.

 

$x=0;
foreach ($_POST as $key => $value) {
     if($x==0) {$sql_string = $value;}    
     else { $sql_string .= ", ".$value;}
     $x++;
}

SELECT * FROM data WHERE flagentTID IN ('$sql_string')

I'm not sure that would work if they skipped an input in the middle for some reason, it might lose all the ones before.  Just use a simple empty($value) check and if not, append to the query.

[Maq]

Try this, haven't tested it.

 

$x=0;
foreach ($_POST as $key => $value) {
     if($x==0) {$sql_string = $value;}    
     else { $sql_string .= ", ".$value;}
     $x++;
}

SELECT * FROM data WHERE flagentTID IN ('$sql_string')

I'm not sure that would work if they skipped an input in the middle for some reason, it might lose all the ones before.  Just use a simple empty($value) check and if not, append to the query.

 

Good point, but with the proper validation it should work.  I guess it depends on how he set his system up.