How do I search 8 different fields (Columns)

[garyneedham]

Hello can anyone help? I have the follow search which works very well but I want to do another search, I want to search 8 different fields for example serial1 > Serial8

 

please can you keep answer very simple and step by step as I am very new to this thanks in advance for any help

 

 

$sql = "SELECT * FROM Customer WHERE Serial LIKE '%" . mysql_escape_string($_GET[serial']) . "%' ORDER BY Serial ";

if (empty($_GET['Serial']))

$sql = "SELECT * FROM Nif WHERE 1 = 2 ";

$res = mysql_query($sql);

 

?>

[AyKay47]

you will want to use the "AND" statement with your WHERE statement

you can look here

[fenway]

Not that it's particularly efficient....

[AyKay47]

Not that it's particularly efficient....

perhaps you can provide a solution rather than writing 5 words that don't help at all?

It looks like i misunderstood the question and you're right, an AND statement would be very long and insufficient...OP perhaps you can better explain your situation.

[fenway]

I simply meant that LIKE '%str%" doesn't take advantage of any index.

[AyKay47]

I simply meant that LIKE '%str%" doesn't take advantage of any index.

 

I'm terribly sorry then Fenway, my apologies..I thought you were talking about my reply...however your reply did make me look back at the original post and realize that it is much more messy than I thought...

[garyneedham]

Hi I am still having problems with this search can anyone make this any clearer for me

 

$sql = "SELECT * FROM Customer WHERE Serial LIKE '%" . mysql_escape_string($_GET[serial']) . "%' ORDER BY Serial ";

if (empty($_GET['Serial']))

  $sql = "SELECT * FROM Serial WHERE 1 = 2 ";

$res = mysql_query($sql);

 

?>

 

I do not really understand the "AND" command and how to use it

 

I want to search 8 columns Serial1 > Serial 8

[AyKay47]

basically an AND statement allows you to add more than one condition in addition to a WHERE statement

 

$sql = "SELECT * FROM Customer WHERE Serial LIKE '%" . mysql_escape_string($_GET[serial']) . "%' AND tbl_col LIKE '%something%' ORDER BY Serial ";

 

Also, I don't recommend sanitizing input inside of a query function...can get messy and at times lead to unnecessary errors.

[garyneedham]

Hi I have tried the following but the search still does not work can you please check what I have done

 

$sql = "SELECT * FROM Customer WHERE MachineSerial1 LIKE '%" . mysql_escape_string($_GET['Search_Box']) . "%' AND MachineSerial12 LIKE '%" . mysql_escape_string($_GET['Search_Box']) . "%' ORDER BY MachineSerial1 ";

if (empty($_GET['Search_Box']))

$sql = "SELECT * FROM MachineSerial1 WHERE 1 = 2  "  ;

$res = mysql_query($sql);

 

Thanks for your time looking at this

[fenway]

"does not work" isn't useful -- what doesn't work about it?

[garyneedham]

Hello

 

Sorry for not explaining myself very clearly.  What I mean by not working is "I get no result back and no error."  If I use the following statement it works OK but this searches only one field

 

$sql = "SELECT * FROM Customer WHERE MachineSerial1 LIKE '%" . mysql_escape_string($_GET['Search_Box']) . "%' ORDER BY MachineSerial1 ";

 

However if I use the AND command like below it does not look at the fields Machineserial1 and Machineserial1

 

$sql = "SELECT * FROM Customer WHERE MachineSerial1 LIKE '%" . mysql_escape_string($_GET['Search_Box']) . "%' AND MachineSerial12 LIKE '%" . mysql_escape_string($_GET['Search_Box']) . "%' ORDER BY MachineSerial1 ";

 

 

What I am looking for is to type a search into the search box called "Search_Box" and bring back a search from eight fields named MachineSerial 1 to 8

 

I hope I have made this clear

 

Thanks again for any help you can give me

[fenway]

I don't think you mean AND -- otherwise, you're only getting back those rows which contain your search value in BOTH fields.

[garyneedham]

Yes I think you are correct But a pointer in the right direction would be nice

[TeNDoLLA]

Use OR instead of AND.

[rallport]

For things like the OPs issue it is always useful to output the contents of the sql query onto the page to debug - as it's hard sometimes to see the generated query directly from code. Just my 2c

[garyneedham]

Hi I have managed to get it to work with the following code, not sure if its the best way though.

 

$sql = "SELECT * FROM Customer WHERE MachineSerial1 = '" . mysql_escape_string($_GET['Search_Box']) . "'

or MachineSerial2 = '" . mysql_escape_string($_GET['Search_Box']) . "'

or MachineSerial3 = '" . mysql_escape_string($_GET['Search_Box']) . "'

or MachineSerial4 = '" . mysql_escape_string($_GET['Search_Box']) . "'

or MachineSerial5 = '" . mysql_escape_string($_GET['Search_Box']) . "'

or MachineSerial6 = '" . mysql_escape_string($_GET['Search_Box']) . "'

or MachineSerial7 = '" . mysql_escape_string($_GET['Search_Box']) . "'

or MachineSerial8 = '" . mysql_escape_string($_GET['Search_Box']) . "'

ORDER BY MachineSerial1 " ;

 

if (empty($_GET['Search_Box']))

$sql = "SELECT * FROM MachineSerial1 WHERE 1 = 2 ";

$res = mysql_query($sql);