CanMan2004 Posted February 8, 2007 Share Posted February 8, 2007 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 Quote Link to comment Share on other sites More sharing options...
trq Posted February 8, 2007 Share Posted February 8, 2007 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>"; } Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted February 8, 2007 Author Share Posted February 8, 2007 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" Quote Link to comment Share on other sites More sharing options...
trq Posted February 8, 2007 Share Posted February 8, 2007 Can I see what a mix of 1 and 2 would look like? Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted February 8, 2007 Author Share Posted February 8, 2007 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 9, 2007 Share Posted February 9, 2007 <?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"; } } ?> Quote Link to comment Share on other sites More sharing options...
trq Posted February 9, 2007 Share Posted February 9, 2007 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 />"; ?> Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted February 9, 2007 Author Share Posted February 9, 2007 Thats great everyone, got it working now, thank you Quote Link to comment Share on other sites More sharing options...
artacus Posted February 9, 2007 Share Posted February 9, 2007 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.