Page 1 of 1

[SOLVED] Accessing the first and last row values

Posted: Thu Jan 15, 2004 1:44 pm
by gomac
This may seem simple to most but I've been banging around for hours and can't figure it out.

How do I assign a variable to the first and last row only of an array returned from MySQL? I can count the rows, I can print the reults of the array, but I can't figure out the code to say that the first result in the array = $first and the last result = $last.

What I have so far is:

Code: Select all

$sum_sql = " SELECT SUM( snapshot.unit_value * snapshot.no_units ) AS sum"
        . " FROM snapshot, snapdate"
        . " WHERE security_id = 7 AND snapshot.date_id = snapdate.date_id"
        . " GROUP BY snapdate.date_id"
        . " ORDER BY snapdate.date_id DESC";
$sum_result = MySQL_query($sum_sql);
$num = mysql_numrows($sum_result);
$i=0;
while ($i < $num) &#123;
$total = mysql_result($sum_result,$i,"sum");
++$i;
$var = $total;
&#125;
This SELECT will produce a varying number of rows in different situations. However, one value is retrieved in each row and is assigned to $var.

Now I can print out each $var no problem, but what I want to do is take the first $var and the last $var and do some math on them.

$var[0] and $var[7] (or $var [$num-1]) aren't accessable - should I be doing something else inside the WHILE? Can I not access the contents of $var outside of the WHILE?

It seems that it should be simple to access the individual row values of an array, but it is escaping me - especially aggravating because I can print them by echoing $var inside the WHILE.

Thanks,
gord

Posted: Thu Jan 15, 2004 1:52 pm
by infolock
well, i think one way you can do this is like :

Code: Select all

echo 'this is my first result : '.$myarray[0]. '<br />';
for($i=0; $i<count($myarray); $i++)
{
   if ($i==count($myarray))
   {
      echo 'my last array value is : '.$myarray[$i];
   }
}
this might not be the simpliest and easiest way ( at school so i don't really test stuff here just give what I know when i'm at school ), but this should work..

edit : or maybe it should be for($i=0; $i<=count($myarray); $i++)

my *#$^% brain isn't working right today...

Posted: Thu Jan 15, 2004 2:24 pm
by kettle_drum

Code: Select all

&lt;?php

$first = $myarray&#1111;0];
$last = end($myarray);

?&gt;
That should work :)

Posted: Thu Jan 15, 2004 4:25 pm
by gomac
Thanks but neither of these work. My problem is not echoing the array but accessing the first and last rows in it and applying a variable to each.

kettle-drum suggests:

Code: Select all

$first = $myarray&#1111;0]; 
$last = end($myarray);
...but that only returns a row number for some reason. If anyone is still reading here's what I have. I have an array that is produced in this while statement:

Code: Select all

While( $rows = MySQL_fetch_array($sum_result)) &#123;
$sum = $rows&#1111;'sum'];
Now if I echo $sum in this WHILE the page returns:
4452.33
4409.72
4062.13
4119.19
4025.81
3781.98
3844.23

I don't want to echo - I want to assign $first = 4452.33 and $last = 3844.23, but all I can do is echo. end($sum) returns nothing, either within the while or after it....I stumped....been at this a while...

Posted: Thu Jan 15, 2004 4:32 pm
by Dr Evil
Have you tried:

Code: Select all

<?php
SELECT  MAX(values) AS top_value,  MIN(values) AS bottom_value FROM table
?>

Posted: Thu Jan 15, 2004 4:39 pm
by infolock
or you can still use the suggestion i gave, and just insert variable declarations where i used echo's...

Code: Select all

$first = $myarray[0];
for($i=0; $i<count($myarray); $i++) 
{ 
   if ($i==count($myarray)) 
   { 
      $last = $myarray[$i]; 
   } 
}

Posted: Thu Jan 15, 2004 4:40 pm
by markl999
I'd just go for the KISS solution myself :o

Code: Select all

$count = 0;
while( $rows = MySQL_fetch_array($sum_result)) &#123; 
$sum = $rows&#1111;'sum'];
$first = $count == 0 ? $sum : $first;
$count++;
$last = $sum;
&#125;
echo $first.' - '.$last;

Posted: Thu Jan 15, 2004 5:37 pm
by gomac
markl999 wrote:I'd just go for the KISS solution myself :o

Code: Select all

$count = 0;
while( $rows = MySQL_fetch_array($sum_result)) &#123; 
$sum = $rows&#1111;'sum'];
$first = $count == 0 ? $sum : $first;
$count++;
$last = $sum;
&#125;
echo $first.' - '.$last;
That's it!

Thanks muchly.

The SELECT MAX won't work as I'm doing a SUM in the selct for these values - or at least I don't know the SELECT syntax that would allow for a SUM and MAX...

gord

Posted: Fri Jan 16, 2004 3:43 am
by McGruff
Assuming you can't do it with query logic (always the preferred solution) try the mysql_data_seek() fn with offset = 0 and offset = mysql_num_rows.

I haven't fully grasped what you're aim is though or where to start to achieve it.