Multi entries showing up once

[mrlayance]

I have a database field with cities listed, many repeat.

For example

LA
LA
LA
LA
New York
New York
New York
New York
Chicago
Chicago

How would I make the form list only show

LA
New York
Chicago

?

[batman]

Give more info. Is this for like multiple people that might live in the same city? Or why are you calling these fields?

If it's the multiple people stuff, then just use a WHERE var='$var'

something like this:

mysql_query = ("SELECT city, city, city FROM table WHERE var='$var' LIMIT 1");

You can modify above script to fit your needs.

-Blake

[toddmarx]

If you are trying to just get it to look at a column in a table, and select only the distinct ones then try using this:

mysql_query =("SELECT distinct (fieldname) FROM tablename ORDER BY fieldname ASC");

Be sure to leave the () in the first fieldname, and you can change the ASC to DSC if you want it list alphabetically from a to z (asc) or Z to A (dsc).

Hope that helps,
Todd

[mrlayance]

Well, here is my code

<select name="PROSTATEBOX">
      <?php
do {  

?>
      <option value="<?php echo $row_Recordset4['state']?>"><?php echo $row_Recordset4['state']?></option>
      <?php
} while ($row_Recordset4 = mysql_fetch_assoc($Recordset4));
  $rows = mysql_num_rows($Recordset4);
  if($rows > 0) {
      mysql_data_seek($Recordset4, 0);
	  $row_Recordset4 = mysql_fetch_assoc($Recordset4);
  }
?>
    </select>

[batman]

Are you wanting it to show an option value for every value the query returns? Also, why is there more than one of the same thing? Is this because of members?

A little more detail on this, and I might be able to help you.
-Blake

[mrlayance]

Ok, here is my project. I have a forum http://www.hipbase.com/mrlayance/addshow.php . Here is what the table looks like.

#

Table structure for table mrlayance

#

CREATE TABLE mrlayance (
id int(11) NOT NULL auto_increment,
date char(255) NOT NULL default '',
artist char(255) NOT NULL default '',
setlist char(255) NOT NULL default '',
venue char(255) NOT NULL default '',
location char(255) NOT NULL default '',
media char(255) NOT NULL default '',
discs char(255) NOT NULL default '',
prostate char(255) NOT NULL default '',
KEY ID (id)
) TYPE=MyISAM;

I am trying to do a join so the is I select a sibgle date from another table the forum will populate the other fields and the out put will basically be like I have now. Instead of doing six selection you only have to do 1.

Here is my current code for addshow.php

<?php require_once('../Connections/Remote_Database_Connection.php'); ?>
 <?php
mysql_select_db($database_Remote_Database_Connection, $Remote_Database_Connection);
$query_Recordset1 = "SELECT * FROM concerts ORDER BY `date` ASC";
$Recordset1 = mysql_query($query_Recordset1, $Remote_Database_Connection) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

mysql_select_db($database_Remote_Database_Connection, $Remote_Database_Connection);
$query_Recordset2 = "SELECT * FROM venues ORDER BY description ASC";
$Recordset2 = mysql_query($query_Recordset2, $Remote_Database_Connection) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

mysql_select_db($database_Remote_Database_Connection, $Remote_Database_Connection);
$query_Recordset3 = "SELECT * FROM locations GROUP BY city ASC";
$Recordset3 = mysql_query($query_Recordset3, $Remote_Database_Connection) or die(mysql_error());
$row_Recordset3 = mysql_fetch_assoc($Recordset3);
$totalRows_Recordset3 = mysql_num_rows($Recordset3);

mysql_select_db($database_Remote_Database_Connection, $Remote_Database_Connection);
$query_Recordset4 = "SELECT * FROM locations GROUP BY `state` ASC";
$Recordset4 = mysql_query($query_Recordset4, $Remote_Database_Connection) or die(mysql_error());
$row_Recordset4 = mysql_fetch_assoc($Recordset4);
$totalRows_Recordset4 = mysql_num_rows($Recordset4);
?>
<font face="verdana" size="1">
<?

// CONNECT INFO
$dbhost="localhost";
$dbuser="hipbase1_hipbase";
$dbpass="tragically0";
$db="hipbase1_stats";
$table="mrlayance";

// CONNECT TO DATABASE
mysql_connect           ($dbhost,$dbuser,$dbpass);
@mysql_select_db        ($db);

if ($delete)
{
  $sql = "DELETE FROM $table WHERE ID = '$delete'";
  $sql_result = mysql_query($sql) or die("Couldn't execute query.");
}

// INSERT LINK
if ($DATEBOX)
{
  $sql = "INSERT INTO $table (DATE, ARTIST, SETLIST, VENUE, LOCATION, PROSTATE, MEDIA, DISCS) VALUES('$DATEBOX', '$ARTISTBOX', '$SETLISTBOX', '$VENUEBOX', '$LOCATIONBOX', '$PROSTATEBOX', '$MEDIABOX', '$DISCSBOX')";
  $sql_result = mysql_query($sql) or die("Couldn't execute query.");
}

