MySQLi to SQLSRV migration - MySQLi's fetch_assoc() vs SQLSRV's fetch()

Question

For my sins, I have to migrate (or, put simply, to rewrite) the backed functionality that was written to work solely with MySQLi. Now I am trying to do everything in PDO but for some bits of the code I think that it can be sped up by reusing bits of the old script and re-purposing them.

So far it has worked like a charm - changing the functions slightly and doing small to almost insignificant changes to the code to get it working. But here I got stumped.

I have always used while($row = $result->fetch_assoc()) to print all of the rows I have off a database. I really do want to keep the code similar to the structure of the old one. Down bellow is my attempt at having the rows printed but in the SQLSRV it does not work the same way. I can easily display the rows (with no content) by using a for loop like for ($i=0; $i < $rowCount; $i++) but that deviates from my code too much.

After observing my sample solutions beneath, is it possible to recreate the code above into an SQLSRV solution AND keep the same structure?

My working MySQLi solution:

                  $sql = "SELECT * FROM tasks ORDER BY PRIORITY DESC;";
                  $result = $conn->query($sql);// $conn is `$conn = new mysqli($servername, $username, $password, $db);`
                  if ($result->num_rows > 0) 
                  {
                     while($row = $result->fetch_assoc()) 
                     {
                        echo "<br> FROM :".$row["FROM"];
                        echo "<br> DEPT :".$row["DEPT"];
                        echo "<br> TASK_NAME :".$row["TASK_NAME"];
                        echo "<br> PRIORITY :".$row["PRIORITY"];    
                     }
                  }

And here is my attempt at solving the issue using MS SQLSRV:

                $stmt = $GLOBALS['conn']->prepare("SELECT * FROM tasks;"); 
                $stmt->execute();
                $stmt->fetchAll();
                //
                $rowCount = $stmt->rowCount();   

                //echo "<br> Row Count: ". $rowCount; 

                  while ($rowCount > 0)
                  {
                      for ($row = $stmt->fetch())// For evident reasons that doesn't not work 
                      {     
                            echo "<br> FROM :".$row["FROM"];
                            echo "<br> DEPT :".$row["DEPT"];
                            echo "<br> TASK_NAME :".$row["TASK_NAME"];
                            echo "<br> PRIORITY :".$row["PRIORITY"]; 
                      }
                  }

I need that condition as my script does something else if the rows is equal or smaller to 0. Though removing that bit does very little. The webpage just doesn't print the table out with error being prompted. — Jeanie Miflin, Apr 04 '19 at 08:05
@Barmar its actually `while` not `for`. I didn't notice it as I was kind of writing an example script without the intricacies of the script. — Jeanie Miflin, Apr 04 '19 at 08:07

score 1 · Answer 1 · answered Apr 04 '19 at 08:04

1

Get rid of $stmt->fetchAll();. That's fetching all the rows, so there's nothing left for the loop to process. And the loop should use while, not for.

Or assign the result of fetchAll() to a variable, and loop over that.

$rows = $stmt->fetchAll();
if (count($rows) > 0) {
    foreach ($rows as $row) {
        echo "<br> FROM :".$row["FROM"];
        echo "<br> DEPT :".$row["DEPT"];
        echo "<br> TASK_NAME :".$row["TASK_NAME"];
        echo "<br> PRIORITY :".$row["PRIORITY"]; 
    }
}

answered Apr 04 '19 at 08:04

Barmar

741,623
53
500
612

Yes, I realised I had input the sample code incorrectly as I typed it in without checking its `while`, not `for`. Furthermore, I tried your code - now my `$rowCount` returns -1 and I get no rows off the database. I am not sure whats happening... – Jeanie Miflin Apr 04 '19 at 08:12
`$stmt->rowCount()` doesn't work for `SELECT` queries in all databases. – Barmar Apr 04 '19 at 08:13
It says it in the [documentation](https://www.php.net/manual/en/pdostatement.rowcount.php): **For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement.** – Barmar Apr 04 '19 at 08:14
Why do you need to use anything? If no rows were returned, the loop will end immediately. – Barmar Apr 04 '19 at 08:15

MySQLi to SQLSRV migration - MySQLi's fetch_assoc() vs SQLSRV's fetch()

1 Answers1