db SELECT

[thoand]

Hi,

In my newsitems SQL table I have a field called published,
the value is either 0(not published) and 1(published)

Each article item is published under catogories,
Catogories is another table in the database so in the newsitems there is a colum that references the catogory id

When I do a loop and displays the catogories I now have a query to the newsitems for counting all newsitems that is not published(0) under each catogory.

But lets say I want to get both published and not published,
can I do this with only one query, and do some math instead of having two queries (one for published and one for not published)

eg.

SELECT * from catogories

do loop
SELECT * from newsitems
Find out how many items that has published = 1 and how many that has published = 0
echo "Catogory name (5,1)
//do loop

hope you understood this, and any tips would be of help,

best regards
Thomas

[planetsim]

Yes use a loop to just query all the newsitems then in the loop use and if to determine which is published and which isnt

e.g.

$query = mysql_query("SELECT * from newsitems");
$published=0;
$nonpublished=0;
while ($row=mysql_fetch_array($query)) {
  if ($row['published']!=1) {
    $nonpublished++;
  } else {
    $published++;
  }
}

That should work you may need to change the query and feild names.

[bubblenut]

You could do it with just three queries in total rather than one to get the categories and then three for each category.

<?php
//the following queries assumes that categories is joined with newsitems on the categories unique id called cat_id
$sql1="SELECT categories.*, count(*) AS num ".
  "FROM categories, newsitems ".
  "WHERE newsitems.cat_id=categories.cat_id && published=0 GROUP BY categories.cat_id";
$sql2="SELECT categories.*, count(*) AS num ".
  "FROM categories, newsitems ".
  "WHERE newsitems.cat_id=categories.cat_id && published=1 GROUP BY categories.cat_id";
$sql3="SELECT categories.*, count(*) AS num ".
  "FROM categories, newsitems ".
  "WHERE newsitems.act_id=categories.cat_id GROUP BY categories.cat_id";

$categories=arary();
for($i=0;$i<3;$i++) {
  //execute the queries and stick the result handles in $res1, $res2 & $res3  

  ${'res'.($i+1)}=mysql_query(${'sql'.($i+1)}) or die('Error running query '.($i+1).':  '.mysql_error());

  //get results from the three queries and place the results in an array or categories, each category having the form
  //array('details' =>array(#details of the categoriy in here#), 
  //  'published'=>#num published#, 
  //  'not_published=>#num published#, 
  //  'total'=>#total (can use for error checking)#);
  while($row=mysql_fetch_assoc(${'res'.($i+1)})) {
    $num=$row['num'];
    unset($row['num']);
    if(!array_key_exists($row['cat_id'], $categories)) {
      $categories[$row['cat_id']]['details']=$row;
    }
    switch($i) {
      case 0: #not published
        $categories[$row['cat_id']]['not_published']=$num;         break;
      case 1: #published
        $categories[$row['cat_id']]['published']=$num;
        break;
      case 2:
        $categories[$row['cat_id']['total']=$num;
        break;
    }
  }
}
//check what's in the array
print_r($categories);

HTH
Not sure why I arranged the array like I did, it seems a little rubbish looking at it now but I'll leave you to see if you change that if you wish.
If you need some more explination on it fire away 🙂
Bubble

[sidney]

somthing like this might do

$pub=0;
$npub=0; 
$query="SELECT * from newsitems ORDER BY published";
$sql=mysql_query($query);
while($results=mysql_fetch_array($sql))
{
if($row['published']==1){$pub++;}
if($row['published']==0){$npub++;}
// print $row 
}
echo "Total Published = ".$pub." Not Published = ".$npub

[planetsim]

while($results=mysql_fetch_array($sql))
{
if($row['published']==1){$pub++;}
if($row['published']==0){$npub++;} 

Shouldnt you be using $results rather than $row

[bubblenut]

OMFG, got me interested so I had a play around with MySQL. Is this the coolest query in the world or what??
assuming unique id for categories is cat_id and the unique id for newsitems is news_id

SELECT categories.*, count(news1.news_id) as published, count(news2.news_id) as unpublished, count(news3.news_id) as all
FROM categories
LEFT JOIN newsitems as news1 on (categories.cat_id=newsitems.cat_id && published=1)
LEFT JOIN newsitems as news2 on (categories.cat_id=newsitems.cat_id && published=0)
LEFT JOIN newsitems as news3 on (categories.cat_id=newsitems.cat_id)
GROUP BY categories.cat_id

😃 😃 😃
I think I need to go change my panties!
Bubble

[planetsim]

You have far too much time on your hands but yes that is a nice query 😃

[edit]

Query needs some optimizing

SELECT categories.cat_id, count(news1.news_id) as published, count(news2.news_id) as unpublished, count(news3.news_id) as all
FROM categories
LEFT JOIN newsitems as news1 on (categories.cat_id=newsitems.cat_id && published=1)
LEFT JOIN newsitems as news2 on (categories.cat_id=newsitems.cat_id && published=0)
LEFT JOIN newsitems as news3 on (categories.cat_id=newsitems.cat_id)
GROUP BY categories.cat_id

Need to only call what feilds are being used