[RESOLVED] Problem with in_array(), and moving an item with in the array

GreyTrilby

Hi all,

Wow - what a change - long time since I've been on here!

Just a quicky, and I think I'm missing something blindingly obvious, as usual.

I'm storing a list of ISBNs in a single db field as a comma separated list.

When a user clicks on a book title, it calls a function, passing that book's ISBN. Because they've clicked on it, I want to add it to their 'recently viewed' list.

The function code is below.

The problem is that the in_array() function is doing something weird...

When I click a title that's already viewed it correctly states "[the isbn] already in list"
When I click a title that's NOT already been viewed, it INcorrectly states "[the isbn] already in list".

BUT...

When I click a title that's already viewed it DOESN'T re-add the isbn to the array to put it back in the db, so on one level it IS correctly picking up the fact that the ISBN is already in the list.

I'm baffled!

Can someone please point out what I'm missing here?

function logISBN($theIsbn, $user) {
    require_once("../includes/bid_conn.php");

//get current list of ISBNs
$logISBNQuery = "SELECT user_titles FROM bid_users WHERE user_email = '$user'";
$logISBN = mysql_query($logISBNQuery) or die(mysql_error());
$log = mysql_fetch_array($logISBN);
extract($log); //gives $user_titles - the current list
//
//turn current comma separated list into array
if(trim($user_titles) !==''){
$list = explode(',', $user_titles);
} else {
    $list = array();
}
if (in_array($theIsbn, $list, true)) {
    echo $theIsbn.' already in list';
} else {
echo $theIsbn.' not in list, adding now...';
        //check number of values in array
        $isbnCount = count($list);
        if ($isbnCount >= 20) {
            $remove = array_shift($list);
        }
        //add current ISBN to the array

    array_push($list, $theIsbn);
}

//make it a comma separated list again
$newList = implode(',', $list);

//add it back to the db
$updateISBNQuery = "UPDATE bid_users SET user_titles = '$newList' WHERE user_email = '$user'";
$updateISBN = mysql_query($updateISBNQuery) or die(mysql_error());
unset($list);
unset($theIsbn);

One other small question - ideally, if the isbn exists, I'd like to move it to the end of the array, but I've no idea how to do that - any pointers would be most gratefully received.

Thanks in advance for your help!

bradgrafelman

GreyTrilby;11007673 wrote:
I'm storing a list of ISBNs in a single db field as a comma separated list.

There's your first problem - your DB design isn't normalized. Why isn't it?

Weedpacket

bradgrafelman wrote:
There's your first problem - your DB design isn't normalized. Why isn't it?

...Because if it were you wouldn't be having to do all this dancing around with explode and in_array and implode and so on in the first place.

Derokorian

Also, he still doesn't need to use explode, in_array etc when you can use WHERE column LIKE '%isbn_to_search_for%'

bradgrafelman

Derokorian;11007707 wrote:
Also, he still doesn't need to use explode, in_array etc when you can use WHERE column LIKE '%isbn_to_search_for%'

But that would match a row with a value of "123,456,789" if you searched for 45, even though 45 wasn't one of the values in that list.

Derokorian

True but seeing as they are ISBN I assumed they were 13 digits with hyphens and so a controlled format. But alas, upon second thought if its not even normalized that's probably a big assumption.

GreyTrilby

sighs

OK - it strikes me that perhaps the comma separated list within a single field isn't quite the way to go then.

Yes, they are all 13 digit ISBNs (without hyphens) - the entry form/db structure enforce/ensure that.

I'll rephrase my question in the hope of more constructive/less mildly-condescending criticism.

<new question>
I want each user to have a 'recently viewed' list when they log in. This should be no more than 20 entries.
On clicking a title, if never been viewed before, it should add this title to the top of the list.
On clicking a title, if it HAS been viewed before, it should move this title from its current position in the list to the top of the list
I'm guessing that a separate table, using the user id as a foreign key would be the way forward, but I can't quite visualise the structure or how to manipulate the data (adding/moving to beginning of list).
How would you recommend I approach this?
</new question>

johanafm

The way to always go when dealing with (possible) multiple entries is a separate table for these entries rather than comma separated lists. In this case, quite easy to ahchieve with something like

recently_used(user_id, isbn, date)

If you wish to keep the items per user down, start with a count(*) ... WHERE user_id=X to see how many entries there are and then delete the oldest entry if necessary before inserting a new one. Or better yet, create a trigger which automates the job for you. But, you could also leave all old data in there and simply use order by date limit 10 to keep the 10 most recents viewed items. And possibly run a script once per day or week to clean out older entries.

edit: by the way, primary key is (user_id, isbn) to avoid having the same item in there multiple times. If the primary key allready exists, consult your database docs to see how you go about updating the date field for this entry in the most simple way to mark is as viewed again. For example, in MySQL you'd use INSERT ... ON DUPLICATE KEY ...

GreyTrilby

lightbulb pings on
learning has occurred

Many, many thanks!

GreyTrilby

Thanks for your help Johan.

Can't believe the solution was so simple!