a php function inside a mysql query?
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.
5 Answers 5
- 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))
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.
@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.
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.
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.
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.
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"; 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.
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.
or you can set your own variable name by putting » as «
If more number of rows has been selected, the variable name will be created as an array for ex :
or you can set your own variable name by putting » as «
else < $username_val=$un[$n]; // for multiple rows selected $user_ids=$uid[$n]; >echo $username_val; echo " "; echo $user_ids; echo "
"; > ?> 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
The php function is below
else< $mysql_err= mysqli_error($dbc); $err= debug_backtrace(); $err_line=$err[0]['line']; $err_file=$err[0]['file']; echo "Error at $err_file on line $err_line query -->$main_q ( $mysql_err )"; 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 "Error at $err_file on line $err_line query -->$q ( $my )"; > > for($t=1;$t <=count($my_var);$t++) < for($p=0;$p> > > > 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]; >> ?> 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
Выбор данных из таблиц базы данных MySQL
В этом уроке вы узнаете, как выбирать записи из таблицы MySQL с помощью PHP.
SQL-запрос с использованием оператора SELECT
На предыдущих уроках мы узнали, как создавать базу данных и таблицу, а также вставлять данные. Теперь пришло время получить данные, которые были вставлены в предыдущем уроке. Для выбора записей из таблиц базы данных используется оператор SQL SELECT .
Мы можем выбрать несколько столбцов из таблицы, разделив каждое имя столбца запятой:
Мы можем выбрать все столбцы в таблице с помощью символа звездочки ( * ):
Давайте сделаем SQL-запрос с использованием оператора SELECT , после чего мы выполним этот SQL-запрос, передав его PHP функции mysqli_query() для получения данных таблицы.
В нашей таблице persons базы данных MySQL есть следующие записи:
Код PHP в следующем примере выбирает все данные, хранящиеся в таблице persons (использование символа звездочки ( * ) вместо имени столбца позволяет выбрать все данные в таблице):
Example
// Попытка выполнения запроса select $sql = "SELECT * FROM persons"; if($result = mysqli_query($link, $sql)) < if(mysqli_num_rows($result) >0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = mysqli_fetch_array($result))< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Доступный набор результатов mysqli_free_result($result); > else < echo "Записей, соответствующих вашему запросу, не найдено."; >> else < echo "ОШИБКА: не удалось выполнить $sql. " . mysqli_error($link); >// Закрыть соединение mysqli_close($link); ?>connect_error); > // Попытка выполнения запроса select $sql = "SELECT * FROM persons"; if($result = $mysqli->query($sql))< if($result->num_rows > 0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = $result->fetch_array())< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Доступный набор результатов $result->free(); > else < echo "Записей, соответствующих вашему запросу, не найдено."; >> else< echo "ОШИБКА: не удалось выполнить $sql. " . $mysqli->error; > // Закрыть соединение $mysqli->close(); ?>setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ОШИБКА: не удалось подключиться. " . $e->getMessage()); > // Попытка выполнения запроса select try< $sql = "SELECT * FROM persons"; $result = $pdo->query($sql); if($result->rowCount() > 0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = $result->fetch())< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Доступный набор результатов unset($result); > else < echo "Записей, соответствующих вашему запросу, не найдено."; >> catch(PDOException $e)< die("ОШИБКА: не удалось выполнить $sql. " . $e->getMessage()); > // Закрыть соединение unset($pdo); ?>Объяснение кода (процедурный стиль)
В приведенном выше примере данные, возвращаемые функцией mysqli_query() , хранятся в переменной $result . Каждый раз при вызове функции mysqli_fetch_array() она возвращает следующую строку из набора результатов в виде массива. Цикл while используется для просмотра всех строк в наборе результатов. Наконец, к значению отдельного поля можно получить доступ из строки, передав индекс поля или имя поля переменной $row , например $row[‘id’] или $row[0] , $row[‘first_name’] или $row[1] , $row[‘last_name’] или $row[2] , и $row[’email’] или $row[3] .
Если вы хотите использовать цикл for , вы можете получить значение счетчика цикла или количество строк, возвращаемых запросом, передав переменную $result в функцию mysqli_num_rows() . Это значение счетчика цикла определяет, сколько раз этот цикл должен выполняться.
Результат выполнения кода:
id first_name last_name email 1 Peter Parker peterparker@mail.com 2 John Rambo johnrambo@mail.com 3 Clark Kent clarkkent@mail.com 4 John Carter johncarter@mail.com 5 Harry Potter harrypotter@mail.com 6 Ron Weasley ronweasley@mail.com
using a php function inside of mysql SELECT query?
We have a database with millions of products and prices. We’re trying to add a filter onto our sql statement so that we can narrow down what we’re looking for and then display it on our table. Some of the things we wanted to filter are related to pricing which is giving us a problem. Since our prices are fairly dynamic, we currently use a php function to calculate our fees in real time rather than store them inside the database. We pass the current price through the function and then display it in the table:
echo profit('99.99'); // output: 12.19 The problem with this is we can’t run these calculations inside the query using our function to find results:
$query = "SELECT * FROM `products` WHERE ".profit(."`price` + `shipping` + `acquisition` + `fees`".)." > 10 "; Is there anyway to get around this? Do we need to iterate through all the table values? We’re trying to avoid this since the database is so large. Thanks for any help!
The way around is to run the same calculation in the query that is performed by profit() or to create a user defined function in MySQL which does it.
If profit() contains logic beyond numeric calculation dependant on the state of your PHP code, you will need to first do a query for the columns that feed its input param and then execute a second query with the function in place.
4 Answers 4
You can create a function in MySQL and make this function estimate the profit for you.
I don’t know how complex your profit function is since you don’t show the code. If your «profit» function is changing constantly: you’re doing it wrong. Code shouldn’t change all the time and it is likely you should change its design.
I would go with above solution. You could also go ahead and at a column that indicates the profit. Update said column whenever the profit might change. This is very bad if products table is somewhat big (note: by somewhat big I mean over 100 thousand rows). You could just update them on a batch process and it would just take about a couple of minutes if the column isn’t indexed, and not much if it is. If you have over 100 thousand rows of products, that means your store is HUGE and there is no excuse for not using above solution for the simplicity of adding a column.