Page 1 of 1

Pause to read database record and start to see next record

Posted: Wed Feb 02, 2011 11:26 am
by pizzipie
Hi,

I am trying to view one record of a database at a time. The following code shows all records at once. Is there any way to interrupt the 'while-loop' at the end of each record and then resume showing the next record by using a 'next record' button. This could be a php or javascript solution. I've tried using a javascript 'alert()' script just to see if it works (really bad way). It doesn't. I've also tried sleep() (equally bad) which doesn't work either. Both hang up firefox. Thanks in advance for your help.

RP

Code: Select all


<!DOCTYPE HTML PUBLIC
                 "-//W3C//DTD HTML 4.01 Transitional//EN"
                 "http://www.w3.org/TR/html401/loose.dtd">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
  <title>Contacts</title>
 <link rel="stylesheet " type=g"text/css" href="css/frontend.css" />
 <link rel="stylesheet " type="text/css" href="css/connect.css" />
</head>

<body>
	  	<div class="header">
 	<h1><img src="../images/AmericanFlag2.png" alt="Company logo" height="90px" />
				Rick and Polly&rsquo;s Pharmaceuticals</h1>
   		<h3>&nbsp;&nbsp;&nbsp;&nbsp;Name Selector </h3>
	 </div>

<?php

  require_once "db-drugs.inc";
  require_once "functions.inc";
  

	$name=$_GET[name];
	
	switch($name) {
		case "rick":
			$id="PatientId='1'";
  	  	break;
  	  	
  	  	case "polly":
			$id="PatientId='2'";  
  	   	break; 	
   	   
  	   	case "all":
			$id="PatientId='1' OR PatientId='2'";
  	   	break;	   		   	
  	}
  	  		

  if (!($cxn = @ mysqli_connect($hostName, $username, $password, $database)))  showerror($cxn);
  	
  	$ddatafields=getTableFields($cxn, $table);
	
	$query="SELECT * FROM $table WHERE $id ;";
		
      if (!($resultId = @ mysqli_query ($cxn, $query)))
        showerror($cxn);

		
printf("<fieldset>\n");	
printf("<legend>Pharmaceuticals</legend>\n");
printf("<form action=\"\">\n");

/*  [b]The following shows all records of the database at once. I would like to pause at the end of each record and then resume
with the following record by clicking a 'next record' button. I don't want to use sleep() as there is no control over various periods of looking.[/b]
*/
       while($row = @ mysqli_fetch_assoc($resultId)) {
 	
 //      $row = @ mysqli_fetch_assoc($resultId);
       
printf("<input type='hidden' name='Id' id='Id'></input><br>\n");
printf("<input type='hidden' name='PatientId' id='PatientId'></input>\n");	 
printf("<input type='hidden' name='DoctorId' id='DoctorId'></input>\n");
printf("<input type='hidden' name='Pharmacy' id='PharmacyId'></input>\n");

printf("<ul>");
		
	for($i=4;$i<count($ddatafields)-1;$i++) {

		printf("<li><label for=%s>%s</label>\n", $ddatafields[$i], $colhead[$i]);
		printf("<input type='text' size=50 name=%s id=%s value='%s'></input></li>\n\n", $ddatafields[$i], strtolower($ddatafields[$i]), $row[$ddatafields[$i]] );

	} 

printf("<li><label for=%s>%s</label>\n", $ddatafields[$i], $colhead[$i]);
printf("<textarea rows='4' cols='70' name=%s id=%s>%s</textarea></li>\n\n", $ddatafields[$i], strtolower($ddatafields[$i]), $row[$ddatafields[$i]] );

/*[b]tried using sleep() and javascript alert() here but that hangs up everything![/b]*/

}
printf("</ul>\n");
printf("</form>\n");
printf("</fieldset>\n");

		
?>


</body>
</html>

Re: Pause to read database record and start to see next reco

Posted: Wed Feb 02, 2011 11:55 am
by Jonah Bron
First, change your query to put a limit on the fetch with the LIMIT keyword.

Code: Select all

SELECT * FROM $table LIMIT 0, 1
That will only grab the first one. 0 is the offset, and 1 is the number of results to fetch at that point.

