Search function on multiple rows in the same table

Question

I'm wondering how do I add more row's on my search script to be searched.

Here's my php script:

<?php

$con = new PDO("mysql:host=localhost;dbname=db",'user','');

if (isset($_POST["submit"])) {
    $str = $_POST["search"];
    $sth = $con->prepare("
SELECT * 
  FROM `players` 
 WHERE region = '$str'
");

    $sth->setFetchMode(PDO:: FETCH_OBJ);
    $sth -> execute();

    if($row = $sth->fetch())
    {
        ?>

As you can see this:

SELECT * FROM `players` WHERE region = '$str'

I want to it to search on region and rank rows. I tried:

SELECT * FROM `players` WHERE region, rank = '$str'

.... it's showing 0 results.

Thank you in advance.

This is not the right way to do PDO queries. It will work, but it is vulnerable to injection. See: [Prepared statements and stored procedures](https://www.php.net/manual/en/pdo.prepared-statements.php). You have to learn that first, and then worry about adding an extra column to your search. — KIKO Software, Nov 08 '20 at 20:08

score 1 · Accepted Answer · answered Nov 08 '20 at 20:06

1

Is this what you want?

SELECT * FROM players WHERE ? IN (region, `rank`)

This searches for the parameter in both columns region and rank.

Side notes:

For this to properly and consistently work, both columns must be of the same datatype
Use prepared statements! Do not concatenate variables in the query string; this is inefficient, and opens up your code to SQL injection. See How can I prevent SQL injection in PHP?
Starting version 8.0.2, rank is a reserved word in MySQL, hence not a good choice for a column name

answered Nov 08 '20 at 20:06

GMB

216,147
25
84
135

You're using column names after the `IN` where you should use values. – KIKO Software Nov 08 '20 at 20:13
@KIKOSoftware: this does work as intented... see: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=36c570cf24798b53455c49e70a422197 – GMB Nov 08 '20 at 20:16
OK, so it seems. Well, I'm trying to find it in the documentation, but looking for `IN` is not easy. – KIKO Software Nov 08 '20 at 20:18
@KIKOSoftware: [IN operator in MySQL](https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_in) – GMB Nov 08 '20 at 20:20
Yes, that's it. I found it as well. Nowhere does it say you can use columns instead of values, or did I miss something? It's never too late to learn a new trick. – KIKO Software Nov 08 '20 at 20:24
I tested it in my own environment, and it does indeed work. Nice! – KIKO Software Nov 08 '20 at 20:26
It does work thanks. By the way, right now it is searching for full data only, if search United or only States as a keyword it show's 0 results, but if I use the entire text like United States of America it will show the result. – S37uP F403 Nov 08 '20 at 21:01
It does work thanks. By the way, right now it is searching for full data only, if search United or only States as a keyword it show's 0 results, but if I use the entire text like United States of America it will show the result. – S37uP F403 Nov 08 '20 at 21:04
@S37uPF403: that's what `IN` does. It is essentially a shortcut for multiple `=`s. If you want pattern matching, then you need multiple `LIKE` conditions, separated by `OR`. – GMB Nov 08 '20 at 21:25

Search function on multiple rows in the same table

1 Answers1