How to Access MySQL Result Set Data With a Foreach Loop

How to access mysql result set data with a foreach loop

You can use foreach here just fine.

foreach ($rows as $row) {
    echo $row['id'];
    echo $row['firstname'];
    echo $row['lastname'];
}

I think you are used to accessing the data with numerical indices (such as $row[0]), but this is not necessary. We can use associative arrays to get the data we're after.

create a foreach with an mysql result

You can see from the console.log that the result is just an array of rows. Javascript has quite a number of ways to iterate over an array. You've specified that you want each steamID to go in a variable "resultsteamid", so for..of or any array helper will assist you here:

for..of

con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT SteamID64 FROM Users", function (err, rows, fields) {
    if (err) throw err;
    for(let row of rows) {
      let resultsteamid = row.SteamID64;
      let account_id = new SteamID(resultsteamid);
      //use account_id however you want
    }
  });
});

forEach

con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT SteamID64 FROM Users", function (err, rows, fields) {
    if (err) throw err;
    rows.forEach(function(row) {
      let resultsteamid = row.SteamID64;
      let account_id = new SteamID(resultsteamid);
      //use account_id however you want
    });
  });
});

Why PHP Mysql query inside a foreach loop always returns the first result from database?

As of now, you are re-assigning a new value to scalar variable $catIDS for each record returned by the query, then you echo it one you are done looping. You would need to put the echo/insert logic inside the loop (or maybe store the values in array).

Another thing to note is that you are splitting with , (a single comma), but you have a space between the two words. As a result, the second value (Restaurant) starts with a space, which will cause the query to return an empty resultset. You probably want to split with , (a comma followed by a space).

$biz_cat = 'Hotel, Restaurants';
$arrs = explode(', ', $biz_cat);
foreach($arrs as $arr){    
    $sql99 = "SELECT * FROM categories WHERE category_name='$arr'";
    $query99 = mysqli_query($db_conx, $sql99);
    while($row99 = mysqli_fetch_array($query99, MYSQLI_ASSOC)){            
        $catIDS = $row99['id'];
        //this is where i need to insert my new data in different tabel.
        echo $catIDS.'<br>';    
    }
}

Loop through Resultset in php

foreach($array as $result) {
    echo $result["selection_id"];
}

How to loop through a mysql result set

Here is a full example:

http://php.net/manual/en/mysqli-result.fetch-array.php

1. Connect
2. Select database
3. Make query
4. Cycle on the result and fetch array to get the row

OOP Challenge How to nest using foreach in php mysql result set?

Rather than PHP, I'd recommend using SQL to do all that heavy lifting. And it only requires one query.

SQL

Based on your desired output of a 5 column table the following SQL query does the trick. I tested it on my local box with the data you supplied. The date column could be omitted as you don't know what row the date is actually coming from.

select 
    datePost,
    partnerid,
    count(*) as `partner_totalrecords`,
    sum(case when `status` = 0 then 1 else 0 end) `count_status_0`,
    sum(case when `status` = 1 then 1 else 0 end) `count_status_1`
from partner_redirect
group by partnerid;

Thanks to this SO answer for how to get the two status count columns. I didn't know how to do that until just now. Neat trick.

PHP

From that one query you now have a simple flat table to output. Forgive the brevity, but I think it gets the point across.

$rows=$db->fetch_all_array($sql);

foreach($rows as $row) {
    var_dump($row);
}

Results (from MySQLWorkbench)

PHP pass variable to foreach loop from a mySQL result

Either out put directly fromt eh loop or build an array and then loop through it.

while($rowGetClaim = mysql_fetch_array($resultGetClaim)) {
  echo $rowGetClaim['Name'];
  echo $rowGetClaim['city'];
  echo $rowGetClaim['region'];

}

OR

while($rowGetClaim = mysql_fetch_array($resultGetClaim)) {
  foreach($rowGetClaim as $k => $v{
    echo $v;
  }
}

OR

$names = array();
while($rowGetClaim = mysql_fetch_array($resultGetClaim)) {
  $names[] = $rowGetClaim;
}


foreach($names as $data){
  foreach($data as $k => $v) {
    echo $v;
  }
}

PHP - mysqli_query in foreach loop

Yes, this is OK. mysqli_query returns a mysqli_result object. The documentation says:

5.4.0 Iterator support was added, as mysqli_result now implements Traversable.

Efficient way to get data from mysql in a php foreach loop

Every time you call that function with the DB connection you're opening, querying and closing the connection to the database... this causes a lot of overhead on the SQL server and the PHP/MySQL connector. You can run the server out of resources if there are a lot of queries. Pull it into an array so you only use one DB connection and one query like this:

<?php
$sql = "select * from database.table;";
$result = mysql_query($sql);
while ($db_entry = mysql_fetch_array($result)){

    if ($db_entry['option_name'] == 'timezone')
    { $_timezone = $db_entry['option_default']; }

    if ($db_entry['option_name'] == 'mode')
    { $_mode = $db_entry['option_default']; }

    if ($db_entry['option_name'] == 'email')
    { $_email = $db_entry['option_default']; }

}
?>

Example with PDO (Not tested):

<?php
try {
$DBH = new PDO("mssql:host=$host;dbname=$dbname, $user, $pass");    
}
catch(PDOException $e) {  
   echo $e->getMessage();  
}  

$STH = $DBH->query('select * from database.table');

$STH->setFetchMode(PDO::FETCH_OBJ); 

while($row = $STH->fetch()) {

    switch($row->option_name){
    case 'mode':
    $_mode = $row->option_default;
    break;
    case 'email':
    $_email = $row->option_default;
    break;
    case 'timezone':
    //timezone
    $_timezone = $row->option_default;
    default:
    //do something else
    }

}

?>

---

Related Topics