sql - count records per weeks using timestamp

Question

I have the table named 'tasaciones' which has 2 columns 'record' and 'fecha' (fecha is timestamp).

record  | fecha
-----------------------------

record1 | 2015-10-09 11:24:52

record1 | 2015-11-09 11:31:52

record2 | 2015-17-09 11:37:17

record3 | 2015-25-09 12:03:31

record3 | 2015-26-09 12:03:31

record4 | 2015-10-10 12:23:25

record4 | 2015-11-10 12:27:25

I need to make weekly report by counting records per weeks from the date of first record until the current time. I found something like this but even if this is what I need, I don't know how to write 'while' to echo the data.

SELECT year(fecha), datepart(week, fecha), count(*)
FROM tasaciones
GROUP BY year(fecha), datepart(week, fecha)

I want to get results like this:

week 1: 15 records

week 2: 10 records

week 3: 25 records

week 4: 25 records

Answer 1

There is no DATEPART() in MySQL.. You can use YEARWEEK() instead. So your query should look something like this.

SELECT count(*) as numRecords, YEARWEEK(fecha) as weekNum
FROM table
GROUP BY YEARWEEK(fecha)

Echoing out the results depends on the api you are using.

PDO:

$query = $SQL->prepare('...query here...');
$query->execute();

while ($row = $query->fetch(PDO::FETCH_ASSOC))
{
    echo $row['tags'];
}

MYSQLI:

$qry = '...query here...';
$results = $mysqli->query($qry);
while($row = $results->fetch_assoc()){
    echo $row['numRecords'], $row['weekNum']
}

Note: I didn't post a way to do this with the Mysql_ api since that has been deprecated and is vunerable to sql injection along with a whole lot of other problems. You should switch to PDO or Mysqli_ if you haven't already