Get All MySQL Selected Rows into an Array

Get all mysql selected rows into an array

I would suggest the use of MySQLi or MySQL PDO for performance and security purposes, but to answer the question:

while($row = mysql_fetch_assoc($result)){
     $json[] = $row;
}

echo json_encode($json);

If you switched to MySQLi you could do:

$query = "SELECT * FROM table";
$result = mysqli_query($db, $query);

$json = mysqli_fetch_all ($result, MYSQLI_ASSOC);
echo json_encode($json );

Fetch all rows based on the query into an array

$result = mysql_query("SELECT * FROM $tableName");  
$arr_json = array();
while ($row = mysql_fetch_assoc($result)) {
   $json = json_encode($row);
   $arr_json[] = $json;
}

EDIT: Looking a j08691's answer, it looks like I might have misunderstood.

Anyway, if you don't know how many columns you have, do this:

$arr = array();
while ($row = mysql_fetch_assoc($result)) {
   $arr2 = array();
   foreach ($row as $val) $arr2[] = $val;
   $arr[] = $arr2;
}

How to retrieve all rows from an SQL table into an array?

If you want to get all rows from the result set then you need to fetch all. Right now you are fetching only one.

To fetch all rows use fetch_all()

$coquery = "Select distinct coName from avgcarcompany";
$crun = $con->query($coquery);

$arrey = $crun->fetch_all(MYSQLI_ASSOC)

print_r($arrey);

Group mysql rows in an array by column value

Assuming the fact that you want to display only the column3 data in the table, the solution would be like this:

Change your query from

SELECT * FROM table;

to

SELECT column1, GROUP_CONCAT(column3 SEPARATOR ',') as column3 
FROM table 
GROUP BY column1 
ORDER BY COUNT(column1) DESC

Execute the query and follow the below steps,

1. Construct an associative array from the result set.
2. Extract the keys from the associative array.
3. Count the number of columns in the table, and finally
4. Construct the table

Here's the code:

$sql = "SELECT column1, GROUP_CONCAT(column3 SEPARATOR ',') as column3 FROM table_name GROUP BY column1 ORDER BY COUNT(column1) DESC";
$result_set = mysqli_query($conn, $sql);

// construct an associative array
$result_array = array();
while($row = mysqli_fetch_assoc($result_set)){
    $result_array[$row['column1']] = explode(",", $row['column3']);
}

// extract the keys from the associative array
$result = array_keys($result_array);

// count the number of columns in the table
$len = count($result_array[$result[0]]);

// construct the table
echo '<table border="1">';

// display table headings 
echo '<tr>';
for($i = 1; $i <= $len + 1; ++$i){
    echo '<th>Header' . $i . '</th>';
}
echo '</tr>';

// display table contents
foreach($result as $k){
    echo '<tr>';
    echo '<td>' . $k . '</td>';
    for($i = 0; $i < $len; ++$i){
        echo '<td>';
        echo isset($result_array[$k][$i]) ? $result_array[$k][$i] : '';
        echo '</td>';
    }
    echo '</tr>';
}

echo '</table>';

Output:

---

Note: If you want to see the structure of $result_array array, do var_dump($result_array);

PHP retrieve MySQL rows into array

I think it will help you

public function getProfileData($username){
        $results = TBWebcam::MySQLQuery("SELECT * FROM `new_user` WHERE `user_name` = \"AndrewAubury\";");        
        if($results == null){
            return null;
        }       

        $data = $results[0];//getting only the first result, if you have more rows the you have use for/foreach loop 
        $userimage = $data["user_image"];
        if($userimage =! ""){
            $userimage = str_replace("%s","",$userimage);
            $userimage = "https://A LINK YOU DONT NEED TO KNOW.net/PF.Base/file/pic/user/".$userimage;
        }else{
            $userimage = null;
        }

        $usergender = $data["gender"];
        if($usergender == "1"){
            $usergender = "Male";
        }else{
            $usergender = "Female";
        }
        //echo($data["user_id"]."<br><br><br><br>");
        $userData = array(
           "id" => $data["user_id"],
           "name" => $data["full_name"],
           "username" => $data["user_name"],
           "image" => $userimage,
           "gender" => $usergender,
        );                  

        echo("Data: ".$data."<br><br><br>");
        return($userData);  
    }

The code of $mehArray[] = $row; of the method MySQLQuery() sending two dimensional result set. So you have to fetch the results by looping in the method getProfileData(). I have put here 0 index value, because of you want to take 1 rows, I think.

Get an array of rows from a MySQL query?

To get a flattened array of the result set, try this:

$result = array();

while ($row = mysql_fetch_assoc($results_result)) {
    $result[$row['id']] = $row;
}

echo json_encode($result);

Also, you should look into either MySQLi or PDO to replace the (now deprecated) MySQL extension.

PHP MySQL Database - All rows into Multi-Dimensional Array

Since you asked for All rows, so the simple code for query is written below:

<?php
     //after connection to mysql db using mysql_connect()
     $sql = "Select dataA, dataB, dataC, dataD from `compdata`.`dataAll`" ;
     $result = mysql_query($sql) ;
     if(mysql_num_rows($result) > 0 ){
            while($row = mysql_fetch_array($result)){
                  $dataArray[] = $row ;
            }
    } 
   echo '<pre>';
   print_r($dataArray) ;//you got the desired 2D array with all results
?>

Select row where id in array of mysql row

Use FIND_IN_SET :
SELECT *
FROM table
where FIND_IN_SET(".$id.",outcomes);

---

Related Topics