[SOLVED] Listing just one instance of a record DISTINCT not working right

[CodeMama]

Why doesn't this do what I want, I need to pull just the Distinct names and addresses

 

so it should only list ONE restaurant name not the name for how ever many times they have been inspected...

    // Grab all restaurants in alphabetical order
                                      $pastDays = strtotime("-30 days");
                                      
                                      
                                    

                                                                       $sql = "SELECT DISTINCT restaurants.ID, name, address FROM restaurants, inspections WHERE restaurants.name != '' AND inpections.inDate <> '$pastDays' ORDER BY name";

                                                                       $result = mysql_query($sql) or die(mysql_error());

[corbin]

Instead of a DISTINCT clause you may be better off with GROUP BY in this situation.

 

 

 

SELECT DISTINCT restaurants.ID, name, address FROM restaurants, inspections WHERE restaurants.name != '' AND inpections.inDate <> $pastDays GROUP BY restraurants.ID ORDER BY name

 

 

 

(Note that I removed the quotes around pastDays....  As it's numeric, it does not need quotes.

[gizmola]

While Corbin's answer will work, you can remedy your query by fixing the underlying problem.

 

Your underlying problem is that you did not specify an inner join criteria to your query between restaurants and inspections.  This results in a "Cartesian Product", or in other terms, you get a result set with every combination possible of restaurants and inspections.  This is why, you unexpectedly get what appears to be multiple rows for the same restaurant, because you inadvertantly created rows in the cartesian product, for every ID and inspection.  If you added to your WHERE clause "inspections.restaurants_ID = restuarants.ID" or whatever the joining key is between the two (I don't have your schema so I'm just guessing there), I think your original DISTINCT will actually work.

[CodeMama]

Well I have it listing only one instance of a restaurant now..but my date part isn't working, I am trying to show the latest inspections in the last 30 days... according to the inDate (inspection date) it's just listing the name of the restaurant and the first ever inspection.. here is more of the code..

 

								    								    								    								    
							    // Check if page is set to show all
							    if(isset($_GET['show']) && $_GET['show'] == 'all')
							    {
							      unset($_SESSION['results']);
							      unset($_SESSION['searchname']);
							      unset($_SESSION['address']);
							      
							     
							    }
							    
							    // Check if there was an empty search sent
							    if(isset($_SESSION['noVarsSent']))
							    {
							      echo "<p><b>No values were submitted for the search.</b></p>";
							      // Unset it so a reload of page doesn't redisplay the error
							      unset($_SESSION['noVarsSent']);
							      // unset($_SESSION['results']);
							    }
							    
							    
							    
unset($_SESSION['fullRestaurantList']);

							    // Check if full list of restaurants has been created and stored yet
							    // Store full results in $_SESSION to limit database hits
							    if(!isset($_SESSION['fullRestaurantList']))
							    {
							      // List not grabbed yet, so run query and store in $_SESSION
							     
							     
							     //check for range
							     
							    if (!(isset($rangenum)))
							      {
							      $rangenum = 1;
							      }
							     
							    // Grab all restaurants in alphabetical order
							    
							    $pastDays = strtotime("-30 days");

							    $sql = "SELECT DISTINCT restaurants.ID, name, address, inDate FROM restaurants, inspections WHERE restaurants.name != '' AND inspections.inDate <> $pastDays GROUP BY restaurants.ID ORDER BY name";

							     $result = mysql_query($sql) or die(mysql_error());

							   
							    
							    //trying to grab it by ranges from the db?
							         $rows = mysql_num_rows($result);




							      $page_rows = 100;
							      $last_row = ceil($rows/$page_rows);
							      
							      if ($rangenum < 1)
							    {
							      $rangenum = 1;
							      }
							      elseif ($rangenum > $last_row)
							      {
							      $rangenum = $last_row;
							      }
							      
							      //This sets the range to display in our query
							      $max = 'limit ' .($rangenum - 1) * $page_rows .',' .$page_rows; 
							      
							      

							      
							      // Process all results into $_SESSION array
							      
							      $position = 1;
							      
							      while ($row = mysql_fetch_array($result))
							      {
								  $_SESSION['fullRestaurantList'][$position] = $row;
								  $position++;
							      }
							      
							      $_SESSION['totalNumberOfRestaurants'] = $position;
							      

							    }
							  ?>

[gizmola]

Yeah I didn't see that before, but it looks like your where clause in regards to inspections.inDate is wrong. The other thing here, is that if what you really want is to use the GROUP BY, then there's no need to use the DISTINCT and vice versa. Pick the approach that makes sense for you.  If you want the indate, then use the GROUP BY only.

 

What you're attempting now, if I understand it, is that you only want to see restaurants where they have not been inspected in the last 30 days?  The approach you're attempting to use is not going to work without some adjustments.

 

Also, could you indicate the mysql datatype of the inDate column?

 

 

[corbin]

While Corbin's answer will work, you can remedy your query by fixing the underlying problem.

 

Your underlying problem is that you did not specify an inner join criteria to your query between restaurants and inspections.  This results in a "Cartesian Product", or in other terms, you get a result set with every combination possible of restaurants and inspections.  This is why, you unexpectedly get what appears to be multiple rows for the same restaurant, because you inadvertantly created rows in the cartesian product, for every ID and inspection.  If you added to your WHERE clause "inspections.restaurants_ID = restuarants.ID" or whatever the joining key is between the two (I don't have your schema so I'm just guessing there), I think your original DISTINCT will actually work.

 

 

 

Oh, I didn't even look at the JOIN to see no conditions....

 

 

 

*Goes back to avoiding the MySQL board*

[CodeMama]

This is what is working all except my date part:

    $sql = "SELECT DISTINCT restaurants.ID, name, address, inDate FROM restaurants, inspections WHERE restaurants.name != '' AND inspections.inDate <= $pastDays GROUP BY restaurants.ID ORDER BY 'name' ";

[gizmola]

Is this really solved if it doesn't work?  If you could answer the questions I posed, I might be able to give you better advice.  Also, it doesn't seem like you're reading what I wrote, as you still are mixing the group by and DISTINCT which is not needed or helpful.  Once you GROUP BY restaurants.ID, you get 1 row per restaurant.  That is guaranteed, however the inDate you get is not predictable.  Usually when you GROUP BY, you will do this to utilize a summary operator, like MAX, SUM or COUNT.