Predefined counter not updating in select statement

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 />";
}

This isn't the exact code I'm using. I just wrote this as a simplification. Fixed, thanks. — masfrost, Oct 15 '14 at 20:44
Do you need the counter? Could you just select "WHERE nID >= $min AND nID < $maxusers" ? — khartnett, Oct 15 '14 at 20:46
This wouldn't really work. My real select statement is `$selectFirst = "SELECT TOP 1 nDateTime,nUserID FROM TB_EVENT_LOG WHERE nUserID = $usercounter AND nDateTime BETWEEN $today AND $tomorrow";`. I need the first record for each user only. Unless there's a way to get only the first row for each nUserID in my select statement. — masfrost, Oct 15 '14 at 20:58

khartnett · Accepted Answer · 2014-10-15T21:20:27.570

0

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";

edited Oct 15 '14 at 21:20

answered Oct 15 '14 at 20:59

khartnett

831
4
14

This is actually a pretty good idea. The only problem is that there's multiple records for each nID, what I need is the first nDateTime. I edited my question to include the code I have so far. – masfrost Oct 15 '14 at 21:02
I've updated my answer for your edit, hopefully that will work – khartnett Oct 15 '14 at 21:21
I couldn't use GROUP BY because nDateTime cannot be aggregated. They're multiple entries with different timestamps and I only need the first one. Your answer however inspired me to search for the correct answer. I found this to do what I needed http://pastebin.com/RFzsfR3T I got it from here http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group I don't really understand how it works, but I won't use it until I do. Thanks. Bottom line: counters suck! :) – masfrost Oct 15 '14 at 21:48

Predefined counter not updating in select statement

1 Answers1