a php function inside a mysql query?

Question

I have this query

$query = "INSERT INTO $new_db.wp_users (user_login, user_pass, user_nicename)
select user_email, md5(user_password), user_name from $source_db.users";

and i have this function

function wp_hash_password($password) {
    require_once('class-phpass.php');
    // By default, use the portable hash from phpass
    $wp_hasher = new PasswordHash(8, TRUE);
    return $wp_hasher->HashPassword($password);
}

i need the query to be like this

$query = "INSERT INTO $new_db.wp_users (user_login, user_pass, user_nicename)
select user_email, ". wp_hash_password(user_password) .", user_name from $source_db.users";

but this is failing ...any ideas

Answer 1

You got 2 options:

• rewrite wp_hash_password function in SQL/PL and use it in query
• load result to PHP - modify and send them back with code like this:

$results = mysql_query ( "SELECT user_login, user_pass, user_nicename FROM $source_db.users" );

while ($row = mysql_fetch_assoc($results)) {
  $sql = "$query = "INSERT INTO $new_db.wp_users (user_login, user_pass, user_nicename) VALUES ( ";
  $sql .= $row['user_login'] . ',';
  $sql .= wp_hash_password($row['user_password']) . ',';
  $sql .= $row['user_nicename;
  $sql .= ' ) ';
  mysql_query ( $sql );
} 

Answer 2

You can't. MySQL cannot communicate back to PHP in that way. Only MySQL functions are valid.

You'll have to write a PHP script that does this with iteration.

Answer 3

You'll need to split your query into two queries. Execute the SELECT query first, run your function on the appropriate column in data you got, and then run the INSERT query last.

Note that you'll need to do this one row at a time (or maybe in chunks) in a loop; you don't want to load the entire table into memory.