mySQL duplicate date column - PHP needs to be displayed once

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

mySQL duplicate date column - PHP needs to be displayed once

Post by drewrockshard »

Hey All,

I have a little issue. I can't seem to get around this, simple (I think) task. I have a mySQL database that holds this table that has 4 columns: ID (auto_increment)- user - date - post. The ID is of course an auto_incrementing number for uniqueness. User is the user who posts to the DB. date is the current date of the post .. which the format of yyyy-mm-dd. Finally, the post is just as you would think - the post content. This all works by the way. I just wanted to explain what the table looked like.

My problem is with the date column. Users are allow to have multiple posts in the same day, and I want this. However, when running a query against the database:

Code: Select all

SELECT date FROM walkthroughs WHERE user='$authUser' ORDER BY date DESC
dbtable is just a table, it will select the date column, and the $authUser is just a variable (that works) that returns the currently logged in user (that authenticates with the mysql database). However, here's the results I get for the "testaccount" user:

Code: Select all

date
2007-11-27
2007-11-27
While this is fine, this will not work for what I'm about to show you. I need to actually retrieve some information from the database - for the currently logged in user. I need to have a drop down menu that holds the month and year. Once you click on the option, and click "filter" it will then retrieve all the posts for that month/year. This works, however, the drop down menu (when there are multiple posts for the month), displays multple entries in the drop down menu. Let me show you.

Per the database output above, the drop down menu shows this:

"November 2007"
"November 2007"

Of course, no one would want this. I would need to for it to save "November 2007" once and when you click it and then click filter, it should show you the two results as links. I want to say this, so far, everything works (even the links). the only problem I actually have is the drop down menu showing a multiple month/year, when I want it to just have one month/year based on the database results.

Here's some code to sample:

Code: Select all

<div id="archives">
<h1 id="form"><?php echo $row['realname']; ?>'s Saved DC Walkthroughs</h1>
<?php
        $userQuery = "SELECT * FROM walkthroughs WHERE user='$authUser' ORDER BY date DESC";
        $userResult = mysql_query($userQuery) or die(mysql_error());

        if (mysql_num_rows($userResult) == 0) {
                echo "<p class=\"regindent\">You currently do not have any saved Walkthroughs</p>";
        }
        else {
?>
<form id="getwalkthroughs" name="getwalkthroughs" method="get" action="<?php $_SERVER['PHP_SELF']; ?>">
        <select id="dcdate" name="dcdate">
                <optgroup label="Please Select Date">
                        <option name="dropdate" value="0">Please Select</option>
                <?php
                        $i=0;
                        $dateQuery = "SELECT date FROM walkthroughs WHERE user='$authUser' ORDER BY date DESC";
                        $dateResult = mysql_query($dateQuery) or die(mysql_error());
                        $num = mysql_num_rows($dateResult);

                        while ($i<$num) {
                                $zDate = mysql_fetch_assoc($dateResult);
                                $yDate = $zDate['date'];
                                $xDate = date('M (Y)', strtotime($yDate));
                                echo "<option name=\"dropdate\" value=\"$yDate\">".$xDate."</option>";
                                $i++;
                        }
                ?>
                </optgroup>
        </select>
        <input id="submit" type="submit" value="Filter Walkthroughs" />
</form>
<?php
        $returnedDate = $_GET['dcdate'];
        $returnedQuery = "SELECT date FROM walkthroughs WHERE date='$returnedDate' ORDER BY date DESC";
        $returnedResult = mysql_query($returnedQuery) or die(mysql_error());

        if (isset($_GET['dropdate']) !== "0") {
                while ($row = mysql_fetch_object($returnedResult)) {
                        $mysqlDate = $row->date;
                        $newDate = date('M d, Y', strtotime($mysqlDate));

                        // Return the links
                        while ($row = mysql_fetch_object($userResult)) {
                                $mysqlDate = $row->date;
                                $newDate = date('M d, Y', strtotime($mysqlDate));
                                echo "<p><a href=\"details/$row->id\">".$newDate."</a></p>\n";
                        }
                }
        }
        else {
                // do nothing 
        }
?>
<?php

}
?>
</div>
Also, just to pull it out - the actual drop down menu to note, is this part of the above code:

Code: Select all

<select id="dcdate" name="dcdate">
                <optgroup label="Please Select Date">
                        <option name="dropdate" value="0">Please Select</option>
                <?php
                        $i=0;
                        $dateQuery = "SELECT date FROM walkthroughs WHERE user='$authUser' ORDER BY date DESC";
                        $dateResult = mysql_query($dateQuery) or die(mysql_error());
                        $num = mysql_num_rows($dateResult);

                        while ($i<$num) {
                                $zDate = mysql_fetch_assoc($dateResult);
                                $yDate = $zDate['date'];
                                $xDate = date('M (Y)', strtotime($yDate));
                                echo "<option name=\"dropdate\" value=\"$yDate\">".$xDate."</option>";
                                $i++;
                        }
                ?>
                </optgroup>
        </select>
So, overall, I'm pretty much wanting a simple PHP function or snippet that will take a mysql result of the "date" column and be able to get multiple results, but not show multiple date/year in the drop down.

Any help is appriciated.
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

Maybe use DISTINCT

Code: Select all

SELECT DISTINCT date FROM walkthroughs WHERE user='$authUser' ORDER BY date DESC
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

Awesome :).

Okay, so this helps. Now, instead of getting multiple days, I get distinct days, but the new problem is, what PHP snippet to write to distinct the year too. In other words, the drop down menu needs to only have one month and multiple years, such as:

