Anomalous DB fields; echo discrepancy

[Lienne]

Hi all,

So I'm getting some strange results from one of my scripts. I have an MSSQL DB which has a list of peoples' addresses. I need to do some other processing to the data, so I decided to assign each person a "counter" (basic ID). In comes the troubling script. Bascially, the script iterates through every row in the DB and assigns it a number one higher than the last row. Very simple. But for some reason, it's not working exactly properly. The echo that I put in the script shows everything working perfectly fine. But for some reason, in the DB itself sometimes the numbers are not correct. For instance, one time through it looked like this:
1
2
3
351
5
...
And the crazy thing is that there was no 4. Nowhere. Another time, there were 2 4's. This problem seems to be randomly scattered throughout the rows with various incorrect numbers.

So does anyone know why there would be an inconsistency between the echo output and what is actually put in the DB? I'm quite stumped on this one....help!


[code]<?
include("includes/db.inc.php");

$counter = 0; //init counter

$master_results = mssql_query("SELECT * FROM contacts_temp WHERE deleted='1' AND id=''"); //get resident results
$master_numrows = mssql_num_rows($master_results);

for ($i=0; $i<$master_numrows; $i++) {
$master_row = mssql_fetch_array($master_results);
$street_name = $master_row['street_name'];
$prefix_directional = $master_row['prefix_directional'];
$street_suffix = $master_row['street_suffix'];
$post_directional = $master_row['post_directional'];
$house_number = $master_row['house_number'];

$master_results2 = mssql_query("UPDATE contacts_temp SET counter=$counter WHERE deleted='1' AND id='' AND street_name='$street_name' AND prefix_directional='$prefix_directional' AND street_suffix='$street_suffix' AND post_directional='$post_directional' AND house_number='$house_number'");
echo $counter . "<br>";
$counter++;
}

?>[/code]

[roopurt18]

Looks ok to me.  Why don't you try echo'ing the actual SQL statement.

Also, why use $counter when it's identical to $i?

[Lienne]

I tried echoing the SQL statement, and it looks fine. For some reason though, the database still has some problems. For instance, there are two row 8 and no row 7. But the output looks like:

[code]6: UPDATE contacts_temp SET counter=6 WHERE deleted='1' AND id='' AND street_name='ALABAMA' AND prefix_directional=' ' AND street_suffix=' ' AND post_directional=' ' AND house_number='2105'
7: UPDATE contacts_temp SET counter=7 WHERE deleted='1' AND id='' AND street_name='ALABAMA' AND prefix_directional=' ' AND street_suffix=' ' AND post_directional=' ' AND house_number='2109'
8: UPDATE contacts_temp SET counter=8 WHERE deleted='1' AND id='' AND street_name='ALABAMA' AND prefix_directional=' ' AND street_suffix=' ' AND post_directional=' ' AND house_number='2109'[/code]

[Lienne]

Oh man, I figured it out. I hadn't noticed it earlier. So thank you, your telling me to echo the sql statement actually helped a lot. The house number was the same on both rows, and it's reproducing the problem now.