Jump to content


Photo

MySQL Help - the maximum from 2 columns


  • Please log in to reply
2 replies to this topic

#1 Seamless

Seamless
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 21 September 2006 - 07:21 AM

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.

<? 
$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"; 
?> 

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.


#2 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 21 September 2006 - 07:27 AM

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

#3 Seamless

Seamless
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 21 September 2006 - 09:25 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users