Limit results in MYSQL with php codes?

Question

I am new on this. I want to generate an XML with this php code:

$result = mysql_query("SELECT * 
                        FROM f6543_virtuemart_products, f6543_virtuemart_products_el_gr, f6543_virtuemart_product_prices, f6543_virtuemart_calcs, f6543_virtuemart_product_categories, f6543_virtuemart_categories_el_gr, f6543_virtuemart_product_medias, f6543_virtuemart_medias, anumbers

                        WHERE f6543_virtuemart_products_el_gr.virtuemart_product_id = f6543_virtuemart_products.virtuemart_product_id
                        AND f6543_virtuemart_product_prices.virtuemart_product_id = f6543_virtuemart_products.virtuemart_product_id
                        AND f6543_virtuemart_product_prices.product_tax_id = f6543_virtuemart_calcs.virtuemart_calc_id
                        AND f6543_virtuemart_product_categories.virtuemart_product_id = f6543_virtuemart_products.virtuemart_product_id
                        AND f6543_virtuemart_categories_el_gr.virtuemart_category_id = f6543_virtuemart_product_categories.virtuemart_category_id
                        AND f6543_virtuemart_product_medias.virtuemart_product_id = f6543_virtuemart_products.virtuemart_product_id
                        AND f6543_virtuemart_product_medias.virtuemart_media_id = f6543_virtuemart_medias.virtuemart_media_id
                        AND f6543_virtuemart_products.virtuemart_product_id = anumbers.virtuemart_product_id", $db);

It's working good but I want to limit the results it gives me for the media (pictures). For example, it gives me for one product four results because I have four pictures in it.

Has anyone any idea about this?

Thank you very much in advance.

That schema looks terrifying if you have to join so many tables together to get a simple result. Are you sure that's properly normalized? — tadman, May 01 '14 at 16:11
Its the problem that virtuemart has all the items i want to include on my xml in different tables. I don't know if i can do it the reverse way. For example to take all the items i want and to create a new table and then to take them for my xml from the new one. — designitgr, May 01 '14 at 16:29

score 1 · Answer 1 · edited May 23 '17 at 12:05

Welcome to Stackoverflow. Seems like you've got much to learn dear padawan, for you've been using the force recklessly!

Right you are if you soon begin your database design training. Follow straight to google hallway and turn to your right door. Even if you didn't design that database. Also your query shows you probably have got the wrong idea on how keys should work. They're meant to uniquely identify table rows, not necessarily to join tables.

On to generating reports, such as an XML, indeed we often do need to join many tables, but you're most likely wrongly joining some tables there. Try using join explicitly instead. Simply by enabling a better reading you'll probably be able to easily fix it, give you know your database design basics.

Not least but last, from your example, "query gives you one 4 results for 1 product because you have 4 pictures in it" it looks like you're either registering the products as duplicates, which is a big no-no, or you're simply querying completely wrong as mentioned.

Good luck in your journey, odd-named-one. May the force be with you.

Limit results in MYSQL with php codes?

1 Answers1