PHP SQL Foreach语句[关闭]

I have known PHP for a while, but have not known how the foreach() command works. I do know that it is similar to while(). I have looked around at how it works, but am not quite sure about how you would connect mySQL statements to it.

Say for example you had an SQL statement as follows:

"SELECT username,password,email,dob FROM users"

How would you implement this into a foreach() statement to echo every user's username, password, email and date of birth?

Thanks for all help in advance!

The foreach method loops over all the rows of an array (or object), from the first until the last. This differs from a while loop in that way that a while keeps on looping until a certain condition is met. This can be after 2 iterations or 1000 iterations, depending on which condition you set.

In a foreach loop, you know that there will be as many iterations as there are keys in the array (unless you use a break statement within it, that aborts it right away).

SQL results can also be returned as array or object, which can be looped over. For example:

$query = "SELECT username,password,email,dob FROM users";
$resultSet = mysqli_fetch_all($query, MYSQLI_BOTH);
foreach ($resultSet as $id => $row) {
    echo $row['username'] // Show username
}

Foreach loops through an array. You need to put the results of that query into an array, which would entail looping around it with a while loop and saving the rows into an array or using something like *mysqli_fetch_all*

You can have a look into the php manual:
http://de2.php.net/manual/en/mysqli-result.fetch-row.php

The first example should be a good startingpoint

The standard way to use MySQL result it is a while loop. Look at example 2 here:

function readDataForwards($dbh) {
  $sql = 'SELECT hand, won, bet FROM mynumbers ORDER BY BET';
  try {
    $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
      $data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "
";
      print $data;
    }
    $stmt = null;
  }
  catch (PDOException $e) {
    print $e->getMessage();
  }
}

http://php.net/manual/en/pdostatement.fetch.php

You can loop through the result with foreach, fetching all the data first:

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:
");
$result = $sth->fetchAll();
foreach($result as $key => $value)
{
    var_dump($value);
}

http://php.net/manual/en/pdostatement.fetchall.php

The problem here is that you're going to need something to iterate over to use foreach - and it looks like you're used to getting single arrays / objects. If you used a database abstraction layer, instead of raw mysql_* commands, then most of these tools will give you back an array of arrays, or objects.

You can see an example of exactly what you're asking here, but note this is using CodeIgniter's database layer to get at the data: http://ellislab.com/codeigniter/user-guide/database/results.html

foreach is a mechanism for iterating through an array. If what ever you are using to run your sql queries is returning an array of data (which it almost definitely is) you can use a foreach loop to run through all of the results.

$my_array = array('cats' => 'meow', 'dogs' => 'bark');
foreach($my_array as $value) { echo "{$vlaue} <br />"; }
echo "<br />";
foreach($my_array as $key => $value) { echo "{$key} make a {$value} sound<br />"; }

outputs:
meow
bark
cats make a meow sound
dogs make a bark sound

For your sql query

$mysqli = new mysqli("example.com", "user", "password", "database");

$users = mysqli_fetch_array( $mysqli->query("SELECT username,password,email,dob FROM users") , MYSQLI_BOTH);

foreach($users as $user) {
   echo "User: {$user['username']} <br /> 
         Password: {$user['password]} <br /> 
         email: {$user['email']} <br />
         DOB: {$user['dob']} <br /><br />";
}

Never use mysqli though, I just used it here for sake simplicity.