...

View Full Version : Ordering Records php/mysql



angst
06-28-2007, 12:39 AM
Hello,

I'm trying to change the order records, via links on a php page.

my code:


$numID = $_REQUEST['id'];
$numPos = ($_REQUEST['Pos']);
if($numPos > 1){
mysql_query("UPDATE FamilyPhotoAlbum SET DisplayOrder = DisplayOrder - 1 WHERE Id = " . $numID);

$numCounter = $numPos;
$result = mysql_query("SELECT Id,DisplayOrder FROM FamilyPhotoAlbum WHERE Id != " . $numID . " ORDER BY DisplayOrder ASC");
while ($row = mysql_fetch_array($result)){
if($row['DisplayOrder'] >= ($numPos - 1)){
mysql_query("UPDATE FamilyPhotoAlbum SET DisplayOrder = DisplayOrder + 1 WHERE Id = " . $row['Id']);
}
}
}


I think my logic is a little off here. I can't seem to get the records to order correctly with out it skipping numbers.

any ideas would be great!

thanks in advance for your time!
-Ken

stevenmw
06-28-2007, 01:34 AM
Hello,

I'm trying to change the order records, via links on a php page.

my code:


$numID = $_REQUEST['id'];
$numPos = ($_REQUEST['Pos']);
if($numPos > 1){
mysql_query("UPDATE FamilyPhotoAlbum SET DisplayOrder = DisplayOrder - 1 WHERE Id = " . $numID);

$numCounter = $numPos;
$result = mysql_query("SELECT Id,DisplayOrder FROM FamilyPhotoAlbum WHERE Id != " . $numID . " ORDER BY DisplayOrder ASC");
while ($row = mysql_fetch_array($result)){
if($row['DisplayOrder'] >= ($numPos - 1)){
mysql_query("UPDATE FamilyPhotoAlbum SET DisplayOrder = DisplayOrder + 1 WHERE Id = " . $row['Id']);
}
}
}


I think my logic is a little off here. I can't seem to get the records to order correctly with out it skipping numbers.

any ideas would be great!

thanks in advance for your time!
-Ken


I'm not sure I understand exactly how you want your records displayed.... If you want to change the order in which your records are being displayed you should change the line below


FamilyPhotoAlbum WHERE Id != " . $numID . " ORDER BY DisplayOrder ASC");

You can try changing ASC (orders alphabeticly) to DESC (orders descending). There are several ways to order your row. Try looking into srrays over at http://www.php.net

angst
06-28-2007, 01:45 AM
no no.
if you look at the code, i've got a DisplayOrder Int field.
that I'm talking about more just the sql "ORDER BY ASC/DESC".
I want to change the item order in the DisplayOrder field, so they all come out correctly.

stevenmw
06-28-2007, 02:48 AM
So then you're wanting to order your query by the ids in DisplayOrder?

angst
06-28-2007, 03:24 AM
correct.

stevenmw
06-28-2007, 04:15 AM
$orderbyid = "SELECT * FROM `DisplayOrder` ORDER BY `id` DESC";


That is the simplest way to order Displayorder by it's id.
DESC will go from highest id to lowest id
ASC will go from lowest ID to highest id

If you want to display the newest record in the database before the oldest record you would use DESC.

If you wanted to display the oldest record before the newest record you would use ASC.

Hope that helps.

Fumigator
06-28-2007, 05:33 AM
DisplayOrder is a column, not a table. So I don't think that's what angst is after. The way I'm seeing it, Angst, you have a table and you are giving your users the ability to change the order that the pictures are displayed using a column named DisplayOrder and you want to give the user to switch the pictures around. Does that cover it?

Here's how I do it... there's probably a better way but this works. It's based upon a loop that swaps two displayOrder values.



function moveLetter($dir) {

$query = sprintf("
SELECT letter_id,
print_order_nr
FROM letter_tbl
WHERE newsletter_id = %s
ORDER BY print_order_nr
"
,mysql_real_escape_string($_POST['nlID'])
);

$result = mysql_query($query);
if (!$result) {
SQLError(mysql_error(), $query, 'moveLetter', $_SERVER['REQUEST_URI'], 'BODY');
}

for ($i = 0; $i < mysql_num_rows($result); $i++) {
$letters[$i] = mysql_fetch_assoc($result);
}

for ($i = 0; $i < count($letters); $i++) {
if ($letters[$i]['letter_id'] == $_POST['lID']) {

switch($dir) {
case 1:
//is this the last letter? Can't move it down then
if ($i == (count($letters) - 1)) {
return false;
}
break;

case -1:
//is this the first letter? Can't move it up then
if ($i == 0) {
return false;
}
break;
}

updateOrder($letters[$i]['letter_id'], //source letter_id
$letters[$i + $dir]['print_order_nr']); //target print_order_nr

updateOrder($letters[$i + $dir]['letter_id'], //target letter_id
$letters[$i]['print_order_nr']); //source print_order_nr
}
}
}

//==================================================================================================== =====//
// updateOrder
//==================================================================================================== =====//
function updateOrder($letterID, $letterOrder) {

$query = sprintf("
UPDATE letter_tbl
SET print_order_nr = %s
WHERE letter_id = %s
",
$letterOrder,
$letterID
);

$result = mysql_query($query);
if (!$result) {
SQLError(mysql_error(), $query, 'updateOrder', $_SERVER['REQUEST_URI'], 'FULL');
}
}

angst
07-04-2007, 05:37 PM
hey, sorry, i've been on vacation.

thanks Fumigator! I actually solved this issue while i was away.
but your logic looks interesting.

thanks again!
-Ken



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum