[PHP] MySQL copy two rows from table to new table
This has been bugging me for too long, and no-one seems to have an answer
I am trying to copy two rows (username and user_email) from phpbb_users to a new table called mail, When I run this script, I get no errors anywhere (apache error log included), so any ideas where im going wrong ?
$sql = 'SELECT username, user_email FROM phpbb_users' ;
if( !$result = $db->sql_query($sql) )
{
$error = true;
$message .= 'Failed to get list for inserting<br /><br />';
}
while( $row = $db->sql_fetchrow($result) )
{
$user_total++;
$sql = 'INSERT INTO mail (name, email) VALUES (' . $row['username'] . ', ' . $row['user_email'] . ')';
if( !$db->sql_query($sql) )
{
$error = true;
$err_msg .= (( !empty($err_msg) ) ? ', ': '') . $row['username'];
}
else
{
$user_success++;
}
}
$message .= $user_success . ' users out of ' . $user_total . ' successfully copied to the table.';Thank in you
Cheers, Marc
Playground | Roll on over . . .
druagord posted this at 22:40 — 3rd November 2003.
He has: 335 posts
Joined: May 2003
try this at least you will see the error if ever it occurs.
i don't know what $db is but i guess it's a class to connect to your db. check the mysql_fetchrow method make sure it's not mysql_fetch_row like the one included in php,
also fetch_row won't give you a keyed array so you have to call $row[0], $row[1] to get the result to use $row['username'] and $row["user_email'] you have to use mysql_fetch_array()
$sql = 'SELECT username, user_email FROM phpbb_users' ;if( !$result = $db->sql_query($sql) )
{
$error = true;
$message .= 'Failed to get list for inserting<br /><br />';
}
while( $row = $db->sql_fetchrow($result) )
{
$user_total++;
$sql = 'INSERT INTO mail (name, email) VALUES (' . $row['username'] . ', ' . $row['user_email'] . ')';
if( !$db->sql_query($sql) )
{
$error = true;
$err_msg .= (( !empty($err_msg) ) ? ', ': '') . $row['username'];
//***************
echo mysql_error();
//****************
}
else
{
$user_success++;
}
}
$message .= $user_success . ' users out of ' . $user_total . ' successfully copied to the table.';
IF , ELSE , WHILE isn't that what life is all about
McPhilly posted this at 23:21 — 3rd November 2003.
They have: 62 posts
Joined: Aug 2001
Ah awesome, I finally got some errors
That times a lot more of the same thing.
Never used array, fairly new to combining php and mysql, still learning. Would you be so kind as to explain a little further ? Or an example of how to use it ?
Many thanks.
Cheers, Marc
Playground | Roll on over . . .
druagord posted this at 23:31 — 3rd November 2003.
He has: 335 posts
Joined: May 2003
your text fields should have ' around them so instead of using those ' use " for the string definition like this (i modifidied the INSERT line)
$sql = 'SELECT username, user_email FROM phpbb_users' ;
if( !$result = $db->sql_query($sql) )
{
$error = true;
$message .= 'Failed to get list for inserting<br /><br />';
}
while( $row = $db->sql_fetchrow($result) )
{
$user_total++;
$sql = "INSERT INTO mail (name, email) VALUES ('" . $row['username'] . "','" . $row['user_email'] . "')";
if( !$db->sql_query($sql) )
{
$error = true;
$err_msg .= (( !empty($err_msg) ) ? ', ': '') . $row['username'];
echo mysql_error();
}
else
{
$user_success++;
}
}
$message .= $user_success . ' users out of ' . $user_total . ' successfully copied to the table.';
forget about the array thing since it seams to work
IF , ELSE , WHILE isn't that what life is all about
McPhilly posted this at 17:56 — 4th November 2003.
They have: 62 posts
Joined: Aug 2001
Excellent, thank you. Shame it was a fairly easy answer, I couldn't figure that out for two / three days.
Thanks again.
m3rajk posted this at 22:38 — 6th November 2003.
They have: 461 posts
Joined: Jul 2003
druagord, in the following example, the variable $db is the db resource you get from mysql_connect() or mysql_pconnect.
it's not needed unless you have multiple databases or want to have code that you can play with later and not have to worry about issues that may arise from a secondary connection.
$errno=mysql_errno($db);
i just looked quickly andnoticed you were trying to figure out what $db is. i am going out on a limb and assuming that it was done based on a script with that. i know i use it just to be through in my coding.
i would look further and make sureit's actually what you are looking for, but the problem was solved
POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.
druagord posted this at 14:09 — 10th November 2003.
He has: 335 posts
Joined: May 2003
thanks m3rajk but from this line i understand it's probably a predefined object
while( $row = $db->sql_fetchrow($result) )the php function is MYsql_fetch_ROW() and fetch_row return an array without any key information.
IF , ELSE , WHILE isn't that what life is all about
McPhilly posted this at 18:24 — 10th November 2003.
They have: 62 posts
Joined: Aug 2001
Seperate phpBB file
// Make the database connection.$db = new sql_db($dbhost, $dbuser, $dbpasswd, $dbname, false);
if(!$db->db_connect_id)
{
message_die(CRITICAL_ERROR, "Could not connect to the database");
}
The above all worked ok. I had problem copying usernames across as some contains characters such as '
I tried this
if (!get_magic_quotes_gpc()) {$username_get = addslashes($row['username']);
}
else {
$username_get = $row['username'];
}
Then used $username_get in the INSERT, but I kept having problem with the very last user, it copied all users minus the last one.
So, although I haven't finished this yet, I probably wont copy usersnames across and go with user_id's instead.
Cheers, Marc
Playground | Roll on over . . .
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.