[RESOLVED] previous / next record

daltman1967

Here's my MYSQL query:

  $query="SELECT * FROM d_license WHERE YEAR(STR_TO_DATE(Date_Completed, '%m/%d/%Y'))='$y' ORDER BY Last_Name, First_Name";
  $result = mysql_query($query);
  $nt=mysql_fetch_array($result);

This gives me the first line of data for the year I specified. In this case, it's 2011.

What I want to do is to proceed to the next record (or previous) in this set.

What I'm doing is recreating a database I made in MS ACCESS. Specifically, I'd prefer to have this app on the web, with the ability for multiple users, portability, etc.

So: Does anyone know how I can get to the next / previous records?

NogDog

You'll need to do a loop on the fetch(), e.g.:

while($nt=mysql_fetch_array($result)) {
   // do stuff with $nt
}

daltman1967

Hmm...

so perhaps I can remember the value of ID in a variable; I set a condition that says, "don't continue the while until this button is pressed," which would act as the 'NEXT" button, and if the "PREV" button is pressed, cycle a new query (actually, the same query re-run), then WHILE forward until you reach ID. It has the same effect as going back one record.

I don't know of any other way.

Does that sound right?

johanafm

What you're looking for is called pagination (splitting up the results from one query over several pages by retrieving subsets of the entire result set using LIMIT in the query). Search the forum or the web for php mysql pagination.

daltman1967

Actually, my previous post was close, but no cigar. If I let the 'while' proceed, the program flipped through ALL the records - INSTANTLY!! If I put a pause to prevent the end of the 'while' statement, the information never got on the screen at all!!

So here's how I solved it:

 $query  = "SELECT * FROM my_table";
 $result = mysql_query($query);
  while($nt=mysql_fetch_array($result))
    {
      $i++;
      $id[$i]=$nt['ID'];
    }
  if(isset($_GET['p']))
    {
      $p=$_GET['p'];
      $ix=$id[$p];
    }
  else
    {
      $p=1;
      $ix=$id[1];
    }
  $query2  = "SELECT * FROM my_table WHERE ID='$ix'";
  $result2 = mysql_query($query2);
  $nt2=mysql_fetch_array($result2);
  ?>

... do stuff here with displaying the variables under $nt2 ...  

<?
  if($p>1)
    {
      ?>
      <form name="form1" onclick="Send1" action="d_entry.php?p=<? echo ($p-1); ?>" method="post">
      </form>
      <?
    }
  if($p<$i)
    {
      ?>
      <form name="form2" onclick="Send2" action="d_entry.php?p=<? echo ($p+1); ?>" method="post">
      </form>
      <?
    }
?>

The Send1 / Send2 code is simple javascript:

function Send1()
  {
    document.form1.submit();
  }
function Send2()
  {
    document.form2.submit();
  }

And that's it. I record each individual ID# {302, 805, 699, 112, 76, etc.} as they come in; once I've got that stored, I test to see if a "page" number has been sent. If not, we're on the first record. If we're on the first record, don't put a 'PREV' button; if we're on the last record, don't put a 'NEXT' button.

I then rerun the query. This time, I'm specifying the exact record number [ID]. Since I only need one record at a time, this works perfectly. I can edit this record as the current record, then go back to the previous one, regardless of its ID#.

Thanks for the help! You helped me steer myself in the right direction.

johanafm

Not really. You are selecting all records rather than just the one you need. Then you loop through all of them and after that you check if a specific record exists. If it does you even retrieve it again.

Just get the record you want directly or your solution will quickly become too inefficient to work properly.

daltman1967

To johanafm:

Yes, I am selecting ALL the records in the particular set. However, ALL of these are "the records I want."

Until the user selects the exact record to edit, there's no way of knowing WHICH record to go to. Once the user selects the record, then editing can begin.

I'm not checking to see if a specific record exists. All the records in the set are being chosen. Then, out of 'n' records, the user is choosing one (or two, or seven) to edit - or, perhaps, add a new record. Perhaps they're just looking at the record to read what's there. The bottom line is, the whole purpose of this is to EXACTLY duplicate the function of my MS Access database. I'm now well on my way.

I don't see how it will be 'inefficient.' Even with thousands of records, the first while only takes a fraction of a second to run. There's also a finite number of records for each year, which makes the subset of the whole even smaller.

As far as I can tell, this was the ONLY way to get what I wanted: A php/MYSQL database interface, so I can edit specific records upon request, based on user input, where the user can do next/previous (if desired).

johanafm

daltman1967;10991944 wrote:
Yes, I am selecting ALL the records in the particular set. However, ALL of these are "the records I want."

No, right after selecting all records and looping over the entire result set to store them all, you discard them all in favor of one single record, which you don't even retain from the allready selected records, but for which you issue a separate query. Well, you don't really discard them, you just never user that data for anything but retrieving the the P'th id from the result set, which you really would be better off doing using SQL instead.

daltman1967;10991944 wrote:
Until the user selects the exact record to edit, there's no way of knowing WHICH record to go to.

Except that you've somewhere stated that until the user selects a specific record, you show them the first record, which means selecting only one single record the first time. As far as I've understood you, from that point on, you provide them with previous and/or next links, which means that you will always know which record to retrieve. The same still holds if you let them specify that they want to go to page 200 directly, rather than clicking next repeatedly.

daltman1967;10991944 wrote:
I'm not checking to see if a specific record exists.

Well you're literally not doing that. You are however assuming it does.

if(isset($_GET['p']))
{
	# fine, due to the above check, you know that $_GET['p'] exists
	$p=$_GET['p'];
	# You then assume that $id[$p] exists. You ought to check that it really does.
	# Either way, this is where you do what I meant by "checking if record exists".
	$ix=$id[$p];
}
# But whatever the case, you then select the exact same data from the db again
# using this SQL query
$query = "SELECT * FROM my_table WHERE ID='$ix'";

