• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

Selecting no duplicate records from the Data Base

OK so I have a data base with the following columns: VIN_No|New_Used|Stock_No|Year|Make|Model|Body_Style|Doors|Trim|Ext_Color|Int_Color|Int_Surface|Engine|Fuel|Drivetrain|Transmission|Mileage|Internet_Price|Certified|Options|Description|Date_In_Stock|Alt_Price|Photo_URL_1|Photo_URL_2|Photo_URL_3|Photo_URL_4|Photo_URL_5|Photo_URL_6|Photo_URL_7|Photo_URL_8|Photo_URL_9|Photo_URL_10|Photo_URL_11

I need to select only the vehicles of a particular model, lets say CR-V for now, and then only select the oldest in stock, in a particular color, with a particular interior color, with a particular trim level. Or.... another way to explain it... If I have three LX CRVs with White exterior and Gray interior, I only want to select one, the oldest one determined by Date in stock. Then the next record should show the Green CRV LX but it to should only show one if there are more than one with the same interior and exterior color and it should show the oldest in stock. Then the record list needs to move to the next trim level EX and do the same thing. So, I need to display all of the CRV inventory but leave out any duplicate vehicles (with the same trim level, interior color, and exterior color) and only show the oldest in stock.

Help would be greatly appreciated.

      
<?php require_once('../Connections/dbcon.php'); ?>
 
<?php
 
 $query_CRV = "SELECT * " .
							"FROM ebizcardata " . 
							"WHERE Modell = 'CR-V' AND New_Used = 'New'" .  
							"ORDER BY STR_TO_DATE(Date_In_Stock, '%m/%d/%Y') ASC";		
 
$query_limit_CRV = sprintf("%s LIMIT %d, %d", $query_CRV, $startRow_CRV, $maxRows_CRV);
$CRV = mysql_query($query_limit_CRV, $BKHmain) or die(mysql_error());
$row_CRV = mysql_fetch_assoc($CRV);
 
if (isset($_GET['totalRows_CRV'])) {
  $totalRows_CRV = $_GET['totalRows_CRV'];
} else {
  $all_CRV = mysql_query($query_CRV);
  $totalRows_CRV = mysql_num_rows($all_CRV);
}
$totalPages_CRV = ceil($totalRows_CRV/$maxRows_CRV)-1;
 
$queryString_CRV = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_CRV") == false && 
        stristr($param, "totalRows_CRV") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_CRV = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_CRV = sprintf("&totalRows_CRV=%d%s", $totalRows_CRV, $queryString_CRV);
?>
 
 
 
<!--Code in page-->
 
 <!-- Start CRV Inventory -->
              <?php if ($pageNum_CRV > 0) { // Show if not first page ?>
                <a href="<?php printf("%s?pageNum_CRV=%d%s", $currentPage, max(0, $pageNum_CRV - 1), $queryString_CRV); ?>">Previous</a>
                <?php } // Show if not first page ?>
              <?php if ($pageNum_CRV < $totalPages_CRV) { // Show if not last page ?>
                <a href="<?php printf("%s?pageNum_CRV=%d%s",  $currentPage, min($totalPages_CRV, $pageNum_CRV + 1), $queryString_CRV); ?>">Next</a>
                <?php } // Show if not last page ?>
              <?php echo ($startRow_CRV + 1) ?><?php echo min($startRow_CRV + $maxRows_CRV, $totalRows_CRV) ?><?php echo $totalRows_CRV ?>
