PHP MySQL Search Function with one textbox

Question

What I'm trying to do currently is have a search function on my site that will look into a local MySQL db, in 1 column. The issue is the end-user could paste 1 item or they could paste 50 for example. And, without creating 50 textbox's I was wondering if it would be possible to make say a large textbox, that would search for a new field based on a space between them, or linebreak. Currently I have two search boxes, one where data is pasted, and the second null(I have null because when I left it blank it search for all(*).

Here is my page.

form action="" method="get">  
Paste your ZZZs here: <br>
<input type="text" name="item" id="textbox" maxlength="10" size="10"><br>
<input type="text" name="item2" id="textbox2" value="null" maxlength="10" size="10"><script>document.getElementById('textbox').focus()</script><br />  
<input type="submit" value="Submit"/>  
</form>  
<?php
if (!empty($_REQUEST['item'])) {

    $item = mysql_real_escape_string($_REQUEST['item']);
    $item2 = mysql_real_escape_string($_REQUEST['item2']);     

    $sql = "select fcsku, fnsku from oak3_zzz_to_boo WHERE fcsku like '%".$item."%' or fcsku like '%".$item2."%'"; 
    $r_query = mysql_query($sql); 

    echo "<table border=1>";
    echo "<tr><td><b>FcSKU</td><td><b>FnSKU</td></tr>";
    while ($row = mysql_fetch_array($r_query)) { 
        echo "<tr><td>".$row['fcsku']."</td><td>".$row['fnsku']."</td></tr>"; 
    }  
}
echo "</table>";
mysql_close();

My Table structure only has 2 columns, FnSKU, and FcSKU

Thanks to the contributors here is the working code!

<form action="" method="get">  
Paste your ZZZs here: <br>
<textarea name="item" id="textbox"></textarea>
<script>document.getElementById('textbox').focus()</script><br />  
<input type="submit" value="Submit"/>  
</form>  
<?php
if (!empty($_REQUEST['item'])) {

    $search = "\"" . strip_tags(preg_replace("/[\n\r ]/","\", \"",$_REQUEST["item"])) . "\""; // for space as delimiter 
    ##$search = "\"" . strip_tags(str_replace(" ","\", \"",$_REQUEST["item"])) . "\""; // for space as delimiter 

    $query = "SELECT fcsku, fnsku FROM oak3_zzz_to_boo WHERE fcsku IN ($search)";
    $r_query = mysql_query($query); 

    echo "<table border=1>";
    echo "<tr><td><b>FcSKU</td><td><b>FnSKU</td></tr>";
    while ($row = mysql_fetch_array($r_query)){ 
        echo "<tr><td>".$row['fcsku']."</td><td>".$row['fnsku']."</td></tr>"; 
    }  
}
echo "</table>";
mysql_close();

you could explode a string (input) into an array then loop through your array searching on each term. — user1336827
– user1336827, Commented Jan 2, 2014 at 20:34
I was thinking the same implode the string at each space and include each item in an array then get the size of the array which would allow you t use a for loop to look through the array and search using each value. — Orville Patterson
– Orville Patterson, Commented Jan 2, 2014 at 20:36

gh0st · Accepted Answer · 2014-01-02 20:51:13Z

1

no need to explode, just go:

$search = "\"" . strip_tags(str_replace(" ","\", \"",$_REQUEST["item"])) . "\""; // for space as delimiter

$query = "SELECT * FROM something WHERE something IN ($search)";

//rest of mysql stuffs here

answered Jan 2, 2014 at 20:51

gh0st

2143 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

gh0st Over a year ago

Awesome! I didn't even test it.

Nomad Over a year ago

It works when they are on the same line. although it does not if they are on separate line (ex. pasted values from excel)

Nomad Over a year ago

Hmm, that didn't seem to do anything, but now not execute the query. Also it appears to work when values are typed in, but when pasted and identical it does not.

gh0st Over a year ago

You might want to change str_replace with preg_replace: preg_replace("/[\n\r ]/","\", \"",$element); this should replace space and newline

Dave Over a year ago

Remove the line breaks. stackoverflow.com/questions/5258543/…. You should also Google "sql injection" while you're at it.

|

Collectives™ on Stack Overflow

PHP MySQL Search Function with one textbox

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related