[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Selecting no duplicate records from the Data Base

Posted on 2008-11-10
14
Medium Priority
?
398 Views
Last Modified: 2013-12-13
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
Comment
Question by:Cutthroat_Trout
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 22925935
did you try using the distinct or group by commands???
0
 
LVL 11

Expert Comment

by:NoiS
ID: 22926126
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
 

Author Comment

by:Cutthroat_Trout
ID: 22926163
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Cutthroat_Trout
ID: 22926263
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22926481
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22926499
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
 

Author Comment

by:Cutthroat_Trout
ID: 22926627
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22926735
Google scriptlancers or freelance coders - those have plenty of folks eager for work.  Good luck! ~Ray
0
 
LVL 11

Expert Comment

by:NoiS
ID: 22927313
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
 

Author Comment

by:Cutthroat_Trout
ID: 22927520
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
 
LVL 11

Accepted Solution

by:
NoiS earned 1500 total points
ID: 22929873
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
 

Author Comment

by:Cutthroat_Trout
ID: 22936591
Cool, well thanks for taking a look.
Hopefully I can sneak into the almost part on this one.

Thanks again.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 23021633
@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
 

Author Comment

by:Cutthroat_Trout
ID: 23021675
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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 a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month19 days, 5 hours left to enroll

834 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