Re: [PHP] Using MySQL user variables in PHP
On Monday 27 January 2003 02:36, Mark Charette wrote: > > -Original Message- > > From: Jason Wong [mailto:[EMAIL PROTECTED]] > > > > > 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 ;-) > > Not true - this is using MySQL's variable approach. The '@' represents > a user variable per connection in MySQL. See > http://www.mysql.com/doc/en/Variables.html for more information. I know. The important point is that although $tempQuery1 & $tempQuery2 are assigned, they aren't being passed to any mysql_query(). -- Jason Wong -> Gremlins Associates -> www.gremlins.biz Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * /* Music in the soul can be heard by the universe. -- Lao Tsu */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Using MySQL user variables in PHP
On Sunday, January 26, 2003, at 06:26 pm, Jason Wong wrote: You're not doing anything with $tempQuery1 & $tempQuery2 this is complete nonsense ;-) Bowing obsequiously, suitably chastened, I read on . . . 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. Knuckles recovering from another knock, I read further . . . Couldn't the above be condensed to: SELECT presenter FROM presenters ORDER BY date DESC LIMIT 1; Much neater indeed. Elegant, even. 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 All code untested, handle with care. Code successfully incorporated and working as intended. Thanks for the help. 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
RE: [PHP] Using MySQL user variables in PHP
> -Original Message- > From: Jason Wong [mailto:[EMAIL PROTECTED]] > > 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 ;-) Not true - this is using MySQL's variable approach. The '@' represents a user variable per connection in MySQL. See http://www.mysql.com/doc/en/Variables.html for more information. mark C. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Using MySQL user variables in PHP
> 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? Pretty sure you can do all that with one query. Something like: 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 ORDER BY p.date DESC LIMIT 1 Not sure, though, as I don't have time to test right now. ---John W. Holmes... PHP Architect - A monthly magazine for PHP Professionals. Get your copy today. http://www.phparch.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] 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. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsu
[PHP] Using MySQL user variables in PHP
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
