Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Location
    Usually at home
    Posts
    20

    Unanswered: Referring to a Field in a Previous Record or Next Record

    Hi,

    I am running PHP and MYSQL and I am struggling with some of the PHP syntax when it comes to manipulating record sets from MYSQL.

    I know how to do basic query for example:
    $result = mysql_query($query);

    And I know how to loop through an array:
    while ($row = mysql_fetch_array ($result, MYSQL_ASSOC))

    But what I can’t find is the proper syntax to refer to a field in the previous, or next record, contained within $result.

    I need to be able to do this for some values for some pretty funky calculations.

    Can anyone help?
    Regards
    BBB

    By day, mild mannered number cruncher.
    By night, caped crime fighter.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so store it in a temporary variable

    eg
    Code:
    $PreviousValue = 0;
    while ($row=mysql_fetch_array($sqlresult))
    { //do something with your resultset
      $avalue = $row['mycolumnname'] * $PreviousValue;
      $PreviousValue = $row['mycolumnname'];
    }
    or develop a subquery which brings the 'previous' value into the main query
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2008
    Location
    Usually at home
    Posts
    20
    No offense healdem,

    But if thats the best PHP has to offer as a method to get a previous record from a recordset then PHP is a poor programming language.

    Isn’t there an index number or ordinal setup for the record set? Can’t I reference the rows index number in order to bring back that rows data?

    If this can't be done is there a way to convert $result into an array before I start calculations?
    Last edited by blue_bottle_boy; 08-15-08 at 19:57.
    Regards
    BBB

    By day, mild mannered number cruncher.
    By night, caped crime fighter.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by blue_bottle_boy
    No offense healdem,

    But if thats the best PHP has to offer as a method to get a previous record from a recordset then PHP is a poor programming language.

    Isn’t there an index number or ordinal setup for the record set? Can’t I reference the rows index number in order to bring back that rows data?

    If this can't be done is there a way to convert $result into an array before I start calculations?
    the resuts from a SQL query are in an Array.. an associative array. yu can reset the array iteration methods, but afaik there is no method to refer back to a specific index in array.. PHP doesn't support that type of array.

    so if thats a problem and you are unable to find the work around then so be it. it doesn't make PHP a poor language (I think there are lots of other areas which may make it a poor language... but for waht its designed for (primarily as a programming language for server based web interactions its pretty good in my books.

    it isn't perfect (no language ever is, but considering its costs and user/developer support base its pretty useful none the less.. it even has its place as a batch control general PC language.... (would I recommend it for that NO Id suggest VBS if you Av product will stomach it)).
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2008
    Location
    Usually at home
    Posts
    20

    Found A Work Around

    For anyone who is interested I have come across a lateral approach to my problem of getting a field value in the previous, or next record, contained within $result.

    What you need to do is:


    a) Convert the mySQL $result into a multidimensional array. Like such in this function:

    function mysql_fetch_all($result) {

    //returns a numeric array of associative arrays containing an entire result set.
    $all = array();
    while ($all[] = mysql_fetch_assoc($result)) {}
    return $all;

    }


    b) Then use a combination of row index number and field name such as below to call the previous field value


    $Rst_array = mysql_fetch_all($result);

    for ($row = 0; $row < count($Rst_array)-1; $row++){
    $n = $row -1;
    $calc=($Rst_array[1]["Lwr_Lmt_Val"]) * $Rst_array[$n]["Int_Rte"]+ $Rst_array[$row]["Instal_Amt"];
    }



    Your can check how your multidimensional array is formed by looping through it like such:

    for ($row = 0; $row < count($Rst_array)-1; $row++){

    echo "<li><b>The row number $row</b>";
    echo "<ul>";

    foreach($Rst_array[$row] as $key => $value) {
    echo "<li>" .$key." : ".$value."</li>";
    }
    echo "</ul>";
    echo "</li>";
    }
    echo "</ol>";
    Last edited by blue_bottle_boy; 08-20-08 at 19:27.
    Regards
    BBB

    By day, mild mannered number cruncher.
    By night, caped crime fighter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •