|
|
Posted by IchBin on 09/19/06 16:31
moosus wrote:
> IchBin
>
> I would love to keep my sort in my SQL but I am grabbing data from 3 tables
> I am sorting the data in the query from each table eg.
>
> $query = 'SELECT `sid`,`title`,`time` FROM `bass_stories` ORDER BY `time`
> DESC LIMIT 10';
>
> BUT
>
> It doesn't help once I have combined all 30 records as I only need the
> latest 10 out of my selection of 30
>
>
>
>
> in article lYacnSHTjKyvEZLYUSdV9g@ptd.net, IchBin at weconsul@ptd.net wrote
> on 19/9/06 4:20 PM:
>
>> moosus wrote:
>>> ... Ok making some head way
>>>
>>> Currently using the following to get all the results into a multidimensional
>>> array->
>>>
>>> $n = 0;
>>> if ($r = mysql_query($query)){
>>> while($breamrow = mysql_fetch_assoc($r)){
>>> $combined[$n] = array(
>>> 'row' => $n,
>>> 'sid' => $breamrow['sid'],
>>> 'title' => $breamrow['title'],
>>> 'time' => $breamrow['time']
>>> );
>>>
>>> $n = $n+1;
>>> }
>>> } else {
>>> die('<p>Could NOT retreive BREAM DATA'. mysql_error() .'</p>');
>>> }
>>>
>>>
>>> ... Weird for some reason $n++ wasn't working for me had to go with $n+1
>>>
>>> Anyhow print_r($combined) gives me what I want:
>>>
>>> Array
>>> (
>>> [0] => Array
>>> (
>>> [row] => 0
>>> [sid] => 200
>>> [title] => Towner Travels to Adelaide for First BREAM Win
>>> [time] => 2006-09-10 08:30:29
>>> )
>>>
>>> [1] => Array
>>> (
>>> [row] => 1
>>> [sid] => 199
>>> [title] => Friebe and Steer win VIC BREAM Classic at Mallacoota
>>> [time] => 2006-08-22 21:53:23
>>> )
>>>
>>>
>>>
>>> Now to sort() it ... Can I sort a multidimensional array?
>>> I have tried sort($combined['time']) with no joy ... Any suggestions?
>>>
>>> Cheers
>>> moosus
>>>
>>>
>>>
>>>
>>> in article C1359710.85D2%junk{@}fishingmonthly.com.au, moosus at
>>> junk{@}fishingmonthly.com.au wrote on 19/9/06 12:55 PM:
>>>
>>>> I have a page which I on which I run 3 independent queries and collect the
>>>> latest 10 entries from each table.
>>>> The tables are identical just the data is different.
>>>>
>>>> What I am trying to do is combine the 10 records I get from each table then
>>>> display the 10 most recent
>>>>
>>>> I have been trying to get each of the 3 queries to output to a
>>>> multidimensional array using the following:
>>>>
>>>> $bream = array($n, 'SID','TITLE','TIMESTAMP' );
>>>> $n = 0;
>>>> if ($r = mysql_query($query)){
>>>> while($breamrow = mysql_fetch_array($r)){
>>>> $bream = array(
>>>> 'row' => '$n',
>>>> 'sid' => $breamrow['sid'],
>>>> 'title' => $breamrow['title'],
>>>> 'time' => $breamrow['time']
>>>> );
>>>> $n = $n++;
>>>> }
>>>>
>>>> I would create 3 identical arrays
>>>>
>>>> $bream = array($n, 'SID','TITLE','TIMESTAMP' );
>>>> $bass = array($n, 'SID','TITLE','TIMESTAMP' );
>>>> $barra = array($n, 'SID','TITLE','TIMESTAMP' );
>>>>
>>>> Then I was looking at doing:
>>>>
>>>> $comb = array_merge($bream, $bass, $barra);
>>>>
>>>> My problem at the moment is that the $bream array is not getting all the
>>>> data from the query
>>>>
>>>> When I echo(count($bream)); I get 4 (I would have assumed 40)
>>>>
>>>> When I do
>>>>
>>>> foreach ($bream as $bream['row'] => $value){
>>>> print "<p>{$value['title']} - {$value['time']}</p>";
>>>>
>>>> I get
>>>>
>>>> $ - $
>>>>
>>>> 1 - 1
>>>>
>>>> K - K
>>>>
>>>> 2 - 2
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> ..... So long story short ... I'm completely lost
>>>>
>>>>
>>>> Any help ... Direction would be appreciated
>>>>
>>>> Cheers
>>>> moosus
>>>>
>> Keep your sort on the server by adding the ORDER BY clause to you SQL
>> statement.
>
I have done this in HSQLDB using a temp table. It is a little different
in MySQL. Basically you do a SELECT INTO TEMPTable table for the three
tables. Then select * from TEMPTable ORDER BY.
You could also create a SQL procedure where you can manipulate the data.
--
Thanks in Advance...
IchBin, Pocono Lake, Pa, USA http://weconsultants.phpnet.us
__________________________________________________________________________
'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor, Regular Guy (1952-)
Navigation:
[Reply to this message]
|