Solved

duplicate entries in a drop down list and search results PHP

Posted on 2008-06-25
9
563 Views
Last Modified: 2013-12-12
Hi,
I have some code (attached) that displays a date extracted from a MySQL database (the format gets changed.)
Does someone know how to add some code to omit the duplicate entries?  That is, all entries that come up as January 2008 when the format is changed will only be displayed once?

When doing this, does it affect the searching, or will all matches be displayed?

I have added the section of the dropdown list code and all the Search code/

Thanks for your help
----------------------------

List.php

----------------------------

<select name="datelist"><option value="0" selected>Happening in...</option> 

<?php

$result = mysql_query("SELECT DATE_FORMAT(StartDate,'%Y-%m-%d') as valDate,  DATE_FORMAT(StartDate,'%M-%Y') as displayDate from event");

 

while ($row = mysql_fetch_assoc($result)) {

/*

    $date_pieces = explode("-",$row["StartDate"]);

        $tstamp = mktime(0,0,0,$date_pieces[1],$date_pieces[2],$date_pieces[0]);

        $displaydate = date("F-Y",$tstamp); 

*/

        ?>

 

    <option value="<?=$row['valDate']?>"><?=$row['displayDate']?></option>

    <?php

        } 

        ?>

	   </select>

<input type="submit" value="Submit">
 

--------------------------------------------

Search.php

--------------------------------------------
 

<?php

include_once("Settings.php"); //Path to Settings.php

 

$ErrorCount = 0;

 

if (isset($_SERVER['REQUEST_METHOD'])) { //The action of the form passes ?action=insert on the end of the URL. This line checks for that to see if the form has been submitted

   //Tells the page if the record should be inserted

 

   if (!empty($_REQUEST["typelist"])) { //If an input named EventType has been submitted

      $EventType = $_REQUEST["typelist"]; //Store its value in $EventType

   } else {

      $EventType = ""; //Declare $EventType as an empty string to prevent errors later

      echo "You must select an event type.";

      $ErrorCount++; //Increment the error count

 

   }

   

   if (!empty($_REQUEST["locationlist"])) { //If an input named Country has been submitted

      $Country = $_REQUEST["locationlist"]; //Store its value in $Country

   } else {

      $Country = ""; //Declare $Country as an empty string to prevent errors later

      echo "You must select a country.";

      $ErrorCount++; //Increment the error count

 

   }

   

   if (!empty($_REQUEST["datelist"])) { //If an input named StartDate has been submitted

      $StartDate = $_REQUEST["datelist"]; //Store its value in $StartDate

   } else {

      $StartDate = ""; //Declare $StartDate as an empty string to prevent errors later

      echo "You must select a start date.";

      $ErrorCount++; //Increment the error count

   }

   

}

 

//If the for was submitted with no errors, run the SQL query and display the results.

if ($ErrorCount == 0) {

 

?>

<table>

<tr>

<td>Event Type</td>

<td>City</td>

<td>Country</td>

<td>StartDate</td>

<td>EndDate</td>

<td>Title</td>

<td>Description</td>

<td>WebLink</td>

<td>BuyTicket</td>

<td>Images</td>

</tr>

<?

      $sql= "SELECT * FROM event WHERE EventType = '" . $EventType . "' AND Country = '" . $Country . "' AND DATE_FORMAT(StartDate,'%Y-%m-%d') = '" . $StartDate . "' ORDER BY StartDate ASC";

 

//echo "SQL Statement: " . $sql ; 
 

$result = mysql_query($sql) or die("Sql Query Failed: " . mysql_error());

 

while ($row = mysql_fetch_assoc($result)){

      //Ive echo'd all of the fields below without any HTML formating, so you can build your HTML around them.

?>

<tr>

<td><? echo $row["EventType"]; ?>

<td><? echo $row["City"]; ?>

<td><? echo $row["Country"]; ?>

<td><? echo $row["StartDate"]; ?>

<td><? echo $row["EndDate"]; ?>

<td><? echo $row["Title"]; ?>

<td><? echo $row["Description"]; ?>

<td><? echo $row["WebLink"]; ?>

<td><? echo $row["BuyTicket"]; ?>

<td><? echo $row["Images"]; ?>

</tr>

<?

     }

}

?>

</table>

<?

@mysql_close($connection);

?>

Open in new window

0
Comment
Question by:Amanda Watson
9 Comments
 
LVL 82

Expert Comment

by:hielo
ID: 21870902
Instead of:
$result = mysql_query("SELECT DATE_FORMAT(StartDate,'%Y-%m-%d') as valDate,  DATE_FORMAT(StartDate,'%M-%Y') as displayDate from event");

try:
$result = mysql_query("SELECT DISTINCT DATE_FORMAT(StartDate,'%Y-%m-%d') as valDate,  DATE_FORMAT(StartDate,'%M-%Y') as displayDate from event");
0
 
LVL 11

Author Comment

by:Amanda Watson
ID: 21870996
Hi,
Well if you look at
http://threerobots.com/List.php

You can see that it worked for the entries that were already in the table,  but then I added another entry and the date is being duplicated.

The new entry has got a different Country too, so I am not sure what is going on?

I have attached the tables I am testing.

Can you see what is wrong?
robot4.jpg
0
 
LVL 11

Author Comment

by:Amanda Watson
ID: 21871195
Hi, can you let me know if you are abadoning this question as the line of questioning is changing a bit and you were successful on the immediate question.

It has got a bit more complicated so I am happy to finish this question and re-ask another one?
0
 
LVL 11

Author Comment

by:Amanda Watson
ID: 21871464
I have changed the database now.
Its not working!

Any other ideas?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 21874397
Try this:

$result = mysql_query("SELECT DISTINCT DATE_FORMAT(StartDate,'%Y-%m-%d') as valDate,  DISTINCT DATE_FORMAT(StartDate,'%M-%Y') as displayDate from event");
0
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 21874438
I saw the html code and it show like this:

    <option value="2008-01-03">January-2008</option>
     
    <option value="2008-07-07">July-2008</option>
     
    <option value="2008-01-06">January-2008</option>

You are having two dates in the month January-2008, so it will display 'January-2008' twice. What you have at present is correct only. If you want to display 'January-2008' only once, then you cannot have two dates "2008-01-03" and "2008-01-06"
0
 
LVL 11

Author Comment

by:Amanda Watson
ID: 21878597
So is ther a way to then get the date lists to retreive the earliest and latest dates from the database and fill in the gap. (Which will stop the duplicate dates).
0
 
LVL 5

Accepted Solution

by:
mms_master earned 500 total points
ID: 21879044
>>>So is ther a way to then get the date lists to retreive the earliest and latest dates from the database and fill in the gap. (Which will stop the duplicate dates).

I have allready done this for you in your other question. I will now change the image from BLOB to varchar and use a folder to store the images instead of the database. I will also fix the links for you. Should be complete tomorrow. I will upload it in your first question when its done.

mms_master
0
 
LVL 11

Author Comment

by:Amanda Watson
ID: 21887922
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now