PHP/MYSQL - date sorting in table my months

  • jokerper
  • Novice
  • Novice
  • User avatar
  • Posts: 22

Post 3+ Months Ago

Hi

I have a quetion about mysql.

In my mysql table field date as format 2011-06-20.

I need some help to get it into a html table splitted in every month like:

Code: [ Select ]
fx 3 posts in january month
<break>
fx 5 posts in february month
<break>
and so on
  1. fx 3 posts in january month
  2. <break>
  3. fx 5 posts in february month
  4. <break>
  5. and so on


So I got the oppotinity to make a header over each month called JANUAR 2011 and so on.

Anyone who knows how to do this.

/jokerper
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

You can use the MONTH() function to group up each post by month.

Code: [ Select ]
SELECT * FROM table WHERE MONTH(date_field) = 5
  • jokerper
  • Novice
  • Novice
  • User avatar
  • Posts: 22

Post 3+ Months Ago

Thanks would it say that I have to make the select 12 times like this:

SELECT * FROM table WHERE MONTH(date_field) = 1
SELECT * FROM table WHERE MONTH(date_field) = 2
SELECT * FROM table WHERE MONTH(date_field) = 3
and so on...

Or howto make it like this:
A Headline January
echo "<td>".$row['']."</td>";
echo "<td>".$row['']."</td></tr>";

<break>When all from January are printed

A Headline February
echo "<td>".$row['']."</td>";
echo "<td>".$row['']."</td></tr>";

<break>When all from February are printed

and so on...
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8411
  • Loc: USA

Post 3+ Months Ago

It would be nice if you give a sample of that database info to help create a working sample...
  • jokerper
  • Novice
  • Novice
  • User avatar
  • Posts: 22

Post 3+ Months Ago

Okay there it come:

