How to add row value in fixed position into an array using PHP and MySQL

Question

I need to keep one row value from table in bottom position in an array using PHP and MySQL. Here is my table:

db_designation:

id      name

1       aaa

2       bbbb

3       Other

4       tttt

My query is below:

$sqldeg=mysqli_query($connect,"select * from db_designation order by id desc");
while ($row=mysqli_fetch_array($sqldeg)) {
            $desdata[]=array("did"=>$row['id'],"dname"=>$row['name']);
}

Here I need this id->3 name->Other row from table value always come in last in the array i.e-$desdata.

I suggest you need another field on your table called Sequence or SortOrder or something, and ORDER BY that instead in your query. — ADyson
– ADyson, Commented Jul 17, 2017 at 11:03
I agree with ADyson that your data model does not adequately describe your data - however it should be named according the REASON you need this record to appear last or the fact it should appear last, not according to the method by which the outcome is achieved. — symcbean
– symcbean, Commented Jul 17, 2017 at 11:28

Ultrazz008 · Accepted Answer · 2017-07-17 11:04:11Z

4

You could do it at query, with ordering before getting to php with.

By id:

ORDER BY (CASE WHEN `id`=3 THEN 1 ELSE 0 END) ASC, `id` DESC

Or by name:

ORDER BY (CASE WHEN `name`='Other' THEN 1 ELSE 0 END) ASC, `id` DESC

this will append 1 when id or name matches, otherwise zero (0), and will order in ASCENDING order.

So your query would be:

$sqldeg=mysqli_query($connect,"select * from db_designation ORDER BY (CASE WHEN `id`=3 THEN 1 ELSE 0 END) ASC, `id` DESC");
while ($row=mysqli_fetch_array($sqldeg)) {
            $desdata[]=array("did"=>$row['id'],"dname"=>$row['name']);
}

answered Jul 17, 2017 at 11:04

Ultrazz008

1,6881 gold badge17 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bibhudatta Sahoo · Accepted Answer · 2017-07-17 11:04:18Z

0

Do like this

$others='';
$sqldeg=mysqli_query($connect,"select * from db_designation order by id desc");
while ($row=mysqli_fetch_array($sqldeg)) {
    if(strpos($row['name'], 'Other')===false)
        $desdata[]=array("did"=>$row['id'],"dname"=>$row['name']);
    else
        $others=array("did"=>$row['id'],"dname"=>$row['name']);
}
$desdata[]=$others;

answered Jul 17, 2017 at 11:04

Bibhudatta Sahoo

4,9142 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

How to add row value in fixed position into an array using PHP and MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related