product suggestions based on cart contents in php and sql

[trix.1210]

Thanks for reading!

I have a record shop online written in php and ezsql.

I am trying to display a list of records from my sql database as suggestions for the customer to add to his cart. These records must not already be in the cart at the time the suggestions are displayed!

The code below partially works. If there is one record in the cart by Madonna, it will show a list of other Madonna records as long as the id in the table isn’t the same which is correct. i.e. if “true blue” is in the cart already, the suggestions list should not contain “true blue” as the user already has added it to their cart.

The problem I’ve got is that if there is more than one record by Madonna in the cart, it displays one or more records in the suggestion list that are already in the cart which is incorrect. I cant seem to work out why. The code iterates through the basket so I should get unique suggestions. Any help appreciated - I’m almost there!

The table in the database contains id, artist, title, price.

The session variable “item” contains product_id, artist

<?php echo '

You may be interested in these records:

'; foreach ($_SESSION['item'] as $item ) { $iid=$item['product_id']; $basket = $db->get_results("SELECT * FROM records WHERE artist LIKE '".$item['artist']."' and id !=$iid "); if (is_array($basket) || is_object($basket)) { foreach ( $basket as $row ) { echo "

matching ".$row->id." with ".$item['product_id']."

"; //if ($row->id !=$iid) //{ $art= shorten("$row->artist", 25); $tit= shorten("$row->title", 25); echo $row->id."
"; echo "
".$art.'
'; echo "
".$tit.'
'; echo "&pound".$row->price; } } } ?>

[phdr]

The reason it isn’t working is because you are running a query inside of a loop. For each query, it is matching the data that doesn’t match the current item id, but when you have two or more item ids in the cart for the same artist, each new query will match the ids that the previous query excluded.

In general, you should never run SELECT queries inside of a loop. They are very inefficient, due to the communications needed, which take many times longer than it actually takes for the database server to execute the query.

Before solving your current problem, there are a few other problems that you should fix -

1. A LIKE comparison, without any wild-card characters, operates almost the same as an equal comparison (there are some collation handling differences between the two) and you should just use an equal comparison for what you are doing.

2. You should have an artist table, which will assign an auto-increment index to each artist. You should store the artist id in the records table, not the artists name.

3. Your cart should use the item id/product id as the cart’s array index and the only data being stored in the cart for each index should be the quantity. By storing an array of data, the id and artist, all your program logic to manipulate the data in the cart (find, prevent duplicates, insert, edit, delete) is more complicated. KISS (Keep It Simple.) If your cart was already setup this way, you could retrieve all the item ids using array_keys(), no loop would be required.

4. You should not put data values directly into the sql query statement, as this would allow any sql special characters in the data to break the sql syntax, which is how sql injection is accomplished. You should use prepared queries, with place-holders in the sql query statement for each value, then supply the data when you execute the query.

5. The ezsql code you are using is insecure nonsense. The escaping it does provide, which you aren’t using, is mangling data, by unconditionally applying stripslashes() and the PDO escaping it is providing is actually using addslashes(), rather then the PDO quote() method and the code doesn’t support prepared queries, which actually simplifies the sql query syntax and makes them nearly fool-proof (using the escape string functions can still allow sql injection,)

Lastly, the way you would accomplish displaying similar items, excluding items already in the cart, would be to run one sql query like the following -

SELECT * FROM records WHERE artist_id IN(a comma separated list of distinct artist ids for items in the cart would go here) AND id NOT IN(a comma separated list of item ids in the cart would go here)

If you were at the point of using sub-queries, the comma separated list of distinct artist ids for items in the cart can be accomplished with a subquery and the comma separated list of item ids in the cart can be gotten using array_keys() and some logic.