[RESOLVED] MoveFirst

GrexP

I normally program in SQLServer/VB/DAO and I'm just moving in to PHP/MySQL. So far so good, but it can be frustrating at times when there I things I could normally do with my eyes closed and now I can't get them to work at all.

What I want to do is move to the first record and display 5 fields and then loop through the recordset and display the other fields. Think in terms of grouping when report writing.

This code works for reading everything.

    if (!$sSQL) {
        die('Query failed: ' . mysql_error());
    }

 while ($row = @mysql_fetch_array($sSQL))

{

        print ("<tr>");
        $Counter = 1
    print ("<td>{$row["Clinic"]}</td>");
        print ("<td>{$row["PCP"]}</td>");
        print ("<td>{$row["FName"]}</td>");
        print ("<td>{$row["LName"]}</td>");
        print ("<td>{$row["DOB"]}</td>");

        print ("<td>{$row["TestName"]}</td>");
	print ("</tr>");

}

I then went to this to try and read just the first row and the loop through the final field. It goes in the If mysql_data_seek block and creates the table row, but it doesn't read the field values. The first row of the table is empty.

    if (!$sSQL) {
        die('Query failed: ' . mysql_error());
        continue;
    }

if (mysql_data_seek($sSQL, 0)) {
        print ("<tr>");

    print ("<td>{$row["Clinic"]}</td>");
        print ("<td>{$row["PCP"]}</td>");
        print ("<td>{$row["FName"]}</td>");
        print ("<td>{$row["LName"]}</td>");
        print ("<td>{$row["DOB"]}</td>");
        print ("</tr>");
}

 while ($row = @mysql_fetch_array($sSQL))

{

        print ("<tr>");

        print ("<td>{$row["TestName"]}</td>");
        print ("</tr>");

Little help!

Greg

NogDog

I don't think I really understand what the ultimate goal here is. However, one thing I can point out is that mysql_data_seek() does not read any data, it just positions the result row pointer (and in this case is only needed if somewhere prior in the code you did a fetch or some other operation that would have moved the row pointer off of the first row). Therefore you need to do a mysql_fetch_assoc() or whatever fetch type you like to use to get the first row's data for the following print statements.

Weedpacket

Another possibility is that you look into using the [man]PDO[/man] interface; I've never done anything in VB, and what I remember of DAO is so hazy I might actually be thinking of ADO, but PDO might look more familiar.

Roger_Ramjet

When you use DOA it automatically fetches records as you move through the recordset, but remember you still have to fully populate a recordset (move_last) before you can eg get an accurate count. So the get record is implicit in the move. This is a client-side cursor. Mysql uses a server-side cursor and you have to explicitly fetch a record even when you have move to it.

PDO will not change this but you should still be using PDO for other reasons, bets read the manual to see what I mean. A side-benefit of using PDO is that it has fetchAll which returns the whole recordset as an array that you can then work with. This is much more efficient if you are going to work through it multiple times.

GrexP

NogDog;10904970 wrote:
I don't think I really understand what the ultimate goal here is. However, one thing I can point out is that mysql_data_seek() does not read any data, it just positions the result row pointer (and in this case is only needed if somewhere prior in the code you did a fetch or some other operation that would have moved the row pointer off of the first row). Therefore you need to do a mysql_fetch_assoc() or whatever fetch type you like to use to get the first row's data for the following print statements.

That go it. In DAO, moving the pointer also retrieves the row. Not that it is important at this point, but what I'm doing is retrieving from 2 tables with a 1-to-many relationship. On the one side is a persons name, date of birth, doctor, etc and on the many side there are any number of tests this person needs. Since the page will be devoted to the one person I only need to show their name once, then I list all of the tests they need. I could do 2 different queries I suppose, but it seems easier just to read the demographic data from the first row and then loop through all of the rows to list the test.

GrexP

Roger Ramjet;10905011 wrote:
When you use DOA it automatically fetches records as you move through the recordset, but remember you still have to fully populate a recordset (move_last) before you can eg get an accurate count. So the get record is implicit in the move. This is a client-side cursor. Mysql uses a server-side cursor and you have to explicitly fetch a record even when you have move to it.

PDO will not change this but you should still be using PDO for other reasons, bets read the manual to see what I mean. A side-benefit of using PDO is that it has fetchAll which returns the whole recordset as an array that you can then work with. This is much more efficient if you are going to work through it multiple times.

Totally: server-side cursor. It makes since now, thanks. I'll look in to PDO. I'm not sure I'll need the fetch-all, though. As I pointed out in the post above, I only need data from the first row twice. After that, I only move through the recordset once. Even in DAO you must issues a MoveLast to get all of the data at one time.

Really, I'm working with very little data here. 99% of what we do is in-house stuff off SQLServer. I just need to put a very small amount of data on the web site. It won't even be available to the general public. The real challenge will be the C syntax of PHP. It has been close to 10 years since I did any programming in C. Even then, it wasn't much.

Thanks for the help. I'm sure I'll be back. 🙂