[SOLVED] complicated drop down

[saltem8]

hi, i was wondering if someone could help me with this

 

<?php include 'main.php';?>
    
       <?php 
      $result=mysql_query(" SELECT * FROM form");
      $option= "date";

      while($row=mysql_fetch_array($result))
     {
      $option .= "<option";
      if (isset($_POST['form']) && $_POST['form'] == $row['date']) {$option.= " selected='selected'";} 
      $option .= ">" . date ('F Y',$row['date']) . "</option>";
      }
      ?>

      <form method= "POST">
      
      <select>
      <?php echo $option ;?>.
      </select>
      
      <input type="submit" name="Submit" id="submit" value="Go">
</label>
      </form><?php mysql_close($conn); ?>  </div>

 

that collects the date from tables in my database, but i only want it to show the month and year once ( not for every post made ( its a blog ))

 

then when the next month comes, if i make a post, or more than one, i want it do display that month and year ( once ), but if i don't make a post, i don't want that month to show in the menu.

 

i also have a pagementation for the main page

 

<?php include 'config.php'; 

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql'); 
mysql_select_db( $dbname, $conn ); 

$perPage = 5; // number to display on per page 

// Start by counting the number of posts 

$res = mysql_query ("SELECT count(*) AS num FROM form") or die (mysql_error() . 'dying hurts'); 

$num = mysql_fetch_assoc($res); 
$num = $num['num']; 

mysql_free_result($res); 

// now work out how many pages there are, check if page has been sent via GET 

$numPages = ceil($num / $perPage); 

// Check to make sure pagenum will be 1 or greater, less than numpages and an actual number 
$pageNum  = (!empty($_GET['id']) &&  
             is_numeric($_GET['id']) &&  
             $_GET['id'] > 1)  
              
             ? ($_GET['id'] < $numPages ?  ($_GET['id']) : $numPages) : 1; // Set to 1 if not set or something dodgy 

// Okay, now we run the query and set the limits LIMIT xx, yy (x= start from , y= limit) 

$sql = "SELECT * FROM form ORDER BY date DESC LIMIT " . ($pageNum - 1) * $perPage . ", $perPage"; 
$res = mysql_query($sql, $conn) or die(mysql_error()); 

while ($row = mysql_fetch_object($res)) {          
echo '<hr></hr>';
print  $row->title ;
echo nl2br("\n");      
print strftime('%d %B %Y', $row->date); 
echo nl2br("\n");
echo nl2br("\n"); 
print $row->blog_mes;   

} 

// Some paginananananananation 

echo '<div>'; 
for ($i = 1; $i <= $numPages; $i++) 
{ 
    if ($pageNum != $i) 
    echo '<a href="index.php?id=', $i, '">', $i, '</a> '; 
    else  
    echo '<b>', $i, '</b>'; 
} 
echo '</div>'; 

?> 

 

also if the person uses the drop down i need it to only display the posts for that month

 

this is getting really complicated, i am new to php but i need this feature to work

 

can you help ?

 

thank you

 

[Barand]

Use "SELECT DISTINCT date FROM form"

[saltem8]

no, sorry not working,

 

if i understand correctly what that means then it would not work anyway, in the database i have the full date

 

print strftime('%d %B %Y', $row->date); 

 

i did try, unless i have placed it wrong ( it had no effect - or errors ) - i changed this and tried it

 

      $result=mysql_query(" SELECT * FROM form");

[Bauer418]

Full date with time?

 

SELECT DISTINCT DATE(date) AS date FROM form

[saltem8]

you are getting closer, but it is now displaying the default January 1970

[Bauer418]

Ah, I should have realized earlier...you are storing UNIX timestamps in the table, aren't you?  Not a default DATETIME?

[saltem8]

yup, thats why i am having so much trouble

[Barand]

SELECT DISTINCT DATE_FORMAT(FROM_UNIXTIME(mydatecol), '%Y %m') as yearmonth FROM form

[saltem8]

nope, i already tried that one and got the error

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\EasyPHP 2.0b1\www\blog\index.php on line 200

 

line 200

 

 while($row=mysql_fetch_array($result))

 

i even tried this

 

$result=mysql_query(" SELECT DISTINCT DATE_FORMAT(FROM_UNIXTIME(date), '%Y %m') as yearmonth FROM form");

 

but got this in return

 

Notice: Undefined index: date in C:\Program Files\EasyPHP 2.0b1\www\blog\index.php on line 204

 

