writing sql with substr data in rows

Question

i have this table in database which called data

and i want make this sql

     $sql = mysql_query("SELECT $menucompare,( substr($menucompare, 13, 2)) AS dayname FROM data WHERE ... ");

Obs

    $menucompare // is variable which the date is selected from table(date or date2 or date3..) 

lets say we have this date from table 04/10/2012-(Tuesday) under the week 6 in w_date, so after substracting as it saids in sql it will be Tu .

in week 7 there is 2 dates one . what i want is to output by every week the days names ordered by days

exemple like that

  week 7

   Tu
   Fr
   .
   .

or

week 6
Tu
.
.

can this happen ?is there a special WHERE clause here ? and is it possible to write sql where claculating how many dates are in the week for exemple how many dates in week 7 , in the table up there is 2 in week 7

Answer 1

You've asked a couple of different questions here, but before I try to address them directly it might be worth considering (if at all possible) changing the data type of your date columns to the DATE type and then using functions such as DATE_FORMAT(`date`, "%a") to obtain the first characters of the relevant day name.

Also, please be very careful to avoid malicious SQL injection when inserting variables from another language into a SQL statement: consider using prepared statements. Sadly, to do so in this case you will stumble upon another problem because of your database design: see this question for more information.

On to your questions:

1. can this [the example output shown] happen?

   Yes, it can. You want to "group" your results, so have a look at the GROUP BY clause to the SELECT statement. Try a command like:

   SELECT
     `w_date`,
     GROUP_CONCAT(SUBSTR(`date`, 13, 2) SEPARATOR "\n") AS `daynames`
   FROM `data`
   WHERE ...
   GROUP BY `w_date`;
   

   As you can see, the results are grouped in the way you want - but the list of day abbreviations is a separate result column to the week number; to output the data precisely as shown is a further manipulation that must occur at the application level.

2. is it possible to write sql where claculating how many dates are in the week for exemple how many dates in week 7

   Yes, absolutely. To obtain the total number of records for a given week:

   SELECT COUNT(*) FROM `data` WHERE `w_date` = 7;
   

   Or for the number of distinct days within a given week:

   SELECT COUNT(DISTINCT `date`) FROM `data` WHERE `w_date` = 7;
   

   Or to get such for every week:

   SELECT `w_date`, COUNT(*) FROM `data` GROUP BY `w_date`;