Code: [ Select ]
CREATE TABLE `kalender` (
 `id` int(11) NOT NULL auto_increment,
 `datonr` date NOT NULL,
 `Titel` varchar(250) NOT NULL,
 `omraade` varchar(200) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `kalender`
--
  1. CREATE TABLE `kalender` (
  2.  `id` int(11) NOT NULL auto_increment,
  3.  `datonr` date NOT NULL,
  4.  `Titel` varchar(250) NOT NULL,
  5.  `omraade` varchar(200) NOT NULL,
  6.  PRIMARY KEY (`id`)
  7. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
  8. --
  9. -- Dumping data for table `kalender`
  10. --


I hope this is what you asking for.
  • jokerper
  • Novice
  • Novice
  • User avatar
  • Posts: 22

Post 3+ Months Ago

Is there any further help to get for my problem.
  • WritingBadCode
  • Graduate
  • Graduate
  • User avatar
  • Posts: 214
  • Loc: Sweden

Post 3+ Months Ago

Here is one suggestion (using two queries):

Code: [ Select ]
<?php
//make database connection here

$qwery1 = mysql_query("SELECT datonr, COUNT(id) FROM kalender GROUP BY MONTH(datonr)");
$qwery2 = mysql_query("SELECT * FROM kalender ORDER BY MONTH(datonr)");

while( $row = mysql_fetch_array($qwery1))
{
    $count = $row['COUNT(id)'];
    $month = strtotime($row['datonr']);
    echo "<h1>" . date ( " F ", $month ) . "</h1>" ;
    while( $count > 0)
        {
            $row = mysql_fetch_array($qwery2);
            echo "article id:" . $row['id'] . "<br />TITLE: " . $row['Titel'] . "<br />more text: " . $row['omraade'];
            echo "<br /> ------------------------<br />";
            $count--;
        }
}
?>
  1. <?php
  2. //make database connection here
  3. $qwery1 = mysql_query("SELECT datonr, COUNT(id) FROM kalender GROUP BY MONTH(datonr)");
  4. $qwery2 = mysql_query("SELECT * FROM kalender ORDER BY MONTH(datonr)");
  5. while( $row = mysql_fetch_array($qwery1))
  6. {
  7.     $count = $row['COUNT(id)'];
  8.     $month = strtotime($row['datonr']);
  9.     echo "<h1>" . date ( " F ", $month ) . "</h1>" ;
  10.     while( $count > 0)
  11.         {
  12.             $row = mysql_fetch_array($qwery2);
  13.             echo "article id:" . $row['id'] . "<br />TITLE: " . $row['Titel'] . "<br />more text: " . $row['omraade'];
  14.             echo "<br /> ------------------------<br />";
  15.             $count--;
  16.         }
  17. }
  18. ?>


Only tested it on a minor database so I'm not sure of the efficiency of it. = P
  • jokerper
  • Novice
  • Novice
  • User avatar
  • Posts: 22

Post 3+ Months Ago

Thanks you, can you please tell me how to replace the name of the months to Danish.

like
June = Juni
july = Juli

ect.
  • WritingBadCode
  • Graduate
  • Graduate
  • User avatar
  • Posts: 214
  • Loc: Sweden

Post 3+ Months Ago

jokerper wrote:
Thanks you, can you please tell me how to replace the name of the months to Danish.


This may or may not work depending on the server:

Code: [ Select ]
<?php
$host= "localhost";
$user= "root";
$psw= "password";
mysql_connect($host, $user, $psw);
mysql_selectdb("database");
$qwery1 = mysql_query("SELECT datonr, COUNT(id) FROM kalender GROUP BY MONTH(datonr)");
$qwery2 = mysql_query("SELECT * FROM kalender ORDER BY MONTH(datonr)");
setlocale(LC_ALL, "danish");

while( $row = mysql_fetch_array($qwery1))
{
  $count = $row['COUNT(id)'];
  $month = strftime("%B", strtotime($row['datonr']));
  echo "<h1>" . $month . "</h1>" ;
  while( $count > 0)
    {
      $row = mysql_fetch_array($qwery2);
      echo "article id:" . $row['id'] . "<br />TITLE: " . $row['Titel'] . "<br />more text: " . $row['omraade'];
      echo "<br /> ------------------------<br />";
      $count--;
    }
}
?>
  1. <?php
  2. $host= "localhost";
  3. $user= "root";
  4. $psw= "password";
  5. mysql_connect($host, $user, $psw);
  6. mysql_selectdb("database");
  7. $qwery1 = mysql_query("SELECT datonr, COUNT(id) FROM kalender GROUP BY MONTH(datonr)");
  8. $qwery2 = mysql_query("SELECT * FROM kalender ORDER BY MONTH(datonr)");
  9. setlocale(LC_ALL, "danish");
  10. while( $row = mysql_fetch_array($qwery1))
  11. {
  12.   $count = $row['COUNT(id)'];
  13.   $month = strftime("%B", strtotime($row['datonr']));
  14.   echo "<h1>" . $month . "</h1>" ;
  15.   while( $count > 0)
  16.     {
  17.       $row = mysql_fetch_array($qwery2);
  18.       echo "article id:" . $row['id'] . "<br />TITLE: " . $row['Titel'] . "<br />more text: " . $row['omraade'];
  19.       echo "<br /> ------------------------<br />";
  20.       $count--;
  21.     }
  22. }
  23. ?>


The new/changed lines are setlocale(LC_ALL, "danish"); that was added and $month that now looks like this:
Code: [ Select ]
$month = strftime("%B", strtotime($row['datonr']));
echo "<h1>" . $month . "</h1>" ;
  1. $month = strftime("%B", strtotime($row['datonr']));
  2. echo "<h1>" . $month . "</h1>" ;


%B basically says "return the month name in letters" and setlocale tells it to do so in Danish (I hope).

strtotime is needed there or you will get a error about bad value bla bla bla.
  • jokerper
  • Novice
  • Novice
  • User avatar
  • Posts: 22

Post 3+ Months Ago

That is working, thanks - I love when things just works!!!

You're doing a good coding... Simple

Thank you, again.
  • WritingBadCode
  • Graduate
  • Graduate
  • User avatar
  • Posts: 214
  • Loc: Sweden

Post 3+ Months Ago

jokerper wrote:
That is working, thanks - I love when things just works!!!

You're doing a good coding... Simple

Thank you, again.



Good thing it worked, my code don't look like that usually/always but that's a different story. = )
  • may
  • Proficient
  • Proficient
  • User avatar
  • Posts: 328
  • Loc: Holland [NL]

Post 3+ Months Ago

Sidenotes,

using curly brackets might clean the code even further. i.e.
PHP Code: [ Select ]
echo "article id:" . $row['id'] . "<br />TITLE: " . $row['Titel'] . "<br />more text:" . $row['omraade'];
      echo "<br /> ------------------------<br />";
 
  1. echo "article id:" . $row['id'] . "<br />TITLE: " . $row['Titel'] . "<br />more text:" . $row['omraade'];
  2.       echo "<br /> ------------------------<br />";
  3.  


vs. with curly tricks (php5 {$array{}} <php5 {$array[]})..

PHP Code: [ Select ]
echo "article id: {$row{'id'}} <br /> TITLE: {$row{'Titel'}} <br />more text: {$row{'omraade'}} <br />------------------------<br />";
 
  1. echo "article id: {$row{'id'}} <br /> TITLE: {$row{'Titel'}} <br />more text: {$row{'omraade'}} <br />------------------------<br />";
  2.  


When parsing the sql resultset you might want to use mysql_fetch_assoc($r) instead of mysql_fetch_array($r). Difference is that mysql_fetch_array will also return column numbers as available arraykeys next to the associated ones that are used in the script.

i.e.
$row{0} = 'value', $row{id} = 'value', $row{1} = 'value1', $row{'Title'} = 'value1'

This might reduce memory consumption dramatically with big applications.

Just for your consideration.. ;-)
  • WritingBadCode
  • Graduate
  • Graduate
  • User avatar
  • Posts: 214
  • Loc: Sweden

