How do you get the last record in a set of query results?[Resolved]

[ober0330]

$query = "SELECT ID FROM Tablexyz WHERE condition = x
$result = mssql_query($query);
// ---- get value in last element of the array --- // 

Ideas? I'm trying to get the last record returned from a query.

[The Hobo]

What are you trying to do? Why do you want the last record? Do you need ALL of the records later on? Do you need them in reverse order?

If so, then there would be an easier way if you change your SQL query.

[ober0330]

This is back to a project I was asking questions about earlier. I'm traversing through records. The page displays one record at a time (~20 fields) and I have a "Next" and "Previous" button. If they are on the first record that is returned by the query, and they hit the "Previous" button, I want to show the last record according to the query.

I had this all working and it walked through the table one after another, but now I've added a search function and I'm using 1 variable and a loop to find which record I should be on next.

The current code, if it helps (I haven't added any special-case checks to the Previous ("last") part):

 if(isset($_GET['numid']))
{
    $loopnum = $_GET['numid'];

    switch($_GET['dir'])
    {
    //----------------Start at first record----------------------------------------------
    //---------------------------------------------------------------------------------------
    case "first":
        $row = mssql_fetch_array($result2);
        $id = $row[0];    
        $loopnum++; 

    break;    
    //----------------Go forward one record----------------------------------------------
    //---------------------------------------------------------------------------------------
    case "next":
    
        if($_GET['numid'] == "-1")
        {
            $row = mssql_fetch_array($result2);
            $row = mssql_fetch_array($result2);
            $loopnum = 1;
        }
        else
        {
            while(($row = mssql_fetch_array($result2)) && ($i != $_GET['numid']))
            {
                $i++;
            }
            $loopnum++;     
        }
        $id = $row[0];
        
    
    break;
    
    //----------------Go back one record----------------------------------------------
    //---------------------------------------------------------------------------------------
    case "last":
    
        while(($row = mssql_fetch_array($result2)) && ($i < $_GET['numid']))
        {
            $i++;
        }
        $id = $row[0];
        $loopnum--; 

    break;
    }// end of switch
}// end else for isset of ID 

[The Hobo]

Where is $result2 set at? I'm going to type up an example of how I'd do this, and maybe you could take what you need from it.

I just hope someone else doesn't answer before I'm done and I end up wasting my time.

[The Hobo]

Okay, here's a quick example of how I would do this. I just whipped it up in the past few minutes, so it may be a bit tacky:

<?php
    // CONNECT TO THE DATABASE:
    mysql_connect('localhost', 'user', 'pass');
    mysql_select_db('dbName');

    // GET THE TOTAL NUMBER OF RECORDS:
    $result = mysql_query("SELECT COUNT(*) FROM tableName");
    $rows = mysql_fetch_array($result);
    $count = $rows[0] - 1; // MYSQL LIMIT IS 0-BASED, SO SUBTRACT 1

    // FIGURE OUT WHAT RECORD TO GET:
    if (isset($_REQUEST['action'])) {
        switch ($_REQUEST['action']) {
            // USER PRESSED NEXT:
            case 'next':
                if ($_REQUEST['currNum'] == $count) {
                    // AT THE END OF RECORDS, GO BACK TO FIRST:
                    $currentNum = 0;
                } else {
                    // INCREMENT CURRENT RECORD NUMBER:
                    $currentNum = $_REQUEST['currNum'] + 1;
                }
                break;

            // USER PRESSED PREVIOUS:        
            case 'previous':
                if ($_REQUEST['currNum'] == 0) {
                    // AT THE BEGINNING OF RECORDS, GO TO LAST:
                    $currentNum = $count;
                } else {
                    // DECREMENT CURRENT RECORD NUMBER:
                    $currentNum = $_REQUEST['currNum'] - 1;
                }
                break;

            // SOMETHING ELSE WAS GIVEN, GET FIRST RECORD:
            default:
                $currentNum = 0;
        }

    } else {
        // NO ACTION WAS SET, GET FIRST RECORD:
        $currentNum = 0;
    }

    // BUILD THE SQL STATEMENT BASED ON $CURRENTNUM:
    $sql = "SELECT * FROM news_news LIMIT $currentNum, 1";

    // GET THE RECORD:
    $rows = mysql_query($sql) or die(mysql_error());
    if ($row = mysql_fetch_array($rows)) {
        echo $row['fieldName'] . '<br /><br />';

        // ECHO THE PREVIOUS AND NEXT LINKS
        echo '<a href="?action=previous&currNum=' . $currentNum . '">previous</a> | 
        <a href="?action=next&currNum=' . $currentNum . '">next</a>';
    } else {
        // FAILED TO GET RECORD:
        die('Error: Failed to get record.');
    }
?>

[ober0330]

Yours won't work as it's based on sequential records. Here is the entire code and it's working now. I've also added some status variables which aren't displayed in the code below, but are later in the code:

 //--- get the maximum id ---------
    $wclause = 0;
    $maxid=0;
    $queryw = "";
    $query2 = "SELECT ID FROM [Cell Equipment Complete]";
    $query3 = "SELECT MAX(ID) FROM [Cell Equipment Complete]";
    
    // === if set, then we're coming from the search page! ========
    if(isset($_GET['ID']))
    {
        if($_GET['ID'] != "")
        {
            $queryw .= " WHERE ID = '" . $_GET['ID'] . "'";
            $wclause = 1;
        }
        if($_GET['CELLNUMBER'] != '*')
        {
            if($wclause == 0)
            {
                $queryw .= " WHERE CELLNUMBER = '" . $_GET['CELLNUMBER'] . "'";
                $wclause = 1;
            }
            else
                $queryw .= " AND WHERE CELLNUMBER = '" . $_GET['CELLNUMBER'] . "'";
        }
        if($_GET['TAG_ID'] != '*')
        {
            if($wclause == 0)
            {
                $queryw .= " WHERE TAG_ID = '" . $_GET['TAG_ID'] . "'";
                $wclause = 1;
            }
            else
                $queryw .= " AND TAG_ID = '" . $_GET['TAG_ID'] . "'";
        }
        if($_GET['TAG_NUM'] != '*')
        {
            if($wclause == 0)
                $queryw .= " WHERE TAG_NUM = '" . $_GET['TAG_NUM'] . "'";
            else    
                $queryw .= " AND TAG_NUM = '" . $_GET['TAG_NUM'] . "'";
        }
    } // end isset check
    $query2 .= $queryw; 
    $result2 = mssql_query($query2); 
    $maxid = mssql_num_rows($result2);
    
    // ==== get the last record in the results ==========
    $query3 .= $queryw . " ORDER BY MAX(ID) DESC";
    $result3 = mssql_query($query3);
    $row = mssql_fetch_array($result3);
    $lastrecord = $row[0];
//-----------------------------        


//=============================================================================================
//=============================================================================================
//=============This method is for traversing through records specified by a search=============
//=============================================================================================
//=============================================================================================


$id = 0;
$recordpos = 0;
if(isset($_GET['numid']))
{
    $loopnum = $_GET['numid'];

    switch($_GET['dir'])
    {
    //----------------Start at first record----------------------------------------------
    //---------------------------------------------------------------------------------------
    case "first":
        $row = mssql_fetch_array($result2);
        $id = $row[0];    
        $recordpos = 1;

    break;    
    //----------------Go forward one record----------------------------------------------
    //---------------------------------------------------------------------------------------
    case "next":
    
        if($_GET['numid'] <= 0) // === special case, move to first record
        {
            $row = mssql_fetch_array($result2);
            $row = mssql_fetch_array($result2);
            $loopnum = 1;
            $recordpos = 1;
        }
        elseif($loopnum >= $maxid) // === on last record, moving to first
        {
            $row = mssql_fetch_array($result2);
            $loopnum = 0;
            $recordpos = 1;
        }        
        else
        {
            $i = 0;
            while(($row = mssql_fetch_array($result2)) && ($i != $loopnum))
            {
                $i++;
                $recordpos++;
            }
            $recordpos++;
        }
        $id = $row[0];        
        
    
    break;
    
    //----------------Go back one record----------------------------------------------
    //---------------------------------------------------------------------------------------
    case "last":
    
        $i = 0;
        if($loopnum < 0) // === on first record, moving to last (backwards)
        {
            $id = $lastrecord;
            $loopnum = $maxid-1;
            $recordpos = $maxid;
        }
        else // === traversing backwards
        {
            while(($row = mssql_fetch_array($result2)) && ($i < $loopnum))
            {
                $i++;
                $recordpos++;
            }
            $recordpos++;
            $id = $row[0];
        }        

    break;
    // ============ some sort of error, grab the first id from the results ============
    default: 
        $row = mssql_fetch_array($result2);
        $id = $row[0];        
    }// end of switch
}
else
{
    echo "<p class=warn>No Record was found!!";
}// end else for isset of ID 

[The Hobo]

Originally posted by ober5861
Yours won't work as it's based on sequential records.

What does that mean?

[ober0330]

To move to the next record, all you're doing is adding one. It has to have the ability to move to a specific record (returned from the query).

So basically you have an array of records returned by a query, and when you hit next, you have to grab that id value from the array by looping through it with a step counter variable.

[The Hobo]

It does have the ability to go to a specific record. I still don't understand why my code wont work for you.

[ober0330]

Because all you're doing is grabbing the number of records from a table. You then add "1" to the current record that you are on to move to the next one.

I am filtering my results from a search form (not shown, but the resulting query is). This query creates an array of IDs. These IDs are not in order like (1,2,3,4,5). They are more like (324,567,2345,23457). So your method cannot work as you propose.

Does that make sense?

It doesn't matter... my method works fine.

[The Hobo]

My method will work regardless of the sequence of IDs. It is not working by the ID number, but by the actual order of the results.

Even the table I tested it on has holes, such as 1, 3, 4, 5, 8, 11, 14. It works just fine.

My code is a lot cleaner, and saves on memory space. You can go with yours if it works. But if you want to be more efficient, I'd take a good look at my code again.

[ober0330]

I'll take a look at this again tomorrow. I don't fully understand the $_REQUEST array (never saw/used it before) and how all that works, but I'll give it a shot.

[The Hobo]

The $_REQUEST[] array contains $_GET, $_POST, $_COOKIE, and others all in one.

I just used it as instinct since in a lot of my scripts, the direction can come from either $_GET or $_POST.

But in my example, you can replace $_REQUEST with $_GET and it will work the same.

[ober0330]

Well then I really don't understand how your script works then. I'm not passing the results from the query through the URL. As you can see, I build the query and execute it right before the script to move to the next or previous.

Like I said, I'll play with it, but I really don't think it's going to do what I want.

[john tindell]

i thought $_GET, $_POST, and $ _COOKIE were all deprecated and wasnt wise to use them?

Correct me if im wrong

[ober0330]

See the note posted at the top of this forum. They are super global variable arrays that are to take the place of user-defined global variables. They (the ones you listed) are not depreciated (sp?) AFAIK.

I don't know how else you would go about retrieving form data.

[The Hobo]

Originally posted by john tindell
i thought $_GET, $_POST, and $ _COOKIE were all deprecated and wasnt wise to use them?

Correct me if im wrong

That's the exact opposite of the truth. Automatic globals (ones created when register_globals is on) are deprecated and it's unwise to use them.

[The Hobo]

Originally posted by ober5861
I'm not passing the results from the query through the URL.

Neither am I. What I am passing through the URL is the current record number (not ID), so that when the user clicks the next or previous link, the script knows what record he was on, and knows how to navigate to the next one.[/B][/QUOTE]

Originally posted by ober5861
As you can see, I build the query and execute it right before the script to move to the next or previous.

So do I. But it looks like you're looping through records, which means you're pulling out more than one record. My code will query the database and return only ONE record. So it saves memory and processing time (no loops).

Originally posted by ober5861
Like I said, I'll play with it, but I really don't think it's going to do what I want.

I think it will do what you want. Although, maybe I'm not sure what that is. But I think I do.

[phpman]

you guys are doing more work than you have to. use the offset of the query to get what you want. doesn't matter if you searched or not.


http://www.snippetlibrary.com/tutori...s.php/2/16/187

[The Hobo]

Originally posted by phpman
you guys are doing more work than you have to. use the offset of the query to get what you want. doesn't matter if you searched or not.


http://www.snippetlibrary.com/tutori...s.php/2/16/187

That seems like more work than what I'm doing. And what you said in your post also doesn't make much sense to me. But I don't think it's me. I think it just doesn't make sense.

[phpman]

yeah the offset

limit 10, 30



thenyou can use a link to get the rest on the next page. less code

[ober0330]

umm... did you guys even bother to look at my code? I'm not using MySQL. It's MS SQL Server. There is no equivalent to the "LIMIT" function.

[phpman]

there should be a limit in all sql statements. if not then use a better database.

but sorry, didn't know there wasn't.

oh tha tis right, we are talking about M$. figures.

[ober0330]

Great solution. Thanks.

[The Hobo]

Originally posted by ober5861
Great solution. Thanks.

There's no LIMIT? Or anything similiar? If that's the case, I agree with phpman. Find a better database...

[ober0330]

Ok, so I guess there is something somewhat similar. It's called TOP or SET ROWCOUNT.