<?php do { ?>
              <table cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" style="width:495px">
                  <tr>
                      <td rowspan="2" valign="top" style="padding-top:10px; padding-left:10px" width="25%">
                          <a href="vehicle_details.php?Year=<?php echo $row_CRV['Year']; ?>&Model=<?php echo $row_CRV['Modell']; ?>&Stock_No=<?php echo $row_CRV['Stock_No']; ?>&Trim=<?php echo $row_CRV['Trim']; ?>&last_page=<?php print "crv.php"; ?>"><img src="<?php if ($row_CRV['Photo_URLs'] == "x") 
																				{
																				print "images/no_photo_1.jpg";
																				}
																			else 
																				{
																				echo $row_CRV['Photo_URLs']; 
																				}; ?>" width="120" height="97" border="0"></a>                                                                </td>
                    <td valign="top" style="padding-top:10px; padding-left:10px; text-align:left" width="50%">
                      <h4>
                        <?php echo $row_CRV['Year']; 
                                                                     print "&nbsp;";
                                                                     echo $row_CRV['Make']; 
                                                                     print "&nbsp;";
                                                                     echo $row_CRV['Modell'];
                                                                     print "&nbsp;";
                                                                     echo $row_CRV['Trim']; ?>                                                                </h4>                      <font style="text-align:left; color:#333333; FONT-SIZE: 8pt">Stock # :: <?php echo $row_CRV['Stock_No']; ?></font>
                      <br>                      <br>                                                                </td>
                    <td valign="top" style="padding-top:10px; padding-right:10px" width="20%">
                      <font style="text-align:left; color:#333333; FONT-SIZE: 10pt"><?php			
																		if ($row_CRV['Alt_Price'] == "" and $row_CRV['Internet_Price'] > "0") 
																		{
																				
																							print "<strong>List Price</strong> $";
																								$number = $row_CRV['Internet_Price'];
																								$add_com_format = number_format($number);
																								
																								echo $add_com_format;
																								
																		}
																		else if ($row_CRV['Alt_Price'] > "0" and $row_CRV['Internet_Price'] == "0") 
																		{
																				
																							print "<strong>List Price</strong> $";
																								$number = $row_CRV['Alt_Price'];
																								$add_com_format = number_format($number);
																								
																								echo $add_com_format;
																								
 
																		}
																		else if ($row_CRV['Alt_Price'] == $row_CRV['Internet_Price']) 
																		{
																							print "<strong>List Price</strong> $";
																								$number = $row_CRV['Alt_Price'];
																								$add_com_format = number_format($number);
																								
																								echo $add_com_format;
																				
																		}
																		else if ($row_CRV['Alt_Price'] > "0" and $row_CRV['Internet_Price'] > "0") 
																		{
																							print "<strong>List Price</strong><strong> <strike>$";
																							$number = $row_CRV['Alt_Price'];
																							$add_com_format = number_format($number);
																							
																							echo $add_com_format;
																							
																							
																							print "</strike></strong><br><strong>Sale Price</strong> $";
																									$number = $row_CRV['Internet_Price'];
																									$add_com_format = number_format($number);
																									
																									echo $add_com_format;
																				
																			}
																			else
																			{
																					print "<strong>Please Call for Price</strong><strong>(888) 762.1177</strong>";
								
																				}
																				
																		?></font>                                                                </td>
                  </tr>
                  <tr>
                    <td colspan="2" style="padding-left:10px; text-align:left; padding-bottom:10px; padding-right:10px" width="75%">
                      <font style="text-align:right; color:#333333; FONT-SIZE: 10pt">
                      <strong>Features &amp; Options ::</strong> <?php echo $row_CRV['Options']; ?><br>
                      <strong>Exterior Color ::</strong> <?php echo $row_CRV['Ext_Color']; ?><br>
                      <strong>Interior Color ::</strong> <?php echo $row_CRV['Int_Color']; ?><br></font>
                      <a href="vehicle_details.php?Year=<?php echo $row_CRV['Year']; ?>&Model=<?php echo $row_CRV['Modell']; ?>&Stock_No=<?php echo $row_CRV['Stock_No']; ?>&Trim=<?php echo $row_CRV['Trim']; ?>&last_page=<?php print "crv.php"; ?>" class="nav_blue" style="FONT-SIZE: 10pt">View Additional Photos &amp; Details</a>                                                                </td>
                </tr>
              </table>
                <?php } while ($row_CRV = mysql_fetch_assoc($CRV)); ?><!-- End CRV Inventory -->

