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
- [PHP] Problems to compile php (cvs) Robert Mena
- Re: [PHP] Problems to compile php (cvs) Evan Nemerson
- Re: [PHP] Using MySQL user variables in PHP Jim MacCormaic
- Re: [PHP] Using MySQL user variables in PHP Jason Wong
- RE: [PHP] Using MySQL user variables in PHP Mark Charette
- Re: [PHP] Using MySQL user variables in PHP Jim MacCormaic
- RE: [PHP] Using MySQL user variables in PHP John W. Holmes