$dbresult = $smcFunc['db_query']('', "SELECT COUNT(*) AS total FROM (SELECT MAX(COL_2) FROM smf_test WHERE COL_3 = '19' GROUP BY COL_1 ) AS m ");
Hi, Can anybody explain why the above is not working? The query works but doesn't work through $smcFunc . I think its to do with FROM (SELECT but dont know how to write the same thing that's $smcFunc compatible.
Help appreciated.
Using the actual query might help figure out a better way to do it. If you're using an alias of m, that kind of suggests you're querying the messages table and there's all kinds of ways to deal with that.
Hi, not really to do with the main tables... It's an extension. M wad just a random letter I picked.
Basically:
Out of many rows I want to get the greatest number for a group Col.
I'm sorry, I don't understand how you get that from your original query.
Yes sorry, that's the second part,I was at my father in laws, Father's Day BBQ .
This query just counts the same records for pagination. Works when I run the query in my editor. But when I add the query in the actual page with the smcfunc it fails somewhere.
Yes, $smcFunc doesn't like subqueries. There's probably a better way to actually achieve your goal but you've been reluctant to provide anything useful so I'm just going to have to give you the half-assed way of doing it, which is to turn off query checking. Oh, and the fact you're using bare ' in there won't help either as that trips the anti-injection filter.
$dbresult = $smcFunc['db_query']('', '
SELECT COUNT(*) AS total
FROM (SELECT MAX(COL_2) FROM smf_test WHERE COL_3 = {string:value} GROUP BY COL_1 ) AS m',
array(
'value' => '19',
),
array(
'security_override' => true,
)
);
Sorry didn't mean to be cagey, i actually thought it might be simpler to explain.
This is the second query that actually pulls the data:
$dbresult = $smcFunc['db_query']('', "SELECT * FROM
(SELECT MAX(ID_BID) FROM {db_prefix}class_bids WHERE ID_MEMBER = " . $user_info['id'] . " GROUP BY ID_LISTING) AS m
INNER JOIN
(SELECT i.ID_LISTING,i.currency,i.title,i.views,i.expiredate,i.commenttotal,i.totalbids,i.ID_BID AS bidID,b.date,b.bid_rejected,b.bid_accepted,b.amount,b.ID_BID FROM {db_prefix}class_bids AS b
LEFT JOIN {db_prefix}class_listing AS i ON (b.ID_LISTING = i.ID_LISTING)
WHERE b.ID_MEMBER = " . $user_info['id'] . ")
AS a ON (m.maxBID = a.ID_BID)");
The original query was:
$dbresult = $smcFunc['db_query']('',
"SELECT i.ID_LISTING, i.currency, i.title, i.views, i.expiredate, i.commenttotal, i.totalbids,i.ID_BID, b.date, b.bid_rejected, b.bid_accepted, b.amount, b.ID_BID bidID , MAX(b.amount)as memberMax
FROM {db_prefix}class_bids as b, {db_prefix}class_listing AS i
WHERE b.ID_LISTING = i.ID_LISTING AND b.ID_MEMBER = " . $user_info['id'] . "
GROUP BY b.ID_LISTING
ORDER BY b.ID_BID DESC LIMIT $context[start]," . $modSettings['class_set_listings_per_page']);
What i was trying to achieve is, a list of 'bids' highest bids for the user, instead of the original which pulls all the bids for the user.
P.S what do you mean by bare ' . Do you mean $smcFunc['db_query']('', ???
also how do i refer to you "the coder with no name formally known as coder"
Thanks for the help
// Sub selects? We don't use those either.
elseif (preg_match('~\([^)]*?select~s', $clean) != 0)
$fail = true;
I think what you are saying is that smcFunc doesn't allow sub queries(above), and to achieve what i need to achieve is to do it the long way round with separate queries. Am i right?
Not being a pro, i have always thought the less queries to achieve the goal the better.(don't understand why you cant use sub selects, but then again not a pro, i don't understand a lot. In my world, if it works, its good......LOL)
$smcFunc doesn't allow sub-selects by default, yes. And it also helps you to improve the readability of your query, amongst other goodies.
Lainaai have always thought the less queries to achieve the goal the better
Not really. Several small and efficient queries can be better than a huge, inefficient one. Sub-queries are easily on the inefficient type :P
In your case, I understand that it might work without major issues because you are working in your own tables that, most likely will not growing to the several thousand rows. But it also depends how often are such queries be executed...
For that, you can choose to disable $smcFunc's verifications with "security_override" as
‽ pointed out 3 posts above.
LainaaI think what you are saying is that smcFunc doesn't allow sub queries(above), and to achieve what i need to achieve is to do it the long way round with separate queries. Am i right?
Correct. It also doesn't allow you to put ' inside the query itself directly, because you are supposed to use parameterisation to insert parameters, because a query that lets a raw ' in the query is probably open to SQL injection, which is a bad thing. Subselects are prevented for two reasons: firstly, SMF 2.0 was mostly built at a time when MySQL 3.23 was still common - before subselects were even a thing so preventing mod authors doing things that wouldn't work was considered a good idea, and secondly it's another route for injection.
Number of queries is a fairly terrible metric for judging performance on its own. Subselects are technically separate queries anyway, but SMF doesn't know how to count that. But my experience has been that smart joins - or completely separate queries - can be more efficient.
Excellent .... thank you to both ‽ and margarett. I'm happy with the explanations and understand now.