PHP - Problem With Select Distinct

Hi guys,

using the code below within an admin panel to create a drop down allowing the user to select the profiles they wish to assign to the record they're creating, problem we have is that once a record is created, if they need to edit it for what ever reason the selected profile option isn't sticking. I've played around with lots of variants of if existing_record to try and get it add selected="selected" into the code but failed at every attempt, any advice gratefully received.

Code: [Select]
<?php

// List only breeder profiles in the database

  echo '<select name="profile" class="textinput noborder">';
  echo '<option value="any">Any</option>';

        $qryGetDistinctProfile = "SELECT * FROM profiles ORDER BY title ASC";
        $resGetDistinctProfile = mysql_query($qryGetDistinctProfile,$connection) or die(mysql_error());

        if(mysql_num_rows($resGetDistinctProfile) > 0){

$id       =  mysql_result($resProfile, 0, "id");
          
         while ($row = mysql_fetch_assoc($resGetDistinctProfile)){
           
          echo  '<option value="'.$row['id'].'" >'.$row['title'].'</option>';

         }

        }

  echo '</select>';
?>

Hi: I have the foll. code. The table "Reports" has multiple records for a given value of CID in the Field CID. I'd like to be able to select only 1 of them so that a list of customers appearing in the Reports table is available for selection in the dropdown alphabetically.  The foll. code does it but it doesnt list the Customers alphabetically. And when I use Join, the query doesnt run. I get a blank list .  The Field CID is common to both tables- Reports and Customers.

Could someone help me with the Join ? Thanks. Swat
Code: [Select]
<?php

$sqlco =  "SELECT DISTINCT CID FROM `Reports` ";