but with the above, it also goes back to January 1970, where with your original code it gives no results in the drop down

 

 

[Barand]

i even tried this

 

$result=mysql_query(" SELECT DISTINCT DATE_FORMAT(FROM_UNIXTIME(date), '%Y %m') as yearmonth FROM form");

 

but got this in return

 

Notice: Undefined index: date in C:\Program Files\EasyPHP 2.0b1\www\blog\index.php on line 204

 

 

Probably because it returns a column called `yearmonth` and not `date`

[saltem8]

ok, i changed that to this

 

$result=mysql_query("SELECT DISTINCT DATE_FORMAT(FROM_UNIXTIME(date), '%Y %m') as date FROM form");

 

and it returned this

 

]Notice: A non well formed numeric value encountered in C:\Program Files\EasyPHP 2.0b1\www\blog\index.php on line 204

 

this is line 204

 

      $option .= ">" . date ('F Y',$row['date']) . "</option>";

[Barand]

Change query to give the data format you want

<?php
$result=mysql_query("SELECT DISTINCT DATE_FORMAT(FROM_UNIXTIME(date), '%M %Y') as date FROM form");
?>

 

then use

 

<?php
$option .= ">" . $row['date'] . "</option>";
?>

 

[saltem8]

i am sorry it has take me this long to reply, but based on your information i have managed to basically re-write what i had, - its not exactly how i wanted it, but it works and it dont look too bad

 

<?php

    //get this month
    $month = date("n");

    $jump = isset($_POST['jump']) ? $_POST['jump'] : (isset($_GET['jump']) ? $_GET['jump'] : $month);

    require('main.php');

    $res = mysql_query ("SELECT count(*) AS num FROM form WHERE MONTH(FROM_UNIXTIME(date)) = 1 ") or die (mysql_error() . 'dying hurts');
    $num = mysql_fetch_assoc($res);
    $num1 = $num['num'];

    $res = mysql_query ("SELECT count(*) AS num FROM form WHERE MONTH(FROM_UNIXTIME(date)) = 2 ") or die (mysql_error() . 'dying hurts');
    $num = mysql_fetch_assoc($res);
    $num2 = $num['num'];

    $res = mysql_query ("SELECT count(*) AS num FROM form WHERE MONTH(FROM_UNIXTIME(date)) = 3 ") or die (mysql_error() . 'dying hurts');
    $num = mysql_fetch_assoc($res);
    $num3 = $num['num'];

    $res = mysql_query ("SELECT count(*) AS num FROM form WHERE MONTH(FROM_UNIXTIME(date)) = 4 ") or die (mysql_error() . 'dying hurts');
    $num = mysql_fetch_assoc($res);
    $num4 = $num['num'];

    $res = mysql_query ("SELECT count(*) AS num FROM form WHERE MONTH(FROM_UNIXTIME(date)) = 5 ") or die (mysql_error() . 'dying hurts');
    $num = mysql_fetch_assoc($res);
    $num5 = $num['num'];

    $res = mysql_query ("SELECT count(*) AS num FROM form WHERE MONTH(FROM_UNIXTIME(date)) = 6 ") or die (mysql_error() . 'dying hurts');
    $num = mysql_fetch_assoc($res);
    $num6 = $num['num'];

    $res = mysql_query ("SELECT count(*) AS num FROM form WHERE MONTH(FROM_UNIXTIME(date)) = 7 ") or die (mysql_error() . 'dying hurts');
    $num = mysql_fetch_assoc($res);
    $num7 = $num['num'];

    $res = mysql_query ("SELECT count(*) AS num FROM form WHERE MONTH(FROM_UNIXTIME(date)) = 8 ") or die (mysql_error() . 'dying hurts');
    $num = mysql_fetch_assoc($res);
    $num8 = $num['num'];

    $res = mysql_query ("SELECT count(*) AS num FROM form WHERE MONTH(FROM_UNIXTIME(date)) = 9 ") or die (mysql_error() . 'dying hurts');
    $num = mysql_fetch_assoc($res);
    $num9 = $num['num'];

    $res = mysql_query ("SELECT count(*) AS num FROM form WHERE MONTH(FROM_UNIXTIME(date)) = 10 ") or die (mysql_error() . 'dying hurts');
    $num = mysql_fetch_assoc($res);
    $num10 = $num['num'];

    $res = mysql_query ("SELECT count(*) AS num FROM form WHERE MONTH(FROM_UNIXTIME(date)) = 11 ") or die (mysql_error() . 'dying hurts');
    $num = mysql_fetch_assoc($res);
    $num11 = $num['num'];

    $res = mysql_query ("SELECT count(*) AS num FROM form WHERE MONTH(FROM_UNIXTIME(date)) = 12 ") or die (mysql_error() . 'dying hurts');
    $num = mysql_fetch_assoc($res);
    $num12 = $num['num'];

    ?>

       <?php

      $result= mysql_query("SELECT * FROM form WHERE MONTH(FROM_UNIXTIME(date)) = '".$jump."' ");
      $option= "date";


      ?>


      <form method= "POST">

      <!--

      <select name="ddate" id="ddate">
      <?php echo $option ;?>.
      </select>

      -->


      <select name="jump" id="jump"  onchange="location.href=this.options[this.selectedIndex].value">
      <option value="index.php?jump=1"<?php  echo $jump=='1'?' selected':''?> >January 2008<?php echo '  ('.$num1.')'; ?></option>
      <option value="index.php?jump=2"<?php  echo $jump=='2'?' selected':''?> >Febuary 2008<?php echo '  ('.$num2.')'; ?></option>
      <option value="index.php?jump=3"<?php  echo $jump=='3'?' selected':''?> >March 2008<?php echo '  ('.$num3.')'; ?></option>
      <option value="index.php?jump=4"<?php  echo $jump=='4'?' selected':''?> >April 2008<?php echo '  ('.$num4.')'; ?></option>
      <option value="index.php?jump=5"<?php  echo $jump=='5'?' selected':''?> >May 2008<?php echo '  ('.$num5.')'; ?></option>
      <option value="index.php?jump=6"<?php  echo $jump=='6'?' selected':''?> >June 2008<?php echo '  ('.$num6.')'; ?></option>
      <option value="index.php?jump=7"<?php  echo $jump=='7'?' selected':''?> >July 2008<?php echo '  ('.$num7.')'; ?></option>
      <option value="index.php?jump=8"<?php  echo $jump=='8'?' selected':''?> >August 2008<?php echo '  ('.$num8.')'; ?></option>
      <option value="index.php?jump=9"<?php  echo $jump=='9'?' selected':''?> >September 2008<?php echo '  ('.$num9.')'; ?></option>
      <option value="index.php?jump=10"<?php echo $jump=='10'?' selected':''?> >October 2008<?php echo '  ('.$num10.')'; ?></option>
      <option value="index.php?jump=11"<?php echo $jump=='11'?' selected':''?> >November 2008<?php echo '  ('.$num11.')'; ?></option>
      <option value="index.php?jump=12"<?php echo $jump=='12'?' selected':''?> >December 2008<?php echo '  ('.$num12.')'; ?></option>

      </select>


      </form><?php mysql_close($conn); ?>

 

