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 | +-----------------+---------------------+ | john@email.com | https://someurl.com | | jane@email.com | https://foobar.com | | bobby@email.com | https://random.com | +-----------------+---------------------+
And I want a JSON object like
{
"john@email.com":"https://someurl.com",
"jane@email.com":"https://foobar.com",
"bobby@email.com":"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.
Advertisement
Answer
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);