|
|
Posted by moosus on 09/19/06 22:33
IchBin
I originally set out to use a CREATE VIEW in MySQL which I assume is like
what you are suggesting
My problem is that my host doesn't have MySQL 5 installed on the server and
our version does not support views.
... I know this is sort of moving in the MySQL field but could I read email
group of 10 and them write them all to a table which I have created (not
really a temp table). Once all 3 sample have been written to the table then
do a new select and sort to get the latest 3
Will this hammer MySQL too much?
.... Thinking
Cheers IchBin you might have put me onto something here :)
Cheers
moosus
in article ru6cnZ9qMKXmho3YUSdV9g@ptd.net, IchBin at weconsul@ptd.net wrote
on 20/9/06 2:31 AM:
> 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.
>
Navigation:
[Reply to this message]
|