CODEWITHSUNDEEP
phpmysqlarraysloops
Morten
Morten

Reputation: 127

Use array to convert mysql dayofweek integers to weekday names in while loop?

I am trying to sum up the amount of money I use on the different days in the week. I keep an MySQL database with a timestamp for each time I use money and the amount of money used at that time.

When summing up the money I use on each weekday, I'd like to have the different weekdays show up with their friendly Danish names, instead of a zero for Sunday, 1 for Monday and so on.

I figure a way to do this is to create an array with the different names of the days and then replacing the values returned by my SQL query with the weekday names from my array.

But since I am an absolute beginner, I am having trouble finding our exactly how to tell my PHP script to replace the MySQL values in my loop.

Here is the code I have so far, which I think could be made to work in some very simple way I just can't work out:

// An array with the names of the different days in the week in Danish
$daynames = array('Søndag', 'Mandag', 'Tirsdag', 'Onsdag', 'Torsdag', 'Fredag', 'Lørdag');

// My SQL query that fetches the days of the weeks from my timestamps
$query = "SELECT DAYOFWEEK(timestamp), SUM(money) FROM databasetable";
$result = mysqli_query($con,$query) or die(mysql_error());

// My loop that prints the sum of money from each day
while($row = mysqli_fetch_array($result)){
echo "". $row['SUM(money)'] ." on day ". $row['DAYOFWEEK(timestamp)'] ."";
echo "<br />";
}

Any help in figuring this out would be much appreciated.

Upvotes: 2

Views: 514

Answers (2)

Morten
Morten

Reputation: 127

I was able to solve this getting the good help above. The exact solution I used is this. I have described the changes I made with added comments:

// An array with the names of the different days in the week in Danish
// I had to have a blank value before my daynames in order to get the correct days for the corresponding MySQL values:
$daynames = array('', 'Søndag', 'Mandag', 'Tirsdag', 'Onsdag', 'Torsdag', 'Fredag', 'Lørdag');

// My SQL query that fetches the days of the weeks from my timestamps
$query = "SELECT DAYOFWEEK(timestamp), SUM(money) FROM databasetable";
$result = mysqli_query($con,$query) or die(mysql_error());

// My loop that prints the sum of money from each day
// Added the part that "uses" the array on the values returned from the database
while($row = mysqli_fetch_array($result)){
echo "". $row['SUM(money)'] ." on day ". $daynames[$row['DAYOFWEEK(timestamp)']] ."";
echo "<br />";
}

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

use mysql alias or try this code :- 

$query = "SELECT DAYOFWEEK(timestamp) day, SUM(money) total FROM databasetable";
$result = mysqli_query($con,$query) or die(mysql_error());
while($row = mysqli_fetch_array($result)){ 

echo $row['total'].' on day '.$daynames[$row['day']];
echo "<br />";

}

Upvotes: 1

Related Questions