William Stokes wrote:
I have one MySQL table with about 500 rows. I need to read the table one row
at a
time, make some changes to data in one field and then store the changed data
to another table.
I'm using PHP to change the data but I have no idea how to select one row at
a time from the DB table.
A couple of possible options, depending on your specific situation:
1. Use INSERT...SELECT syntax. If you're doing a regular transformation
on all selected rows that can be handled by MySQL function(s), this
would be easiest and fastest. e.g. (table_b.id is an auto-incremented
primary key):
INSERT INTO table_b (id, table_a_id, transformed_value)
SELECT NULL, id, SOME_FUNCTION(mycolumn)
FROM table_a
2. Depending on the size of the records, you might want to just read all
500 rows into a two-dimensional array and use an array fn such as
array_walk to apply a function to change the relevant data and insert
into your new table. To get all rows into an array, you set an array
variable and iterate over the MySQL result set to build the members of
the array.
If you need details on how to do *that*, you'll need to indicate which
version of PHP you're using and whether you're using an abstraction
layer for database access. For an example in PHP 4, you might have:
$db_cursor = mysql_query("SELECT * FROM my_table", $db);
//check for errors [omitted]
//count the records
$recordcount = mysql_num_rows($db_cursor);
//assemble the recordset array
$recordset = array();
for($i=0;$i<$recordcount; $i++)
{
$recordset[] = mysql_fetch_assoc($db_cursor);
}
//clean up, etc. [omitted]
Then use array_walk() or similar...
HTH
--
Max Schwanekamp
http://www.neptunewebworks.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
