How to display data from database MSSQL using foreach/for loop in SQLSRV PHP

Question

I'd like to display each rows in a specific column from my db_table. Found below is the code:

$get_sname = sqlsrv_query($conn, "SELECT [columnName] FROM [db_table] inner join [db_table2] on [col_fkey] = [col_pkey] WHERE userName = '$user'",array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
$rdr_sname = sqlsrv_fetch_array($get_sname, SQLSRV_FETCH_ASSOC);
$str = $rdr_sname['[columnName]'];
$rows = sqlsrv_num_rows($get_sname);
if($rows == false){
echo "\nerror\n";
}else{
$a = array($str);
for($i = 0;`$i < $rows;` $i++){
  foreach($a as $b){
     echo implode($a)."</br>";
  }
}

This loop

$a = array($str);
for($i = 0;$i < $rows; $i++){
    foreach($a as $b){
       echo implode($a)."</br>";
    }
}

returns the rows from my db in this format:

rowName

rowName

rowName

what I would like it to return is something like:

rowName1

rowName2

rowName3

I really appreciate all the help I can get on this. Thanks.

`` isn't valid. The slash goes after the "r" and not before the "b" or none at all. — Funk Forty Niner, Dec 08 '19 at 16:03
@FunkFortyNiner works on both sides, either after "r" or before "b". — Carl, Dec 08 '19 at 16:39

Zhorov · Answer 1 · 2019-12-08T17:57:50.307

Explanations:

You need to make some corrections in your code:

Use parameterized statements to prevent SQL injections. Function sqlsrv_query() does both statement preparation and statement execution and can be used to execute parameterized queries.
The code in the question fetches only the first row in the result set, because sqlsrv_fecth_array() is executed only once. You need to execute sqlsrv_fetch_array() in a loop to fetch the data from all rows.

Example:

You may try with the following code:

<?php
// Statements
$sql = "
   SELECT [columnName] 
   FROM [db_table] 
   inner join [db_table2] on [col_fkey] = [col_pkey] 
   WHERE userName = ?
";
$get_sname = sqlsrv_query($conn, $sql, array($user), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
if ($get_sname === false){
    echo print_r( sqlsrv_errors()); 
    echo "<br>";
    exit;
}

// Results
$rows = sqlsrv_num_rows($get_sname);
if ($rows === false) {
    echo "Error"."<br>";
} else {
    while ($rdr_sname = sqlsrv_fetch_array($get_sname, SQLSRV_FETCH_ASSOC)) {  
        echo $rdr_sname['columnName']."<br>";
    }
}

// End
sqlsrv_free_stmt($get_sname);  
?>

you are right, I needed to loop the sqlsrv_fetch_array. I found the solution [here](https://stackoverflow.com/a/29968459/11106866). — Carl, Dec 08 '19 at 17:41
@Carl I'm glad you have a solution to your problem. Just have in mind, that it's not necessary to use two loops to output the data. Thanks. — Zhorov, Dec 09 '19 at 13:44

score 0 · Answer 2 · answered Dec 08 '19 at 15:56

0

Use this code,

first declare the $count variable. like

$count = 0;
$a = array($str);
for($i = 0;$i < $rows; $i++){
    foreach($a as $b){
       echo implode($a).$count."</br>";
       $count++;
    }
}

answered Dec 08 '19 at 15:56

mufazmi

1,103
4
18
37

I'm actually expecting for the output to display the actual [rowName] from my db. not just [rowName1], [rowName2]. My expectation is (e.g [John], [Joe], [Joseph], the code you provided returns [John1], [John2], [John3]. – Carl Dec 08 '19 at 16:03

score 0 · Answer 3 · answered Dec 08 '19 at 17:48

In case somebody lands here looking for answers, here is the code that's fully working.

$get_store = sqlsrv_query($conn, "SELECT [columnName] FROM [db_table] inner join [db_table2] on [col_fKey] = [col_pKey] where userName = '{$user}'");
if($rows == false){
  echo "\nerror\n";
  }
else{
  $sname = array();
while($storename = sqlsrv_fetch_array($get_store, SQLSRV_FETCH_ASSOC))
  {
    $sname[] = $storename;
}
  foreach($sname as $storename){
    echo $storename['storeName'];
}

How to display data from database MSSQL using foreach/for loop in SQLSRV PHP

3 Answers3