[SOLVED] PHP Query

[CanMan2004]

Hi all

 

I have a database which holds events, the database looks like

 

ID  TYPE  DATE FROM

1    1      01-02-2007

2    1      01-02-2007

3    2      01-02-2007

 

What I want to do is run the query below

 

$sql = "SELECT * FROM `events` WHERE `datefrom` = '01-02-2007";
$query = @mysql_query($sql,$connection) or die(mysql_error());
while ($row = mysql_fetch_array($query))
{
print $row['type'];
print "<br>";
}

 

The query above currently returns the result

 

1

1

2

 

What I want to do is to change the above query and say

 

if all the rows returned all have 1 as there $row['type'] value then print "one";

 

if all the rows returned all have 2 as there $row['type'] value then print "two";

 

and if all the rows returned all have a mix of 1 and 2 as there $row['type'] value then print "mixed of one and two";

 

Does this make much sense?

 

Any help would be great

 

Thank in advance

 

Dave

[trq]

I don't really understand how they could have a mix but something like this may do....

 

while ($row = mysql_fetch_array($query))
{
  switch ($row['type']) {
    case 1: echo "one" ; break;
    case 2: echo "two"; break;
    default: echo "mixed of one and two" ; break;
  }
print "<br>";
}

[CanMan2004]

That doesnt seem to work correctly, if there is a mix of 1's and 2's then it doesnt print "mixed" it just print "one<br>two"

[trq]

Can I see what a mix of 1 and 2 would look like?

[CanMan2004]

Hi

 

Basically here is a snippet of my db

 

ID  TYPE  DATE FROM

1    1      01-02-2007

2    1      01-02-2007

3    2      01-02-2007

 

So with my query all the above are returned, which is a mix of 1's and 2's, so if that happens, it should print "mixed" just once. On the otherhand, if the database looked like

 

ID  TYPE  DATE FROM

1    1      01-02-2007

2    1      01-02-2007

 

Then it would print "one" as all the rows returned have 1 under there type.

 

Does that clear any questions up?

 

Thanks so far

[Psycho]

<?php
$sql = "SELECT *
       FROM `events`
       WHERE `datefrom` = '01-02-2007
       GROUP BY `type`";
$query = @mysql_query($sql,$connection) or die(mysql_error());

if (mysql_num_rows($query)==0) {
   echo "No results returned.";
} elseif (mysql_num_rows($query)>1) {
   echo "Mixed of one and two.";
} else {
   $result = mysql_fetch_assoc($query);
   if ($result['type']=="1") {
      echo "one";
   } else {
       echo "two";
   }
}
?>

[trq]

A bit messy but should do the trick.

 

<?php

  function whattype($arr) {
    
    $tmp = array_count_values($arr);

    if ($tmp[1] > 0 && $tmp[2] > 0) {
      return "mixed";
    } else {
      if ($tmp[1] > 0) {
        return "one";
      } else {
        if ($tmp[2] > 0) {
          return "two";
        }  
      }  
    }
  }

  $sql = "SELECT * FROM `events` WHERE `datefrom` = '01-02-2007";
  $query = @mysql_query($sql,$connection) or die(mysql_error());
  while ($row = mysql_fetch_array($query)) {
    $tmp[] = $row['type'];
  }

  echo whattype($tmp)."<br />";

?>

[CanMan2004]

Thats great everyone, got it working now, thank you

[artacus]

Don't make your life so hard.

SELECT `datefrom`, AVG(`type`) AS avg_type
FROM `events` 
WHERE `datefrom` BETWEEN '2007-01-01' AND NOW()
GROUP BY `datefrom`

 

If avg is anywhere between 1 and 2 its mixed.