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

If you're trying to set the same password for all users then coreyward is correct. If user_password is a db column and you're trying to operate on multiple rows at once, then Peter and Jonah are correct. — Brad Mace
– Brad Mace, Commented Jan 20, 2011 at 18:27

bensiu · Accepted Answer · 2011-01-20 19:03:46Z

5

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 );
}

edited Jan 20, 2011 at 19:03

answered Jan 20, 2011 at 18:24

bensiu

25.7k56 gold badges81 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jonah Over a year ago

Stored procedures are not really portable.

Peter Bailey Over a year ago

Did I miss the sentence where portability was stated as a requirement? It even looks like a WordPress database - I think native stored procs are completely viable, especially for something that looks like a one-time migration task.

Jonah Over a year ago

@Peter Bailey: Good point. It still might not be viable, as the code loads a class: there's potentially a lot of code to port. But I suppose there's no harm in presenting that as an option.

bensiu Over a year ago

If it one one time taks and PHP class is ready I will transform it in PHP (option 2)

Peter Bailey · Accepted Answer · 2011-01-20 18:21:48Z

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.

answered Jan 20, 2011 at 18:21

Peter Bailey

106k32 gold badges186 silver badges206 bronze badges

Comments

Jonah · Accepted Answer · 2011-01-20 18:29:17Z

2

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.

edited Jan 20, 2011 at 18:29

answered Jan 20, 2011 at 18:21

Jonah

10.1k5 gold badges49 silver badges80 bronze badges

1 Comment

Peter Bailey Over a year ago

This is viable too, but if the data-size is too large then he'll run out of memory. I'd personally do this in a memory-light way and iterate - it might take more time but it also might be necessary.

coreyward · Accepted Answer · 2011-01-20 18:49:39Z

1

Update: I think I misunderstood what you were trying to do (thanks to Peter Bailey for pointing it out).

You need to wrap the result of that function call in single quotes when it is inserted into your query so that it's interpreted as a string in your query, and not a reference to a column:

$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";

edited Jan 20, 2011 at 18:49

answered Jan 20, 2011 at 18:20

coreyward

80.5k21 gold badges148 silver badges176 bronze badges

1 Comment

Peter Bailey Over a year ago

No, that's not what he's asking for. He wants MySQL to select the column using that PHP function, not build the query using that function.

Sakthikanth · Accepted Answer · 2017-03-25 06:45:32Z

I have created a simple php function to use MySQL queries.

Any query can be executed in 1 simple function.

In case of select query, We can get the selected arguments as variable name contains the selected argument value.

For ex :

<?php

q("select user_name,email_id from users where user_id=48");



   echo $user_name;   echo "<br>";
   echo $email_id;
?>

or you can set your own variable name by putting " as "

<?php

q("select user_name as uname, email_id as email from users where user_id=48");



   echo $uname;    echo "<br>";
   echo $email;
?>

result output will be :

  someuser
  someemail

If more number of rows has been selected, the variable name will be created as an array for ex :

<?php

      q("select user_name,user_id from users");

      for($n=0;$n<count($user_name);$n++)
      {

            if(count($user_name)==1)  // if single row is selected
            {

                $username_val=$user_name;
                $user_ids=$user_id;


            }else{
                $username_val=$user_name[$n]; // for multiple rows selected

               $user_ids=$user_id[$n];
            }

             echo $username;

      }

?>

or you can set your own variable name by putting " as "

<?php

      q("select user_name as un,user_id as uid from users");

      for($n=0;$n<count($user_name);$n++)
      {

            if(count($user_name)==1)  // if single row is selected
            {

                $username_val=$un;
                $user_ids=$uid;


            }else{
                $username_val=$un[$n]; // for multiple rows selected
                 $user_ids=$uid[$n];
            }

             echo $username_val; echo " "; 
             echo $user_ids; echo "<br>";

      }

?>

Result output will be : (If the user table has three rows )

User1 4043
User2 4048
User3 4056

Create mysql Connection file ex : mysql_connect_file.php

<?php

$dbc=new mysqli('localhost', 'my_user', 'my_password', 'my_db');

?>

The php function is below

<?php

   require_once './mysql_connect_file.php';
