预定义计数器未在select语句中更新

Question

Here's a simplified code similar to what I'm using. In this one, I'm pulling Names from ID's.

$counter = 0;
$select = "SELECT nID,nName WHERE nID = $counter";
$result = sqlsrv_query($connection, $select);

$maxusers = 10;

while($counter<$maxusers) {
  while($row = sqlsrv_fetch_array($result)) {
      echo $row['nName'];
  }
$counter++
}

What I get is the same name, the counter in the select statement stays at 0.

I had to put the definition of the $select statement and the $result inside the loop, it redefines everything every time we enter the while loop, looks like the code below. That doesn't seem practical and optimal to me. What are the best work-around for situations like these? I'm not really familiar with variable scopes in PHP, I haven't found any good documentation on that matter when it comes to sql functions.

$counter = 0;
$maxusers = 10;

while($counter<$maxusers) {
$select = "SELECT nID,nName WHERE nID = $counter";
$result = sqlsrv_query($connection, $select);
  while($row = sqlsrv_fetch_array($result)) {
      echo $row['nName'];
  }
$counter++
}

Here's the code that I've actually written.

$selectFirst = "SELECT TOP 1 nDateTime,nUserID FROM TB_EVENT_LOG WHERE nUserID = $usercounter AND nDateTime BETWEEN $today AND $tomorrow";
$selectLast = "SELECT TOP 1 nDateTime,nUserID FROM TB_EVENT_LOG WHERE nUserID = $usercounter DateTime BETWEEN $today AND $tomorrow DESC";

$resultFirst = sqlsrv_query($bscon, $selectFirst);      
$resultLast = sqlsrv_query($bscon, $selectLast);


$selectnumberofUsers = "SELECT TOP 1 nUserIdn FROM TB_USER ORDER by nUserIdn DESC";
$usersmaxq = sqlsrv_query($bscon, $selectnumberofUsers);
$usersmax = sqlsrv_fetch_object($usersmaxq)->nUserIdn;


while($usercounter<$usersmax){
    $usercounter = $usercounter + 1;

    while($rowfirst = sqlsrv_fetch_array($resultFirst)) {
        $intime = $rowfirst['nDateTime'];
    }
    echo $intime." ".$usercounter."<br />";
}

Answer 1

Your issue doesn't have to do with variable scope. The $select variable is set once as string with the current value of $counter. Your second example works because this value is reset every time. In your second example however, you're creating a sql statement that gets 1 row (assuming nID is unique), then looping through your result retrieve that one row. You're doing 10 sql calls, but you only need one if you modify your query like so:

$minusers = 0;
$maxusers = 10;

$select = "SELECT nID,nName WHERE nID >= $minusers AND nID < $maxusers ORDER BY nID";

$result = sqlsrv_query($connection, $select);
while($row = sqlsrv_fetch_array($result)) {
    echo $row['nName'];
}

For your actual code, you should be able to get one record per nUserId by using GROUP BY. Try this:

$selectFirst = "SELECT nDateTime,nUserID FROM TB_EVENT_LOG WHERE nUserID >= $usersmin AND nUserID <= $usersmax AND nDateTime BETWEEN $today AND $tomorrow GROUP BY nUserID";