using MAX() with varchar

[cmattoon]

I'm trying to select the highest/most recent trip number from a database. Trip numbers are in the format YY-#### (10-1000). When drivers go to enter trip information, the trip ID should automatically populate with the next available trip number (10-1001). When I use this code, it gives a result "-1". The database is up to 1300-1500 trips... any ideas?

 

$sql = mysql_query("SELECT max(trip_id) FROM wcv2"); 	// should pull highest number only	
while($row = mysql_fetch_assoc($sql)){
	$trip = $row['trip_id'];
	$trip = explode("-",$trip);
	$number = $trip[1];												// should be most recent trip #
	$newid = $trip[1] + 1;											// New trip # (1234 only)
	$newid = $trip[0]."-".$newid;									// 10-1234
}

[Mchl]

Use alias in your query, because right now you don't have a column called trip_id in resultset.

$sql = mysql_query("SELECT max(trip_id) AS trip_id FROM wcv2"); 

 

you might also want to try alternative query:

$sql = mysql_query("SELECT  trip_id FROM wcv2 ORDER BY trip_id DESC LIMIT 1"); 

 

might be faster...

[cmattoon]

Thanks! I was using ORDER BY, DESC at first, but didn't have the LIMIT 1 in..

 

 

[Mchl]

The difference is that SELECT MAX() will always return one row, even if the table is empty (you will get a NULL value).

On the other habd SELECT ... ORDER BY ... DESC LIMIT 1 will return 0 rows if table is empty. In your case it shouldn't make a difference, because your table is already populated, but that's something to keep in mind.