Problem looping through database

befidled
befidled used Ask the Experts™
on
I just set up the following code where I'm trying to loop through my database and display the results in a table. The query should result in about 900 records and I'm using jQuery DataTables to paginate the results.  However it appears that my query is getting stuck in a loop. What am I missing and what is a better way to write this?

thanks.
<?php
$dbhost = 'localhost';
$dbuser = 'myusername';
$dbpass = 'mypassword';
$dbname = 'mydatabase';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);

// get the article info from database
$query = "SELECT 
	dbtable1.DATE, 
	dbtable1.ADDR AS address, 
	dbtable1.CITY AS city, 
	dbtable1.STATE AS state, 
	dbtable1.ZIP AS zip, 
	dbtable1.SUBDIVISIN AS subdivision, 
	dbtable1.DATE AS date, 
	dbtable1.TIME AS time, 
	dbtable1.STATUS AS status,
	dbtable1.ASSESVALUE AS assessedValue, 
	dbtable1.LOTSIZE AS lotSize, 
	dbtable1.POOLSIZE AS poolSize, 
	dbtable1.HOMESQFT AS squareFeet, 
	dbtable1.YEARBLT AS yearBuilt,
	dbtable1.OPENBID AS openBid, 
	dbtable1.ID
FROM dbtable1
WHERE dbtable1.date ='2010-10-21' AND dbtable1.STATUS = 'PD'
ORDER BY dbtable1.date ASC LIMIT 10";
	
	$result = mysql_query($query) or die('Error : ' . mysql_error());
   
   $row = mysql_fetch_array($result, MYSQL_ASSOC);

	$num=10;
	
	mysql_close($conn);
?>
<html>
<head>
<title>My Database Tool</title>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
		
		<style type="text/css" title="currentStyle">
			@import "../../media/css/demo_page.css";
			@import "../../media/css/demo_table.css";
			@import "media/css/TableTools.css";
			@import "../../media/js/themes/bfi/jquery-ui-1.8.5.custom.css";
		</style>
		<script type="text/javascript" language="javascript" src="../../media/js/jquery.js"></script>
		<script type="text/javascript" language="javascript" src="../../media/js/jquery-ui-1.8.5.custom.min.js"></script>
		<script type="text/javascript" language="javascript" src="../../media/js/jquery.dataTables.js"></script>
		<script type="text/javascript" charset="utf-8" src="media/ZeroClipboard/ZeroClipboard.js"></script>
		<script type="text/javascript" charset="utf-8" src="media/js/TableTools.js"></script>
		<script type="text/javascript" charset="utf-8">
			
			var oTable;
			var asInitVals = new Array();
			
			$(document).ready(function() {

				TableToolsInit.sSwfPath = "media/swf/ZeroClipboard.swf";
				$( ".showCalendar" ).datepicker({ dateFormat: 'yy-mm-dd' });
								
					oTable = $('#mydata').dataTable({
					"iDisplayLength": 10,
					"aaSorting": [ [0,'asc'], [1,'asc'], [2,'asc'] ],
					"oLanguage": {"sSearch": "Search all columns:"},			
					"aoColumns": [
						null,
						null,
						null,
						null,
						null,
						null,
						null,
						null,
						null,
						null,
						null,
						null,
						null,
						null,
						null,
						null,
						null
					]		
					
				} );
				
				$("tfoot input").keyup( function () {
					/* Filter on the column (the index) of this element */
					oTable.fnFilter( this.value, $("tfoot input").index(this) );
				} );
				
				$("categoryselect2 input").keyup( function () {
					/* Filter on the column (the index) of this element */
					oTable.fnFilter( this.value, $("categoryselect2 input").index(this) );
				} );
				
				
				/*
				 * Support functions to provide a little bit of 'user friendlyness' to the textboxes in 
				 * the footer
				 */
				$("tfoot input").each( function (i) {
					asInitVals[i] = this.value;
				} );
				
				$("tfoot input").focus( function () {
					if ( this.className == "search_init" )
					{
						this.className = "";
						this.value = "";
					}
				} );
				
				$("tfoot input").blur( function (i) {
					if ( this.value == "" )
					{
						this.className = "search_init";
						this.value = asInitVals[$("tfoot input").index(this)];
					}
				} );


			} );
		</script>
		
</head>

