Seamless Posted September 21, 2006 Share Posted September 21, 2006 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&"; }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&"; } //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. Quote Link to comment Share on other sites More sharing options...
akitchin Posted September 21, 2006 Share Posted September 21, 2006 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 Quote Link to comment Share on other sites More sharing options...
Seamless Posted September 21, 2006 Author Share Posted September 21, 2006 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.