Dynamic sort for mysqli query

Question

I've been trying to get this query to work for a while ... and I just don't know how to build it in order for it to work. The WHERE options are working fine WHERE ".$follow." ".$active." ".$setPub." ".$setLetter." ".$setNumber." ".$setYear." ".$setCond."

But the sort by options are not returning the items in the order that I want.

Is it the way the query is built? Can that be causing the problem? Or is it just the query? I can't see the actual query because I'm using mysqli prepared statements. So I'm a little stuck.

if(array_key_exists('option', $sorts) && $sorts['option'] === "low-price-bids")
        {
            //echo "LOW PRICE BIDS";
            $order = "i.item_start, maxbid ASC";
            $sort = "maxbid IS NOT NULL";
        }

if(array_key_exists('option', $sorts) && $sorts['option'] === "low-price-no-bids")
        {
            //echo "LOW PRICE NO BIDS";
            $order = "i.item_start, maxbid ASC";
            $sort = "maxbid IS NULL";
        }

$q = $this->db->mysqli->prepare("SELECT c.ship_cost, 
                c.item_id, 
                i.id, 
                i.user_id, 
                i.item_title, 
                i.item_number, 
                i.item_year, 
                i.item_publisher, 
                i.item_condition, 
                i.item_start, 
                i.item_description, 
                i.active, 
                u.first_name, 
                u.last_name, 
                CAST(u.fb_id AS CHAR(50)) AS fb_id, 
                u.user_pic, 
                MAX(b.bid) AS maxbid, 
                COUNT(b.bid) AS bids, 
                p.publisher_name
                        FROM countries_ship c
                        JOIN items i
                            ON c.item_id = i.id
                        JOIN users u
                            ON i.user_id = u.id
                        LEFT JOIN bids b
                            ON i.id = b.item_id
                        LEFT JOIN publishers p
                            ON i.item_publisher = p.id
                        WHERE ".$follow." ".$active." ".$setPub." ".$setLetter." ".$setNumber." ".$setYear." ".$setCond." ".$sort."
                        GROUP BY i.id
                        ORDER BY ".$order." LIMIT 18");

Highest price return:

0: {ship_cost: "5", item_id: "624", id: "624", user_id: "62", item_start: "5", maxbid: "",…}
1: {ship_cost: "4", item_id: "623", id: "623", user_id: "62", item_start: "4", maxbid: "",…}
2: {ship_cost: "3", item_id: "622", id: "622", user_id: "296", item_start: "3", maxbid: "10",…}
3: {ship_cost: "2", item_id: "621", id: "621", user_id: "296", item_start: "2", maxbid: "",…}
4: {ship_cost: "0", item_id: "620", id: "620", user_id: "296", item_start: "1", maxbid: "",…}

Lowest price:

0: {ship_cost: "0", item_id: "620", id: "620", user_id: "296", item_start: "1", maxbid: "",…}
1: {ship_cost: "2", item_id: "621", id: "621", user_id: "296", item_start: "2", maxbid: "",…}
2: {ship_cost: "3", item_id: "622", id: "622", user_id: "296", item_start: "3", maxbid: "10",…}
3: {ship_cost: "4", item_id: "623", id: "623", user_id: "62", item_start: "4", maxbid: "",…}
4: {ship_cost: "5", item_id: "624", id: "624", user_id: "62", item_start: "5", maxbid: "",…}

Its hard to guess what "are not returning the items in the order" means,but I would say your WHERE clause transforms your LEFT join into an INNER join. — Mihai, Jun 08 '15 at 06:36
They're just not sorted from low to high or from high to low. — Ciprian, Jun 08 '15 at 06:41
May be this q/a will help -- http://stackoverflow.com/questions/514943/php-mysql-order-by-two-columns — Dexter Huinda, Jun 08 '15 at 07:05
I tried that ... but it didn't work. Just occurred to me that I'm working with data which I changed and altered while testing. I ll delete everything and add new data and see what happens. — Ciprian, Jun 08 '15 at 07:08
from the result that you posted, the order by item_start ASC, max_bid DESC actually did its job, unless you're expecting some other result? take a look at item_start's = 25 for example, the order by ascending and descending is working — Dexter Huinda, Jun 08 '15 at 07:23
I don not get where is your problem, you results are fine based on your query! you are sorting the result first by `i.item_start` and then `maxbid` and with what I see in the sample results, it OK. for example, you have 4 records with `item_start: 15` and then in both samples the records are sorted accordingly with correct `maxbid` after it. correct me if I'm wrong. — EhsanT, Jun 08 '15 at 07:24
Well I wanted the query to take into consideration the maxbid ... so in the high price, I was hoping to get the maxbid row first. Does that make sense? After someone bids on an item, the `item_start` value becomes irrelevant. But I think I'll do `Low price - Bids` and `Low price - No bids`. How would I set that up? I can add another clause to the where and do a `maxbid IS NOT NULL` — Ciprian, Jun 08 '15 at 07:26
Again, take a look at the four rows with item_start = 25, the max_bid column is ordered by DESC in the high price and ordered in ASC in the low price, the query is working fine. Anyhow, it seems you edited the result set while I was typing this, lmao. The field `item_start` is always relevant, your query requires it to be in `ASC` mode all the time, and you can see that `max_bid` is clearly working ONLY when `item_start` is the same in the result set. — Dexter Huinda, Jun 08 '15 at 07:29
does not changing the place of `i.item_start` and `maxbid` on the `order by` do the trick? — EhsanT, Jun 08 '15 at 07:35
Ok ... Please look at my question. I'm so glad I posted this. Maybe I can finally finish. So since the query is working ... I want to do bids and no bids by adding a new where clause. But the way I have it set up now is giving me `You have an error in your SQL syntax; ...... near 'maxbid IS NOT NULL`. How can I set it up to work? — Ciprian, Jun 08 '15 at 07:38
Review your query statement, the where clause should have the 'AND' or 'OR' [whichever you require] between each conditions — Dexter Huinda, Jun 08 '15 at 07:40

Dynamic sort for mysqli query

0 Answers0