$resultco = mysql_query($sqlco) or die (mysql_error() ) ;

 if ($myrowco = mysql_fetch_array($resultco) )
 {

  do {
$cid = $myrowco["CID"];
$sqlrep = "SELECT * FROM `Customers` WHERE `CID` = '$cid' " ;
$resultrep = mysql_query($sqlrep) or die (mysql_error() ) ;
$myrowrep = mysql_fetch_array($resultrep);
$company = $myrowrep["Company"];

     printf("<option value=%d>
      %s , %s", $myrowco["CID"], $myrowrep["Company"], $myrowco["Mdate"]);

  } while ($myrowco = mysql_fetch_array($resultco));

}

else 
{

echo "No records found." ;
}
 
?></select></a>


What i tried was this :

Code: [Select]

<?php

$sqlco =  "SELECT DISTINCT CID FROM `Reports` r JOIN  `Customers` c  WHERE r.CID = c.CID ORDER BY c.Company asc ";

$resultco = mysql_query($sqlco) or die (mysql_error() );

 if ($myrowco = mysql_fetch_array($resultco) )
 {

  do {

     printf("<option value=%d>
      %s ", $myrowco["CID"], $myrowco["Company"]);

  } while ($myrowco = mysql_fetch_array($resultco));

}

else 
{

echo "No records found." ;
}
 
?>

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=322629.0

Hello folks,   In trying to improve the user experience for my first WebApp I have decided to create two new tables - one a master file to contain a list of all stores, and the second a master file to contain a list of all products that are normally purchased - and I would like to use the values from these two tables as lookup values in dropdown listboxes, to which the user can also add new entries.   As it turns out, I'm stuck on the very first objective i.e. to lookup/pull-in the distinct values from the master tables.   The problem I'm having is that the query seems to return no rows at all...in spite of the fact that there a records in the table, and the exact same query (when run within the MySQL environment) returns all the rows correctly.   Is there something wrong with my code, or how can I debug to see whether or not the query is being executed?   Objective # 2, which is to allow new values to be entered into the dropdown listbox, and then inserted into the respective table is certainly waaay beyond my beginner skills, and I'll most certainly need to some help with that as well..so if I can get some code/directions in that regard it will be most appreciated.   Thank you.

<?php
   $sql = "SELECT DISTINCT store_name FROM store_master ORDER BY store_name ASC";

   $statement = $conn->prepare($sql);
   $statement->execute();

   $count = $statement->rowCount();                                    
   echo $count;

   // fetch ALL results pertaining to the query
   $result = $statement->fetchAll();

   echo "<select name=\"store_name\">";
   echo '<option value="">Please select...</option>';
   foreach($result as $row)
       {
          echo "<option value='" . $row['store_name'] . "'></option>";
       }
       echo "</select>";
?>

I am trying to print the list of a table which I requested with "SELECT DISTINCT" as below

Code: [Select]
$db_connect = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

$sql_get = "SELECT DISTINCT category FROM con";

$sql_run = mysqli_query($db_connect, $sql_get) or mysqli_error($db_connect);

$sql_assoc = mysqli_fetch_assoc($sql_run);

What is now needed to print the list of the table data by this conditions?

I tried the while loop, but I seem to approach wrong, and get endless loops or errors.

hirealimo.com.au/code1.php

this works as i want it:
Quote

SELECT * FROM price INNER JOIN vehicle USING (vehicleID) WHERE vehicle.passengers >= 1 AND price.townID = 1 AND price.eventID = 1



but apparelty selecting * is not a good thing????

but if I do this:
Quote

SELECT priceID, price FROM price INNER JOIN vehicle....etc


it works but i lose the info from the vehicle table.

but how do i make this work:
Quote

SELECT priceID, price, type, description, passengers FROM price INNER JOIN vehicle....etc


so that i am specifiying which colums from which tables to query??

thanks

Hello,

Hoping someone can help...

I am pulling records from a db of cities and state abbrevs.  My db has many duplicate city names but i want to echo out only the distinct ones.  My query is based on a radius around a city and i think this might be what is tripping up the DISTINCT mysql query.  Here is what i have:
Code: [Select]
$teachradius = 50;
$add_under = array(" " => "_");
$query = sprintf("SELECT DISTINCT city, state_abbrev, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM cities HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20",
  mysql_real_escape_string($lat_i),
  mysql_real_escape_string($lng_i),
  mysql_real_escape_string($lat_i),
  mysql_real_escape_string($teachradius));
$result = mysql_query($query);
while ($row = @mysql_fetch_assoc($result)){
echo '<li><a href="/' . strtolower($row['state_abbrev']) . '/' . strtolower(strtr($row['city'],$add_under)) . '.html">' . ucwords($row['city']) . ', ' . $row['state_abbrev'] . ' Dogs</a></li>',"\n";
}

Can you see what I might be doing incorrectly in my query so that i can echo out distinct city names / state abbrevs only?

Thanks in advance...

Hello,

Ive got a mysql database but i used a script to add a bunch of file names but it entered with a few errors and theres quite a few that got entered 4-5 times.

I can get all the names fine using "SELECT DISTINCT name FROM games" but how can i export all the other fields not just the names. I want to select everything but only DISTINCT on the names

I need to add likes_username to this query and use a DISTINCT on it.   It currently counts how many likes a status has but in the table there are some statuses with multiple likes from the same username. 

SELECT s.*, COUNT(l.likes_location_id) AS likeCount
 FROM stream AS s
  LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id )
 GROUP BY s.stream_id
 ORDER BY s.stream_id DESC LIMIT 50

Many thanks,  

field1 / field2
10  /  England
15  /  Italy
20  / France
15  /  France
30  /  USA

When searching for France:
SELECT DISTINCT field2, field1 FROM $tableName would return a distinct value. I want to ensure it returns the highest value in field1.

Something like this:

SELECT DISTINCT field2 (but ensure returns highest field 1 value), field1 FROM $tableName

I am getting a little frustrated, and I need some help. I've working on this all day (I am new... otherwise I'd fly through it). This is a simple problem I just can't seem to put a ' in the right spot or something...

Here's the gist. In file myphotos.php is the following:
<?PHP
include('functions.php');
$link
$title = 'Light Graspers Certification';
$linktent = '<div class="titler">Photo Review</div><div class="contentm">'.$data[index].'</div>';
$excess = '<div class="titlerex">Testimonials</div><div class="contentmex">'.$displayoptions.'</div>';
echo getphoto($title, $linktent, $excess);
?>

Now my goal is under $displayoptions the following will happen.

SELECT DISTINCT name FROM homework WHERE uid='$loggedin' ORDER BY id";
With proper code I should see a distinct list of all the "name" rows. The user clicks on it to be directed to just those rows... etc...

Here is was some old code I tried manipulating to get the same result... but I couldn't seem to get it to work. Of course I only need one column in my new code.
Any help would be a appreciated.

