Solved

Problem with a MySQL UPDATE statement

Posted on 2013-06-17
10
440 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 31

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 31

Expert Comment

by:Marco Gasi
ID: 39255293
You can also omit the WHERE clause: in this case, all your records will be updated
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 31

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

895 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

16 Experts available now in Live!

Get 1:1 Help Now