0

I have the following sql code:

$management_lcfruh_sql = "SELECT COUNT(schicht), codes.lcfruh, personal.status, dienstplan.kw, personal.perso_id, personal.sort_order, dienstplan.datum FROM dienstplan INNER JOIN codes ON dienstplan.schicht=codes.lcfruh INNER JOIN personal ON personal.perso_id=dienstplan.perso_id WHERE codes.lcfruh!='' AND personal.status='management' AND dienstplan.kw='$kw' ORDER BY personal.sort_order, dienstplan.datum";
$management_lcfruh_result= mysql_query($management_lcfruh_sql);

how can I get a list of counts instead of only one count, dienstplan.kw='$kw' is a week of the year which have seve days, so I should get seven result listed instead of a one count of all of the seven.

<?php
while($rows=mysql_fetch_array($management_lcfruh_result)){
?>
<? echo $rows['COUNT(schicht)']; ?>
<?php
}
?>
user2615859
  • 37
  • 1
  • 6
  • So is there a day / date somewhere in one of those tables? Even then you'd only get counts for the days that have records for. You could get round that but if dayofweek or date isn't in there, you are stuffed. – Tony Hopkinson Sep 07 '13 at 11:29
  • so I can only do it only per day, and make 7 queries with 7 results – user2615859 Sep 07 '13 at 11:34
  • You are joining on week, so to get day, one of the tables in the join has to have day in it. It would be Monday, it could be 9/9/2013, but without it doesn't matter how many queries you do they'll all have the same count for the same week. – Tony Hopkinson Sep 07 '13 at 11:52
  • If you have a daynumber or a date and you tell us which table it's in and it's column name, you can do what you want in one query. – Tony Hopkinson Sep 07 '13 at 11:54
  • i have a date column with 2013-09-02...., how can i apply this in order to get the query working. I though if I am using =$kw that it is going to list all the results with kw=$kw value. – user2615859 Sep 07 '13 at 12:03

1 Answers1

0

Um well you haven't been a big help on the extra info front, but may be this will get you going in the right direction.

Given Table1(WeekNo int, Category int, Schict int) and Table2 (WeekNo int, Schict int, CategoryDate dateTime)

Then

Select t1.Catgeory, DayName(t2.CategoryDate), t1.Schict, Count(t1.schict)
From Table1 t1
Inner join Table2 t2 On t1.Schict = t2.schict and t1.WeekNo = t2.weekNo
Group by t1.Category,DayName(t2.CategoryDate), t1.Schict

Would get you a count by Category and schict across the two tables, but the number of records you get would be dependent on how many days of the week you had records for.

You could deal with that in Sql but it would be much easier to fill in the missing days in your array client side in PHP. Posibly even easier, if you were to use the mysql DayOfWeek function instead of DayName.

Some clues anyway Perhaps even easier would to simply add this date you won't tell us about to the select clause of your current query and then use PHP to get the day.

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39
  • the problem is that I do not have a weekNo in both tables, the 2 tables are only connected with the perso_id column. – user2615859 Sep 07 '13 at 13:52
  • oh! So do you have the year it's a week number of? Have a look at http://stackoverflow.com/questions/7078730/how-to-convert-number-of-week-into-date. Me I'd be having a rethink and get rid of week number for datestart and dateend, then this would be join on Somedate Between datestart and dateend. – Tony Hopkinson Sep 07 '13 at 15:03
  • I have found a solution, just instead of ORDER I have used GROUP BY an that have made a list of seven results. thanks – user2615859 Sep 08 '13 at 06:14
  • Good stuff. Glad to help even if it was just someone to bounce an idea off. – Tony Hopkinson Sep 08 '13 at 11:05