Using CONCAT to order a list, read to understand better

[slyte33]

What I'm trying to do is allow people to change the order of their navigation bar.

I don't have a problem doing this using a listbox to move the values up and down.

 

But to start me off, I'd like to know how I can use CONCAT or explode to draw data from the DB and list it.

 

To better understand:

 

I have a database with a table called "navbar".

I have the fields: ID, name, link, icon, caption

 

What we'll be looking at most is the ID.

The ID's range from 1-19, so 19 links on the navbar.

They are extracted from the database like so:

 

$navbar=$db->execute("select * from navbar");
for($i=1;$nav=$navbar->fetchrow();$i++)
{
$name=$nav['name'];
$link=$nav['link'];
$title=$nav['caption'];
$image=$nav['icon'];
?>
<tr>
<td background="images/sub_btn.gif" class="norepeat" valign=top>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td></td>
<td><img src="images/spacer.gif" width="198" height="1"></td>
</tr>
<tr>
<td><img src="images/spacer.gif" width="1" height="19"></td>
<td style="padding-left: 10px;" class=sub><img src=<?=$image?> width=13 height=13 align=absmiddle> <a target=middle href="<?=$link?>" class="sub_lnk" title='<?=$title?>'><?=$name?></a>
</td>
</tr>
</table></td>
</tr>
<?}?>

 

This is for my online game, so we then go to my players table and add a field called "navorder".

The default value is: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19

 

How would I, just for tests, lists the values from the navbar table, by ID, on the page?

Example:

 

My value is set to 1,2,3,4,5

When drawing the data, however I do it, would show:

 

Link1
Link2
Link3
Link4
Link5

 

Now if I changed the order, with a listbox which I can do, would change it to 3,1,5,2,4

It would then show:

Link3
Link1
Link5
Link2
Link4

 

 

Sorry if it seems confusing, I tried my best at explaining it.

Someone told me to use the explode function, or CONCAT. I tried to read up on explode() but just got confused as to how to use it. If someone could tell me how to do this it'd be much appreciated

 

Thanks!

 

[slyte33]

If possible, could a mod please change the topic name to:

"Separating string by commas and drawing data from DB by those values"

Thanks:)

[AbraCadaver]

Two different questions.  To list in order of ID you could do it in your query:

 

SELECT * FROM navbar ORDER BY ID

 

To order by the list in the players table, something like this (not tested)

 

$players = $db->execute("SELECT navorder FROM players WHERE player_id = 'whatever'");
$row = $players->fetchrow();

$navbar = $db->execute("SELECT * FROM navbar ORDER BY FIELD(ID," . $row['navorder'] . ")");
while($row = $navbar->fetchrow()) {
    ///do stuff with row
}

 

 

[slyte33]

Two different questions.  To list in order of ID you could do it in your query:

 

SELECT * FROM navbar ORDER BY ID

 

To order by the list in the players table, something like this (not tested)

 

$players = $db->execute("SELECT navorder FROM players WHERE player_id = 'whatever'");
$row = $players->fetchrow();

$navbar = $db->execute("SELECT * FROM navbar ORDER BY FIELD(ID," . $row['navorder'] . ")");
while($row = $navbar->fetchrow()) {
    ///do stuff with row
}

 

 

Thanks.

I know how to order something by the ID, or whatever else, I think I wrote it/you read it wrong

Other than that, this seems to work fine.

 

Could you tell me how you did this with:

$navbar = $db->execute("SELECT * FROM navbar ORDER BY FIELD(ID," . $row['navorder'] . ")");

 

It works fine, but how did this seperate the numbers by a comma and actually fetch the data correctly?

I've never seen:

ORDER BY FIELD(ID," . $row['navorder'] . ")");

before, and would like to know how it works

 

 

[AbraCadaver]

If you echo out the query:

echo "SELECT * FROM navbar ORDER BY FIELD(ID," . $row['navorder'] . ")";

It might look like this:

SELECT * FROM navbar ORDER BY FIELD(ID,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)

 

Because you said the ids were stored in that format in the navorder column.

 

So the order by field() is comparing and sorting on ID in the order of the ids that you listed.

[slyte33]

If you echo out the query:

echo "SELECT * FROM navbar ORDER BY FIELD(ID," . $row['navorder'] . ")";

It might look like this:

SELECT * FROM navbar ORDER BY FIELD(ID,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)

 

Because you said the ids were stored in that format in the navorder column.

 

So the order by field() is comparing and sorting on ID in the order of the ids that you listed.

 

That's awesome, thank you very much.

I was expecting to have to go through a bunch of explode(), str_replace() and all that stuff to get this working. Didn't realize it would work in a simple query