On Monday 27 January 2003 01:51, Jim MacCormaic wrote:
> 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";
The php-mysql interface doesn't support multiple queries in a single operation
so you can forget this approach.
> 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";
You're not doing anything with $tempQuery1 & $tempQuery2 this is complete
nonsense ;-)
> 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";
This is getting closer. Unfortunately you haven't been reading the manual.
mysql_query() returns a "resource ID". See examples in manual for details on
how to properly query and return results from a db.
Looking at your queries, it seems that you're doing more steps than is
strictly required.
SELECT @most_recent:=MAX(date)
FROM presenters;
SELECT @recent_presenter:=presenter
FROM presenters
WHERE date = @most_recent;
Couldn't the above be condensed to:
SELECT presenter FROM presenters ORDER BY date DESC LIMIT 1;
Which means you can get by with 2 queries:
$query = "SELECT presenter FROM presenters ORDER BY date DESC LIMIT 1";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$presenter = $row['presenter'];
You can now use $presenter in your last query:
$presenterQuery = "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 = $presenter
ORDER BY p.date DESC";
> $presenterResult = mysql_db_query($database, $presenterQuery,
> $connection) or die ( mysql_error() );
All code untested, handle with care.
--
Jason Wong -> Gremlins Associates -> www.gremlins.biz
Open Source Software Systems Integrators
* Web Design & Hosting * Internet & Intranet Applications Development *
/*
If we won't stand together, we don't stand a chance.
*/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
