,标题建议表格中数据库中的日期格式存储为varchar中的dd/mm/yyyy格式(带有slashes(。我只想按升序排序结果。但是我的查询不允许我这样做。简单的ORDER by order_date ASC
不起作用。此前这样做的开发人员犯了这个错误,即不以日期格式插入它。现在的问题是,它拥有数千个记录,现在无法一一编辑。因此,我需要有一个解决方案来通过编写PHP函数来以升序显示。但是我不知道该怎么做。有人可以帮忙吗?
sql
<?php
$this->db->select('str_to_date(your_col, '%d/%m/%Y') as my_date');
$this->db->order_by('my_date','ASC');
$this->db->where('customer_id',$id);
$qry3 = $this->db->get('due');
foreach ($qry3->result() as $row){
$total=$total+ ($row->due-$row->paid_amount);
$total=$total-$row->discount_allowed;
$total=$total+$row->credit_note_amount;
?>
<tr>
<td><?php echo $row->my_date?> </td>
<td><?php echo $row->order_no?></td>
<td>
<?php
if (isset($row->due)) {
echo "SALE A/C";
} else if (isset($row->particulars)) {
echo $row->particulars;
} else {
echo $row->particulars;
}
?>
</td>
<td align="">
<?php if (isset($row->due))
{
echo $row->due;
} else {
echo $row->credit_note_amount;
}
?>
</td>
<td align="">
<?php
if (isset($row->paid_amount)) {
echo $row->paid_amount;
} else if (isset($row->discount_allowed)) {
echo $row->discount_allowed;
} else {
}
?> </td>
<td class="info"><?php echo $total?></td>
</tr>
<?php }?>
您可以尝试使用mysql
的str_to_dateSELECT STR_TO_DATE(yourdatefield, '%m/%d/%Y') as newcolumnname
FROM yourtable order by newcolumnname asc
您可以简单地将varchar列转换为日期
select str_to_date(your_col, '%d/%m/%Y') my_date
from your_table
order by my_date ASC
$this->db->select(' paid_amount, discount_allowed, str_to_date(your_col, '%d/%m/%Y') as my_date');
$this->db->order_by('my_date','ASC');