<body id="dt_example">
		<div id="container" style="margin-left:20px;">
			<div class="full_width big">
				<i>My Data
			</div>

			<div id="dynamic">
				<table cellpadding="0" cellspacing="0" border="0" class="display" id="mydata">
					<thead>
						<tr>
							<th style="width:350px;">Address</th>
							<th>City(s)</th>
							<th>Zip</th>
							<th>Subdivision</th>
							<th style="width:180px;">Date</th>
							<th>Time</th>
							<th>Value</th>
							<th>Value</th>
							<th>Lot Size</th>
							<th>Pool Size</th>
							<th>Sqft</th>
							<th>Year Blt</th>
							<th>Open Bid</th>
							<th>Mrgn</th>
							<th>P/SF</th>
							<th>Real Mrgn</th>
							<th>Status</th>
							
						</tr>
					</thead>
					<tfoot>
						<tr>
							<th><input class="search_init" type="text" value="ADDRESS" name="ADDR" /></th>
							<th><input class="search_init" type="text" value="CITY" name="CITY" /></th>
							<th><input class="search_init" type="text" value="ZIP CODE" name="ZIP" /></th>
							<th><input class="search_init" type="text" value="SUBDIVISION" name="SUBDIVISIN" /></th>
							<th><input class="search_init showCalendar" type="text" value="DATE" name="date" /></th>
							<th>Time</th>
							<th>Assess Value</th>
							<th>Value</th>
							<th>Lot Size</th>
							<th>Pool Size</th>
							<th>Sqft</th>
							<th>Year Blt</th>
							<th>Open Bid</th>
							<th>Mrgn</th>
							<th>P/SF</th>
							<th>Real Margin</th>
							<th>Status</th>
							
						</tr>
					</tfoot>
					<tbody>
						<?php
						
						//Create Table Rows
						
						$i=0;
						while ($i < 10) {
						
						$address = mysql_result($result,$i, $row['address']);
						$city = mysql_result($result,$i, $row['city']);
						$zip = mysql_result($result,$i, $row['zip']);
						$subdivision = mysql_result($result,$i, $row['subdivision']);
						$date = mysql_result($result,$i, $row['date']);
						$time = mysql_result($result,$i, $row['time']);
						$assessedValue = mysql_result($result,$i, $row['value']);
						$lotSize = mysql_result($result,$i, $row['lotSize']);
						$poolSize = mysql_result($result,$i, $row['poolSize']);
						$squareFeet = mysql_result($result,$i, $row['squareFeet']);
						$yearBuilt = mysql_result($result,$i, $row['yearBuilt']);
						$openBid = mysql_result($result,$i, $row['openBid']);
						$status = mysql_result($result,$i, $row['status']);
						
					?>						

							<tr>
								<td><?php echo $address; ?></td>
								<td><?php echo $city; ?></td>
								<td><?php echo $zip; ?></td>
								<td><?php echo $subdivision; ?></td>
								<td><?php echo $date; ?></td>
								<td><?php echo $time; ?></td>
								<td><?php echo $value; ?></td>
								<td>0</td>
								<td><?php echo $lotSize; ?></td>
								<td><?php echo $poolSize; ?></td>
								<td><?php echo $squareFeet; ?></td>
								<td><?php echo $yearBuilt; ?></td>
								<td><?php echo $openBid; ?></td>
								<td>$0</td>
								<td>$sqft</td>
								<td>0%</td>
								<td><?php echo $status; ?></td>
							</tr>
					<?php
						}
					?>
					</tbody>
				</table>
			</div>
			
			<div class="spacer"></div>
			
		</div>
	</body>
</html>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You never increment $i in your while loop. You should put
 $i++
at the end of your while loop.

This is going to be slow... you should use mysql_fetch_assoc() inside of your loop to fetch an entire row into an array, then use the contents of that array in the body of your loop. That way you make one call to the mysql server per row, instead of 10-15 calls to mysql_result() per row.
Most Valuable Expert 2011
Top Expert 2016
Commented:
See near line 32:

    $result = mysql_query($query) or die('Error : ' . mysql_error());
   
    // RETRIEVES ONLY ONE ROW OF THE RESULTS SET
    $row = mysql_fetch_array($result, MYSQL_ASSOC);

The usual way of retrieving the results from a data base query goes something like this (see the iterator on line 86).
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-real-escape-string.php
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://us2.php.net/manual/en/function.mysql-query.php
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-fetch-assoc.php
echo "<pre>\n"; // MAKE IT EASY TO READ
while ($row = mysql_fetch_assoc($res))
{
    // MAN PAGE: http://us2.php.net/manual/en/function.var-dump.php
    var_dump($row);
}

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial