php function in mysql

onse

hi.. can i use a php function in a mysql query?

thanks.. any help would surely be appreciated. 🙂

bpat1434

which function?

I'm gonna say: No.

PHP functions are run in PHP. mySQL functions in mySQL.

Now, there are plenty of related functions (time() for PHP, NOW() for mySQL) so depending upon the function, you may be able to use a similar mySQL function. Otherwise, there may be a work around.

TobyRT

I'm sure you're right bpat (you're wiser than me!) but I'm not sure if that's what onse is asking.

onse: please elaborate on what it is you want to do.

If you're writing in php and have some SQL you need to send to the server, then yes, you can incorporate certain elements of php into it. eg

$search = 'value that has been sent to be in a form by a user';
$query ="SELECT * FROM my_table WHERE content LIKE '$search'";
mysql_query($query) or die ("There was an error:" mysql_error());

You couldn't do it directly into SQL though. It would have to be processed by the php first.

laserlight

As far as I know there is no way to apply functions written in PHP directly into the SQL statement with MySQL.

You couldn't do it directly into SQL though. It would have to be processed by the php first.

This is not always true. Consider SQLite, where [man]sqlite_create_function/man can be used.

TobyRT

I would consider SQL Lite, if I knew what it was (I'm not much of an expert meself - just trying to help (or maybe hinder, who knows!)

bpat1434

I'm sure you're right bpat (you're wiser than me!)

WARNING! Looks can be deceiving!! 😉

SQLite is a well, beefed up trimmed down version of SQL. Basically it adds functionality while decreasing its usage of resources. It's faster, and more flexible. Although relatively new, I haven't used it much, and I'm not sure exactly how to harness its power effectively.

And to answer onse:
You can't use php functions directly; however, you can concatentate the result of functions into the statements. mySQL (or any SQL server for that matter) has its own set of built-in functions for Date handeling, string manipulation and other things. But these are limited in scope and are sometimes more powerful then the PHP equivilant. If harnessed correctly, mySQL can do a lot of the grunt work and PHP would just have to show the results. But the more you give mySQL to do, the longer you have to wait for a response.

I think you asked if you can do something like:

<?php
function foo()
{
  // Do something here to calculate or create a new value
}

$query = "SELECT * FROM table WHERE foo()=bar()";

Or something along those lines. While in essence at certain times things like this might work, it's not always guaranteed. And if you meant something like:

$query = "SELECT * FROM table WHERE datecolumn=date('I', time())"

Well, mySQL has functions that are better suited for that type of comparison, and would actually be faster.

So PHP functions can't go into an SQL statement; however, some general PHP functions have SQL counterparts.

Sxooter

You can, however, use php functions in another database...

plPHP

plPHP download page

as well as a dozen or more other languages (ruby, perl, tcl, java, C, R, and on and on)

For MySQL you can store PHP in the database, and retrieve it and run it IN the web server using the eval (I think that's the name of it) function.

Sxooter

About SQL Lite, bpat said:

It's faster, and more flexible.

For certain values of "fast". Generally speaking, SQL Lite is lightening fast as a simple "store it once and recall it many times" database, and I would rather use it than MySQL for most stuff.

But, for things where you'll be doing a lot of transactional stuff, it's not a great choice, as it uses the "one big lock" method of handing access contention. At that point, MySQL with innodb tables, or PostgreSQL, or Firebird SQL would all be better choices.

But again, it's all dependent on what you're doing with it.

laserlight

In particular, the author of SQLite wrote to the SQLite mailing list:

D. Richard Hipp wrote:
SQLite is designed to support a smaller number of clients
all located on the same host computer and accessing a portable
data store of only a few dozen gigabytes which is eaily copied
or moved. SQLite is designed to replace fopen().