Wanting to echo only the most recent of a product....

[suttercain]

Hi everyone and good morning.

 

Right now I have a basic drop down menu which is populated by the MySQL database. Once the user submits the form they are taken to the results page that match their criteria.

 

Example of a results page:

1: HORIZON - ELECTRIC PARTICULATE FILTER

Engine Family Name: RND254C6DAAA

Executive Order Number: DE-05-010

Level: 3

Manufacturer: Cleaire

 

2: DPM DPF SYSTEM W/ SERIES 6300 CATALYST

Engine Family Name: RND254C6DAAA

Executive Order Number: DE-04-005

Level: 3

Manufacturer: Donaldson

 

3: DPM DPF SYSTEM W/ SERIES 6300 CATALYST

Engine Family Name: RND254C6DAAA

Executive Order Number: DE-04-005-01

Level: 3

Manufacturer: Donaldson

 

4: DPM DPF SYSTEM W/ SERIES 6300 CATALYST

Engine Family Name: RND254C6DAAA

Executive Order Number: DE-04-005-02

Level: 3

Manufacturer: Donaldson

 

My Question:

In the results they are given the Executive Order Number. In the example I listed above, results 2-4 are nearly identical but result 4, DE-04-005-02, is the most recent Executive Order. Is it possible to only display the most recent (the highest number)?

 

So if I have the following numbers in the database:

DE-122

DE-122-01

DE-122-02

DE-122-03

 

Can I have it so it only displays the DE-122-03 record?

 

 

Thank you, in advance, for your help and suggestions.

[pocobueno1388]

You can create a unique ID for the table in the database and make it auto-increment, then you could order the results by that.

 

Then again, I don't know exactly how your table setup works.

[per1os]

Or, now lets think about this, you have a dateadded field! That way you enter in the date it was added and run a query by date and viola, you have the exact time the newest product was added!

 

CraZy!

[suttercain]

Hi Frost,

 

We thought about trying to run it that way but this is our dilemma. Let's say DE-200-01 was added on 02/01/2007 and DE-300-01 was added on 03/01/2007. Will this not cause it to only echo DE-300-01? I would need it to still echo DE-200-01 too. With that said, if I added DE-200-02 on 04/01/2007 I would now only need DE-300-01 and DE-200-02 to echo.

 

With the E0 number it can be difficult to explain so I hope I have done ok.

 

Example:

 

These are the same:

DE-200

DE-200-01

DE-200-02

 

This is NOT the same as DE-200

DE-300

DE-300-01

DE-300-02

DE-300-03

 

But if I only echo the latest date added I will only get one product echoed, when in fact I would need both, just the most recent of that product... so based on the info above it would be:

 

DE-200-02 AND DE-300-03

[pocobueno1388]

Frost - Yeah, that would work the same way as an auto-increment field...so either way would do the trick. Although it would be more beneficial to use a date, because that just gives you that much more information about the product and all.

 

I wasn't sure if you had it setup to add the newest ones first, or if you just randomly inserted them into the DB. That is why I didn't know if the auto-increment would work. If it is that case, the date method won't work either.

 

Yeah, you just posted and it seems there is a problem with this.

[grimmier]

what if you stored your EO's as 2 pieces?

 

the first would be the order number

DE-200

DE-300 etc.

 

the 2nd would be the revision number.

-01

-02

etc.

 

then just search for the EO then display the highest Rev.

 

just an idea.

[suttercain]

Hi Grimmier,

 

I think that may be the only choice. We discussed this in a meeting the other day, we were just hesitant to do so, because there are over 80,000 records.

 

Good news is, I won't have to do it... the data entry guys will

 

Thanks for the tip.

[grimmier]

you could always use a script to do that update =)

 

just dissect the original EO into segments using the - as the seperator. then update the record setting both the EO and the Rev, with the  parts.

 

example:

 

//First run an SQL query on the DB, Then I would put something like this in a loop parsing the results row by row. 
$eo_parts = explode("-", $eo_number_original);

//creates an array of the EO and Rev numbers.
$old_eo[] = $eo_number_original; 
//setting up an array for the original EO will make the Update loops easier and the indices's will stay the same for all the parts. 

$new_eo[] = $eo_parts[0].'-'.$eo_parts[1].'-';
$rev[] = $eo_parts[2];

 

then you can use the arrays to cycle through and do the updates,

foreach ($old_eo as $value){
   if ($value != "") {
      $sql_update = 'UPDATE `DB_Name` SET `EO` = "'.$new_eo[$value].'", `Rev` = "' .$rev[$value]. '" WHERE `EO` = "' .$old_eo[$value]. '"';
      mysql_query($sql_update,$con) or die('Error: ' . mysql_error()); 
     //or whatever your connection is set up as if done earlier.
    }
}

 

mind you this is untested, just throwing out some ideas that come to mind.

[sasa]

try

SELECT max(EO) FROM `xxx` WHERE 1 GROUP BY LEFT(EO,6)

[suttercain]

Hi Sasa,

 

This is how I have my mysql query as of now:

$sql = "SELECT * FROM " .$year_arr[$i]. "data WHERE ENGFAM = '$efn' ORDER BY Verified DESC";

 

How would I incorporate the code you gave into that statement?

 

Thanks.

[sasa]

$sql = "SELECT * FROM " .$year_arr[$i]. "data WHERE ENGFAM = '$efn' AND EO IN (SELECT max(EO) FROM " .$year_arr[$i]. "data WHERE 1 GROUP BY LEFT(EO,6)) ORDER BY Verified DESC";

[suttercain]

Hi Sasa,

 

Thanks for the code:

 

I Just tried the code in the above and I got the following:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\ARB\jonnel_backup\verdevquery.php on line 280

 

 

Here is the code from line 280

<?php
$sql = "SELECT * FROM " .$year_arr[$i]. "data WHERE ENGFAM = '$efn' AND EO IN (SELECT max(EO) FROM " .$year_arr[$i]. "data WHERE 1 GROUP BY LEFT(EO,6)) ORDER BY Verified DESC";
           
              //Connect to server
              include (connect.php);
           
              //count the number of records
              $result = mysql_query($sql);
           
              $rows = mysql_num_rows($result); 
?>

Thanks again for the help.

[sasa]

is EO name of your field where you store the number (DE-200, DE-200-01) ?

[suttercain]

Yes it is.

[sasa]

try

$sql = "SELECT * FROM " .$year_arr[$i]. "data WHERE ENGFAM = '$efn' AND EO IN (SELECT max(EO) FROM " .$year_arr[$i]. "data WHERE ENGFAM = '$efn' GROUP BY LEFT(EO,6)) ORDER BY Verified DESC";

[suttercain]

I ran the code above and got the following error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\ARB\jonnel_backup\verdevquery.php on line 280

Sorry, no records were found matching your search criteria.