subject:"RE\: \[PHP\] Using MySQL user variables in PHP"

Re: [PHP] Using MySQL user variables in PHP

2003-01-26 Thread Jason Wong

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

RE: [PHP] Using MySQL user variables in PHP

2003-01-26 Thread John W. Holmes

 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

2003-01-26 Thread Mark Charette


 -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

2003-01-26 Thread Jim MacCormaic

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

2003-01-26 Thread Jason Wong

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

RE: [PHP] Using MySQL user variables in PHP

RE: [PHP] Using MySQL user variables in PHP

Re: [PHP] Using MySQL user variables in PHP

Re: [PHP] Using MySQL user variables in PHP

5 matches

Site Navigation

Mail list logo

Footer information