Next Last Record

Question

thearts.beach 0 Light Poster

11 Years Ago

Hi, I am trying to learn how to use a data base to store photos and such,
I can now get info into the base and can get all the records to display , this is not what I had planed,
So that lead me on a surch for answers and thus broght me here,

this code gets me a record on the screen,

<?php

  include('conect.php');

  $data = "select * from table1 where id = 1";

  $query = mysql_query($data);

  $data2 = mysql_fetch_array($query);


?><HTML><HEAD><TITLE></TITLE><META name=GENERATOR content="MSHTML 9.00.8112.16457"></HEAD><BODY>

// form to display record from database
<FORM method=get action=next.php target=_self><P align=center>
  id: <INPUT value="<?php echo $data2[a]?>"> <BR>
  nick: <INPUT value="<?php echo $data2[b]?>"> <BR>
  a1: <INPUT value="<?php echo $data2[c]?>"> </P><P align=center>
  a2: <INPUT value="<?php echo $data2[d]?>">
  a3: <INPUT value="<?php echo $data2[e]?>"></P><P align=center>
  a4: <INPUT value="<?php echo $data2[f]?>">
  a5: <INPUT value="<?php echo $data2[g]?>">
  a6:</P><P></P><P align=center> <INPUT value="<?php echo $data2[h]?>">
  a7: <INPUT value="<?php echo $data2[c]?>"></P>
  <P align=center>
  <INPUT name=Last value=-1 type=submit>          
  <INPUT name=Next value=+1 type=submit></P>

</FORM><P align=center></P>



</BODY></HTML>

and now I have no clue on how to get the form to go collect the next or last record and post it back to the same form, ,,, if I get id=1 then when next button is pressed it adds 1 to id and then over write the page ?
hope you can help , thanks.

mysql php

3 Contributors
16 Replies
457 Views
3 Weeks Discussion Span
Latest Post 11 Years Ago Latest Post by thearts.beach

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

if I get id=1 then when next button is pressed it adds 1 to id and then over write the page ?

No. That's not how it works. You will have to process these values yourself. Store the last use id, and check whether you want to increment or decrement it in PHP code.

Apart from that, using id to select a specific record may be tricky, as they don't need to have consecutive values. I suggest using MySQL's LIMIT to retrieve a specific row.

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

You can pass the id or row in a variable, so you can pass it back via the URL, like in: index.php?id=1

diafol

11 Years Ago

SELECT COUNT(id) FROM table

Should give you the number of records in the table. Alternatively you could:

SELECT MAX(id) FROM table

To get the last row id - probably!

Or

SELECT ... FROM table ORDER BY id DESC LIMIT 1

To get the last record in the table. Depends on how you want to handle it and how these calls may help you with the prev/next calls. You'll want to run as few queries as possible. Unless you use some sort of storage, e.g. sessions, then you'll have to run each of your queries on every button click, unless you use Ajax.

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

Line 5 in your previous code is the one you need. The one where $startPoint is used in the query.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

thearts.beach 0 Light Poster · Answer 1 · 2013-06-19T19:21:24+00:00

I understand about why not to use 'id' , so use something like

SELECT *FROM table1 LIMIT 1;

How do I go about storing the id to be able to use the php to move it onto the next record ? is it befor the <body> or after ? so when the next button is pressed it reloads itself ?

thearts.beach 0 Light Poster · Answer 2 · 2013-06-19T19:49:32+00:00

Is this looking like we are going in the right direction ?

<?php
include('conect.php');
    $page = (isset($_GET['page'])) ? $_GET['page'] : 1;
$startPoint = $page - 1;
$data = "SELECT * FROM table1 ORDER BY ordering ASC LIMIT $startPoint,1";
 $query = mysql_query($data); 
  $data2 = mysql_fetch_array($query); 
$rowCount = 0;
?><HTML><HEAD><TITLE></TITLE><META content="text/html; charset=utf-8" http-equiv=Content-Type><META name=GENERATOR content="MSHTML 9.00.8112.16457"><STYLE type=text/css>BODY {
    FONT-FAMILY: verdana, arial, sans-serif
}</STYLE></HEAD><BODY>
<P>&nbsp;</P>
<P>
<FORM method=post action=input212.php>
<P>&nbsp;</P>
<P></P>
  Name: <INPUT value="<?php echo $data2[a]?>"> <BR>
  Club: <INPUT value="<?php echo $data2[b]?>"> <BR>
  hobby: <INPUT value="<?php echo $data2[c]?>">