Adam
$columns = 5;
//change the query to get another field from the database
$query = "SELECT DISTINCT city,state FROM church WHERE state='$state' ORDER BY city";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
$rows = ceil($num_rows / $columns);
while($row = mysql_fetch_array($result)) {
$data[] = $row['city'];
//store the other field into an array

}
for($i = 0; $i < $rows; $i++) {
{
echo "<tr valign=bottom>";
echo "<td bgcolor=#2172A1 colspan=10><img src=img/blank.gif width=1 height=1></td>";
echo "</tr>";
}
echo "<TR valign=center>";
for($j = 0; $j < $columns; $j++) {
if(isset($data[$i + ($j * $rows)])) {
echo "<td class=tabval><b><a href='findnew.php?state=".$state."&city=" . $data[$i + ($j * $rows)] . "'>" . $data[$i + ($j * $rows)] . "</a></b></td>";
}
}
echo "</TR>";
}
echo "<tr valign=bottom>";
    echo "<td bgcolor=#2172A1 colspan=10><img src=img/blank.gif width=1 height=8></td>";
    echo "</tr>";

echo "$state represents $num_rows3 \n of our $num_rows2 churches\n that we serve. ";

}

i have 12 distinct albums displaying 8 per page
$number rows echo's out to be 1
so to me says my query is wrong how to count only distinct albums

Code: [Select]
$sql = "SELECT  COUNT(DISTINCT album) FROM belsgallery GROUP BY album";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r['0'];
echo $numrows; // RETURNS 1

$rowsperpage = 8;

$totalpages = ceil($numrows / $rowsperpage);

Ive been scanning over and over this code and I cant work out why my 2nd drop down menu doesnt have unique values.
Please if anyone can give me guidance, you never know I may be able to get rid of my headache!

Code: [Select]
<body>
<p>
<form action="" method="post">
<select name="drop_1" id="drop_1">
<option value="" selected="selected" disabled="disabled">Select a Category</option> 
<?php getTierOne(); ?>
</select>
<span id="wait_1" style="display: none;">
<img alt="Please Wait" src="ajax-loader.gif"/>
</span>
<span id="result_1" style="display: none;"></span>   
</form>
</p>
<p>
<?php if(isset($_POST['submit'])){
$drop = $_POST['drop_1'];
$tier_two = $_POST['Subtype'];
echo "You selected ";
echo $drop." & ".$tier_two;
}
?>
</body>
Code: [Select]
<?php
function getTierOne()
{
$result = mysql_query("SELECT DISTINCT Type FROM business") 
or die(mysql_error());
while($tier = mysql_fetch_array( $result ))   
{
echo '<option value="'.$tier['Type'].'">'.$tier['Type'].'</option>';
}
}
if($_GET['func'] == "drop_1" && isset($_GET['func'])) { 
   drop_1($_GET['drop_var']); 
}
function drop_1($drop_var)
{  
include_once('db.php');
$result = mysql_query("SELECT DISTINCT Subtype FROM business WHERE Type='$drop_var'") 
or die(mysql_error());
echo '<select name="Subtype" id="Subtype">
<option value=" " disabled="disabled" selected="selected">Choose one</option>';
while($drop_2 = mysql_fetch_array( $result )) 
{
echo '<option value="'.$drop_2['Subtype'].'">'.$drop_2['Subtype'].'</option>';
}
echo '</select> ';
echo '<input type="submit" name="submit" value="Submit" />';
}
?>

I am trying to create an autocomplete form for 'city,state zip'   I want to be able to search by either a distinct zip code that will show 'city, state zip' or by distinct city 'city, state'

Can anyone tell me how to fix my script?


$sql = "SELECT DISTINCT zip,city,state FROM `residential` WHERE `zip` LIKE '$input%' OR `city` LIKE '$input%' OR `state` LIKE '$input%' UNION SELECT DISTINCT city,state,zip FROM `residential`  WHERE `zip` LIKE '$input%' OR `city` LIKE '$input%' OR `state` LIKE '$input%'  UNION SELECT DISTINCT state,city,zip FROM `residential` WHERE  `zip` LIKE '$input%' OR `city` LIKE '$input%' OR `state` LIKE '$input%' LIMIT $limit";

$result = mysql_query($sql);

if (!$result || !mysql_num_rows($result))
exit;

include_once "headers.php";

echo "<response>";

while ($row = mysql_fetch_array($result))
{
   $keywords = "$row[city], $row[state] $row[zip]";
   echo "<keywords>". $keywords ."</keywords>";       
}
while ($row = mysql_fetch_array($result))
{
   $keywords = "$row[city], $row[state]";
   echo "<keywords>". $keywords ."</keywords>";       
}
echo "</response>";