Calling a php Function from a SELECT statement

[LouisC4]

Hello, How can I call a php fucntion from within a MySQL SELECT statement?

What I have is:

$result = mysql_query("SELECT GetCountry(IPAddrss), IPAddrss FROM pgstats");

GetCountry is my php function, and it does work when I call it like this:

echo GetCountry($ip);

What am I doing wrong?

TIA,
Louis

 

[OsakaWebbie]

Try this:

$result = mysql_query("SELECT concat('GetCountry(',IPAddrss,')' AS FunctionCall, IPAddrss FROM pgstats");

I added the AS part so that if you are handling the results in a way that uses the field names (like mysql_fetch_object()), that field will have an intelligible name.

The reason yours didn't work is that you were asking MySQL to interpret GetCountry as a function of its own, rather than returning the text so that PHP could do something with it. My version will return the name of the function rather than an SQL error, but your next challenge will be how to get a string variable in PHP to be interpreted as a function call - I don't know how to do that, but I think I got you past the SQL part of your snag.

I don't know what the rest of your PHP code is doing, but why are you not simply calling the function after you get the results? For example:

$result = mysql_query("SELECT IPAddrss FROM pgstats");
$row = mysql_fetch_object($result); //just getting the first one as an example
echo GetCountry($row->IPAddrss);

Just a thought.

 

[LouisC4]

Osaka, thanks for your response.

But unfortunately that wont work for me. This is what I am trying to do:

$result = mysql_query("SELECT GetCountry(IPAddrss) AS Country, Count(DISTINCT IPAddrss) AS Count FROM pgstats GROUP BY 'Country'");

The reason I need the php function in the SELECT statement is so that I can group the result by Country.

Do you know if it is possible to call a php function from within a MySQL SELECT statement?

Thanks,
Louis

 

[lgarner]

You cannot call a PHP function within a SELECT statement; MySQL has no knowledge of PHP functions. You can include the results of a function call in the select as so:

$query = "select ". GetCountry($ip) ." as Country" ...

This will call the function "GetCountry" and insert the result into the $query variable at that point.

The PHP forum (and

http://www.php.net)

will help more with PHP constructs.

 

[OsakaWebbie]

Maybe you could query for the IP addresses, loop through them in PHP to get the countries, make a temporary table in MySQL with the results, and then do the GROUP BY query on the temp table.