How to Iterate by Row Through a MySQL Query in PHP

How to iterate by row through a mysql query in php

$result = mysql_query("SELECT id, name FROM mytable");

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    printf("ID: %s  Name: %s", $row[0], $row[1]);  
}

or using MYSQL_ASSOC will allow you to use named columns

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    printf("ID: %s  Name: %s", $row["id"], $row["name"]);
}

PHP for-loop with MySQL rows depending on the MySQL query result

If concurrency or table locks would not be a problem, you could achieve with two query, a SELECT and then a single UPDATE, so you could calculate the final result and modify the value just one time per row.

Increasing the numerosity of records, performance may start to become a problem, so it would worth limiting the SELECT to the first n (which is the positive integer) records, and to do so, be sure to have added an index to the primary incrementing field.

The loop you're asking in point two, would operate on the resultset data, to produce the elaborated result, and the to execute the query update.

$positiveInteger = $_REQUEST['iterations'];
//sanitization...

$sql = 'SELECT id, actions FROM item_counters LIMIT :limit';
$db->connect();
$db->prepare($sql);
$db->bindInteger(':limit',$positiveInteger);
$db->execute();
$rows = $db->fetchAll();

$totalRecords = count($rows);

$incrementValue = intval($positiveInteger / $totalRecords);
$maxIncrementLimit = $positiveInteger % $totalRecords;

$currentRecord = 1;
$results = [];
foreach($rows as $row){
    if($currentRecord <= $maxIncrementLimit){
        $addition = $incrementValue + 1;
    }else{
        $addition = $incrementValue;
    }
    $results[$row['id']] = $row['action'] + $addition;
    $currentRecord++;
}

//then build the query (a little hacky: https://stackoverflow.com/a/27593831/2622455)

$sql = "...";
$db->query($sql);
$db->close();

Looping through a MySQL query in PHP

The de facto way to do this is with a while loop:

$no_of_rows = mysql_num_rows($result);
if ($no_of_rows == 0) {
    return mysql_error();
} else {
    while ($row = mysql_fetch_assoc($result)) {
        // ... use $row['aid'], $row['uname'], etc.
    }
}

Your own code may work, but you were overwriting $result:

$result = mysql_fetch_array($result);

So, after one iteration of the loop, you had lost the result of the query.

Note: mysql_* functions are deprecated due to security issues, you are advised to learn mysqli_* or PDO instead.

PHP iterate through every row

Fetch rows in a loop using mysqli_fetch_assoc. In the loop use variable returned by this function:

<?php
if (! $r = $link->query("SELECT * FROM collections")) {
    // handle error
}

echo '<div id="collectionsDiv">';

while ($row = $r->fetch_assoc()) {
    echo <<<EOS
    <div class="collection" id="{$row['id']}">
        <div class="collection-hover">{$row['collection']}</div>
    </div>
EOS
}

echo '</div>';

$r->free();

Iterate through Mysql Rows in PHP

use like this

<?php
while($row = mysql_fetch_array($result))
{
?>
<table width="581" height="299" border="1">
<tr>
<td>Union Assurance Questionnaire</td>
</tr>
<tr>
<td>1.<?php echo $row['questions']; ?>

</td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td> </td>
</tr>
</table>
<?php
}
?>

You have to use while() loop for iterating all possible answers.

how to iterate through mysql_query() result

while($row = mysql_fetch_assoc($result)) {
   // Your code goes here...
   // OR
   echo "<pre>"; print_r($row); echo "</pre>";
}

php loop through MySQL records - make decision based on row value

You need to output your header only when the date changes. Because the data row needs to be displayed all the time (for each each row) it does not need to be in the if condition at all.

Your code should look something like this:

if ($result->num_rows > 0) {
    echo "<table>";

    while($row = $result->fetch_assoc()) {    

       // insert header stuff on change of $date1
       if ($row["ServiceDate"] <> $date1) { 

           echo "<tr><th>".$row["ServiceDate"]. "(".$date1.")"."</th></tr>";
           echo "<tr><th>Service Date</th><th>Service Time</th>
    <th>Church</th><th>Service</th><th>Service Leader</th></tr>";

            $date1 = $row["ServiceDate"]; 

        } 

          //  echo row data 
          echo "<tr><td>" . 
          $row["ServiceDate"]. "</td><td>" . 
          $row["Service_Time__c"]. "</td><td>" .
          $row["Location__c"]. "</td><td>" .
          $row["PublicName"]. "</td><td>" . 
          $row["FullName"]."</td></tr>";

     }
     echo "</table>";
  } else {
     echo "0 results";
}

How do I loop through a mysql query with php

Here's the basic template for this kind of thing, using built-in php functions (assuming old-style mysql, but similar using other database back-ends, or higher-level libraries). In this example, errors are handled by throwing exceptions, but that's just one way to do it.

1. Connect to the database
2. Make sure connection was successful
3. Run the query
4. Make sure the query didn't fail for some reason (usually a SQL syntax error). If it did fail, find out why and handle that error
5. Check that the query returned at least one row (zero rows typically is a special case)
6. Loop over the returned rows, doing whatever it is you need done.

The exception classes would need to be defined (they're the only non-built-in syntax here, but you shouldn't throw plain-vanilla Exceptions).

Example Code:

<?PHP
//try to connect to your database.
$conn = mysql_connect(...);

//handle errors if connection failed.
if (! $conn){
    throw new Db_Connect_Error(..); 
}   

// (try to) run your query.
$resultset = mysql_query('SELECT ...');

//handle errors if query failed.  mysql_error() will give you some handy hints.
if (! $resultset){ 
    // probably a syntax error in your SQL, 
    // but could be some other error
    throw new Db_Query_Exception("DB Error: " . mysql_error()); 
}

//so now we know we have a valid resultset

//zero-length results are usually a a special case    
if (mysql_num_rows($resultset) == 0){   
    //do something sensible, like tell the user no records match, etc....
}else{
    // our query returned at least one result. loop over results and do stuff.
    while($row = mysql_fetch_assoc($resultset)){
        //do something with the contents of $row
    }
}

How do I loop through a MySQL query via PDO in PHP?

Here is an example for using PDO to connect to a DB, to tell it to throw Exceptions instead of php errors (will help with your debugging), and using parameterised statements instead of substituting dynamic values into the query yourself (highly recommended):

// connect to PDO
$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "password");

// the following tells PDO we want it to throw Exceptions for every error.
// this is far more useful than the default mode of throwing php errors
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// prepare the statement. the placeholders allow PDO to handle substituting
// the values, which also prevents SQL injection
$stmt = $pdo->prepare("SELECT * FROM product WHERE productTypeId=:productTypeId AND brand=:brand");

// bind the parameters
$stmt->bindValue(":productTypeId", 6);
$stmt->bindValue(":brand", "Slurm");

// initialise an array for the results
$products = array();
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $products[] = $row;
}

---

Related Topics