Loop to select and update records using classes

gulysses3

I am using a group of classes to manipulate MySQL tables. I would like to be able to go through a table one line at a time to convert fields from all upper case to word upper case.

The problem I have, I think anyway, is that my result set from the first query is abandoned by the second query. I am wondering if there is an easy way around this? Thanks in advance.

Here is the code:

require("./classes/db_mysql.class");
require("./classes/connection.class");
$query = "select currentid, first, last from current";
$db=new connection;
$db->query($query);
While($db->next_record()) {
$first=ucwords(strtolower($db->Record[first]));
$last=ucwords(strtolower($db->Record[last]));
$currentid=$db->Record[currentid];
$query="UPDATE current set first='$first', last='$last' where currentid=$currentid";
$db->query($query);

echo $db->Record[first];
}

Shrike

What does the connection class look like? Could you use the

...[/PHP vBulletin tag when posting code please.

tunage

Try creating a new instance of the db connection in the while loop as it looks like the first $db result set will be overwritten as soon as you query in the loop -

require("./classes/db_mysql.class"); 
require("./classes/connection.class"); 
$query = "select currentid, first, last from current"; 
$db=new connection; 
$db->query($query); 
While($db->next_record()) { 
$db2=new connection; 
$first=ucwords(strtolower($db->Record[first])); 
$last=ucwords(strtolower($db->Record[last])); 
$currentid=$db->Record[currentid]; 
$query="UPDATE current set first='$first', last='$last' where currentid=$currentid"; 
$db2->query($query); 
echo $db2->Record[first]; 
}

Shrike

It seems to me some refactoring is in order. A class which queries a database and also iterates over a record set, perhaps this should be two separate classes. Instantiating a second instance of a class to overcome it's shortcomings is not the answer.

gulysses3

Your solution worked quite well. Not sure about the ramifications performance wise, etc. alluded to in the previous post, but in a pinch it solved my delimma. Thanks for your help.