Reputation: 13216
Hi everyone I've been extracting rows from a SQL table 1 by 1. I wrote this code two years ago and realise how hideously ineffective it is. I'd like to speed things up by typing up a simple for-loop to automate the coding.
$maxRows_dd1 = 10;
$pageNum_dd1 = 0;
if (isset($_GET['pageNum_dd1'])) {
$pageNum_dd1 = $_GET['pageNum_dd1'];
}
$startRow_dd1 = $pageNum_dd1 * $maxRows_dd1;
$maxRows_dd2 = 10;
$pageNum_dd2 = 1;
if (isset($_GET['pageNum_dd2'])) {
$pageNum_dd2 = $_GET['pageNum_dd2'];
}
$startRow_dd2 = $pageNum_dd2 * $maxRows_dd2;
$maxRows_dd3 = 10;
$pageNum_dd3 = 2;
if (isset($_GET['pageNum_dd3'])) {
$pageNum_dd3 = $_GET['pageNum_dd3'];
}
$startRow_dd3 = $pageNum_dd3 * $maxRows_dd3;
... dd4 to dd99 go in between!
$maxRows_dd100 = 10;
$pageNum_dd100 = 99;
if (isset($_GET['pageNum_dd99'])) {
$pageNum_dd32 = $_GET['pageNum_dd99'];
}
$startRow_dd99 = $pageNum_dd99 * $maxRows_dd99;
which corresponds to:
mysql_select_db($database_rent, $rent);
$query_dd1 = "SELECT * FROM rent";
$query_limit_dd1 = sprintf("%s LIMIT %d, %d", $query_dd1, $startRow_dd1, $maxRows_dd1);
$dd1 = mysql_query($query_limit_dd1, $rent) or die(mysql_error());
$row_dd1 = mysql_fetch_assoc($dd1);
if (isset($_GET['totalRows_dd1'])) {
$totalRows_dd1 = $_GET['totalRows_dd1'];
} else {
$all_dd1 = mysql_query($query_dd1);
$totalRows_dd1 = mysql_num_rows($all_dd1);
}
$totalPages_dd1 = ceil($totalRows_dd1/$maxRows_dd1)-1;
mysql_select_db($database_rent, $rent);
$query_dd2 = "SELECT * FROM rent";
$query_limit_dd2 = sprintf("%s LIMIT %d, %d", $query_dd2, $startRow_dd2, $maxRows_dd2);
$dd2 = mysql_query($query_limit_dd2, $rent) or die(mysql_error());
$row_dd2 = mysql_fetch_assoc($dd2);
if (isset($_GET['totalRows_dd2'])) {
$totalRows_dd2 = $_GET['totalRows_dd2'];
} else {
$all_dd2 = mysql_query($query_dd2);
$totalRows_dd2 = mysql_num_rows($all_dd2);
}
$totalPages_dd2 = ceil($totalRows_dd2/$maxRows_dd2)-1;
mysql_select_db($database_rent, $rent);
$query_dd3 = "SELECT * FROM rent";
$query_limit_dd3 = sprintf("%s LIMIT %d, %d", $query_dd3, $startRow_dd3, $maxRows_dd3);
$dd3 = mysql_query($query_limit_dd3, $rent) or die(mysql_error());
$row_dd3 = mysql_fetch_assoc($dd3);
if (isset($_GET['totalRows_dd3'])) {
$totalRows_dd3 = $_GET['totalRows_dd3'];
} else {
$all_dd3 = mysql_query($query_dd3);
$totalRows_dd3 = mysql_num_rows($all_dd3);
}
$totalPages_dd3 = ceil($totalRows_dd3/$maxRows_dd3)-1;
... all the way to dd100!!!
How would I use a for loop to speed typing up all this code for each block of code from dd1 to dd100?
Upvotes: 2
Views: 281
Reputation: 88697
This is a much more efficient code to do exactly what you do above:
<?php
// You only need to do these once as they are the same throughout
mysql_select_db($database_rent, $rent);
$maxRows = 10;
// This code gets the total number of rows in the database
$totalRowsAll = mysql_fetch_assoc(mysql_query("SELECT count(*) AS total FROM rent", $rent));
$totalRowsAll = (int) $totalRowsAll['total'];
?>
<div class="tab_container">
<div id="tab1" class="tab_content">
<table width="100%" border="0" cellspacing="5" cellpadding="5" id="1">
<?php
for ($i = 0; $i < 100; $i++) {
// Calcluate value for this iteration and query database
$pageNum = (isset($_GET['pageNum_dd'.($i + 1)])) ? (int) $_GET['pageNum_dd'.($i + 1)] : $i;
$startRow = $pageNum * $maxRows;
$query = "SELECT * FROM rent LIMIT $startRow, $maxRows";
$result = mysql_query($query, $rent) or die(mysql_error($rent));
$totalRows = (isset($_GET['totalRows_dd1'])) ? (int) $_GET['totalRows_dd1'] : $totalRowsAll;
${'totalPages_dd'.($i + 1)} = ceil($totalRows / $maxRows) - 1;
// Now print this row
?>
<tr height="100px" align="center">
<?php
while ($row = mysql_fetch_assoc($query)) {
?>
<td style="background-color: <?php echo $row['colour']; ?>;" onclick="window.location='pay.php?id=<?php echo $row['dNo']; ?>&user=<?php echo $username; ?>'" onmouseover="this.style.cursor='pointer'">
<form action="pay.php?id=<?php echo $row['dNo']; ?>&user=<?php echo $username; ?>" method="post">
<input type="hidden" id="<?php echo $row['dNo']; ?>">
<input type="hidden" value="<?php echo $username; ?>">
<button type="submit" class="link" id="t<?php echo $row['dNo']; ?>">
<span><?php echo $row['dNo']; ?></span>
</button>
</form>
</td>
<?php
} // End while
?>
</tr>
<?php
} // End for
?>
</table>
</div>
</div>
...however:
I'm fairly sure this could be summed up in a single query to get all the results you need, which would be much more efficient and drastically reduce database load. But because of the $_GET['pageNum_dd*']
and $_GET['totalPages_dd*']
variables which are used on a per row basis, I am not 100% sure about this, and I can't work out how this would be done without knowing more about what is produced. You need to examine whether or not these parameters that can be passed are actually necessary/useful. As it is, they may be cause rows of a varying length, with an unequal number of cells per row - which is probably not what you want.
The same also goes for the variables $totalPages_dd*
, which are assigned below but never used anywhere. They may not be useful, and assigning them may be pointless.
Upvotes: 1