// Grab
$result = mysql_query("select ID,DATE,ARTIST,SETLIST,VENUE,LOCATION,PROSTATE,MEDIA,DISCS from $table");
// $result = mysql_query("SELECT * FROM $table order by DATE ASC LIMIT 0, 30");
$num_fields = mysql_num_fields($result);
$num_rows = mysql_num_rows($result);
$row_cnt = 0;

while ($num_rows>$row_cnt)
{
  $record = @mysql_fetch_row($result);
echo  "<br>$record[0] - $record[1] - $record[2] - $record[3] - $record[4] - $record[5] - $record[6] - $record[7] - $record[8] - "."<a href=\"$PHP_SELF?delete=$record[0]\">DELETE</a>\n";
        $row_cnt++;
}
?>
<br>
<br>
<p>
<FORM method=post action="<? $PHP_SELF ?>">
  <p align="left"><b> Add to links: </b><BR>
    Date: 
    <select name="DATEBOX" id="DATEBOX">
      <?php
do {  

?>
      <option value="<?php echo $row_Recordset1['date']?>"><?php echo $row_Recordset1['date']?></option>
      <?php
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
  $rows = mysql_num_rows($Recordset1);
  if($rows > 0) {
      mysql_data_seek($Recordset1, 0);
	  $row_Recordset1 = mysql_fetch_assoc($Recordset1);
  }
?>
    </select>
    <BR>
    Artist: 
    <input name="ARTISTBOX" type="text" id="ARTISTBOX" value="The Tragically Hip" size="40">
    <BR>
    Setlist: 
    <select name="SETLISTBOX" id="SETLISTBOX">
      <?php
do {  

?>
      <option value="<?php echo $row_Recordset1['id']?>"><?php echo $row_Recordset1['date']?></option>
      <?php
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
  $rows = mysql_num_rows($Recordset1);
  if($rows > 0) {
      mysql_data_seek($Recordset1, 0);
	  $row_Recordset1 = mysql_fetch_assoc($Recordset1);
  }
?>
    </select>
    <br>
    Venue : 
    <select name="VENUEBOX">
      <?php
do {  

?>
      <option value="<?php echo $row_Recordset2['description']?>"<?php if (!(strcmp($row_Recordset2['description'], ucfirst($row_Recordset2['description'])))) {echo "SELECTED";} ?>><?php echo $row_Recordset2['description']?></option>
      <?php
} while ($row_Recordset2 = mysql_fetch_assoc($Recordset2));
  $rows = mysql_num_rows($Recordset2);
  if($rows > 0) {
      mysql_data_seek($Recordset2, 0);
	  $row_Recordset2 = mysql_fetch_assoc($Recordset2);
  }
?>
    </select>
    <br>
    Location : 
    <select name="LOCATIONBOX">
      <?php
do {  

?>
      <option value="<?php echo $row_Recordset3['city']?>"><?php echo $row_Recordset3['city']?></option>
      <?php
} while ($row_Recordset3 = mysql_fetch_assoc($Recordset3));
  $rows = mysql_num_rows($Recordset3);
  if($rows > 0) {
      mysql_data_seek($Recordset3, 0);
	  $row_Recordset3 = mysql_fetch_assoc($Recordset3);
  }
?>
    </select>
    <br>
    Pro/State: 
    <select name="PROSTATEBOX">
      <?php
do {  

?>
      <option value="<?php echo $row_Recordset4['state']?>"><?php echo $row_Recordset4['state']?></option>
      <?php
} while ($row_Recordset4 = mysql_fetch_assoc($Recordset4));
  $rows = mysql_num_rows($Recordset4);
  if($rows > 0) {
      mysql_data_seek($Recordset4, 0);
	  $row_Recordset4 = mysql_fetch_assoc($Recordset4);
  }
?>
    </select>
    <br>
    Media : 
    <SELECT name="MEDIABOX" size=1>
      <OPTION selected> 
      <OPTION>Audio 
      <OPTION>SHN 
      <OPTION>DVD 
      <OPTION>MP3 
      <OPTION>MPG 
      <OPTION>AVI 
      <OPTION>MOV 
    </SELECT>
    <br>
    Discs : 
    <SELECT name="DISCSBOX" size=1>
      <OPTION selected> 
      <OPTION>1 
      <OPTION>2 
      <OPTION>3 
    </SELECT>
    <br>
    <input type=submit name="submit" value="submit" >
</FORM>

<?php
mysql_free_result($Recordset1);

mysql_free_result($Recordset2);

mysql_free_result($Recordset3);

mysql_free_result($Recordset4);
?>

Thanks in advance

[batman]

Well...obviously it is pulling the cities and stuff from a database. Why don't you just edit your database to have only one of each?
That seems like the best idea.

-Blake