get the WEEK of a date by using mysqli

Question

How can I get the week number of a date by using mysqli for each entry?

Given I have table1 containing a date column with the following two entries:

2018-03-14
2018-05-14

I wish to see the week numbers of the above two entries. My main problem is that I do not know how to process the result of my query.

$sql = 'SELECT WEEK(date) FROM table1 WHERE YEAR(beginn) = "2018"';

$db_erg = mysqli_query($con, $sql);

while ($row = $db_erg->fetch_array())
{
  echo $row[0]; // or $row['week'] if you used AS week
}

[Did you check this](https://stackoverflow.com/questions/13116222/how-to-get-week-number-of-the-month-from-the-date-in-sql-server-2008) ? — Niklesh Raut, Apr 16 '18 at 11:13
Your query should work. Please add your expected result and the current result. — RiWe, Apr 16 '18 at 11:19
Yes, I think that the jquery is correct, too but I can I display it? echo..? — user9554121, Apr 16 '18 at 11:23
If you think the query is correct, what is the problem exactly? — jeroen, Apr 16 '18 at 11:30
The Problem ist, how to get the result for each entry? while ($row = $db_erg->fetch_array()) { ??? — user9554121, Apr 16 '18 at 11:37
@Aneconomist mssql and mysql are two different animals. I can't see how that would be a duplicate of that. — Funk Forty Niner, Apr 16 '18 at 11:46

vogomatix · Answer 1 · 2018-04-16T11:35:08.533

1

$sql = 'SELECT WEEK(date) AS week FROM table1 WHERE YEAR(date) = 2018';

For the WHERE clause, you might want to investigate whether avoiding the YEAR method provides performance advantages.

WHERE date BETWEEN CAST('2018-01-01' AS DATE) AND CAST('2018-12-31' AS DATE);

WHERE date >= CAST('2018-01-01' AS DATE) AND date <= CAST('2018-12-31' AS DATE);

$db_erg in the above is the results (a mysqi_result object), so you should be able to get them with a while loop.

// check query succeeded
if ($db_erg) {

    while ($row = $db_erg->fetch_array())
    {
        // process the data
        echo $row[0]; // or $row['week'] if you used AS week
    }
}

edited Apr 16 '18 at 11:35

answered Apr 16 '18 at 11:17

vogomatix

4,856
2
23
46

How can I get the result of the query? $db_erg = mysqli_query($con, $sql); echo? – user9554121 Apr 16 '18 at 11:24
Added extra info – vogomatix Apr 16 '18 at 11:28
Thanks, But how do I build the syntax in the WHILE loop to get the two calendar weeks from the example? – user9554121 Apr 16 '18 at 11:34
Just `echo $row[0]` – vogomatix Apr 16 '18 at 11:36
Thanks, @vogomatix that was the solution ;) – user9554121 Apr 16 '18 at 11:44
Cool. Please accept and upvote answer if/when you can. I've also edited your question to clear up what you needed answering. – vogomatix Apr 16 '18 at 11:47

get the WEEK of a date by using mysqli

1 Answers1