Search code examples
phpmysqlmysqlipdo

How do I return data as a Key & Value pair from MySQL

I'm trying to return a JSON object with a Key,Value pair, both of which are seperate columns in my MySQL table.

So the MySQL table Looks (simplified 1000%) like this:

+-----------------+---------------------+
|      Email      |   ProfilePicture    |
+-----------------+---------------------+
| [email protected]  | https://someurl.com |
| [email protected]  | https://foobar.com  |
| [email protected] | https://random.com  |
+-----------------+---------------------+

And I want a JSON object like

{
"[email protected]":"https://someurl.com",
"[email protected]":"https://foobar.com",
"[email protected]":"https://random.com"
}

I could build it up as a string in MySQL by looping through the table and concat everything together, then just parse it in JS. I know that. But it seems messy, and I know there must be some built in functions for this in PHP. I just don't know them.

All my other PHP/MySQL pairings are using mysqli_fetch_assoc and json_encode in the PHP as they don't need the JSON Key to change dynamically only the value.

The eventual JSON object is being returned from a JavaScript function, so I am happy with a fix any where along the chain from JavaScript (or jQuery), to PHP, to MySQL Procedure, and back along.

Solution

  • If you use PDO to connect the database, you can use something like...

    $query = $db->query("SELECT Email, ProfilePicture FROM users");
$data  = $query->fetchAll(PDO::FETCH_KEY_PAIR);
$out = json_encode($data);

    The PDO::FETCH_KEY_PAIR uses the first column returned as the key and the second column as the value.

    Sticking to mysqli

    $result = $db->query("SELECT Email, ProfilePicture FROM users");
$data = [];
while ($row = $result->fetch_assoc()) {
    $data[$row['Email']] = $row['ProfilePicture'];
}
$out = json_encode($data);

    MySQLi version - slightly shorter...

    $result = $db->query("SELECT Email, ProfilePicture FROM users");
$data = $result->fetch_all(MYSQLI_ASSOC);
$out = array_column($data, 'ProfilePicture', 'Email');
$out = json_encode($data);