PHP function within a mysql select statement.

[mctoys]

Hi Guys

 

I don't know if this is possible but can someone point me in the right direction. I have a php function which takes two inputs and returns an output. for simplicity's sake let's say it's an addition function. What I want to do is use a mysql select statement to show all the rows from a database where field1 and field2 equal '3'. Here's the sort of thing I mean.

 

function addNumbers($one,$two) {

return $one + $two;

}

 

mysql_query("SELECT * FROM table WHERE 'addNumbers(field1,field2)' = '3'");

 

What I actually want to do is a lot more complex than this but I am trying to understand how to make the syntax work in simple terms first.

 

Can anybody help?

 

Many Thanks

Dan

 

 

[gristoi]

SELECT * FROM table WHERE field1 + field2 = '3'"

given that field 1 and 2 where both INT fields

[mctoys]

Thank you but It's not really what I need. The function I am actually using isn't just addition it's taking a text field, analysing it in comparison with the second variable and returning a relevant result from another database! What I need to know is how to get the syntax right on the example I have shown!

 

Cheers

Dan

 

[gristoi]

You cant execute or call php functions directly from within an sql statement

[mctoys]

OK - thank you

 

Any suggestions as to how I might achieve the same goal?

 

 

[trq]

What exactly is the problem?

[mctoys]

OK

 

I have made a site for displaying song chords and each user can change the key of the song on the fly This key change is stored in a database so it always displays in that key for that user. The original song, chords and key are in one table and the user's preference of key for each song is in another table. My function takes the original key for example 'C#', and a value for the number of semitones that the song is transposed, and outputs the new note. for example changeKey('C#','2'); would return 'Eb'.

 

I want to be able to list all the songs in a certain key. This list will be dynamic for each user as they have set their preferences for the key of each song.

 

My select statement looks like this:

 

$songResult = mysql_query("SELECT chords.id,chords.title,chords.artist,chords.keynote,userkeys.songid,userkeys.keychange FROM chords LEFT JOIN userkeys ON chords.id = userkeys.songid WHERE keynote = 'C' ORDER BY title");

 

What I need to do is perform my function on chords.keynote and userkeys.keychange to return the key which is being searched for.

 

Complicated I know....

 

 

[Psycho]

As already stated, you cannot run a function within a query. So, you need to take another approach.

 

From your explanation I am assuming that there is one unique record per user in the 'userkeys' table. Also, your example query above is using a WHERE clause on the field 'keynote', but based on your first explanation I'm guessing that isn't what you really want.

 

I do not understand music whatsoever. But, if the user has a selected keychange of '2' and the user is doing a search for the key 'Eb' is it correct that the only matches would be on songs with an original keynote of 'C#'. If so, then you could use a revised function that takes the users keychange and the target keynote to determine the original keynote. Then do your query based on that.

[mctoys]

Yes, sort of - the problem is that there is a record in the userkeys for each song which the user has transposed, so I won't be searching the chords database for everything in 'Eb' rather I need to check what key the user has transposed each song to before I then return all the results in That key. The original keys might all be different but the user may have transposed them all to C# for example. I was trying to go down the JOIN route so that I could get an amalgamation of both tables but if I can't use a function within the SQL it sounds like I might have bitten off more than I can chew!

 

Thanks for your input anyway.

[mctoys]

Thanks everyone for their help - I solved the problem for all those who are interested! The answer was in drawing down all the songs from the chords table and all the associated changed keys from the userkeys table using a JOIN LEFT select statement. I then dumped the results in an array and added an extra key which was the result of my php function on the keynote and keychange fields. Finally I filtered the array to only display the results which contained the searched key. It's a bit convoluted but it works!

 

For anyone interested the (rather sloppy) code is below!

 

<?php

 

    function filter_by_value ($array, $index, $value){

        if(is_array($array) && count($array)>0) 

        {

            foreach(array_keys($array) as $key){

                $temp[$key] = $array[$key][$index];

               

                if ($temp[$key] == $value){

                    $newarray[$key] = $array[$key];

                }

            }

          }

      return $newarray;

    }

 

$songResult = mysql_query("SELECT chords.id,chords.title,chords.artist,chords.keynote,userkeys.songid,userkeys.keychange FROM chords LEFT JOIN userkeys ON chords.id = userkeys.songid");

 

$songArray = array();

$i = 0;

