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:

Post by drewrockshard »

ianhull wrote: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
The SQL statement shows this:

Code: Select all

2007-11-22
As for the drop down menu - It gets the same thing (2007-11-22) - but I use this

Code: Select all

date('M (Y)', strtotime($yDate));
To reformat the drop down menu.
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

Right, I should be able to do this now.

I needed

Code: Select all

2007-11-22
Give me 1 hour as I need to pop out for ciggies too :)
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

Hah!

Awesome! :) Thanks again, I'll be monitoring this thread.

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

Post by ianhull »

Try 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 = "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));
								
								//added tempDate array
								$tempDate = array();
								$tempYDate = substr($yDate, 0, 4);
								
								if(in_array($tempDate, $tempYDate)){
								//do nothing
								}else{
								 
                                echo "<option name=\"dropdate\" value=\"$yDate\">".$xDate."</option>";
								};//end if
								$tempDate[] = $yDate;
                                $i++; 
                        } 
                ?> 
                </optgroup> 
        </select>
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

Would probably help if I put the array in the right order :)

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));
								
								//added tempDate array
								$tempDate = array();
								$tempYDate = substr($yDate, 0, 4);
								
								if(in_array($tempYDate, $tempDate)){
								//do nothing
								}else{
								 
                                echo "<option name=\"dropdate\" value=\"$yDate\">".$xDate."</option>";
								};//end if
								$tempDate[] = $yDate;
                                $i++; 
                        } 
                ?> 
                </optgroup> 
        </select>
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

tooooo, many mistakes now, i guess it's time for bed for me soon :)

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));
								
								//added tempDate array
								$tempDate = array();
								$tempYDate = substr($yDate, 0, 4);
								
								if(in_array($tempYDate, $tempDate)){
								//do nothing
								}else{
								 
                                echo "<option name=\"dropdate\" value=\"$yDate\">".$xDate."</option>";
								};//end if
								$tempDate[] = $tempYDate;
                                $i++; 
                        } 
                ?> 
                </optgroup> 
        </select>
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

Hey,

Alright - better - but still no cigar.

No errors in the log files anymore. However, I am still getting multiple items in the drop down. Pretty much, however many results I get from the database (say I have 3 date results from the mysql query for November 2007), then I get 3 options from the drop down. So right now, after adding the code, I have three drop down options of:

November 2007
November 2007
November 2007
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

Did you try the last one, or was you typing your reply?
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

Hey,

Yuppers - that's with that last bit of code you sent me. No errors - but I do have 3 drop downs of:

November 2007
November 2007
November 2007
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

Try this one, let me know how it goes :)

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));
								
								//added tempDate array
								$tempDate = array();
								$tempYDate = substr($yDate, 0, 7);
								
								if(in_array($tempYDate, $tempDate)){
								//do nothing
								}else{
								 
                                echo "<option name=\"dropdate\" value=\"$yDate\">".$xDate."</option>";
								};//end if
								$tempDate[] = $tempYDate;
                                $i++; 
                        } 
                ?> 
                </optgroup> 
        </select>
DONT FORGET DISTINCT IN THE SELECT :)
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

Darn ...

Same results.
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

even with DISTINCT?

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 DISTINCT 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));
								
								//added tempDate array
								$tempDate = array();
								$tempYDate = substr($yDate, 0, 7);
								
								if(in_array($tempYDate, $tempDate)){
								//do nothing
								}else{
								 
                                echo "<option name=\"dropdate\" value=\"$yDate\">".$xDate."</option>";
								};//end if
								$tempDate[] = $tempYDate;
                                $i++; 
                        } 
                ?> 
                </optgroup> 
        </select>
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

Alright!

Improvements :) - still not there yet.

Now, with DISTINCT, I get only two results instead of 3. After looking through the source of the HTML code (in IE or Firefox), it shows that it is giving the two results the value of:

Code: Select all

2007-11-27
2007-11-22
If I DO NOT use DISTINCT i get:

Code: Select all

2007-11-27
2007-11-27
2007-11-22
So, it looks like if there are two of the same days - it doesn't display both days, but if there are two different days (but the same month), then it still displays mutliple entries in the dropdown.

Hope the additional info has helped.
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

Yes it has helped,

I think the issue now is with the substr()
try this one, see if there is any luck.

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 DISTINCT 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));
								
								//added tempDate array
								$tempDate = array();
								$tempYDate = substr($yDate, 0, -3);
								
								if(in_array($tempYDate, $tempDate)){
								//do nothing
								}else{
								 
                                echo "<option name=\"dropdate\" value=\"$yDate\">".$xDate."</option>";
								};//end if
								$tempDate[] = $tempYDate;
                                $i++; 
                        } 
                ?> 
                </optgroup> 
        </select>
drewrockshard
Forum Commoner
Posts: 37
Joined: Sat May 29, 2004 6:07 pm
Location: Dallas, Texas
Contact:

Post by drewrockshard »

Man, this script is really kickin my ass.

Here's the results again (2 drop downs, 3 database results):

2007-11-27
2007-11-22
Post Reply