I have a PHP file which generates a webshop layout. The problem is when I started to split the products to pages. I have the following query which runs in the products database where every kind of product can be found. There is a prodducttype column which specifies under which menu it should be displayed.
The query:
$sql = "SELECT id,descr, nm, img, price FROM c1640644.products WHERE
producttype = '".$GLOBALS["useableproductid"]."' LIMIT ".($start- 1).",".$stop;
There is one row which always missing I used echo to display the query just before running it its the following:
SELECT id,descr, nm, img, price FROM c1640644.products WHERE
producttype = 'laptop' LIMIT 0,8
Briefly about the database: Currently 3 types of products laptops, headphones, desktops. When displaying laptops they are from ID 1- 17 and ID=1 is missing. Headphones from ID 18-22 and ID=21 is missing. Desktops from ID 23-27 and ID=23 is missing. Always the same products are missing.
The displaying method is:
while($row = $result->fetch_assoc()){
echo $row["nm"]; //just an example echo in the code it gets displayed with design
...
}
Thank you for all the answers!
The most reasonable explanation for the observed behavior ("missing" row for 'laptop' and a "missing" row for 'headphone') is that the rows do not satisfy the predicate, equality comparison to producttype. (This assumes there are fewer than $stop-$start rows is being returned.
I'd verify the values of producttype.
SELECT producttype, COUNT(1)
FROM c1640644.products
GROUP BY producttype
ORDER BY producttype
Is the count returned equal to the number of rows you expect to be returned? In particular, I'd look for values that are visually close to "laptop", but would not satisfy an equality match, such as 'laptop ultra-portable', or 'lapt0p'. Those values would not satisfy an equality comparison to 'laptop'.
Without an ORDER BY clause, MySQL is free to return rows in any order. The result from the query with the LIMIT clause is indeterminate. It's possible that a query with LIMIT 0,8 and LIMIT 8,16 may include the same row. It's also possible that a row will be "skipped".
If the case is that there is always "one row missing", I'm suspicious that there's a fetch before the while loop. The loop shown in the question looks right. It's also possible that nm column contains an empty string, and it makes it look like a row is "skipped". For debugging that, consider including echo "row fetched"; as the first line inside the loop. Or initializing a counter variable before the loop $i=0;, and then inside the loop, increment $i and include $i in the string that in the debug output.