Update not updating

timstring

this has got to be the simplest script in the world, but it don't work. I'm clueless as usual. I modified this script from one that actually worked. I'm at a loss. Here's what I'm trying to make work:

<?php


$host="localhost";
$user="root";
$password="g1G3m#1989";
$Dbname = "CallsToDispatch";


$cxn = mysql_connect($host,$user,$password)
or die (mysql_error() . "couldn’t connect to server");


mysql_select_db($Dbname, $cxn)
or die(mysql_error() . "couldn't select database");


$result = mysql_query("SELECT * FROM CallCountSlim" , $cxn)
	or die ('Santa Claus is watching you  ' . mysql_error());

while ($row = mysql_fetch_array($result))
	{
		$i++;
		$TechNum = $row['TechNum'];
		$CellNum = $row['CellNum'];

			echo $i . ' ' . $TechNum . ' ' . $CellNum . '<br />';

		$result1 = mysql_query("UPDATE CallCountSlim SET IdNum = '$i'")
			or die ("cannot update   ". $TechNum);


	}

?>

$TechNum & $CellNum return the proper values, but the update fails. What simple thing am I overlooking? Oh, don't tell me that the mysql commands have been deprecated. mysql is what I know, and right now, I don't have the time to learn another language.

timstring

Persistance sometimes pays off, sortta.

This at least will update the table:

<?php


$host="localhost";
$user="root";
$password="g1G3m#1989";
$Dbname = "CallsToDispatch";


$cxn = mysql_connect($host,$user,$password)
or die (mysql_error() . "couldn’t connect to server");


mysql_select_db($Dbname, $cxn)
or die(mysql_error() . "couldn't select database");

		$Quest = "SELECT * FROM CallCount";
		$result = mysql_query($Quest, $cxn)
			or die ('Santa Claus is watching you' . mysql_error());

		while ($row = mysql_fetch_array($result))
			{
				$TechNum = $row['TechNum'];
				$CellNum = $row['CellNum'];

				$i++;

				echo $i . ' ' . $TechNum . '<br />';

				$result1 = mysql_query("UPDATE `CallCount` SET `IdNum` = '$i'")
					or die ("cannot update   ". $TechNum);

			}

			?>

Now the question is, How do I read from one row, write to that row, and then move on to the next row. The way the script is set up now, the update sets IdNum = $i, and doesn't advance to the following record. So, the script writes $i to all 52,215 rows, advances to the next number, and writes that on all 57,215 records, and on and on, and on.

Weedpacket

What are you actually wanting to do? You have an UPDATE statement that sets IdNum to $i, but without any WHERE conditions to narrow down exactly which row or rows you want updated, it will update all of them, as you've found. What should those conditions be? We can't necessarily divine what the objective of the code if it doesn't work because it is - by definition - wrong. (This is where comments and useful error messages come in: the code tells you what is being done, the comments explain why it is being done, and the error messages say what broke.)

Looking at that code (though, remember, it is wrong so my guesses may be wrong as well) I'm guessing that the whole thing could be replaced by a single UPDATE statement, without any need for SELECTs or a loop to "read from one row, write to that row, and then move on to the next row".

Incidentally, since you seem to be finding answers even while you're writing posts, have you considered getting yourself a rubber duck?

timstring

It's fairly obvious what I'm trying to accomplish. I want to set IdNum on the first row to 1. I want to set IdNum to 2 on the next row and so on until all 57,215 rows are numbered. I have tried different 'WHERE' clauses in the updater, but none have worked so far.

Weedpacket

There is no inherent order to rows in a relational database table - it is necessary to supply one when selecting via an ORDER BY clause.

You will need to determine some way of distinguishing one row from another based on information in the table, so that you can craft a WHERE clause to update that row and not another.

You could I guess read all the rows into an array; delete them from the table; then reinsert them all back into the table in a loop, with the value of the loop counter included. (Or maybe create another table that has the same structure as the existing table but with this ID column autoincrementing, then copy everything except the id column into the new table, empty the original table, then copy everything (including the id column) back.)

I don't know what MySQL would do if you tried to add an autoincrementing primary key column to an existing table.

bemore

Weedpacket;11023495 wrote:
I don't know what MySQL would do if you tried to add an autoincrementing primary key column to an existing table.

It works well, as it will auto increment each existing row within the table. I just tested it with a new table and a bunch of rows. It labeled them from the top down 1-50.

bradgrafelman

bemore;11023497 wrote:
It works well, as it will auto increment each existing row within the table. I just tested it with a new table and a bunch of rows. It labeled them from the top down 1-50.

The top... of what? Without an ORDER BY clause, the rows are "displayed in no particular order" (reference).

timstring

At the time of this post, I had no key to this table, hence the reason to add an IdNum field. I finally took the wimp's way out and re-imported the CSV file, this time with an index.

Weedpacket

timstring wrote:
re-imported the CSV file, this time with an index.

If I'd known you were importing the data from elsewhere, that's what I would have suggested.