Problem with a MySQL UPDATE statement

I'm trying to change all incidences of "FAU" to "Florida Atlantic University" in a column of a MySQL database table.  I'm a complete knucklehead, and my script isn't working.  Could someone tell me what I'm doing wrong and how to fix it?

Thanks!

<?php

include_once('./db.inc.php');

$i = 1;

while ( $i < 23000 ) {
	$sql = "SELECT school FROM mytable WHERE pid = $i";
	$result = mysql_query($sql) or die( "Query Error: " . mysql_error() );
	
	$row = mysql_fetch_assoc($result);
	
	$school = $row['school'];
	
	if ( $school == "FAU" ) { $school = "Florida Atlantic University"; }
		
	$sql = "UPDATE mytable SET school='{$school}' WHERE pid = $i";
	$result = mysql_query($sql) or die( "Query Error: " . mysql_error() );
	
	$i++;
}

Open in new window

Jonathan GreenbergAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
I'm also a knucklehead it seems - the sql part looks Ok

I do wonder why you are looping 23000 times for this though

UPDATE mytable
SET school='Florida Atlantic University'
WHERE school = 'FAU'

would do it in one pass (in sql)
0
 
Jonathan GreenbergAuthor Commented:
Thanks, Paul, but still nothing.

Here's the complete script that I just tried running:

<?php

include_once('./db.inc.php');

$sql = "UPDATE mytable
	SET school='Florida Atlantic University'
	WHERE school = 'FAU'";
$result = mysql_query($sql) or die( "Query Error: " . mysql_error() );
	
?>

Open in new window

Is that not what you meant?

I was looping because I thought it was necessary to hit all of my nearly 23,000 rows.  In case it's not obvious, I'm really pretty inexperienced with MySQL -- nevertheless, that's how I've always done it in the past.  Unnecessary?
0
 
Jonathan GreenbergAuthor Commented:
Sorry, my bad, I had my db.inc.php pointing to the wrong database!  The best-written script in the world won't work that way!

With that small correction, the script now works.  Total knucklehead I tell ya!

Thanks for your help, Paul.

Regards,
Jon
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Marco GasiFreelancerCommented:
The loop is not necessary: your second snippet is  ok. Do you get any error message?
Secondly, can you post your table structure here? Maybe the field definition can answer to your question
0
 
Marco GasiFreelancerCommented:
You can also omit the WHERE clause: in this case, all your records will be updated
0
 
Jonathan GreenbergAuthor Commented:
Thank you for trying to help, marqusG.  It was just a really obvious mistake that I was missing: I had my db.inc.php file connecting to another database with the same contents.  The script worked on the other database -- I just wasn't looking at that one.  (Fortunately this is all in my test environment, not the production environment.)

It's all working fine now.  Thanks!
0
 
PortletPaulfreelancerCommented:
:) no connection = no result I have found lol (how does one do chuckle?)

happy you got the result though & thanks for the points, cheers, Paul
0
 
Marco GasiFreelancerCommented:
Well. But I suggest you to use a more concise query and without any loop. Simply use

$sql = "UPDATE mytable SET school='Florida Atlantic University'";

That's all.

Good luck with your project.
Marco
0
 
Jonathan GreenbergAuthor Commented:
Thanks, Marco, but I need to leave the WHERE school = 'FAU' part in.  There are about 50 different schools, and I'd have a real problem if I named them all Florida Atlantic University!

But I see now how unnecessary the looping was.  I was running the script an extra 22,999 times.

Thanks again!

Regards,
Jon
0
 
Jonathan GreenbergAuthor Commented:
Paul, the Urban Dictionary recommends CTS (chuckle to self).  Just a thought :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.