Post 3+ Months Ago

may wrote:
vs. with curly tricks (php5 {$array{}} <php5 {$array[]})..

PHP Code: [ Select ]
echo "article id: {$row{'id'}} <br /> TITLE: {$row{'Titel'}} <br />more text: {$row{'omraade'}} <br />------------------------<br />";
 
  1. echo "article id: {$row{'id'}} <br /> TITLE: {$row{'Titel'}} <br />more text: {$row{'omraade'}} <br />------------------------<br />";
  2.  



Interesting, this is new syntax for me. Does it affect performance and is there a place where I can read about this further? :)
  • may
  • Proficient
  • Proficient
  • User avatar
  • Posts: 328
  • Loc: Holland [NL]

Post 3+ Months Ago

the curly wrapper is pretty old, as I can remember using it in PHP4.

I read about the curly brackets in arrays in some book. It was suggested to be the PHP5 method of accessing array indexes. And as fas as I know it works, not sure if it will be finally adopted or if it will actually enhances performance for there is not much about it in the php manuals. But the following can be found on the subject using the following page in the php manual. http://www.php.net/manual/en/language.types.string.php

about the array index
Characters within strings may be accessed and modified by specifying the zero-based offset of the desired character after the string using square array brackets, as in $str[42]. Think of a string as an array of characters for this purpose. The functions substr() and substr_replace() can be used when you want to extract or replace more than 1 character.
Note: Strings may also be accessed using braces, as in $str{42}, for the same purpose.

About curly..
PHP Code: [ Select ]
$great = 'fantastic';
 
// Won't work, outputs: This is { fantastic}
echo "This is { $great}";
 
// Works, outputs: This is fantastic
echo "This is {$great}";
echo "This is ${great}";
 
// Works
echo "This square is {$square->width}00 centimeters broad.";
 
 
// Works, quoted keys only work using the curly brace syntax
echo "This works: {$arr['key']}";
 
 
// Works
echo "This works: {$arr[4][3]}";
 
// This is wrong for the same reason as $foo[bar] is wrong  outside a string.
// In other words, it will still work, but only because PHP first looks for a
// constant named foo; an error of level E_NOTICE (undefined constant) will be
// thrown.
echo "This is wrong: {$arr[foo][3]}";
 
// Works. When using multi-dimensional arrays, always use braces around arrays
// when inside of strings
echo "This works: {$arr['foo'][3]}";
 
// Works.
echo "This works: " . $arr['foo'][3];
 
echo "This works too: {$obj->values[3]->name}";
 
echo "This is the value of the var named $name: {${$name}}";
 
echo "This is the value of the var named by the return value of getName(): {${getName()}}";
 
echo "This is the value of the var named by the return value of \$object->getName(): {${$object->getName()}}";
 
// Won't work, outputs: This is the return value of getName(): {getName()}
echo "This is the return value of getName(): {getName()}";
 
  1. $great = 'fantastic';
  2.  
  3. // Won't work, outputs: This is { fantastic}
  4. echo "This is { $great}";
  5.  
  6. // Works, outputs: This is fantastic
  7. echo "This is {$great}";
  8. echo "This is ${great}";
  9.  
  10. // Works
  11. echo "This square is {$square->width}00 centimeters broad.";
  12.  
  13.  
  14. // Works, quoted keys only work using the curly brace syntax
  15. echo "This works: {$arr['key']}";
  16.  
  17.  
  18. // Works
  19. echo "This works: {$arr[4][3]}";
  20.  
  21. // This is wrong for the same reason as $foo[bar] is wrong  outside a string.
  22. // In other words, it will still work, but only because PHP first looks for a
  23. // constant named foo; an error of level E_NOTICE (undefined constant) will be
  24. // thrown.
  25. echo "This is wrong: {$arr[foo][3]}";
  26.  
  27. // Works. When using multi-dimensional arrays, always use braces around arrays
  28. // when inside of strings
  29. echo "This works: {$arr['foo'][3]}";
  30.  
  31. // Works.
  32. echo "This works: " . $arr['foo'][3];
  33.  
  34. echo "This works too: {$obj->values[3]->name}";
  35.  
  36. echo "This is the value of the var named $name: {${$name}}";
  37.  
  38. echo "This is the value of the var named by the return value of getName(): {${getName()}}";
  39.  
  40. echo "This is the value of the var named by the return value of \$object->getName(): {${$object->getName()}}";
  41.  
  42. // Won't work, outputs: This is the return value of getName(): {getName()}
  43. echo "This is the return value of getName(): {getName()}";
  44.  



Using the correct mysql resultset parser will surly improve memory usage, simply because you are generating and popping less information into your arrays.

Using the curly wrapper instead of escapes is part of the PHP complex syntax.

Rgrds, Chris

Post Information

  • Total Posts in this topic: 14 posts
  • Users browsing this forum: No registered users and 55 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.