[PHP-DB] JOIN - Not working.

[Martin E. Koss]

I have read the parts of the manual(s) but am just not grasping something...

I have 2 tables, one holds the Property ID etc., and the other holds all the
Property Details. They are connected by a field called 'PropertyID' which is
a unique ID that appears in both records.

I want to count the number of records in PropertyDetails that have an entry
in a field called 'Image1Desc', but I need to do this based on a previously
chosen field from Property called 'PropertyType'. However, the code below is
simply not giving the results I need:

$phselect = select COUNT(*) AS phTotal from properties LEFT JOIN
propertydetails ON properties.PropertyID=propertydetails.PropertyID where
((propertydetails.Image1Desc='') AND
(properties.PropertyType='$PropertyType'));

$phresult = mysql_query ($phselect,$conID);
while ($phrow = mysql_fetch_array($phresult)) {
$phTotal = $phrow[phTotal];
PRINT td align=\right\$phresult/td\n;
} // end WHILE

Is there an obvious problem here and can anyone help?

Martin E. Koss
M: 07946-706459
E: [EMAIL PROTECTED]


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[Fwd: [PHP-DB] JOIN - Not working.]

[Daniel Barton]

--
--
Dan Barton
Terrestrial Program Biologist
Asst. Data Manager
Point Reyes Bird Observatory
http://www.prbo.org
[EMAIL PROTECTED]
[EMAIL PROTECTED]


---BeginMessage---

Try:

select sum(if(whatever=whatever,1,0)) as whatever from wherever where
whatever=whatever;

This is a pretty fast command for simple cross-tabs (outperforms left join?) and
is easier to understand for a quick fix. You can also write a loop that creates
a query with multiple rows like:

select sum(if(whatever=whatever,1,0)) as whatever1
select sum(if(whatever=whatever,1,0)) as whatever2
select sum(if(whatever=whatever,1,0)) as whatever3
select sum(if(whatever=whatever,1,0)) as whatever4 from wherever where
whatever=whatever group by whatever;

yay!
-db

Martin E. Koss wrote:

 I have read the parts of the manual(s) but am just not grasping something...

 I have 2 tables, one holds the Property ID etc., and the other holds all the
 Property Details. They are connected by a field called 'PropertyID' which is
 a unique ID that appears in both records.

 I want to count the number of records in PropertyDetails that have an entry
 in a field called 'Image1Desc', but I need to do this based on a previously
 chosen field from Property called 'PropertyType'. However, the code below is
 simply not giving the results I need:

 $phselect = select COUNT(*) AS phTotal from properties LEFT JOIN
 propertydetails ON properties.PropertyID=propertydetails.PropertyID where
 ((propertydetails.Image1Desc='') AND
 (properties.PropertyType='$PropertyType'));

 $phresult = mysql_query ($phselect,$conID);
 while ($phrow = mysql_fetch_array($phresult)) {
 $phTotal = $phrow[phTotal];
 PRINT td align=\right\$phresult/td\n;
 } // end WHILE

 Is there an obvious problem here and can anyone help?

 Martin E. Koss
 M: 07946-706459
 E: [EMAIL PROTECTED]

 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, e-mail: [EMAIL PROTECTED]
 For additional commands, e-mail: [EMAIL PROTECTED]
 To contact the list administrators, e-mail: [EMAIL PROTECTED]

--
--
Dan Barton
Terrestrial Program Biologist
Asst. Data Manager
Point Reyes Bird Observatory
http://www.prbo.org
[EMAIL PROTECTED]
[EMAIL PROTECTED]




---End Message---

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] JOIN - Not working.

[Daniel Barton]

Sorry, that was a bit hasty.

I was just trying to get the method across quickly.

SELECT SUM(IF(Image1Desc!='insert default value of Image1Desc here'),1,0) as
phTotal from PropertyDetails;

would count the number of entries where Image1Desc!=default in PropertyDetails.

This can be used in combination with a LEFT JOIN or a WHERE or a GROUP BY or
anything else to perform the operation you're interested in, like:

SELECT SUM(IF(Image1Desc!='insert default value of Image1Desc here'),1,0) as
phTotal from PropertyDetails WHERE 'fieldname'='condition';

or

SELECT SUM(IF(Image1Desc!='insert default value of Image1Desc here'),1,0) as
phTotal from PropertyDetails WHERE 'fieldname'='condition';

or

SELECT SUM(IF(PropertyDetails.Image1Desc!='insert default value of Image1Desc
here'),1,0) as phTotal from Properties LEFT JOIN PropertyDetails ON
Properties.PropertyID=propertydetails.PropertyID WHERE
Properties.PropertyType='$PropertyType';

Add a GROUP BY if you need one.

Cheers,
db


Martin E. Koss wrote:

 Ehm...?

 You've lost me. Perhaps a real-world example may have explained it better.
 All the repeated 'whatever' kind of makes it a bit unexplained.

 Martin.

 -Original Message-
 From: Daniel Barton [mailto:[EMAIL PROTECTED]]
 Sent: 28 November 2001 4:14 PM
 To: Martin E. Koss
 Subject: Re: [PHP-DB] JOIN - Not working.

 Try:

 select sum(if(whatever=whatever,1,0)) as whatever from wherever where
 whatever=whatever;

 This is a pretty fast command for simple cross-tabs (outperforms left join?)
 and
 is easier to understand for a quick fix. You can also write a loop that
 creates
 a query with multiple rows like:

 select sum(if(whatever=whatever,1,0)) as whatever1
 select sum(if(whatever=whatever,1,0)) as whatever2
 select sum(if(whatever=whatever,1,0)) as whatever3
 select sum(if(whatever=whatever,1,0)) as whatever4 from wherever where
 whatever=whatever group by whatever;

 yay!
 -db

 Martin E. Koss wrote:

  I have read the parts of the manual(s) but am just not grasping
 something...
 
  I have 2 tables, one holds the Property ID etc., and the other holds all
 the
  Property Details. They are connected by a field called 'PropertyID' which
 is
  a unique ID that appears in both records.
 
  I want to count the number of records in PropertyDetails that have an
 entry
  in a field called 'Image1Desc', but I need to do this based on a
 previously
  chosen field from Property called 'PropertyType'. However, the code below
 is
  simply not giving the results I need:
 
  $phselect = select COUNT(*) AS phTotal from properties LEFT JOIN
  propertydetails ON properties.PropertyID=propertydetails.PropertyID where
  ((propertydetails.Image1Desc='') AND
  (properties.PropertyType='$PropertyType'));
 
  $phresult = mysql_query ($phselect,$conID);
  while ($phrow = mysql_fetch_array($phresult)) {
  $phTotal = $phrow[phTotal];
  PRINT td align=\right\$phresult/td\n;
  } // end WHILE
 
  Is there an obvious problem here and can anyone help?
 
  Martin E. Koss
  M: 07946-706459
  E: [EMAIL PROTECTED]
 
  --
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]
  To contact the list administrators, e-mail: [EMAIL PROTECTED]

 --
 --
 Dan Barton
 Terrestrial Program Biologist
 Asst. Data Manager
 Point Reyes Bird Observatory
 http://www.prbo.org
 [EMAIL PROTECTED]
 [EMAIL PROTECTED]

--
--
Dan Barton
Terrestrial Program Biologist
Asst. Data Manager
Point Reyes Bird Observatory
http://www.prbo.org
[EMAIL PROTECTED]
[EMAIL PROTECTED]



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]