查找两个varchar字段的最大数值

I'm trying to find the max value contained in two separate fields of my table.

The code I'm using in my model is:

$query = $this->db->query("select max($field1) as max_id_1 from default_table1");
$row1 = $query->row_array();
$query = $this->db->query("select max($field2) as max_id_2 from default_table1");
$row2 = $query->row_array();
return max($row1['max_id_1'], $row2['max_id_2']);

I'm a complete novice where PHP and CodeIgniter is concerned - as I'm sure my code demonstrates :)

It is working insofar as it's returning values, but not the maximum values I have in the fields. For instance I know there is a 4000 value but the highest returned is 750.

I'm wondering if this is because the fields are of type VARCHAR because although they predominantly contain numbers there are some that contain characters (- or &) or the word 'to' so I couldn't use the INT type. Because of using VARCHAR is it failing to see that 4000 is larger than 750?

If so is there a way to cast the field contents as integer before checking for the max value, and will this be affected by the non-integer values in the fields?

All offers of help and advice is gratefully received.

Tony.

This can be done using SQL using MySQL's implicit type conversion:

select max(case when (field1+0)>(field2+0) then field1+0 else field2+0 end)
from default_table1

Using +0 would convert varchar to number and also ignore any characters that follow after the number. If you still need the original content, you can write the query like this:

select case when (field1+0)>(field2+0) then field1 else field2 end
from default_table1
order by case when (field1+0)>(field2+0) then field1+0 else field2+0 end desc
limit 1

Oh, oops, I've forgot the mysql part You should do the same thing, cast to integer:

select max(cast($field1 to unsigned)) as max_id_1 from default_table1

It depends of your data, but you may try something like that

return max((int)$row1['max_id_1'], (int)$row2['max_id_2']);

Have a look to the PHP doc on string to int conversion

What you are really asking for is a way for codeigniter to convert the strings to numbers, and the find the max values of those. I don't know of any way to do this in codeigniter.

If you really want this, you have two options:

loop through all the rows in the table, and use php to parse out the number, while looking for the maximum number
Add a number column to the db, and do the string-to-number parsing in this new column whenever the values are inserted.

The first option is incredibly inefficient, and the second is probably your best bet.