while($row = mysql_fetch_assoc($songResult)) {

$songArray[$i]['title'] = $row['title'];

$songArray[$i]['newkey'] = changeKey($row['keynote'],$row['keychange']);

$i++;

}

mysql_free_result($songResult);

 

$newResults = filter_by_value($songArray, 'newkey', '$searchcriteria');

 

foreach($newResults as $song) {

echo $song['title']." - ".$song['newkey'];

}

           

[mikosiko]

other possible approach:

 

Even when is true that you can't run in a SQL query a PHP function, that doesn't mean that you can't run a SQL STORED FUNCTION, therefore the alternative is write/store your FUNCTION in MYSQL and use it directly in your select... that would be simpler than reading all the rows every time and filter results in PHP... your query then should looks like the one you posted originally using the function

 

[mctoys]

That sounds absolutely ideal but I wouldn't know how to rewrite the function in MYSQL! Is it easy? The function is below:

 

function changeKey($keyFrom,$amount) {

 

// CHECK IF SPLIT CHORD

$splitchord = strpos($keyFrom,'/');

if($splitchord == true) {

$parts = explode("/",$keyFrom);

return getChange($parts[0],$amount)."/".getChange($parts[1],$amount);

} else {

return getChange($keyFrom,$amount);

}

 

}

 

// OUTPUT NEW CHORDS

function getChange($chord,$amount2) {

 

// SEPARATE KEY FROM SUFFIX

$thekey = substr($chord,0,1);

 

$flatsharp = substr($chord,1,1);

if($flatsharp == 'b' || $flatsharp == '#') {

$thekey = substr($chord,0,2);

$suffix = substr($chord,2,10);

} else {

$suffix = substr($chord,1,5);

}

 

 

$keyResult = mysql_query("SELECT * FROM keynotes WHERE keynote = '$thekey'") or die(mysql_error());

while($row = mysql_fetch_assoc($keyResult)) {

$keypos = $row['id'];

}

mysql_free_result($keyResult);

 

$newkey = $keypos + $amount2;

 

while($newkey > 12) {

$newkey = $newkey -12;

}

 

$newKeyResult = mysql_query("SELECT * FROM keynotes WHERE id = '$newkey'");

while($row = mysql_fetch_assoc($newKeyResult)) {

$thekey = $row['keynote'];

}

mysql_free_result($newKeyResult);

 

return $thekey.$suffix;

}

[mikosiko]

function doesn't seems to have mayor complexity, at first glance I don't see why it couldn't be done in and stored function or even in a procedure

[mctoys]

Thanks for the input - It sounds like this is exactly what I should be using, unfortunately I'd never even heard of stored procedures until now so I don't think I'll be able to write one anytime soon! I'll have to stick with my longhand way of doing it although it is quite slow already!

[mikosiko]

try to invest some time studying them... you will be happy with the outcome for sure... here is a small/sloppy example of one function (as I said very sloppy it could be optimized in many ways.. but valid to show you how it works)

DELIMITER $$

DROP FUNCTION IF EXISTS `miltostandard` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `miltostandard`(mtime VARCHAR(4)) RETURNS varchar( CHARSET latin1
BEGIN
  DECLARE milTime VARCHAR(4);
  DECLARE ampm VARCHAR(2);
  DECLARE milTimeHours VARCHAR(2);
  DECLARE milTimeMinutes VARCHAR(2);
  DECLARE standardTimeHours VARCHAR(4);
  DECLARE standardTime VARCHAR(;

  SET milTime = mtime;
  SET amPm = 'am';


  SET milTimeHours = substr(milTime, 1, 2);
  SET milTimeMinutes = substr(milTime, 3, 2);

  if milTimeHours >=12 then
    SET amPm = 'pm';
    if milTimeHours > 12 then
      SET standardTimeHours = milTimeHours - 12;
    else
      SET standardTimeHours = milTimeHours;
    end if;
  elseif milTimeHours = 0 then
    SET standardTimeHours = 12;
  else
    SET standardTimeHours = milTimeHours;
  end if;

  SET standardTime = CONCAT_WS( ":", standardTimeHours,milTimeMinutes);
  SET standardTime = CONCAT_WS( " ",standardTime,amPm);
  SELECT 'KAKA' INTO standardTime;
  return standardTime;
END $$

DELIMITER ;

 

and as a simple example too you can call it in this way:

SELECT miltsostandard('2400');

 

and the results should be : 12:00 pm

 

Stored Procedures are a little more tricky but a very powerful tool