Solved

Problem with a MySQL UPDATE statement

Posted on 2013-06-17
10
437 Views
Last Modified: 2013-06-17
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
Comment
Question by:Jonathan Greenberg
  • 5
  • 3
  • 2
10 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39255219
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
 

Author Comment

by:Jonathan Greenberg
ID: 39255246
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
 

Author Comment

by:Jonathan Greenberg
ID: 39255283
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
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 39255289
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
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 39255293
You can also omit the WHERE clause: in this case, all your records will be updated
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Jonathan Greenberg
ID: 39255299
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39255305
:) 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
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 39255308
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
 

Author Comment

by:Jonathan Greenberg
ID: 39255314
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
 

Author Comment

by:Jonathan Greenberg
ID: 39255318
Paul, the Urban Dictionary recommends CTS (chuckle to self).  Just a thought :)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now