How to Loop Through a MySQL Result Set

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

Looping Over Result Sets in MySQL

Something like this should do the trick (However, read after the snippet for more info)

CREATE PROCEDURE GetFilteredData()
BEGIN
  DECLARE bDone INT;

  DECLARE var1 CHAR(16);    -- or approriate type
  DECLARE var2 INT;
  DECLARE var3 VARCHAR(50);
  
  DECLARE curs CURSOR FOR  SELECT something FROM somewhere WHERE some stuff;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  DROP TEMPORARY TABLE IF EXISTS tblResults;
  CREATE TEMPORARY TABLE IF NOT EXISTS tblResults  (
    --Fld1 type,
    --Fld2 type,
    --...
  );

  OPEN curs;

  SET bDone = 0;
  REPEAT
    FETCH curs INTO var1, var2, var3;

    IF whatever_filtering_desired
       -- here for whatever_transformation_may_be_desired
       INSERT INTO tblResults VALUES (var1, var2, var3);
    END IF;
  UNTIL bDone END REPEAT;

  CLOSE curs;
  SELECT * FROM tblResults;
END

A few things to consider...

Concerning the snippet above:

• may want to pass part of the query to the Stored Procedure, maybe particularly the search criteria, to make it more generic.
• If this method is to be called by multiple sessions etc. may want to pass a Session ID of sort to create a unique temporary table name (actually unnecessary concern since different sessions do not share the same temporary file namespace; see comment by Gruber, below)
• A few parts such as the variable declarations, the SELECT query etc. need to be properly specified

More generally: trying to avoid needing a cursor.

I purposely named the cursor variable curs[e], because cursors are a mixed blessing. They can help us implement complicated business rules that may be difficult to express in the declarative form of SQL, but it then brings us to use the procedural (imperative) form of SQL, which is a general feature of SQL which is neither very friendly/expressive, programming-wise, and often less efficient performance-wise.

Maybe you can look into expressing the transformation and filtering desired in the context of a "plain" (declarative) SQL query.

PHP loop through mysql results and count

If x==1 and AREAID 1 is missing, and $row['areaid'] ==2, then you do not find the rest.

You do not find 1, you say that it's missing, you increment x, which might now match $row['areaid'] if it is 2, but you also get a new row, losing a possible AREAID 2 etc.

How about

$x=0;
while($row = do_fetch_result($result)){
    $areaid=$row['areaid'];
    $areaname=$row['areaname'];
    while($x < $areaid) {
        echo "AreaID:".$areaid;
        echo "not found".$x."<br>";
        $x=$x+1;
    }
    echo "AreaID:".$areaid;
    echo "found".$x."<br>";
    $x=$x+1;
}
while($x <= 24) {
    echo "AreaID:".$areaid;
    echo "not found".$x."<br>";
    $x=$x+1;
}

How can I loop through a MySQL result set more than once using the mysql_* functions?

This is how you can do it:

$result = mysql_query(/* Your query */);
while($row = mysql_fetch_assoc($result)){
 // do whatever here...
}

// set the pointer back to the beginning
mysql_data_seek($result, 0);
while($row = mysql_fetch_assoc($result)){
 // do whatever here...
}

However, I would have to say, this doesn't seem the right way to handle this. Why not do the processing within the first loop?

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();

Loop through Resultset in php

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

Loop through a set of mysql SELECT results, everything in query language

The 'iteration through rows' is actually implicit when you work in sql. if you do select a+b from table, it will iterate through all rows and return a+b for each.

For your purposes you can probably use something like

select a+(select other_thing from other_table)
from table

you can even use conditions between the two queries like

select month, 
category, 
sum(sales), 
sum(sales)/(select month, sum(sales) from table as innertable where innertable.month=outertable.month group by 1) as category_percentage_of_monthly
from table as outertable
group by 1,2

---

Related Topics