<P><INPUT style="Z-INDEX: 132; POSITION: absolute; TOP: 59px; LEFT: 181px" value="<?php echo $data2[id]?>">&nbsp;
<P><INPUT style="Z-INDEX: 132; POSITION: absolute; TOP: 58px; LEFT: 17px" value="<?php echo $data2[timenow]?>">&nbsp;
<P><INPUT style="Z-INDEX: 132; POSITION: absolute; TOP: 60px; LEFT: 346px" value="<?php echo $data2[a]?>">&nbsp;
<INPUT style="Z-INDEX: 131; POSITION: absolute; TOP: 129px; LEFT: 503px" value="<?php echo $data2[b]?>">&nbsp;
<INPUT style="Z-INDEX: 130; POSITION: absolute; TOP: 129px; LEFT: 678px" value="<?php echo $data2[c]?>">&nbsp;
<P><BR>&nbsp;</P>
<a href="index.php?page=1">First</a>
<a href="index.php?page=<?php echo $page - 1?>">Prev</a>
<a href="index.php?page=<?php echo $page + 1?>">Next</a>
<a href="index.php?page=<?php echo $rowCount;?>Last</a>

</FORM>
<P></P></BODY></HTML>

thanks.

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 3 · 2013-06-19T21:28:40+00:00

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

Nice one.

thearts.beach 0 Light Poster · Answer 4 · 2013-06-19T23:08:01+00:00

still got bugs with it, got rid of a few, am stuck here now,

 <?php
    include('conect.php');
    $page = (isset($_GET['page'])) ? $_GET['page'] : 1;
    $startPoint = $page - 1;
    $data = "SELECT * FROM table1 ORDER BY a ASC LIMIT $startPoint,1";
    $query = mysql_query($data);
    $data2 = mysql_fetch_array($query);
    $rowCount = 0;
    ?><HTML><HEAD><TITLE></TITLE><META content="text/html; charset=utf-8" http-equiv=Content-Type><META name=GENERATOR content="MSHTML 9.00.8112.16457">
    <STYLE type=text/css>BODY {
    FONT-FAMILY: verdana, arial, sans-serif
    }</STYLE></HEAD><BODY>
    <P> </P>
    <P>
    <FORM method=post action=input212.php>
    <P> </P>
    <P></P>
    Name: <INPUT value="<?php echo $data2[a]?>"> <BR>
    Club: <INPUT value="<?php echo $data2[b]?>"> <BR>
    hobby: <INPUT value="<?php echo $data2[c]?>">
    <P><INPUT style="Z-INDEX: 132; POSITION: absolute; TOP: 59px; LEFT: 181px" value="<?php echo $data2[id]?>"> 
    <P><INPUT style="Z-INDEX: 132; POSITION: absolute; TOP: 58px; LEFT: 17px" value="<?php echo $data2[timenow]?>"> 
    <P><INPUT style="Z-INDEX: 132; POSITION: absolute; TOP: 60px; LEFT: 346px" value="<?php echo $data2[a]?>"> 
    <INPUT style="Z-INDEX: 131; POSITION: absolute; TOP: 129px; LEFT: 503px" value="<?php echo $data2[b]?>"> 
    <INPUT style="Z-INDEX: 130; POSITION: absolute; TOP: 129px; LEFT: 678px" value="<?php echo $data2[c]?>"> 
    <P><BR> </P>
    <a href="index.php?page=1">First</a>
    <a href="index.php?page=<?php echo $page - 1?>">Prev</a>
    <a href="index.php?page=<?php echo $page + 1?>">Next</a>
    <a href="index.php?page=<?php echo $rowCount?;">Last</a>
    </FORM>
    <P></P></BODY></HTML>

for this code to work do i need to have a coloum in my table1 called 'page' ?

if not I am thinking that $rowCount= 0 is the problem ?

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 5 · 2013-06-20T10:08:35+00:00

Correct, LIMIT cannot use 0. You will have to count the actual number of records to get the "last" record in this situation.

