1. 在修改bug时遇到一个问题，想优化查询语句时发现有字段是空的，结果在比较时出错，或是比较了之后查询不到结果，语句如下时，
select * from p_info pi left join job j on j.id = 710
where pi.requestid = 0
and (ifnull(pi.standard_20_ft_container, 0) <= ifnull(j.standard_20_ft_container, 0));
查询到的结果并不是想要的，因为结果都是空的，就是没有查询到数据，但事实上应该要得到数据。因为pi.standard_20_ft_container, j.standard_20_ft_container都是空值，在插入数据库时没有值，但是因为是修改bug，所以数据库是不能改的，只能修改sql语句，在网上搜到说用减法就可以，因此写了下面的语句：
select * from p_info pi left join job j on j.id = 710
where pi.requestid = 0
and ((ifnull(pi.standard_20_ft_container, 0) - ifnull(j.standard_20_ft_container, 0)) <= 0)
。
在这里使用时，不知道为什么，遇到一个问题，昨天晚上写这个sql时还能查到数据，但是到今天就不行了。好奇怪啊。
原先的sql如下:
select * from p_info pi left join job j on j.id = 710
where pi.requestid = 0
and to_days(j.home_day1) <= to_days(pi.date1) and to_days(pi.date2) <= to_days(j.home_day2);
这里的j.home_day1的格式是‘2014-5-10’，而pi.date1的格式是‘10/5/2014’,昨天晚上用上述sql语句时明明可以的，也已经查到数据了，今天再调试时就不行了，超级郁闷，看看数据库后发现，
select j.home_day1, pi.date1,pi.p_preferred_time, pi.date2, pi.d_preferred_time, j.home_day2 from p_info pi left join job j on j.id = 710
where pi.requestid = 0;
+------------+------------+------------------+------------+------------------+------------+
| home_day1 | date1 | p_preferred_time | date2 | d_preferred_time | home_day2 |
+------------+------------+------------------+------------+------------------+------------+
| 2014-05-10 | 08/05/2014 | 08/05/2014 | 08/05/2014 | 08/05/2014 | 2014-05-10 |
| 2014-05-10 | 10/05/2014 | 10/05/2014 | 10/05/2014 | 10/05/2014 | 2014-05-10 |
| 2014-05-10 | 10/05/2014 | 10/05/2014 | 10/05/2014 | 10/05/2014 | 2014-05-10 |
| 2014-05-10 | 10/05/2014 | 10/05/2014 | 10/05/2014 | 10/05/2014 | 2014-05-10 |
| 2014-05-10 | 10/05/2014 | 10/05/2014 | 10/05/2014 | 10/05/2014 | 2014-05-10 |
| 2014-05-10 | 10/05/2014 | 10/05/2014 | 10/05/2014 | 10/05/2014 | 2014-05-10 |
。
发现用to_days之后的结果是这样的：
mysql> select to_days(j.home_day1) , to_days(pi.date1) ,
to_days(pi.date2) , to_days(j.home_day2) from p_info pi left join job j on j.id = 710;
+----------------------+-------------------+-------------------+----------------------+
| to_days(j.home_day1) | to_days(pi.date1) | to_days(pi.date2) | to_days(j.home_day2) |
+----------------------+-------------------+-------------------+----------------------+
| 735728 | NULL | NULL | 735728 |
| 735728 | NULL | NULL | 735728 |
| 735728 | NULL | NULL | 735728 |
所以估计是pi.date1的格式不对，在网上找到了转换字符串的函数str_to_date(pi.date2, '%d/%m/%Y')，这样就能把字符串转化成date，格式：‘2014-5-10’。
sql如下：
select to_days(j.home_day1) , to_days(str_to_date(pi.date1, '%d/%m/%Y')) ,
to_days(str_to_date(pi.date2, '%d/%m/%Y')) , to_days(j.home_day2) from p_info pi left join job j on j.id = 723
where pi.requestid = 0;
这样就能比较date的大小了：
select * from p_info pi left join job j on j.id = 710
where pi.requestid = 0
and to_days(j.home_day1) <= to_days(str_to_date(pi.date1, '%d/%m/%Y'))
and to_days(str_to_date(pi.date2, '%d/%m/%Y')) <= to_days(j.home_day2);
这里奇怪的是昨天都不用这样转就能比较，今天必须这样，比如查询的数据为空。
我的环境是php 的codeigniter+wampserver.
在调用时还遇到一个问题，昨天的sql语句很长时依旧能调用，结果因为今天修改了日期的比较，结果貌似因为语句太长，sql语句被自动去掉了长度，发现查询不到结果了。代码如下：
$sql1 = "select * from p_info pi left join job j on j.id = " . $id .
"where pi.requestid = 0
and ((ifnull(pi.standard_20_ft_container, 0) - ifnull(j.standard_20_ft_container, 0)) <= 0)
and ((ifnull(pi.standard_40_ft_container, 0) - ifnull(j.standard_40_ft_container, 0)) <= 0)
and ((ifnull(pi.high_cube_40_ft_container, 0) - ifnull(j.high_cube_40_ft_container, 0)) <= 0)
and ((ifnull(pi.capacity_head_of_cattle, 0) - ifnull(j.capacity_head_of_cattle, 0)) <= 0)
and ((ifnull(pi.capacity_head_of_sheep, 0) - ifnull(j.capacity_head_of_sheep, 0)) <= 0)
and ((ifnull(pi.capacity_other_livestock, 0) - ifnull(j.capacity_other_livestock, 0)) <= 0)
and ((ifnull(pi.capacity_number_of_horses, 0) - ifnull(j.capacity_number_of_horses, 0)) <= 0) ";
$sql2 = "
and ((ifnull(pi.grain_feed_kg, 0) - ifnull(j.carrying_capacity_kg, 0)) <= 0)
and ((ifnull(pi.length_cm, 0) - ifnull(j.tray_length_cm, 0)) <= 0)
and ((ifnull(pi.width_cm, 0) - ifnull(j.tray_width_cm, 0)) <= 0)
and ((ifnull(pi.height_cm, 0) - ifnull(j.max_load_height_cm, 0)) <= 0)
and ((ifnull(pi.weight_kg, 0) - ifnull(j.carrying_capacity_kg, 0)) <= 0)
and to_days(j.home_day1) <= to_days(str_to_date(pi.date1, '%d/%m/%Y'))
and to_days(str_to_date(pi.date2, '%d/%m/%Y')) <= to_days(j.home_day2)
";
$query = mysql_query ( $sql);
发现这里的sql语句到ifnull(j.max_load_height_cm, 0)) <= 0)这里之后就没有了，这是调试时发现的，不知道怎么解决了。只好想到了存储过程，代码如下：
（1）数据库建procedure
create procedure search_p_info(in jobid int)
begin
select * from p_info pi left join job j on j.id = jobid
where pi.requestid = 0
and ((ifnull(pi.standard_20_ft_container, 0) - ifnull(j.standard_20_ft_container, 0)) <= 0)
and ((ifnull(pi.standard_40_ft_container, 0) - ifnull(j.standard_40_ft_container, 0)) <= 0)
and ((ifnull(pi.high_cube_40_ft_container, 0) - ifnull(j.high_cube_40_ft_container, 0)) <= 0)
and ((ifnull(pi.capacity_head_of_cattle, 0) - ifnull(j.capacity_head_of_cattle, 0)) <= 0)
and ((ifnull(pi.capacity_head_of_sheep, 0) - ifnull(j.capacity_head_of_sheep, 0)) <= 0)
and ((ifnull(pi.capacity_other_livestock, 0) - ifnull(j.capacity_other_livestock, 0)) <= 0)
and ((ifnull(pi.capacity_number_of_horses, 0) - ifnull(j.capacity_number_of_horses, 0)) <= 0)
and ((ifnull(pi.grain_feed_kg, 0) - ifnull(j.carrying_capacity_kg, 0)) <= 0)
and ((ifnull(pi.length_cm, 0) - ifnull(j.tray_length_cm, 0)) <= 0)
and ((ifnull(pi.width_cm, 0) - ifnull(j.tray_width_cm, 0)) <= 0)
and ((ifnull(pi.height_cm, 0) - ifnull(j.max_load_height_cm, 0)) <= 0)
and ((ifnull(pi.weight_kg, 0) - ifnull(j.carrying_capacity_kg, 0)) <= 0)
and to_days(j.home_day1) <= to_days(str_to_date(pi.date1, '%d/%m/%Y'))
and to_days(str_to_date(pi.date2, '%d/%m/%Y')) <= to_days(j.home_day2);
end;
（2）php代码：
$sql = 'call admin_backloadmeau.search_p_info(' . $id. ')';
$query = mysql_query ( $sql);
结果调用存储过程时还报错了，弄得好郁闷，错误如下：
（1）
A Database Error Occurred
Error Number: 2014
Commands out of sync; you can't run this command now
select u.email,j.uid,j.id jobid,j.departure_town,j.distance_route,j.vid from job j left join vehicles v on v.id=j.vid left join users u on v.uid=u.id where j.id=743
Filename: D:\Program Files\wamp\www\backloadme\system\database\DB_driver.php
Line Number: 330
(2).
A Database Error Occurred
Unable to select the specified database: admin_backloadmeau
Filename: D:\Program Files\wamp\www\backloadme\system\database\DB_driver.php
Line Number: 140
在运行完那个procedure后，再接着运行了下面这些代码：
if(count($results) > 0) {
$sql = 'select u.email,j.uid,j.id jobid,j.departure_town,j.distance_route,j.vid from job j ';
$sql .= 'left join vehicles v on v.id=j.vid ';
$sql .= 'left join users u on v.uid=u.id ';
$where.= ' where j.id='.$id;
$q = $this->db->query ( $sql . $where );
$vehicles = $q->result_array ();
报了（1）的错，返回运行其他界面时报了第二个错。
网上查了说是用multi-query mode，是在链接数据库时给一个参数MUTIPLE_QUERY_RESULT,好像是这么写。但是因为CI是这样加载数据库的，所以不知道往哪里加了，
（http://codeigniter.org.cn/user_guide/database/connecting.html）$autoload['libraries'] = array('database', 'session', 'encrypt');自动在每个页面连接数据库，
网上看的这个解决方法（http://codeigniter.org.cn/forums/thread-6420-1-1.html）：
[数据库] 存储过程返回结果集
codeigniter可以这么使用