Jump to content

Archived

This topic is now archived and is closed to further replies.

Seamless

MySQL Help - the maximum from 2 columns

Recommended Posts

I have been developing an engineering drawing register on a companies intranet. This enables a user to upload a drawing and enter the drawing details. The drawing itself (PDF) is uploaded to the server and the data entered into the MySQL database.
I have written some php code to do a search on the drawing database e.g. customer search.

The trouble is some customers when issuing a new drawing to the company don't update the revision number only the date of the drawing.

Here's my problem:
I need a MySQL statement to select the drawings with the highest revsion AND the most recent date.

Here's what i have so far.

[code]
<?
$search_vals = array("drg_desc", "drg_num", "cust_id");
while (list ($key, $value) = each ($HTTP_POST_VARS)) {
if (in_array ($key, $search_vals)) {
if (is_numeric($value) and!empty($value)) {
$addtosql .= " $key LIKE '%$value%' AND";
$get_val .= "$key=$value&amp;";
}elseif(!is_numeric($value) and!empty($value)){
$newval = urlencode($value);
$topost .= "&$key=$newval"; //used later in reposting
$value = addslashes($value);
$addtosql .= " $key LIKE '%$value%' AND";
$get_val .= "$key=$value&amp;";
} //fi
} //fi
} //wend

$addtosql = substr("$addtosql", 0, -3);
$sql = "SELECT cust_id, drg_num, drg_desc, max(rev) as rev, drg_date, date, filename FROM drawings WHERE $addtosql GROUP BY drg_num";
?>
[/code]

There are 3 search fields - drg_desc (Drawing Description), drg_num (Drawing Number) and cust_id (Customer).

The above code will select all drawings with the highest revision according to what is entered into the search fields BUT it if there are 2 drawings with the same details but one has a more recent date, it doesn't neccessarily select the latest drawing.

So to simplify how do i add a max(drg_date) to the SQL statement to make the query select drawings with the highest revision and the most recent date.

Share this post


Link to post
Share on other sites
may seem simple, but could you simply add an ORDER BY clause using the drg_date as the sort factor?  using DESC will target the most recent drawing.

alternatively, you can probably add a MAX() to the SELECT clause using the UNIX timestamp version of the date:

MAX(UNIX_TIMESTAMP(drg_date)) AS most_recent

Share this post


Link to post
Share on other sites
your absolutely right, i think i was reading into it too much.

A new revision would also have a new date (later date).

Thanks for your help.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.