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: "",…}