January (2007)
February (2007)
March (2007)
April (2007)

..... etc .....

Let me know if you have any ideas.
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

you may have to write some function which will store the months/years in an array then use if else and in_array
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

Do you have any snippets or sample code that you can help me figure it out? I've been pulling my hair out for the past 6 hours trying to figure this out.
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

Drew, what I would do is something like this.

Code: Select all

<select id="dcdate" name="dcdate"> 
                <optgroup label="Please Select Date"> 
                        <option name="dropdate" value="0">Please Select</option> 
                <?php 
                        $i=0; 
                        
$dateQuery = mysql_query("SELECT DISTINCT date FROM walkthroughs WHERE user='$authUser' ORDER BY date DESC")or die(mysql_error()); 

                        //setup a new temp array $tempDates

                        $tempDates[];

                        while($dateQueried = mysql_fetch_array($dateQuery)){
extract($dateQueried);

$tempDates[] = substr(str_replace('(',str_replace(')',$date)), -6, 6);
$theDates = substr(str_replace('(',str_replace(')',$date)), -6, 6);
if(in_array($theDates, $tempDates)){
//do nothing
}else{
echo '<option name="dropdate" value="$date">$date</option>';

                        } 
                ?> 
                </optgroup> 
        </select>
let me know if that works.
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

Hello Ianhull,

I really appriciate the help, really I do.

After incorporating this into my code I get an error of "Fatal error: Cannot use [] for reading in" ...

That line of code points to this peice of code:

Code: Select all

$tempDates[];
I'm using PHP:

PHP 5.1.6 (cli) (built: Sep 20 2007 10:04:27)
Copyright (c) 1997-2006 The PHP Group
Zend Engine v2.1.0, Copyright (c) 1998-2006 Zend Technologies

Any ideas?
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

sorry, my mistake,

should be

Code: Select all

$tempDates = array();
or you could remove the line totally
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

Hey,

Now the drop down menu doesn't have any results. So, somewhere your code works - and it doesn't - which is better than where I've been. Looks like its giving errors in the log file though:

[Wed Nov 28 18:04:09 2007] [error] [client x.x.x.x] PHP Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/x.com/public_html/sl.x.com/x/index.php on line 194, referer: http://sl.x.com/

Line 194 is ...

Code: Select all

while($dateQueried = mysql_fetch_array($dateQuery)) {
Which references this:

Code: Select all

$dateQuery = "SELECT DISTINCT date FROM walkthroughs WHERE user='$authUser' ORDER BY date DESC";
Which i just ran it through and it works (the SQL statement).

Any ideas?
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

Yes, that error will probably be because there is no records returned.

Right, give me half hour and I will look though your code again and see if there is anything else i can do.

Code: Select all

<select id="dcdate" name="dcdate"> 
                <optgroup label="Please Select Date"> 
                        <option name="dropdate" value="0">Please Select</option> 
                <?php 
                        $i=0; 
                        $dateQuery = "SELECT date FROM walkthroughs WHERE user='$authUser' ORDER BY date DESC"; 
                        $dateResult = mysql_query($dateQuery) or die(mysql_error()); 
                        $num = mysql_num_rows($dateResult); 

                        while ($i<$num) { 
                                $zDate = mysql_fetch_assoc($dateResult); 
                                $yDate = $zDate['date']; 
                                $xDate = date('M (Y)', strtotime($yDate)); 
                                echo "<option name=\"dropdate\" value=\"$yDate\">".$xDate."</option>"; 
                                $i++; 
                        } 
                ?> 
                </optgroup> 
        </select>
Tell me above what xy and zDate echo out.

also, do you need to have the "(Y)" brackets around it?

Thanks
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

Hey,

Interesting ...

$xDate outputs -> Dec (1969) - this isn't write - i think it defaults to the standard first unix datestamp
$yDate outputs -> nothing at all
$zDate outputs -> nothing at all

So xDate outputs crap (pretty much) and the other two don't output anything, which tells me these variables might be messed up .. maybe?
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

Right, so the reason the attempt failed is because the record was not recieved.

I think that php date starts at dec 1969???(anyone??)

So were not getting any result from the DB, maybe because "date" is a reserved word for php even though we are using it as a variable.

This:
January (2007)
February (2007)
March (2007)
April (2007)


is what you would liketo be output, or what you have had output before?
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

Wooopsie!

Okay, I had to put it in an area that I could see the output heh. I have some valid results now for you of the variables that you wanted:

$yDate output -> 2007-11-27
$xDate output -> Nov (2007)
$zDate output -> Array

Hope this helps.
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

ianhull wrote:Right, so the reason the attempt failed is because the record was not recieved.

I think that php date starts at dec 1969???(anyone??)

So were not getting any result from the DB, maybe because "date" is a reserved word for php even though we are using it as a variable.

This:
January (2007)
February (2007)
March (2007)
April (2007)


is what you would liketo be output, or what you have had output before?
The output I'm wanting is that, however, based on the database. So for now, users are starting to use this app I wrote, and so far there are only entries for November. So it shouldn't have any results for December or even January-October - because there is nothing in the database regarding that.

So basically If a user has 20 entries for November, I want the output to say "November 2007" only once, as they can click on the "November 2007" and click a button that says "Filter" and then it will show all their entries for November 2007.

Hope you understand :)
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

show me exactly what comes from the date column of the db.

for example, is it

November 2007
or November (2007)
or a UNIX timestamp?

Thanks
Post Reply