Update not updating

timstring

This seems to be a recurring theme around here. The update command works about two-thirds of the time--roughly 33600 records are not being updated properly. Here's the routine:


$Quest = "SELECT * FROM TOAWorkorders";
 		$FindTechResult = mysql_query($Quest, $cxn)
			or die ('The easter bunny is watching you' . mysql_error());

	while ($row = mysql_fetch_array($FindTechResult))
		{			
			if (strpos($BBT, 0, 3) != 'Sys')
				{

					$IdNum = $row['IdNum'];

					$BBT = $row['BBT'];		
					$BBTArray = explode("-", $BBT);
					$TechNum = $BBTArray["0"];
					$Title = $BBTArray["2"];

					$Name = explode(" ", $BBTArray['1']);
					$FirstName = $Name["0"];
					$LastName = $Name["1"];
					}

	echo $TechNum . ' !! ' . $FirstName . ' !! ' . $LastName . ' !! ' . $Title . '<br>';


			$Quest = "UPDATE TOAWorkorders SET TechNum = '$TechNum', FirstName = '$FirstName', LastName = '$LastName', Title = '$Title' WHERE IdNum = '$IdNum'";
			$result = mysql_query($Quest, $cxn)  or die(mysql_error());	  
	 }

Why does the update work some of the time, but not all of it?

Weedpacket

                if (strpos($BBT, 0, 3) != 'Sys')

Where does $BBT get its value from, here? You don't set it until later.

Also, you don't need to quote numeric array indexes. And you don't need to quote values for numeric fields in SQL.

And you could probably do all that with a single UPDATE statement, instead of one SELECT and who knows how many UPDATEs. You may know MySQL better than me, but in PostgreSQL I'd replace all that code with

UPDATE TOAWorkorders
SET
	TechNum   = substring(BBT from '^[^-]*'),
	FirstName = substring(BBT from '^[^-]*-([^ ]*)'),
	LastName  = substring(BBT from '^[^-]*-[^ ]* ([^-]*)'),
	Title     = substring(BBT from '^[^-]*-[^-]*-([^-]*)')
WHERE
	substring(BBT from 1 for 3) = 'Sys'

As ever, though, it would probably make more sense to put the data into the database properly in the first place.

timstring

I understand what you are trying to do with the '*' for 'truncate', however, your sin tax eludes me. I have tried everything i can think of (which isn't much, but I can research) to fold, spindle, and mutilate your code, but nada. Yes, I did put in 'mysql_query()'

As ever, though, it would probably make more sense to put the data into the database properly in the first place.

Don't I wish. I am just playing with the records that I'm given. I have asked repeatedly to break the BBT field into logical columns, but the powers-at-be don't want to shell out the $1,500 needed to get the software developer to make any changes. I know, sounds stupid to me, too.

Weedpacket

timstring wrote:
sin tax

You mean syntax, right? A "sin tax" is something governments impose on socially destructive yet legal activities to pay for cleaning up the resulting mess.

Yes, I did put in 'mysql_query()

Well, that wouldn't work, since as I said it was for PostgreSQL. MySQL no doubt has different string manipulation functions.

the powers-at-be don't want to shell out the $1,500 needed to get the software developer to make any changes

So they don't want to pay the software developer for any software development, but they're happy to pay you for software development? What makes them think they're going to get any software developed that way?

Oh, and did you read the first line of what I wrote?

timstring

You mean syntax, right? A "sin tax" . . .

Of course, I mean syntax. Some people just don't have a sense of humor.

Where does $BBT get its value from, here? You don't set it until later.

Give me a duck

You may know MySQL better than me

I thought you were being facetious