sorting 'recently viewed' items

(Not strictly a PHP question, but as yet, there is no MySQL section in General Topics)

further to my post on another thread, i have a recordset of items which is using an array as its filter criteria.

showthread.php?t=15059

the array is populated from the last few items viewed, I then loop through the array, so that the resultant query is something like this..

SELECT DISTINCT... FROM tblitems WHERE ItemID = 3 OR ItemID = 14 OR ItemID = 8 OR ItemID = 47 OR ItemID = 22

The itemID's are in the order in which they are added to the array.

What I haven't worked out is how to sort the resulting recordset, so that the row order reflects the input parameters.

can anyone advise ?

you mean so that the results set is the same order as the the array?


If so, you would need to hand code the output to loop through the array and compare the id in the array against the id of the record and only display if the match

the pseudo code logic will look like:

i=0;
loop through the recordset {
   loop through the recordset {
     if( $array[i] == recordset['idColumn']) {
        echo recordset value
      }
     i++;
   }
}

thanks for the logic Jason.

i had a go, and depending on how I organise the loops, i get a different result, (neither being the one I want).
I also output the array values in order to check my results, (in red)

First attempt...

<?php do { ?>
<?php for ($i = 0; $i < $totalRows_rslastfiveviewed; $i++){ ?>
<?php print_r ($_SESSION["criteria"][$i]); ?>
<?php if ($_SESSION["criteria"][$i] == $row_rslastfiveviewed['ItemID']) echo $row_rslastfiveviewed['ItemName']; ?>
<?php } ?>
<?php } while ($row_rslastfiveviewed = mysql_fetch_assoc($rslastfiveviewed)); ?>

This results in the array value [$i] being matched in every increment, but in the same order as in the recordset, ie ItemID


Second attempt...

<?php for ($i = 0; $i < $totalRows_rslastfiveviewed; $i++){ ?>
<?php do { ?>
<?php print_r ($_SESSION["criteria"][$i]); ?>
<?php if ($row_rslastfiveviewed['ItemID'] == $_SESSION["criteria"][$i]) echo $row_rslastfiveviewed['ItemName']; ?>
<?php } while ($row_rslastfiveviewed = mysql_fetch_assoc($rslastfiveviewed)); ?>
<?php } ?>

This loops through until I get a match, but I only get one result returned, (although it is the correct one)

can you help me with the logic please ?

try this instead:

<?php 
$j = 0;
do {
    for ($i = 0; $i < $totalRows_rslastfiveviewed; $i++){
       print_r ($_SESSION["criteria"][$i]);  
       if ($_SESSION["criteria"][$j] == $row_rslastfiveviewed['ItemID']) echo $row_rslastfiveviewed['ItemName']; 
    }
   $j++;
} while ($row_rslastfiveviewed = mysql_fetch_assoc($rslastfiveviewed));
?>

thanks Jason.

that didn't work properly at all, until I changed the $j to $i like so,

<?php
$j = 0;
do {
for ($i = 0; $i < $totalRows_rslastfiveviewed; $i++){
//print_r ($_SESSION["criteria"][$i]);
if ($_SESSION["criteria"][$i] == $row_rslastfiveviewed['ItemID']) echo $row_rslastfiveviewed['ItemName'];
}
$j++;
} while ($row_rslastfiveviewed = mysql_fetch_assoc($rslastfiveviewed));
?>


but I still get the products listed in order of itemid, as in my first example above

perhaps it would help if I had real values to work with


what is the contents of the $_SESSION["criteria"]?


what is returned by the recordset?

the session variable $_SESSION["criteria"] contains an array of integer variables.

an example would be

Array ( [0] => 32 [1] => 134 [2] => 135 [3] => 143 [4] => 2 )

which reflects the order they were added to the array

when an additional item is viewed, the first element is removed from the array and another is added to the end, so it has a maximum of 5 elements

for example, adding itemid = 5 to the above array would produce the following array...

Array ( [0] => 134 [1] => 135 [2] => 143 [3] => 2 [4] => 5)

the select statement based on the first array would be as follows...

SELECT ItemID, ItemTypeID, ItemName, ItemImageURL, ItemThumbnailURL FROM tblitem WHERE ItemID = 32 OR ItemID = 134 OR ItemID = 135 OR ItemID = 143 OR ItemID = 2

i did a test with the following query:
SELECT * FROM ps3_products WHERE ps3_products.ProductID = 2 OR ps3_products.ProductID = 13 OR ps3_products.ProductID = 6 OR ps3_products.ProductID = 20


using powerstores product database.

then created the critiria session as:

<?php

$_SESSION["criteria"][] = 2;

$_SESSION["criteria"][] = 13;

$_SESSION["criteria"][] = 6;

$_SESSION["criteria"][] = 20;

?>

and using the following code:

<table width="200" border="1">

<?php $j=0; ?>

  <?php do { ?>

    <?php for($i = 0;$i < $totalRows_rslastfiveviewed; $i++) { ?>

    <?php if($_SESSION["criteria"][$j] == $row_rslastfiveviewed['ProductID']) { ?>

    <tr>

      <td><?php echo $row_rslastfiveviewed['ProductID']; ?></td>

      <td><?php echo $row_rslastfiveviewed['ProductName']; ?></td>

    </tr>

    <?php } ?>

    <?php $j++; } ?>

    <?php } while ($row_rslastfiveviewed = mysql_fetch_assoc($rslastfiveviewed)); ?>

</table>

got the correct order output of 2, 13, 6, 20, the order of the items in the array.

I used a different order in the array just to make sure the array was effecting the output on the page.

hi Jason.

maybe i'm missing something here.
if i use the code you posted, (but changing field names accordingly)

i get a lot of rows declaring

Notice: Undefined offset: 5

up to ...

Notice: Undefined offset: 24

on line 353 which is

<?php if($_SESSION["criteria"][$j] == $row_rslastfiveviewed['ItemID']) { ?>

where i change $j to $i this gets rid of the error, but gives me the wrong order

**UPDATE
i just set my page to stop displaying all errors, and the undefined offset error disappears, but i only get one row returned.**

i then tried redeclaring my criteria array as you example, so now my sql statement reads..

SELECT ItemID, ItemTypeID, ItemName, ItemImageURL, ItemThumbnailURL FROM tblitem WHERE ItemID = 2 OR ItemID = 13 OR ItemID = 6 OR ItemID = 20

I only get one row returned, item id 2

i then changed the order of the array, swapping 2 and 13 around, and item id 2 (only) is still returned.

very odd.

never mind, i appreciate your help, there must be something else amiss.

not sure what could be going wrong, in my case it returned all four records ordered by the array. sorry I cant offer more help on this one.