How to retrieve multiple rows from a MySQL database and access them in a PHP array with a multidimensional structure?

Selecting Multiple Rows from MySQL and Accessing Them in PHP

Question:

How can I select multiple rows from a MySQL database using a single query and retrieve them in a PHP array with a multidimensional structure?

Answer:

To retrieve multiple rows from MySQL and access them in PHP through a multidimensional array, you can utilize the iterative nature of the mysql_fetch_assoc() function.

Solution:

1. Establish MySQL Connection: Connect to your MySQL database using the mysql_connect() or mysqli_connect() function.
2. Execute SQL Query: Run your SQL query that selects the desired rows from the database. The result of the query is stored in a result set.
3. Loop Through Rows: To access multiple rows, you need to loop through the result set using while or foreach loops.
4. Fetch Each Row: Inside the loop, call mysql_fetch_assoc() repeatedly to retrieve the current row as an associative array. Each call to mysql_fetch_assoc() returns the next row in the result set.
5. Build Multidimensional Array: As you fetch each row, store it in a multidimensional array. For instance, you could create an array with the first dimension being the row number and the second dimension being the column names.

Example Code:

<code class="php"><?php
// Connect to MySQL
$connection = mysql_connect('host', 'username', 'password');
mysql_select_db('database', $connection);

// Execute SQL Query
$query = "SELECT * FROM table_name WHERE number1 = 1";
$result = mysql_query($query);

// Loop Through Rows and Build Array
$multidimensionalArray = array();
$i = 0;
while ($row = mysql_fetch_assoc($result)) {
    $multidimensionalArray[$i] = $row;
    $i++;
}

// Access Row Data
echo $multidimensionalArray[1]['number2'];
?></code>

By utilizing this approach, you can retrieve multiple rows from a MySQL database and access their data in a multidimensional PHP array.

The above is the detailed content of How to retrieve multiple rows from a MySQL database and access them in a PHP array with a multidimensional structure?. For more information, please follow other related articles on the PHP Chinese website!