Open in new window

0
Cutthroat_Trout
Asked:
Cutthroat_Trout
  • 6
  • 4
  • 3
  • +1
1 Solution
 
Haris DulicCommented:
did you try using the distinct or group by commands???
0
 
NoiSCommented:
You need to use query with subqueries and group BY clause

Something LIKE

SELECT * FROM tbl_cars
WHERE ids in (
   SELECT MIN(id) FROM tbl_cars GROUP BY color,interior, level ORDER BY date )
0
 
Cutthroat_TroutAuthor Commented:
No but I think I am going to need this to be some sort of superlitive subquery. This is all mostly above my head. I understand group by but distinct????...... Got more advice or have an example I can try...

Sorry... but please help.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Cutthroat_TroutAuthor Commented:
NoiS -

That looks a little closer but....????..... what???.... How would this exclude the duplicate entries? Can you get it a little closer using my DB columns? Becaues I still do not understand what you are trying to tell me.

Sorry... This advanced SQL stuff is new to me. But Thanks.
0
 
Ray PaseurCommented:
SELECT DISTINCT has very bad performance on large data sets, but it's a useful concept.

Read up on it here:
http://dev.mysql.com/doc/refman/5.1/en/select.html
0
 
Ray PaseurCommented:
Also, as I read your description of the output you want, you might want to consider creating a temporary table as a down-select before you do something like SELECT DISTINCT.

I think you can do a lot of this with GROUP BY and ORDER BY clauses.  Same man page as above!

Good luck, ~Ray
0
 
Cutthroat_TroutAuthor Commented:
Ok I will try to understand what is on that page.

But I still don't think I am good enough to pull this one off, the page has a lot of content and I am very bogged down with other tasks right now to learn all that.

Do you know of any locations where I can just pay someone to help me get this working?
0
 
Ray PaseurCommented:
Google scriptlancers or freelance coders - those have plenty of folks eager for work.  Good luck! ~Ray
0
 
NoiSCommented:
If I had your DDL I can gave you the query.

First you need to get only the ids from your table that match your criteria.
Second, based on the first resultset you can retrieve de correct data.

put your "create table " code here


0
 
Cutthroat_TroutAuthor Commented:
Ok. Thanks for sticking this one out with me NoiS. I think I attached the file you need.

Thanks again, your help is greatly apprecated.
DB-export
0
 
NoiSCommented:
Man, sorry but, your DDL is to bad.
You must learn a little how to model a Database.
To solve your trouble is necessary to change your table, create others and change some values.

First You need to separate all entities. (Photos, Address, Companies, etc). Identify them and create one table for each entity. Is good to use autoincrement key fields.
Second Use InnoDB instead MyISAM.

Is almost impossible to do that query with your current DDL.
0
 
Cutthroat_TroutAuthor Commented:
Cool, well thanks for taking a look.
Hopefully I can sneak into the almost part on this one.

Thanks again.
0
 
Ray PaseurCommented:
@Cutthroat_Trout: Actually several answers to your question were given, and it's not in the spirit of EE to close a question without awarding points to those who have tried, in good faith, to help you.  It is not possible to give every answer to every tiny detail - we have to assume SOME level of skill since you identified yourself as "intermediate" on this topic.  And you have some obligation to use your own skills and knowledge to adapt the proposed solutions to a perfect fit for your particular case.

You asked questions about data base queries.  We answered those.

You asked where you could hire help.  We answered that.

So please explain why you want to close the question.  Thanks, ~Ray
0
 
Cutthroat_TroutAuthor Commented:
Well the points were posted for the original question which was never answered. I would divide the points if possible but to accept any of the above answers as a full answer would not be fare. But in the spirt of EE I would give most of the point to NoiS as that users did go above and beyond. But if I can't split points????    
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now