Anyone know how to do this? I have a standard 3D array:
$item[0]['title']
$item[0]['content']
$item[0]['date']
$item[1]['title']
$item[1]['content']
$item[1]['date']
etc...
How do I sort it based on date so the most recent item (the one with
the highest date) is $item[0] ?
Do I need a custom function or is there something built in that I
don't know about/can't find?
Thanks in advance
Ciar=E1n
Ciaran schrieb:
> Anyone know how to do this? I have a standard 3D array:
>
> $item[0]['title']
> $item[0]['content']
> $item[0]['date']
> $item[1]['title']
> $item[1]['content']
> $item[1]['date']
> etc...
It's _still_ about changing the SELECT. You are trying at the wrong end.
SELECT stuff,foo,bar,date,...,...
FROM t1,t2,t3,...
WHERE .................
ORDER BY date DESC
LIMIT 1
Now you only get the results for the highest date value.
OLLi
--
"You know what? If you were my mom I'd smoke pot too!"
[Rex to Bree, DH 109]
Ciaran wrote:
> Anyone know how to do this? I have a standard 3D array:
>
> $item[0]['title']
> $item[0]['content']
> $item[0]['date']
> $item[1]['title']
> $item[1]['content']
> $item[1]['date']
> etc...
>
> How do I sort it based on date so the most recent item (the one with
> the highest date) is $item[0] ?
> Do I need a custom function or is there something built in that I
> don't know about/can't find?
I think the function is called usort. You can pass a callback function
that in your case should compare the correct array fields of the "2D"
arrays that are passed.
If you have never worked with callbacks, look them up as well. They can
be plain functions, class methods or instance methods.
sorting does not necessarily have to take place in the database itself,
but as Oliver said, it can be more optimal to do it on the database
server than to do it in PHP. The database uses an index if possible, so
it is already sorted.
Best regards
Ciaran wrote:
> Anyone know how to do this? I have a standard 3D array:
>
> $item[0]['title']
> $item[0]['content']
> $item[0]['date']
> $item[1]['title']
> $item[1]['content']
> $item[1]['date']
> etc...
>
>
> How do I sort it based on date so the most recent item (the one with
> the highest date) is $item[0] ?
> Do I need a custom function or is there something built in that I
> don't know about/can't find?
>
> Thanks in advance
> Ciarán
>
It's built in. array_multisort.
(I may not be using this function the best way, and if I'm missing a
simpler way to do this then hopefully someone will point that out. This
function does somewhat baffle me, but by trial and error I have found
that this works.)
While building this structure - create the $date array:
$item[0]['title']
$item[0]['content']
$item[0]['date']
$date[0] = $item[0]['date']
$item[1]['title']
$item[1]['content']
$item[1]['date']
$date[1] = $item[1]['date']
etc...
Then this will work
array_multisort($date, SORT_DESC, $item);
--
*****************************
Chuck Anderson Boulder, CO
http://www.CycleTourist.com
Nothing he's got he really needs
Twenty first century schizoid man.
***********************************
On Dec 2, 12:21 am, Chuck Anderson
> Ciaran wrote:
> > Anyone know how to do this? I have a standard 3D array:
>
> > $item[0]['title']
> > $item[0]['content']
> > $item[0]['date']
> > $item[1]['title']
> > $item[1]['content']
> > $item[1]['date']
> > etc...
>
> > How do I sort it based on date so the most recent item (the one with
> > the highest date) is $item[0] ?
> > Do I need a custom function or is there something built in that I
> > don't know about/can't find?
>
> > Thanks in advance
> > Ciar=E1n
>
> It's built in. array_multisort.
>
> (I may not be using this function the best way, and if I'm missing a
> simpler way to do this then hopefully someone will point that out. This
> function does somewhat baffle me, but by trial and error I have found
> that this works.)
>
> While building this structure - create the $date array:
> $item[0]['title']
> $item[0]['content']
> $item[0]['date']
>
> $date[0] =3D $item[0]['date']
>
> $item[1]['title']
> $item[1]['content']
> $item[1]['date']
>
> $date[1] =3D $item[1]['date']
>
> etc...
>
> Then this will work
>
> array_multisort($date, SORT_DESC, $item);
>
Cool solution there Chuck - best one so far by my reckoning! My select
statement is too complicated for me to sort results by date as Oliver
suggested - It's selecting various fields across multiple tables.
Besides the php solution has other advantages for my page design.
I'll try out the array_multisort tomorrow and let you know how I get
on.
Thanks for all replies,
Ciar=E1n
Ciaran schrieb:
> Cool solution there Chuck - best one so far by my reckoning! My select
> statement is too complicated for me to sort results by date as Oliver
> suggested - It's selecting various fields across multiple tables.
That doesn't matter. If your query is "too complicated", you can always
work with subqueries:
SELECT wanted_field_1,wanted_field_2
FROM (SELECT with your complicated query)
AS subtable
ORDER BY field_you_want_to have_the_max_value DESC
LIMIT 1
You are learning at the wrong end! If you say "my select statement is
too complicated" you are saying "I should learn to understand my query"
and not "It's bloated already, so let me find an even more bloated way
to search through my results".
Rule of thumb: If you have to sort/rearrange/manipulate your database
query result afterwards, then your query was wrong in the first place.
MySQL in version 5 has matured on the language level. You can even store
your complicated query as a VIEW and then select the maximum from that
view instead.
=> Invest one or two hours in reading some docs on http://dev.mysql.com
OLLi
--
No, that's me talking. I'm the doctor, you're the police woman, remember?
[Fitz on Cracker 206]
Oliver Grätz wrote:
> Ciaran schrieb:
>> Cool solution there Chuck - best one so far by my reckoning! My select
>> statement is too complicated for me to sort results by date as Oliver
>> suggested - It's selecting various fields across multiple tables.
>
> That doesn't matter. If your query is "too complicated", you can always
> work with subqueries:
>
> SELECT wanted_field_1,wanted_field_2
> FROM (SELECT with your complicated query)
> AS subtable
> ORDER BY field_you_want_to have_the_max_value DESC
> LIMIT 1
>
Subqueries are almost never correct. Joins are much better.
> You are learning at the wrong end! If you say "my select statement is
> too complicated" you are saying "I should learn to understand my query"
> and not "It's bloated already, so let me find an even more bloated way
> to search through my results".
>
> Rule of thumb: If you have to sort/rearrange/manipulate your database
> query result afterwards, then your query was wrong in the first place.
>
> MySQL in version 5 has matured on the language level. You can even store
> your complicated query as a VIEW and then select the maximum from that
> view instead.
>
> => Invest one or two hours in reading some docs on http://dev.mysql.com
>
> OLLi
>
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Jerry Stuckle schrieb:
> Oliver Grätz wrote:
>> If your query is "too complicated", you can always
>> work with subqueries:
>>
>> SELECT wanted_field_1,wanted_field_2
>> FROM (SELECT with your complicated query)
>> AS subtable [...]
>=20
> Subqueries are almost never correct. Joins are much better.
1. This was an explicit response to "my query is too complicated" and
the whole complicated query should be encapsulated as a subquery.
2. You are not stating your argument with any explanation. It reads like
"Blue is lame, red is much better."
3. Subqueries are aqn important part of SQL nad the fact that they can
often be replaced by joins does not render them incorrect. There is
always more than one way to do it.
4. Please stop full-quoting when you are in fact just anserinfg to one
sentence.
OLLi
--=20
According to my calculations the problem doesn't exist.
On Mon, 03 Dec 2007 12:00:05 +0100, Oliver Grätz
wrote:
> Jerry Stuckle schrieb:
>> Oliver Grätz wrote:
>>> If your query is "too complicated", you can always
>>> work with subqueries:
>>>
>>> SELECT wanted_field_1,wanted_field_2
>>> FROM (SELECT with your complicated query)
>>> AS subtable [...]
>>
>> Subqueries are almost never correct. Joins are much better.
>
> 2. You are not stating your argument with any explanation. It reads like
> "Blue is lame, red is much better."
Which is why this should be a discussion in some sql-related ng in which
case more details and background can and will be stated.
> 3. Subqueries are aqn important part of SQL nad the fact that they can
> often be replaced by joins does not render them incorrect. There is
> always more than one way to do it.
General concensus is joins are both more portable and in most cases a lot
faster. You can use subqueries offcourse, that doesn't mean it's the best
solution. In php is possible, yet we all strongly advise
against it...
--
Rik Wasmus
Rik Wasmus schrieb:
> General concensus is joins are both more portable and in most cases a lot
> faster. You can use subqueries offcourse, that doesn't mean it's the best
> solution. In php is possible, yet we all strongly advise
> against it...
Yes it IS true, but whether that's true is off-topic here since my
advice was a direct response to someone who said his query was too
complex. So, telling him to change it and use more joins is pointless.
The example was only brought up for one reason: to illustrate the
possibility to completely detach the ORDER BY and LIMIT part from the
rest of the "complicated query" as leverage against the "too complicated
to change" argument.
OLLi
--
Bender: "There's someone you know!"
[Futurama 101]
Oliver Grätz wrote:
> Jerry Stuckle schrieb:
>> Oliver Grätz wrote:
>>> If your query is "too complicated", you can always
>>> work with subqueries:
>>>
>>> SELECT wanted_field_1,wanted_field_2
>>> FROM (SELECT with your complicated query)
>>> AS subtable [...]
>> Subqueries are almost never correct. Joins are much better.
>
> 1. This was an explicit response to "my query is too complicated" and
> the whole complicated query should be encapsulated as a subquery.
>
I understand. And I still stand by my statement.
> 2. You are not stating your argument with any explanation. It reads like
> "Blue is lame, red is much better."
>
Because SQL is not PHP, and this is a PHP newsgroup. But you can get a
lot of help and explanation on how to structure your SQL statements in
the appropriate newsgroup - such as comp.databases.mysql. That's where
the MySQL experts hang out.
> 3. Subqueries are aqn important part of SQL nad the fact that they can
> often be replaced by joins does not render them incorrect. There is
> always more than one way to do it.
>
Just because they are legal doesn't mean they are correct. There are
many reasons why JOINs are better.
> 4. Please stop full-quoting when you are in fact just anserinfg to one
> sentence.
>
> OLLi
>
Please learn to post SQL questions in a SQL newsgroup. This is a PHP
newsgroup, in case you haven't noticed the name.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Oliver Grätz wrote:
> Rik Wasmus schrieb:
>> General concensus is joins are both more portable and in most cases a lot
>> faster. You can use subqueries offcourse, that doesn't mean it's the best
>> solution. In php is possible, yet we all strongly advise
>> against it...
>
> Yes it IS true, but whether that's true is off-topic here since my
> advice was a direct response to someone who said his query was too
> complex. So, telling him to change it and use more joins is pointless.
> The example was only brought up for one reason: to illustrate the
> possibility to completely detach the ORDER BY and LIMIT part from the
> rest of the "complicated query" as leverage against the "too complicated
> to change" argument.
>
> OLLi
>
And for that question he should have been directed to a SQL newsgroup
instead of being given a bad answer.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Jerry Stuckle schrieb:
>
> Please learn to post SQL questions in a SQL newsgroup. This is a PHP
> newsgroup, in case you haven't noticed the name.
Please learn how to identify the original poster. I was ANSWERING.
OLLi
--
Blue Hawaii, piccadilly whore/A cocktail is what I'm longing for
[Hooverphonic]
Oliver Grätz wrote:
> Jerry Stuckle schrieb:
>> Please learn to post SQL questions in a SQL newsgroup. This is a PHP
>> newsgroup, in case you haven't noticed the name.
>
> Please learn how to identify the original poster. I was ANSWERING.
>
> OLLi
>
I responded to you because you gave the poor advice. A better response
would have been to redirect him to an appropriate newsgroup.
And note I did respond to the original poster, in another message.
You don't like my responses? Quite recommending such poor practices.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================