you can see the blog working here http://www.fmat.co.uk with the drop down working, ( please pay no attention to the template, it is just something i threw together )

 

thank you very much for your help, you definitely pointed me in the right direction

[Barand]

Having read the code, please accept my apologies for the direction

 

Something like this would save on all those queries

<?php

    //get this month and year
    $month = date("n");
    $year = date("Y");
    
    $jump = isset($_POST['jump']) ? $_POST['jump'] : (isset($_GET['jump']) ? $_GET['jump'] : $month);

    require('main.php');
    
    $month_tots = array();
    for ($i=1; $i<=12; $i++) $month_tots[$i] = 0;       // ensure total for every month

    $res = mysql_query ("SELECT MONTH(FROM_UNIXTIME(date)) as mth, COUNT(*) AS num 
                        FROM form
                        WHERE YEAR(FROM_UNIXTIME(date)) = '$year'
                        GROUP BY mth");
     while (list($mth, $num) = mysql_fetch_row($res)) 
     {
         $month_tots[$mth] = $num;         // put month totals in the array
     }
     
     /**
     * create the dropdown
     */
     echo '<form method="POST">
     <select name="jump" id="jump"  onchange="location.href=this.options[this.selectedIndex].value">';
     foreach ($month_tots as $m => $num)
     {
         $sel = $jump==$m ? 'selected':'';
         $dt = date ('F Y', mktime(0,0,0,$m, 1, $year));
         echo "<option value='index.php?jump=$m' $sel>$dt ($num)</option>";
     }
     echo '</select></form>';
?>

[saltem8]

wow thanks, you have done in 10 minutes, what i have been trying to do for 4 hours

 

shorten the code and make it look better

 

cheers, i really appreciate your help