Re: Using MySQL user variables in PHP

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. */