sorting 'recently viewed' items

[CraigR]

(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.

http://www.webassist.com/forums/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 ?

[Jason Byrnes]

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++;
}
}

[CraigR]

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 ?

[Jason Byrnes]

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));
?>

[CraigR]

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

[Jason Byrnes]

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?

[CraigR]

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

[Jason Byrnes]

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.

[CraigR]

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.

[Jason Byrnes]

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.

[CraigR]

thanks for your help Jason.

out of curiosity, i created a new blank page and did the powerstore test similar to your work.

attached is the result ( i get an empty table)

strictly low priority, but if you could shed some light.

thanks

[Jason Byrnes]

I think i found the logic error ;)


change:
<?php $j=0; ?>
<?php do { ?>


to:
<?php do { ?>
<?php $j=0; ?>

[CraigR]

thanks for going 'above and beyond' the call of duty.

that fixes the problem in so far as all the rows are returned. but still not in the order of the array,

i amended your code slightly to build rows into the table, so that you can more easily see when the values correspond

here is the amended file

never mind, i'll quit on this for the time being

[Jason Byrnes]

never one to be defeated:

lets try a different tack.

Instead of trying to be tricky with nested loops, put the recordset in a loop through the Criteria session:


<p>start of data</p>
<table width="200" border="1">

<?php
for($i=0;$i<sizeof($_SESSION["criteria"]);$i++) {
mysql_select_db($database_PowerStoreConnection, $PowerStoreConnection);
$query_rslastfiveviewed = "SELECT * FROM ps3_products WHERE ps3_products.ProductID = ".$_SESSION["criteria"][$i];
$rslastfiveviewed = mysql_query($query_rslastfiveviewed, $PowerStoreConnection) or die(mysql_error());
$row_rslastfiveviewed = mysql_fetch_assoc($rslastfiveviewed);
$totalRows_rslastfiveviewed = mysql_num_rows($rslastfiveviewed);
?>
<tr>
<td><?php echo $row_rslastfiveviewed['ProductID']; ?></td>
<td><?php echo $row_rslastfiveviewed['ProductName']; ?></td>
</tr>
<?php } ?>

</table>
<p>end of data</p>

[CraigR]

you star !

I managed to work out WHY it wasn't working, but not how to fix it.
(don't really understand how it worked with your data and not mine)

This new approach definitely works, i'll have a go at using this method with proper data.

I took a slightly different approach

i created a new associative array and populated it with the recordset productid's as keys and the productnames as values.

i was in the process of playing with some php array functions to compare the 2 arrays to get the result I need, but will use your method first.

thanks for persevering :-)

[Jason Byrnes]

No worries. I cant say why it worked in my case and not your either, but I think this is a simpler approach, and simpler is usually better.

[CraigR]

i transferred the code and it works.

the only minor niggle is that when i did it the clumsy way around, by adding a list of filter criteria, instad of one at a time and repeating the loop, i could eliminate duplicates by using SELECT DISTINCT.

printing the rows one at a time won't let me do this.

i think what i'll do is pass the query results into a new array, run the array unique() function, and print that out.

do you think that would be the best approach ?

[Jason Byrnes]

yeah, that sounds like it would fix the issue.

or you could modify the code that adds the value to the session.

Loop through the session to see if the ID has already been added, then add it if it isn't already there.