Dan Dan - 18 days ago 7
PHP Question

Turning a SQL row into an array

It's been a long day but I'm struggling to think how to turn the following into a comma seperated array:

$sql = "SELECT * FROM respondent_data WHERE respondent_firstname = 'John'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {

while($row = mysqli_fetch_assoc($result)) {

$categoriesTest[] = $row["respondent_sdo"];
$row["respondent_dcto"];
$row["respondent_ed"];
$row["respondent_ca"];
$row["respondent_dhpt"];
$row["respondent_irt"];
$row["respondent_gl"];
$row["respondent_il"];

// Turn my output into an array ready to be used for the JSON string

}

}


So each of those values outputs an integer from the column rows I need them to be turned into an array like: 2,4,3,5....

Answer

If you only want specific associative properties from all columns queried from the MySQL call, just set them in an array with their respective properties:

$categoriesTest = array();
$sql    = "SELECT * FROM `respondent_data` WHERE `respondent_firstname` = 'John'";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)) {
    $categoriesTest[] = array(
        'respondent_sdo'    => $row['respondent_sdo'],
        'respondent_dcto'   => $row['respondent_dcto'],
        'respondent_ed'     => $row['respondent_ed'],
        'respondent_ca'     => $row['respondent_ca'],
        'respondent_dhpt'   => $row['respondent_dhpt'],
        'respondent_irt'    => $row['respondent_irt'],
        'respondent_gl'     => $row['respondent_gl'],
        'respondent_il'     => $row['respondent_il']
    );
}
$categoriesTest = json_encode($categoriesTest); // get JSON

However, if you wanted to keep all columns quried, but create custom elements you'll want to use array_merge:

$categoriesTest = array();
$sql    = "SELECT * FROM `respondent_data` WHERE `respondent_firstname` = 'John'";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)) {
    $categoriesTest[] = array_merge($row, array(
        'custom_value_1'    => 'test',
        'custom_value_2'    => 'test2'
    ));
}
$categoriesTest = json_encode($categoriesTest); // get JSON