Qty as dropdown

[pranay1995]

So I'm new to php but I've managed to create a drop-down that fetches products and displays them, I want to display quantity in the drop-down below it and I have set that up too it fetches other data if the query is modified. The problem is I don't know how to change that into quantity based on the foreign key.

I have 2 tables:

Products : prod_id (primary key), prod_name, price.
s_keys : id (primary key), prod_id (foreign key) and ss_keys
So if there are 2 records in the products table such as:

Prod_id prod_name price
1 fifa 20
2 football 30
And the s_keys table will have:

id prod_id ss_keys
1 1 whatever
2 1 whateveragain
3 1 whatever soon
Based on the number of times the prod_id is inserted I want to show a drop down like:

Qty(dropdown)1
2
3
And the customer can choose how many qty he or she needs. Based on the Sale it will delete the key from the table and then the new qty in the drop down will change to 2.

Thanks in advance

I tried several things such as using select count but it only shows the number of total records.

Before you come at me with show us your code, we won't write your code etc. I'm not expecting anyone to write code for me I don't know how to implement it, I have tried researching and all I see is woocommerce I am not using woocommerce. I dont know what else to include.

[Dani]

I gather from your question that you have one table that lists products and their prices. However, I'm confused what the ss table is meant to do? What is ss_keys referring to? Are you meaning to do something like this:

SELECT
    products.prod_id as Product_id,
    products.prod_name AS Product_name,
    products.prod_price AS Product_price,
    COUNT(quantity_table.id) AS Product_quantity
FROM products_table
LEFT JOIN quantity_table ON (quantity_table.prod_id = products_table.prod_id)
GROUP BY products.prod_id

The problem is that I'm really confused by what you mean by ss_keys and sales? Perhaps you can explain a little better what the second table, aside from the products table, is meant to do?

Also, is it just the MySQL query you're having difficulty with, or do you need help with the dropdown as well? My guess is that you tried to use COUNT(*) without a GROUP BY clause.