Each match in each columns of a table to array

This is how my table tables looks:

tables

I basically want to make an API that will return every "table" the user (Admin, here) is in, by checking if he is either in 1_1, 1_2, 1_3, 1_4 or 1_5.

This is how I tried to do it:

$positions = $database->fetchAll("SELECT * FROM tables WHERE 1_1 = :username OR 1_2 = :username OR 1_3 = :username OR 1_4 = :username OR 1_5 = :username", array(":username" => "Admin"));

print_r($positions);

And this is what this do return:

Array
(
    [0] => Array
        (
            [id] => 1
            [code] => abc123
            [1_1] => 
            [1_2] => Admin
            [1_3] => 
            [1_4] => Admin
            [1_5] => 
        )
)

The problems are:

  1. The SQL query looks very unclean and long.
  2. If the user is in 1_2 and also in 1_4, it won't return both results.

I'd like my API to return such an array:

Array
(
    [0] => Array
        (
            [code] => abc123
            [location] => 1_2
        )

    [1] => Array
        (
            [code] => abc123
            [location] => 1_4
        )
)

How could I please do it, in a clean way?

3 answers

  • answered 2019-08-13 03:44 Khaled Alam

    For the query maybe you can use MATCH(ex1, ex2, ex3) AGAINST ('$search')

    for the output parse it using PHP for loop

  • answered 2019-08-13 03:48 Nick

    One way to get the data you want directly from the SQL query is to use a UNION, testing each column independently:

    SELECT code, '1_1' AS location
    FROM tables WHERE `1_1` = 'Admin'
    UNION
    SELECT code, '1_2' AS location
    FROM tables WHERE `1_2` = 'Admin'
    UNION
    SELECT code, '1_3' AS location
    FROM tables WHERE `1_3` = 'Admin'
    UNION
    SELECT code, '1_4' AS location
    FROM tables WHERE `1_4` = 'Admin'
    UNION
    SELECT code, '1_5' AS location
    FROM tables WHERE `1_5` = 'Admin'
    

    Output:

    code    location
    abc123  1_2
    abc123  1_4
    

    Demo on dbfiddle

    Alternately, you can post-process your existing result:

    $locations = array('1_1', '1_2', '1_3', '1_4', '1_5');
    $output = array();
    foreach ($positions as $position) {
        foreach ($locations as $location) {
            if ($position[$location] == 'Admin') {
                $output[] = array('code' => $position['code'], 'location' => $location);
            }
        }
    }
    print_r($output);
    

    Output:

    Array
    (
        [0] => Array
            (
                [code] => abc123
                [location] => 1_2
            )    
        [1] => Array
            (
                [code] => abc123
                [location] => 1_4
            )    
    )
    

    Demo on 3v4l.org

  • answered 2019-08-13 05:18 Laiteux

    This is what I came up with, thanks to Nick's answer :

    $levelsSeats = array(
         1 => 8,
         2 => 4,
         3 => 2,
         4 => 1
     );
    
     $query = "";
     foreach($levelsSeats as $level => $seat)
         for($position = 1; $position < $seat + 1; $position++)
             $query .= $level . "_" . $position . " = :username OR ";
     $query = "SELECT * FROM tables WHERE " . substr($query, 0, -3);
    
     $results = $database->fetchAll($query, array(":username" => $USER["username"]));
    
     $output = array();
     foreach($results as $result)
         foreach($levelsSeats as $level => $seat)
             for($position = 1; $position < $seat + 1; $position++)
                 if($result[$level . "_" . $position] == $USER["username"])
                     array_push($output, array("id" => $result["code"], "position" => array("level" => $level, "seat" => $position)));
    
    echo json_encode($output);
    

    Output:

    [
        {
            "id": "abc123",
            "position": {
                "level": 1,
                "seat": 2
            }
        },
        {
            "id": "abc123",
            "position": {
                "level": 1,
                "seat": 4
            }
        }
    ]