How do I scroll through records in mysql?

[kjspear]

What is the simplest way to scroll through the records in a database. What I had done was create a table which stores links to image files. These image files are then queried and displaye on screen. Not a problem here. But what I would like to do is have the images display on screen on at a time. The user will press either an arrow key or link to scroll either foward or backwards. I checked out some tutorials, but the ones I've checked seen a little complicated. Then again this is all new to me. I was trying to use the LIMIT command.


Any assistance would be appreciated.

Thank you,

KJ

PS here is a sample of my code, if needed;

db=mysql_connect("anyhost","anyuser","&quot
or die("Could not connect : " . mysql_error());
print "Connected successfully";
print &quot;<br>&quot;;
print &quot;<center><h2><b>User Logon Database</b></h2></center>&quot;;
mysql_pconnect();
mysql_select_db(&quot;mylogin&quot or die(&quot;Could not select database&quot;
$query = (&quot;SELECT * FROM myimagexx &quot;.limit 1.1);


$result = mysql_query($query) or die(&quot;Query failed : &quot; . mysql_error());

/* Printing results in HTML */
print &quot;<br>&quot;;
print &quot;<table width=75% border=1>\n&quot;;
print &quot;<td width=14%><b>UserID</b></td>&quot;;
print &quot;</tr>&quot;;
print &quot;</table>&quot;;

while ($row= mysql_fetch_array($result))

{
print &quot;<table width=75% border=1>\n&quot;;
print &quot;<td width=8%>&quot;;
//echo '<img src=' .$row['imagefilepath']. '>';

//or

$image = $row['imagefilepath'];
echo &quot;<img src=$image>&quot;;


print &quot;</td>&quot;;


}
/* Free resultset */
mysql_free_result($result);

/* Closing connection */
mysql_close($db);

 

[sleipnir214]

The LIMIT clause is the only way to do it efficiently. However, I can't make any sense of the query in your code.

You select a set of records. You output them with a link that will show the next set. That link will be of the form <a href=&quot;youroutputscript.php?recordset=<somenumber>&quot;>. Your script can then interpret $_GET['recordset'] to determine which records to put out next time.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[kjspear]

This is further explaination,

I'm trying to find a way where I can use the limit command to print one image on the screen at a time. Lets say, the database has 113 records, but more records are constantly being added.

I would like the image appear on the screen and when the user inserts something into the table, the next record with the image appears and so on. I know LIMIT come into play here, but where do I place this command so that perhaps it can loop until the last record of the database.

Here's a sample of the code below;

I was thinking something like;

$page=0+1;

The above would increase into a loop for LIMIT command.

$query = (&quot;SELECT * FROM myimagexx &quot; limit $page,1);


$result = mysql_query($query) or die(&quot;Query failed : &quot; . mysql_error());
$num_rows = mysql_num_rows($result);

echo &quot;$num_rows Rows\n&quot;;

while ($row= mysql_fetch_array($result))

{

print &quot;<table width=75% border=1>\n&quot;;
print &quot;<td width=8%>&quot;;
echo '<img src=' .$row['imagefilepath']. '>';


print &quot;</td>&quot;;


}
/* Free resultset */
mysql_free_result($result);

/* Closing connection */
mysql_close($db);

?>

I hope I was clear. It's for modifiying the records as the user scrolls through the records.

Any assistance would be helpful.

Thanks.

KJ

 

[sleipnir214]

It is not clear.

The code you have posted has nothing to do with modifying records. I see no update queries in your code.

If you are displaying images one at a time, then you don't need the LIMIT clause.

Each record should have some kind of unique ID. When you display the current image, two links can be displayed, too: one with a link containing the ID of the previous image, one with a link containing the ID of the next image.

When your script gets an image ID via GET-method input, it displays that image, plus links to the previous and next images.

With this method, you need only perform a query like:

SELECT necessary-columns from tablename where tableid = somevalue.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[kjspear]

O.K., I'm missing something. How do I apply a GET command for this code? If that's appropiate for this script.

$query = (&quot;SELECT * FROM myimage &quot;
//THE ABOVE IS MY TABLE SELECTION.

$result = mysql_query($query) or die(&quot;Query failed : &quot; . mysql_error());
//THE ABOVE IS MY QUERY.
$num_rows = mysql_num_rows($result);
//THE ABOVE TELLS ME THE TOTAL NUMBER OF ROWS QUERIED.
//I WAS TRYING TO TAKE THAT TOTAL FROM $num_rows AND
//LETS SAY 123 ROWS AND HAVE PHP LOOP THROUGH IT
//DISPLAYING ON IMAGE ON THE SCREEN AT A TIME. THESE
//IMAGES ARE NOT STORED IN ANY FILES LIKE HTML OR PHP.
//JUST THE LINKS ARE STORED IN THE myimage TABLE.

echo &quot;$num_rows Rows\n&quot;;

//THE ABOVE JUST DISPLAYS THE NUMBER OF ROWS. ITS NOT
//REALLY NECESSARY.




while ($row= mysql_fetch_array($result))

/THE ABOVE SIMPLY RETRIEVES THE ROWS.
{

print &quot;<table width=75% border=1>\n&quot;;
print &quot;<td width=8%>&quot;;
echo '<img src=' .$row['imagefilepath']. '>';

print &quot;</td>&quot;;


}
//THE ABOVE SIMPLY DISPLAYS THE LINKED IMAGES.

/* Free resultset */
mysql_free_result($result);

/* Closing connection */
mysql_close($db);

?>

I tried to run an increment statement in the While loop. Sure, it counts but I it's generally doing the same thing as the $num_rows = mysql_num_rows($result);.

Is there anything that I could plug within this code for it to work? Again, I simply want to be able to scroll through this database displaying on record at a time.

Any assistance would be appreciated.

Thanks
KJ

 

[sleipnir214]

You're thinking like a desktop application programmer. You need to stop that.

A desktop app differs from a web app in one crucial thing. A desktop app runs for a long time and communicates continuously with the user. A web app runs for a short period of time, and communicates with the user only once during its run: it takes input as it begins to run and produces output each time it is run.

Thus what you are thinking of as multiple interactions between a user and a single run of a program is actually a set of single interactions, each with one run of the program.

So you're not writing a script that scrolls through all images in a database. You're writing a script that, each time it is run, outputs one image from the database. It knows which specific image to output based on the specific input the user gives it.

The script can also provide links (call them &quot;previous&quot; and &quot;next&quot that provide <a> links back to the script itself, which provide input specifying the image that is one before and one after the image it just displayed.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[webscripter]

My resolution would be to make a index column with AUTO_INCREMENT on. Then when the user queries the database get one more or one less than the index number, depending on whether he's going forward or backward.

Don't select * from the table. Only seclect the index number.

Thanks
Tricia
yorkeylady@earthlink.net

 

[kjspear]

O.K. I see your point, Sleipnir214. That's exactly what I was thinking as. A desktop application developer. No wonder why I'm having alot of challenges with MYSQL. But I'm still learning.

Anyway, I have another plan to get it to do what I need it to do.

I would like to know if there is another command that I can use instead of the one below;

header('locationhotoview9a.php?num_rows='.$num_rows);

I know that I may not use 'Print' or 'Echo', otherwise I will get the following;

Warning: Cannot modify header information - headers already sent by
(output started at C:\mysql\data\mylogin\photoview9.php:6)


I'm passing varibles to another page.

Thank you.
KJ

 

[sleipnir214]

The header problem is because a web page consists of two parts: a header section and a content section. The two are separated by a blank line. A valid HTML page transmitted by HTTP must have the two parts in that order.

If your script generates output, PHP assumes that you're finished sending headers and have begun to send output. So it generates whatever headers it normally sends then sends that blank line. If after that point you try to use session_start(), set_cookie() or header(), which all manipulate headers, PHP will barf.

There are two workarounds. One is to always output headers before content. The other is to use output buffering, which is described in the PHP online manual:

http://www.php.net/manual/en/ref.outcontrol.php

Why are you using the location header? That HTTP header immediately redirects the browser to a new page. Aren't you going to wait for the user's to get finished looking at the image and then sent himself send the browser on?

Try something like:

print '<a href=&quot;photoview9a.php?num_rows='.$num_rows . &quot;>some text</a>';

in your page content. Then $_GET['num_rows'] will be available to the script on its next run.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!