Select multiple rows from MySQL [duplicate]

Question

I have a MySQL table:

id     header     content
-------------------------
10     test3      <p>test</p>
9      test2      <p>test</p>
8      test       <p>test</p>

but when I run this code:

$sql = "SELECT * FROM posts";
$query = mysqli_query($db_conx, $sql);

if($query) {
    $row = mysqli_fetch_row($query);
    for($i; $i < count($row); $i++){
        echo $row[$i] . " ";
    }
}

it only selects the first row.

My ideal output would by an array that looks like:

$dataArray[0][0] = "10"
$dataArray[0][1] = "test3"
$dataArray[0][2] = "<p>test</p>"

$dataArray[1][0] = "9"
$dataArray[1][1] = "test2"
$dataArray[1][2] = "<p>test</p>"

$dataArray[2][0] = "8"
$dataArray[2][1] = "test"
$dataArray[2][2] = "<p>test</p>"

etc;

What is the correct way to select multiple rows?

Answer 1

Use a while loop with your myslqi_fetch_row() function.

if($query) {
    while($row = mysqli_fetch_row($query)){

        echo $row[0] . " ";
        echo $row[1] . " ";

    }
}

You might find it easier to use mysqli_fetch_assoc() instead, so that you can access the data using the column name..

if($query) {
    while($row = mysqli_fetch_assoc($query)){

        echo $row['header'];
        echo $row['content'];

    }
}

Answer 2

you need to change your query this way,

$sql = "SELECT * FROM posts";
$result = mysqli_query($db_conx, $sql);

if ($result->num_rows > 0) {   
while($row = $result->fetch_assoc()) {
   echo $row['id'].' '. $row['header'].' '. $row['content']. "<br>";
}
}