What do I need to do to correct this SQL statement?

Question

I have the following tables:

1. products - contains products in inventory.
2. suppliers - contains supplier details.
3. product_category - contains the names of product categories.
4. product_suppliers - contains relationships between products and suppliers.

The important one here is probably, product_suppliers, so here are the columns:

id //(auto-increment)  
prod_id //(id of product)  
sup_id //(id of supplier)  
preferred //(1 if it's the preferred supplier for that product - 0 if not)  
cost_per_outer //(this suppliers price for this item)  
qty_in_outer //(the number in a box)  

The product table, among storing the product details, also has a field for stock_level and reorder_level. When the former is less than the latter, the product should be included in the list for re-ordering.

I'm trying to build the query for the list, which is not working quite right.

I have 4 items qualified for re-ordering affecting two suppliers. There should be two items for each supplier, but currently it is only retrieving the results for the first supplier.

I'm using MySQL and PHP.

I've run the following query to give us some insight to the actual data in the database:

mysql> SELECT
    ->         p.prod_id,
    ->         AES_DECRYPT(p.alt_id, 'MW4KQLg1Irfo3Xz7Nxht')                         AS sku,
    ->         AES_DECRYPT(p.prod_name, 'MW4KQLg1Irfo3Xz7Nxht')                      AS prod_name,
    ->         AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht')                    AS prod_type,
    ->         AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht')                    AS stock_level,
    ->         AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht')            AS reorder_level,
    ->         AES_DECRYPT(c.category_name, 'MW4KQLg1Irfo3Xz7Nxht')            AS category_name,
    ->         ps.sup_id,
    ->         ps.preferred
    -> FROM
    ->         products p
    -> INNER JOIN
    ->         product_category c
    ->   ON
    ->         p.category_id = c.category_id
    -> INNER JOIN
    ->         product_supplier ps
    ->   ON
    ->         p.prod_id = ps.prod_id
    -> INNER JOIN
    ->         suppliers s
    ->   ON
    ->         ps.sup_id = s.supplier_id
    -> ORDER BY
    ->         ps.sup_id;
+---------+------+-----------------+-----------+-------------+---------------+---------------+--------+-----------+
| prod_id | sku  | prod_name       | prod_type | stock_level | reorder_level | category_name | sup_id | preferred |
+---------+------+-----------------+-----------+-------------+---------------+---------------+--------+-----------+
|       7 | 7    | Term Block      | 1         | 3           | 5             | Electrical    |      2 |         1 |
|       5 | 5    | Electrical Tape | 1         | 12          | 20            | Electrical    |      2 |         1 |
|       6 | 6    | BlowGas         | 1         | 6           | 15            | Plumbing      |     12 |         1 |
|       1 | 1    | PTFE Tape       | 1         | 9           | 10            | Plumbing      |     12 |         1 |
+---------+------+-----------------+-----------+-------------+---------------+---------------+--------+-----------+

Here is the query for the list with result showing only two of the four items I was expecting:

mysql> SELECT
    ->         p.prod_id,
    ->         AES_DECRYPT(p.alt_id, 'MW4KQLg1Irfo3Xz7Nxht')                         AS sku,
    ->         AES_DECRYPT(p.prod_name, 'MW4KQLg1Irfo3Xz7Nxht')                AS prod_name,
    ->         AES_DECRYPT(p.prod_desc, 'MW4KQLg1Irfo3Xz7Nxht')                 AS prod_desc,
    ->         AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht')                AS stock_level,
    ->         AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht')             AS reorder_level,
    ->         AES_DECRYPT(p.reorder_qty, 'MW4KQLg1Irfo3Xz7Nxht')               AS reorder_qty,
    ->         p.vat_exempt,
    ->         AES_DECRYPT(p.lastorderdate, 'MW4KQLg1Irfo3Xz7Nxht')            AS lastorderdate,
    ->         AES_DECRYPT(p.lastorderqty, 'MW4KQLg1Irfo3Xz7Nxht')               AS lastorderqty,
    ->         AES_DECRYPT(c.category_name, 'MW4KQLg1Irfo3Xz7Nxht')          AS category_name,
    ->         ps.sup_id
    -> FROM
    ->         products p
    -> INNER JOIN
    ->         product_category c
    ->   ON
    ->         p.category_id = c.category_id
    -> INNER JOIN
    ->         product_supplier ps
    ->   ON
    ->         p.prod_id = ps.prod_id
    -> INNER JOIN
    ->         suppliers s
    ->   ON
    ->         ps.sup_id = s.supplier_id
    -> WHERE
    ->         AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht') = 1
    ->   AND
    ->           AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht') <= AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht')
    ->   AND
    ->           ps.preferred = 1
    -> ORDER BY
    ->         ps.sup_id;
+---------+------+-----------------+------------------------+-------------+---------------+-------------+------------+---------------+--------------+---------------+--------+
| prod_id | sku  | prod_name       | prod_desc              | stock_level | reorder_level | reorder_qty | vat_exempt | lastorderdate | lastorderqty | category_name | sup_id |
+---------+------+-----------------+------------------------+-------------+---------------+-------------+------------+---------------+--------------+---------------+--------+
|       7 | 7    | Term Block      | Nylon connector block. | 3           | 5             | 20          |          0 | NULL          | NULL         | Electrical    |      2 |
|       5 | 5    | Electrical Tape | Black                  | 12          | 20            | 100         |          0 | NULL          | NULL         | Electrical    |      2 |
+---------+------+-----------------+------------------------+-------------+---------------+-------------+------------+---------------+--------------+---------------+--------+

Answer 1

The problem is that your result is coming from AES_DECRYPT() is coming of string type and that is why comparing for stock_level and reorder level values of 3 - 5 and 12 - 20 it is showing while for 6 -15 and 9 - 20 it is not showing because if you comapre 6 and 15 as string 6 will be more than 15 same is the case for 9 and 20.

Hope you got your problem...

Convert the result of AES_DECRYPT() to numeric before comparison..

Change your where clause to this

WHERE
     (AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht') + 0) = 1 AND
     (AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht') + 0) <= (AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht') + 0)

adding +0 will cast the result to numeric

Hope this helps.