befidled
asked on
Problem looping through database
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.
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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
$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.