我正在尝试将以下 SQL 查询转换为 Codeigniter 格式。
SELECT tb.pickup_trip_location,
tb.drop_trip_location,
CONCAT_WS(' ', tp.firstname, tp.lastname) AS passenger_name,
tb.tkt_passenger_id_no AS tkt_passenger_id_no,
tb.trip_id_no AS trip_id_no,
tb.id_no AS booking_id_no,
DATE_FORMAT(tb.booking_date, '%m/%d/%Y %h:%i %p') as booking_date,
DATE_FORMAT(tb.date, '%b %e %Y %T') as booking_time,
tr.name AS route_name,
tb.request_facilities AS request_facilities,
tb.price AS price,
tb.total_seat AS quantity,
tb.discount AS discount,
tb.seat_numbers AS seat_serial,
tb.adult,
tb.child,
tb.special,
tb.booking_type,
tb.trip_route_id,
tp.nid,
tp.phone AS phone,
tb.payment_status,
fr.owner,
fr.reg_no,
tras.`assign_time` AS assign_time
from tkt_booking AS tb
join tkt_passenger AS tp ON tb.tkt_passenger_id_no = tp.id_no
join trip_route AS tr ON tr.id = tb.trip_route_id
join trip_assign AS tras ON tras.trip = tb.trip_id_no
join fleet_registration AS fr ON fr.id = tb.fleet_id
where tb.id_no = "$booking_id_no" AND
DATE(tb.booking_date) = DATE(tras.`assign_time`)
我设法编写了以下代码,但它没有返回任何数据
return $this->db->select("
tb.pickup_trip_location,
tb.drop_trip_location,
CONCAT_WS(' ', tp.firstname, tp.lastname) AS passenger_name,
tb.tkt_passenger_id_no AS tkt_passenger_id_no,
tb.trip_id_no AS trip_id_no,
tb.id_no AS booking_id_no,
DATE_FORMAT(tb.booking_date, '%m/%d/%Y %h:%i %p') as booking_date,
DATE_FORMAT(tb.date, '%b %e %Y %T') as booking_time,
tr.name AS route_name,
tb.request_facilities AS request_facilities,
tb.price AS price,
tb.total_seat AS quantity,
tb.discount AS discount,
tb.seat_numbers AS seat_serial,
tb.adult,
tb.child,
tb.special,
tb.booking_type,
tb.trip_route_id,
tp.nid,
tp.phone AS phone,
tb.payment_status,
fr.owner,
fr.reg_no,
tras.`assign_time` AS assign_time
")
->from('tkt_booking AS tb')
->join('tkt_passenger AS tp', 'tb.tkt_passenger_id_no = tp.id_no' ,'full')
->join('trip_route AS tr', 'tr.id = tb.trip_route_id','full')
->join('trip_assign AS tras', 'tras.trip = tb.trip_id_no','full')
->join('fleet_registration AS fr', 'fr.id = tb.fleet_id','full')
->where('tb.id_no', $booking_id_no)
->where(DATE(tb.booking_date),DATE(tras.assign_time))
->get()
->row();
问题在于以下行 ->where(Dat(tb.booking_date),DATE(tras.assign_time)) 因为当我将其注释掉时,我得到了一些数据
请帮忙
$this->db->where() 接受可选的第三个参数。如果您将其设置为 FALSE,则 CodeIgniter 将不会尝试保护您的字段或表名称。(来源)
因此,将您的代码更改为以下
$this->where('DATE(tb.booking_date)','DATE(tras.assign_time)', false)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句