[PHP] Using MySQL user variables in PHP

[Jim MacCormaic]

I'm currently developing a PHP/MySQL project, one aspect of which involves displaying a default set from the database which picks out all records for whichever individual is associated with the most recent date. I know this is a very general description, but I don't think it's necessary to burden list members with further detail, nor to pass on details of my table structures.

Suffice it to say that I successfully achieved my goal with MySQL from the command line, but I'm not sure how to write this functionality into my PHP code.

In MySQL, the following sequence of commands works the magic:

SELECT @most_recent:=MAX(date)
FROM presenters;

SELECT @recent_presenter:=presenter
FROM presenters
WHERE date = @most_recent;

SELECT p.date, p.theme, p.presenter,
c.itemNo, c.composer, c.composition, c.note
FROM presenters p, compositions c
WHERE p.date = c.date AND p.presenter = @recent_presenter
ORDER BY p.date DESC;

So how do I transfer all this to PHP?

I've tried a number of approaches:

1. $presenterQuery = "
SELECT @most_recent:=MAX(date) from presenters;
SELECT @recent_presenter:=presenter
FROM presenters WHERE date=@most_recent;
SELECT date_format(p.date, '%d/%m/%y') AS readable_date, p.theme, p.presenter,
c.itemNo, c.composer, c.composition, c.note
FROM presenters p, compositions c
WHERE p.date = c.date AND p.presenter = @recent_presenter
ORDER BY p.date DESC";

2. $tempQuery1 = "
SELECT @most_recent:=MAX(date) from presenters";
$tempQuery2 = "
SELECT @recent_presenter:=presenter FROM presenters WHERE date=@most_recent;
$presenterQuery = "
SELECT date_format(p.date, '%d/%m/%y') AS readable_date, p.theme, p.presenter,
c.itemNo, c.composer, c.composition, c.note
FROM presenters p, compositions c
WHERE p.date = c.date AND p.presenter = @recent_presenter
ORDER BY p.date DESC";

3. $tempQuery1 = "
SELECT @most_recent:=MAX(date) from presenters";
$tempQuery1Result = mysql_db_query($database, $tempQuery1, $connection) or die ( mysql_error() );
$tempQuery2 = "
SELECT @recent_presenter:=presenter FROM presenters WHERE date = \"$tempQuery1Result\"";
$tempQuery2Result = mysql_db_query($database, $tempQuery2, $connection) or die ( mysql_error() );
$presenterQuery = "
SELECT date_format(p.date, '%d/%m/%y') AS readable_date, p.theme, p.presenter,
c.itemNo, c.composer, c.composition, c.note
FROM presenters p, compositions c
WHERE p.date = c.date AND p.presenter = \"$tempQuery2Result\"
ORDER BY p.date DESC";

All of these options are followed by

$presenterResult = mysql_db_query($database, $presenterQuery, $connection) or die ( mysql_error() );

All queries indicate that they have been successfully executed when I add relevant debug code, but it seems that rather than substitute the variable values, either a variable literal (e.g. @recent_presenter) or a value like Resource ID#2 is being used instead.

Is it possible to do what I desire. If so, where am I going wrong?

Jim MacCormaic
Dublin, Ireland
iChat/AIM : [EMAIL PROTECTED]


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php