mysql return result from table related to other tables

Question

I'm trying to show result from table that related to many tables but my problem is that the query is return only one product that is on special what I want to return are both products products on special and also products that are not. My Php Query is :

$query = "SELECT DISTINCT p.product_id, p.price ,sp.date_end, f.percentage AS special_percentage , p.model, pd.name AS title, pd.description AS text, cd.name AS section, p.image, pd.tag, p.date_added AS created "
                ."FROM #__mijoshop_product AS p "
                ."JOIN #__mijoshop_product_special AS sp ON  p.product_id = sp.product_id "
                ."JOIN #__mijoshop_flordeco_product_special_percentage AS f ON sp.product_id = f.product_id "
                ."INNER JOIN #__mijoshop_product_description AS pd ON p.product_id = pd.product_id "
                ."LEFT JOIN #__mijoshop_product_to_store AS ps ON p.product_id = ps.product_id "
                ."LEFT JOIN #__mijoshop_product_to_category AS pc ON p.product_id = pc.product_id "
                ."LEFT JOIN #__mijoshop_category_description AS cd ON (pc.category_id = cd.category_id AND cd.language_id = {$language_id}) "
                ."LEFT JOIN #__mijoshop_category_to_store AS cs ON (pc.category_id = cs.category_id AND cs.store_id = {$store_id}) "
                ."WHERE (LOWER(pd.name) LIKE '%" . $search_text . "%' OR
                        LOWER(pd.description) LIKE '%" . $search_text . "%' OR 
                        LOWER(p.sku) LIKE '%" . $search_text . "%' OR ";

                        if( $model ) {
                            $query .= "LOWER(p.model) LIKE '%" . $search_text . "%' OR ";
                        }

                        $query .= "LOWER(pd.tag) LIKE '%" . $search_text . "%') "
                ."AND p.status = '1' "
                ."AND date(sp.date_end) >= date(NOW()) "
                ."AND p.date_available <= NOW() "
                ."AND ps.store_id = {$store_id} "
                ."AND pd.language_id = '" . $language_id . "' "
                ."GROUP BY p.product_id "
                ."ORDER BY {$order_by} "
                ."LIMIT ".$limit;

        $db->setQuery($query);
        $results = $db->loadObjectList();

Please provide the final constructed query, sample data, expected result, and current result. — Patrick Q, Sep 05 '19 at 20:22
Your script is at risk for [SQL Injection Attacks](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Learn about [prepared statements](https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection) for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) the string is not safe! — Jason K, Sep 05 '19 at 20:27
This isn't MySQL, it doesn't use `#` prefix for temporary tables. I think that's SQL-Server. — Barmar, Sep 05 '19 at 20:41
Based on the functions being used, I'm not so sure it's SQL Server either — Patrick Q, Sep 05 '19 at 20:47
To receive the best possible advice, I recommend that you: 1. Provide a minimal dbfiddle demo that expresses the issue so that volunteers can test and tefine their suggestions and 2. Post your Joomla-related questions at [joomla.se] Stack Exchange -- a place where volunteers are immediately aware of what `#_` means and can help you to secure your query with query builder methods. ...Also, this post is worth a read: https://stackoverflow.com/q/2514548/2943403 — mickmackusa, Sep 06 '19 at 20:17

score 1 · Accepted Answer · answered Sep 05 '19 at 20:59

Change the joins with #__mijoshop_product_special and #__mijoshop_flordeco_product_special_percentage to LEFT JOIN so that it won't restrict the results only to products that have matches in this table.

Also, you don't need to use SELECT DISTINCT when you use GROUP BY p.product_id; since there's only 1 row for each product ID, you can't get any duplicates. However, it also doesn't make sense to use GROUP BY when you're not using any aggregation functions, like SUM() or COUNT(). If all these tables are 1-to-1 correspondences, you shouldn't get any duplicates that need to be removed with either option.

mysql return result from table related to other tables

1 Answers1