Do note that when I say "exact same data", I refer to the data you originally retrieved from which you then stored only the id. This is also a waste of resources, since you can tell the DBMS exactly which columns you want to retrieve. It's considered good practice to always specify that, even if you actually need the entire set of columns.

daltman1967;10991944 wrote:
All the records in the set are being chosen. Then, out of 'n' records, the user is choosing one (or two, or seven) to edit - or, perhaps, add a new record. Perhaps they're just looking at the record to read what's there.

No, the user have allready selected a record (or you have said it should default to the first record), which means that you have ONE SINGLE record on which to operate. Retrieve that ONE record and do whatever you want with it. Do NOT retrieve all records first, and then DIRECTLY after that retrieve the one you want.

What you are doing here is equivalent to deciding that you want to talk to the youngest member in a family. You then ask them all to leave their house and tell you their ages. They are 5, 14, 40 and 44. Then they go back inside the house. Finally you tell them to send out the five-year-old.

Do note that it's entirely possible to tell them to send out the youngest person straight away. Or the second youngest. The family knows who that will be even though you have no idea of their ages until you have the person in front of you. But telling them to send out one person is enough.

daltman1967;10991944 wrote:
The bottom line is, the whole purpose of this is to EXACTLY duplicate the function of my MS Access database. I'm now well on my way.

There is no reason to do it this way in MS Access either, although it is entirely possible. But I suspect you are trying to mimic behaviour in MS Access which you can get without writing your own code to handle the pagination.

daltman1967;10991944 wrote:
I don't see how it will be 'inefficient.'

Let N be the number of records in your table. When comparing your approach
First SELECTING N records,
then performing N iterations to retrieve that data from the result set resource
then SELECTING 1 record (the one you need)
then retrieving that data from the result set resource

to
SELECTING 1 record (the one you need)

it is obvious that you are first telling the db to return N more rows than you need (N + 1) - 1, and then perform N more iterations than you need N - 0. Thus, your approach is inefficient.

daltman1967;10991944 wrote:
Even with thousands of records, the first while only takes a fraction of a second to run. There's also a finite number of records for each year, which makes the subset of the whole even smaller.

I'm not saying it's impossible to use your approach if you have less than a certain amount of rows and one single user. I'm saying that your approach will not scale since it is inefficient. And I believe it's a good idea to get such advice as this should you ever need more concurrent users or have so much data that it takes too long. Also note that you do not specify that you only want records from this year when first selecting all records. There is no where clause at all.

daltman1967;10991944 wrote:
As far as I can tell, this was the ONLY way to get what I wanted:

And I've been trying to tell you it is not. But I am not telling you that you HAVE to do it in a specific way. If you want to stick with yours, by all means, don't let me hold you back.
I was merely trying to give you some advice which I personally believe to be very good advice. I was also hoping to help you avoid headache down the road. I also believe you came to this board because you wanted to learn.

daltman1967

If there's a better way, I'm all ears.

I could have chosen just the drop-down box method of choosing a record. But I also want the next / previous function. If there's a different way of doing that, something easier/cleaner than what I'm doing, let me know.

johanafm

That's exactly what I answered in my initial post. Answered might be stretching the truth a bit, but at least I told you how to find the information.

daltman1967

Hmm... now I think I see. You suggested mysql pagination. I looked at about a half dozen articles on the subject, and they all said the same thing.

First, you see if the page number is set (which I'm doing), and make sure it's in range (which I'm also doing).

Then, once you've determined what page you should be on, you rerun the query as such:

mysql_query("SELECT * FROM mydatabase LIMIT 1, 100")

I knew about limit, but I didn't know about the second parameter. I thought limit would only give me the first record in the set. I didn't know you could use it to give the Nth record also.

So: Here's my pseudocode as it stands:

  $query  = "SELECT * FROM d_license WHERE ... stuff ";
  $result = mysql_query($query);
  $rows = mysql_num_rows($result); //Now we have the rows

  $query  = "SELECT * FROM d_license WHERE ... stuff LIMIT $p, $rows";
  $result = mysql_query($query);
  $nt=mysql_fetch_array($result);
  //$p (the page number) brings us the Nth record in the list.
  //This is ALL we need to do.
?>

It's a much simpler (and cleaner) way to do it than the way I'd figured out. Yes, my ways DOES work... but I like this a bit better. No mucking around with the $id variable - all I need to take care of is $p.

<> The student bows to the Zen Master <>

jrahma

I am trying to do a NEXT..PREVIOUS paging as well.

I tried your code but getting an error:

here is the code I have after using your code:

<?php
$conn = mysql_connect('mysql.jassimrahma.com', 'jassimxxxx', 'xxxxx');
mysql_select_db("jassimrahma", $conn);
$query = "SELECT * FROM news ORDER BY created_date DESC, news_id DESC";
$result = mysql_query($query);
$rows = mysql_num_rows($result); //Now we have the rows
$query  = "SELECT * FROM news LIMIT $p, $rows";
$result = mysql_query($query);
$nt=mysql_fetch_array($result);
//$p (the page number) brings us the Nth record in the list.
//This is ALL we need to do.
?>

and here is the error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/jassimrahma/JassimRahma.com/news.php on line 10

and this is line 10:

$nt=mysql_fetch_array($result);

NogDog

That usually means that your query failed to be parsed/processed by MySQL, so mysql_query() returned Boolean false instead of a result resource ID. You need to check the return values of mysql_connect() and then mysql_query() to see if they are false, and if so, invoke the desired error-handling so that you can debug the problem (typically making use of mysql_error() to get more info).