BIG problem

[viveleroi0]

I have been developing a shopping cart and just notices a big issue...

when someone selects a product and adds it to their cart, the item # gets added into a table called "Hold", which means that product is currently "sale pending". I wrote a script that as the products are listed for a shopper to see from the Products database, it compares the products ID to the IDs in the Hold table, if they match, that means that product is being sold and the script does not display that item. If there isn't a match, those items ARE displayed.

Buit upon testing it, say I have it set to show 5 items per page, but four of those are currently on hold. That means it will only show 1 product, until you go to the next page!
Can I make some sort of loop or something that makes the DB show one more product for each one that is in the Hold table?

So, if five are read from the mysql db, and one is on hold, only four would be shown. I need something that tells it to go on to the 6th product, to make up for the missing one. So, 5 products are shown.

I hope this makes sense. I have attached the file that reads the db and removes any on hold items. (I took out the other html crap, so all thats in here is the code I'm dealing with)

[jeremuck]

First, try not to use mysql_result. It's very slow compared to the others. [man]mysql_fetch_array[/man]

Second, it sounds like what you need to do is first get enough records from the database to make sure you have enough.
When you are going through each record is where you decide how many items to show, not in the SQL query.
Then you are just counting how many items you are actually showing. As long as you still have more items to actually show, you show them, checking for a held item on the way.

[[deleted]]

This is one of those places where MySQL's lack of subqueries really scr*ws you.

WHen you are selecting the products to display, you'd normally do:

SELECT *
FROM products
LIMIT foo,bar;

Now you have a table that holds all the product-id's of those products that are not available, so you could just substract those from the products table using a subquery.... in any database except mysql.

This will not work in mysql:
SELECT *
FROM products
WHERE productid NOT IN (SELECT productid FROM hold)
ORDER BY productid
LIMIT foo,bar;

But it does get the point across: you have to find a way to skip all those products in 'products' whose ID is already in 'hold'.

In other words, you want to display all those products that don't appear in 'hold'. And in MySQL that can be done using a LEFT JOIN.

LEFT JOIN will merge two tables, and if the second table does not have an entry to match the ON clause, then NULL's are used instead, and these NULL can be noticed:

SELECT *
FROM products LEFT JOIN hold ON products.productid = hold.productid
WHERE hold.productid IS NULL
ORDER BY products.productid
LIMIT foo,bar;

[jeremuck]

whoops... I forgot about JOINs.