thearts.beach 0 Light Poster · Answer 6 · 2013-06-21T02:41:48+00:00

thearts.beach 0 Light Poster

11 Years Ago

I do not understand.

thearts.beach 0 Light Poster · Answer 7 · 2013-06-23T03:38:47+00:00

I have now got the page numbers in the address bar to run page1, page2,page3 ect and the buttons work for NEXT, PREVs, and FIRST, last gives an error,

HOWEVER-
the data record stays the same, allways the 1st one in the table,

using the

 `SELECT ... FROM table ORDER BY id DESC LIMIT 1`

from diafol the code now looks like this

  <?php
include('conect.php');
$page = (isset($_GET['page'])) ? $_GET['page'] : 1;
$startPoint = $page - 1;
$data = "Select * FROM table1 ORDER BY id DESC LIMIT 5";
$query = mysql_query($data);
$data2 = mysql_fetch_array($query);
$rowCount = 0;
?><HTML><HEAD><TITLE></TITLE><META content="text/html; charset=utf-8" http-equiv=Content-Type><META name=GENERATOR content="MSHTML 9.00.8112.16457">
<STYLE type=text/css>BODY {
FONT-FAMILY: verdana, arial, sans-serif
}</STYLE></HEAD><BODY>
<P> </P>
<P>
<FORM method=post action=input212.php>
<P> </P>
<P></P>
Name: <INPUT value="<?php echo $data2[a]?>"> <BR>
Club: <INPUT value="<?php echo $data2[b]?>"> <BR>
hobby: <INPUT value="<?php echo $data2[c]?>">
<P><INPUT style="Z-INDEX: 132; POSITION: absolute; TOP: 59px; LEFT: 181px" value="<?php echo $data2[id]?>"> 
<P><INPUT style="Z-INDEX: 132; POSITION: absolute; TOP: 58px; LEFT: 17px" value="<?php echo $data2[timenow]?>"> 
<P><INPUT style="Z-INDEX: 132; POSITION: absolute; TOP: 60px; LEFT: 346px" value="<?php echo $data2[a]?>"> 
<INPUT style="Z-INDEX: 131; POSITION: absolute; TOP: 129px; LEFT: 503px" value="<?php echo $data2[b]?>"> 
<INPUT style="Z-INDEX: 130; POSITION: absolute; TOP: 129px; LEFT: 678px" value="<?php echo $data2[c]?>"> 
<P><BR> </P>
<a href="OUTtest1.php?page=1">First</a>
<a href="OUTtest1.php?page=<?php echo $page - 1?>">Prev</a>
<a href="OUTtest1.php?page=<?php echo $page + 1?>">Next</a>
<a href="OUTtest1.php?page=<?php echo $rowCount?;">Last</a>
</FORM>
<P></P></BODY></HTML>

i cant see anywhere where it sends a call to table1 to get the next record,
i was thinking that it was only because there was one record set in the table however that is not the case.

thearts.beach 0 Light Poster · Answer 8 · 2013-06-23T18:38:12+00:00

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/a3024539/public_html/OUTtest1.php on line 7

works except for when I try the 'last` button, , i think i need to make an array and find the last number and store that in the headder ? is that what you were on about priteas ?
then make the line
number of rows =$last
$last = $rowCount_max(array) ?

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 9 · 2013-06-23T19:45:03+00:00

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

Diafol already showed you how you can get to the last record.

thearts.beach 0 Light Poster · Answer 10 · 2013-06-24T04:00:15+00:00

got it, insteed of

SELECT * FROM table

use

SELECT COUNT(id) FROM table

so it becomes like

$data = "Select COUNT(id) FROM table1 ORDER BY id DESC LIMIT 5";

Thanks you pritaeas and dialfol i was readding them as 3 seprate lines not to mix and make one line,

diafol · Answer 11 · 2013-06-24T06:01:07+00:00

Erm, that's not what I was saying. Go through the SQL you've written to see what you're actually asking of the DB. It doesn't make much sense to me.

thearts.beach 0 Light Poster · Answer 12 · 2013-07-12T17:34:31+00:00

Allthough this is marked as solved I still do not understand it so am going to try again ,

I know where i was thinking wrong, it it 4 diffrent buttons, so must = 4 diffrent calls to the database.