Page 1 of 1
Populating a new field in an established database
Posted: Mon Mar 07, 2005 1:02 pm
by SawkenDotCom
I have a database with about 1,000 names in it, I want to track if they were sent a welcome email so I have set up a new column that will be just "y" for yes and "n" for no.
How would I go about inserting the "y" into the new database column for those 1,000 accounts?
Posted: Mon Mar 07, 2005 2:19 pm
by Chris Corbyn
Not sure if you can do this but
"INSERT INTO `yourtablename` (mailrecvd) VALUES ('y')"
Otherwise... if you have a primary key with some sort of auto-increment just loop over each row and insert it like this
Code: Select all
$query = "SELECT * FROM `tablename`";
$result = mysql_query($query);
$numrows = mysql_num_rows($result);
for ($i=0; $i<$numrows; $i++) {
$id = mysql_result($result, $i, 'id');
$insert_query = "INSERT INTO `tablename` (`mailrecvd`) VALUES ('y') WHERE id=`$id`";
$insert_result = mysql_query($insert_query);
}
Posted: Mon Mar 07, 2005 2:22 pm
by Burrito
why not just include another field on the table that has the user information and then update that row when the mail has been sent?
Posted: Mon Mar 07, 2005 3:59 pm
by SawkenDotCom
Burrito wrote:why not just include another field on the table that has the user information and then update that row when the mail has been sent?
That's the point, it's just that I already have over a thousand accounts in the table and for the life of me couldn't figure out the best way to make it so that the accounts that already recieved the welcome email were checked by the cron job.
Posted: Mon Mar 07, 2005 4:00 pm
by SawkenDotCom
d11wtq wrote:Not sure if you can do this but
"INSERT INTO `yourtablename` (mailrecvd) VALUES ('y')"
Otherwise... if you have a primary key with some sort of auto-increment just loop over each row and insert it like this
Code: Select all
$query = "SELECT * FROM `tablename`";
$result = mysql_query($query);
$numrows = mysql_num_rows($result);
for ($i=0; $i<$numrows; $i++) {
$id = mysql_result($result, $i, 'id');
$insert_query = "INSERT INTO `tablename` (`mailrecvd`) VALUES ('y') WHERE id=`$id`";
$insert_result = mysql_query($insert_query);
}
Thanks! I will try it later tonight.
Posted: Mon Mar 07, 2005 5:11 pm
by feyd
Posted: Mon Mar 07, 2005 5:50 pm
by Chris Corbyn
d11wtq wrote:Not sure if you can do this but
"INSERT INTO `yourtablename` (mailrecvd) VALUES ('y')"
Otherwise... if you have a primary key with some sort of auto-increment just loop over each row and insert it like this
Code: Select all
$query = "SELECT * FROM `tablename`";
$result = mysql_query($query);
$numrows = mysql_num_rows($result);
for ($i=0; $i<$numrows; $i++) {
$id = mysql_result($result, $i, 'id');
$insert_query = "INSERT INTO `tablename` (`mailrecvd`) VALUES ('y') WHERE id=`$id`";
$insert_result = mysql_query($insert_query);
}
Grrr

I can't believe I wrote this... I must be ready for bed. INSERT??? Grrr some more. *Banging head on wall now*
*cough* UPDATE is what you need yes.
*Banging head on wall again*
Nite then guys
zzzzzzzzzzzzzzzzzzzzzz.........zzz...........
LOL