• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

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

0
Jonathan Greenberg
Asked:
Jonathan Greenberg
  • 5
  • 3
  • 2
1 Solution
 
PortletPaulCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 
PortletPaulCommented:
:) 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now