Re: Pause to read database record and start to see next reco

Posted: Wed Feb 02, 2011 12:01 pm
by MindOverBody
http://php.about.com/od/mysqlcommands/g/Limit_sql.htm wrote:Limit is used to limit your MySQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results. It is phrased as Limit X, Y and included at the end of your query. X is the starting point (remember the first record is 0) and Y is the duration (how many records to display).
This might help if I understanded you correctly.

Code: Select all

<?php
// Here connect to database 
$query = "SELECT * FROM " . $table . "  LIMIT " . $_POST["item"] . ",1";
// Here show all fetched data for item
?>
<a href="?item=<?php echo $_POST["item"] + 1; ?>">Next item</a>

Re: Pause to read database record and start to see next reco

Posted: Wed Feb 02, 2011 4:15 pm
by pizzipie
Thanks for the quick answer. I'm confused, however.
//The initial $_POST['item'] is "", therefore, $query shows "... LIMIT , 1" not "... LIMIT 0,1" and will not work

Can you explain what //<a href="?item=<?php echo $_POST["item"] + 1; ?>">Next item</a> does and where it goes.


My intent was to:

1. Query MySQL to obtain data.
2. Use Form to show data from record
3. View Form
4. Press a button to obtain next record
5. Repeat at 2 above until no more data.

if (!($cxn = @ mysqli_connect($hostName, $username, $password, $database))) showerror($cxn);

$ddatafields=getTableFields($cxn, $table);

// Here connect to database
$query = "SELECT * FROM " . $table . " WHERE " . $id ." LIMIT " . $_POST['item'] .",1;";

// Here show all fetched data for item .. Using form below

?>


<?php

if (!($resultId = @ mysqli_query ($cxn, $query)))
showerror($cxn);


printf("<fieldset>\n");
printf("<legend>Pharmaceuticals</legend>\n");
printf("<form action=\"\">\n");

Get a records worth of data ,,, I eliminated the 'While-loop'
$row = @ mysqli_fetch_assoc($resultId);

printf("<input type='hidden' name='Id' id='Id'></input><br>\n");
printf("<input type='hidden' name='PatientId' id='PatientId'></input>\n");
printf("<input type='hidden' name='DoctorId' id='DoctorId'></input>\n");
printf("<input type='hidden' name='Pharmacy' id='PharmacyId'></input>\n");

printf("<ul>");

for($i=4;$i<count($ddatafields)-1;$i++) {

printf("<li><label for=%s>%s</label>\n", $ddatafields[$i], $colhead[$i]);
printf("<input type='text' size=50 name=%s id=%s value='%s'></input></li>\n\n", $ddatafields[$i], strtolower($ddatafields[$i]), $row[$ddatafields[$i]] );

}

printf("<li><label for=%s>%s</label>\n", $ddatafields[$i], $colhead[$i]);
printf("<textarea rows='4' cols='70' name=%s id=%s>%s</textarea></li>\n\n", $ddatafields[$i], strtolower($ddatafields[$i]), $row[$ddatafields[$i]] );


printf("</ul>\n");
printf("</form>\n");
printf("</fieldset>\n");

Put button here to get another record ?????
?>


</body>
</html>

Re: Pause to read database record and start to see next reco

Posted: Wed Feb 02, 2011 4:56 pm
by Neilos
MindOverBody wrote:

Code: Select all

<a href="?item=<?php echo $_POST["item"] + 1; ?>">Next item</a>
Well this is saying that every time you load the page you get a hyperlink that will have the next number in a POST variable item which will be + 1 compared to the last time (current one for the loaded page). This will give you a reference as to which row you should be looking at.

The parsed code in HTML will look like(assuming that item is set to 0 for 1st page);

1st page

Code: Select all

<a href="?item=1">Next item</a>
2nd page

Code: Select all

<a href="?item=2">Next item</a>
3rd page

Code: Select all

<a href="?item=3">Next item</a>
You can think of this as a loop where you are viewing each iteration, if it were a while loop then this is equivalent to doing;

Code: Select all

$number++;