function q($q)
       {

    $main_q=$q;
    $q=  strtolower($q);
      global $dbc;

              $temp=$q;
              $temp=str_replace(" ", "", $temp);
              $temp=  strtolower($temp);
         $temp=".$temp";
              if(strpos($temp, "update")==1 || strpos($temp, "insert")==1 || strpos($temp, "delete")==1 || strpos($temp, "alter")==1 || strpos($temp, "create")==1)
              {
                  $rd2=  mysqli_query($dbc,$main_q);
                  if($rd2)
                  {
                      return TRUE;
                  }
                  else{


       $mysql_err=  mysqli_error($dbc);

              $err=  debug_backtrace();
              $err_line=$err[0]['line'];
              $err_file=$err[0]['file'];
        echo  "<font color='black'>Error at <b>$err_file on line $err_line  </b>query --></font><font color='maroon'>$main_q</font> (<font color='red'> $mysql_err </font> )";

        return FALSE;

                  }

              }elseif(strpos($temp, "select")==1){


     $qn=  str_replace("select ", "", $q);

     $qn=substr($qn,0,  strpos($qn, " from"));
     $qn="$qn,";

       $selc=  str_replace("`","", $qn);
       $qn=  str_replace("`","", $qn);
       $my_var=array();

      $my_nm=array();
       for($m=1;$m<=substr_count($selc, ',');$m++)
       {
              $my_nm[$m]=substr($qn,0,  strpos($qn, ","));

              $qn=substr($qn,strpos($qn, ",")+1, strlen($qn));
              if(strpos($my_nm[$m]," as ")>0)
              {
      $my_var[$m]=  str_replace(" as ", "~", $my_nm[$m]);
      $my_var[$m]=  str_replace(" ", "", $my_var[$m]);


      $my_var[$m]=substr($my_var[$m],strpos($my_var[$m],"~")+1,strlen($my_var[$m]));
              }else
              {
  $my_var[$m]=substr($my_nm[$m],0,  strlen($my_nm[$m]));  
  $my_var[$m]=  str_replace(" ","", $my_var[$m]);
              }

       }

       $rn=mysqli_query($dbc, $main_q);

       if($rn)
      {

              if(mysqli_num_rows($rn)>0)
              {       

               for($t=1;$t<=count($my_var);$t++)
             {

          $$my_var[$t]=array();


             }


    while($row=mysqli_fetch_array($rn,MYSQLI_ASSOC))
    {

           if(mysqli_num_rows($rn)>1)
           {


              for($t=1;$t<=count($my_var);$t++)
             {

             ${$my_var[$t]}[]=$row[$my_var[$t]];
    }

     }else{

             for($t=1;$t<=count($my_var);$t++)
             {
    $$my_var[$t]=$row[$my_var[$t]];

             }


           }
    }

  if(mysqli_num_rows($rn)>1)
  {
     for($t=1;$t<=count($my_var);$t++)
             {
     $GLOBALS[$my_var[$t]]= sel_mr($my_var,$$my_var[$t]);


             }   


             for($t=1;$t<=count($my_var);$t++)
             {
     return $$my_var[$t];


             }
  }
  if(mysqli_num_rows($rn)==1)
  {

              for($t=1;$t<=count($my_var);$t++)
             {
     $GLOBALS[$my_var[$t]]=$$my_var[$t];

             }
             for($t=1;$t<=count($my_var);$t++)
             {
     return $$my_var[$t];

             }

  }



              }else
              {

       for($t=1;$t<=count($my_var);$t++)
             {
     $GLOBALS[$my_var[$t]]=NULL;

             }



             for($t=1;$t<=count($my_var);$t++)
             {
     return $my_var[$t];


             }

              }

      }else
      {

             for($t=1;$t<=count($my_var);$t++)
             {
     $my=  mysqli_error($dbc);
     if($t==1)
     {
            $err=  debug_backtrace();
            $err_line=$err[0]['line'];
            $err_file=$err[0]['file'];
      echo  "<font color='#ef0000'>Error at <b>$err_file on line $err_line  </b>query --></font><font color='maroon'>$q</font> (<font color='red'> $my </font> )";

     }


             }



             for($t=1;$t<=count($my_var);$t++)
             {
     for($p=0;$p<count($$my_var[$t]);$p++)
     {
            $a=$$my_var[$t];
            return $a;    
     }


             }

      }
              }


       }
     function sel_mr($a,$ab)
     {
            for($t=1;$t<=count($a);$t++)
            {
      foreach ($ab as $my)
      {

             ${$a[$t]}[]=$my;

      }
            }

            for($t=1;$t<=count($a);$t++)
            { 
      return $$a[$t];
            }


     } 

?>

Notes :

You can save this code into a file then you can call this function by including that file name

for ex : if your file name is q.php ( --> contains q function ) then you can use the code for another files by including

<?php

include 'q.php';

  q("select user_name from users where user_id=4048");
   echo $user_name; 
?>

Collectives™ on Stack Overflow

a php function inside a mysql query?

5 Answers 5

4 Comments

Comments

1 Comment

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

1 Comment

1 Comment

Comments

Linked

Related