I'm having a problem with a search form that pulls results from a MySQL database. I'm building a website that includes 100 logos for the 2008 Fortune 500, Top 100 companies.
I want the search results to always display 5 columns and 2 rows in the XHTML table, even if there are fewer than 10 search results.
Also, if there are more than 10 search results, I want the XHTML table to display 5 columns and 2 rows with a "Next" link on the first search result page and then "Previous" and possibly "Next" links on the next pages depending on how many search results come up.
If you go to the test homepage at http://celebritas.com/logos/template/index2.php 5 columns and 2 rows in tables and the "Next" and "Previous" links are displayed correctly.
Below is the code to select the MySQL data for the search function (see piece of code named "SHOWMAX" and variable named "$prepSearch"):
<?php
//mysqlPrepINC.php START
// include MySQL connector function
if (! @include('connectINC.php')) {
echo 'Sorry, page unavailable';
exit;
}
// define number of columns in table
define('COLS', 5);
// set maximum number of records per page
define('SHOWMAX', 10);
// create a connection to MySQL
$conn = dbConnect('query');
// prepare SQL to get total records
//$getTotal = 'SELECT COUNT(*) FROM logos_captions2';
$getTotal = 'SELECT COUNT(*) FROM logos_caps';
// submit query and store result as $totalPix
$total = mysql_query($getTotal);
$row = mysql_fetch_row($total);
$totalPix = $row[0];
// set the current page
$curPage = isset($_GET['curPage']) ? $_GET['curPage'] : 0;
// calculate the start row of the subset
$startRow = $curPage * SHOWMAX;
// prepare SQL to retrieve subset of image details
$prep = basename($_SERVER['SCRIPT_NAME'], '.php');
$prep = str_replace('_', ' ', $prep);
if ($prep == 'index') {
$sql = "SELECT * FROM logos_caps ORDER BY altName ASC LIMIT $startRow,".SHOWMAX;
}
elseif ($prep == 'index2') {
$sql = "SELECT * FROM logos_caps ORDER BY altName ASC LIMIT $startRow,".SHOWMAX;
}
elseif ($prep == 'alpha2') {
$sql = "SELECT * FROM logos_caps ORDER BY altName ASC LIMIT $startRow,".SHOWMAX;
}
elseif ($prep == 'alpha3') {
$sql = "SELECT * FROM logos_caps ORDER BY altName ASC LIMIT $startRow,".SHOWMAX;
}
elseif ($prep == 'rank') {
$sql = "SELECT * FROM logos_caps ORDER BY ID ASC LIMIT $startRow,".SHOWMAX;
}
elseif ($prep == 'rank2') {
$sql = "SELECT * FROM logos_caps ORDER BY ID ASC LIMIT $startRow,".SHOWMAX;
}
elseif ($prep == 'rank3') {
$sql = "SELECT * FROM logos_caps ORDER BY ID ASC LIMIT $startRow,".SHOWMAX;
}
elseif ($prep == 'search') {
// $sql= "SELECT rank, altName, name FROM logos_caps WHERE rank LIKE '%" . $biz . "%' OR altName LIKE '%" . $biz ."%' OR name LIKE '%" . $biz ."%'";
$sql= "SELECT ID, rank, altName, name FROM logos_caps WHERE rank LIKE '%" . $biz . "%' OR altName LIKE '" . $biz ."%' OR name LIKE '" . $biz ."%' LIMIT 0, 10";
}
//VARIABLE TO DEFINE SQL ROWS SELECTED FOR A SEARCH
$prepSearch = $_SERVER["QUERY_STRING"];
if ($prepSearch == 'go') {
$sql= "SELECT ID, rank, altName, name FROM logos_caps WHERE rank LIKE '" . $biz . "' OR altName LIKE '" . $biz ."%' OR name LIKE '" . $biz ."%'ORDER BY altName ASC LIMIT $startRow,".SHOWMAX;
}
// submit the query (MySQL original)
$result = mysql_query($sql) or die(mysql_error());
// extract the first record as an array
$row = mysql_fetch_assoc($result);
//mysqlPrepINC.php END
?> Below is the code to create the "Next" and "Previous" links and the extra, blank table cells if there's a lack of records:
<!-- directionsINC.php START -->
<table class="directions">
<tr>
<td>
<?php
// create a back link if current page greater than 0
if ($curPage > 0) {
echo '<a href="'.$_SERVER['PHP_SELF'].'?curPage='.($curPage-1).'">< Previous</a>';
}
// otherwise leave the cell empty
else {
echo ' ';
}
?>
</td>
<?php
// pad the final row with empty cells if more than 2 columns
if (COLS-2 > 0) {
for ($i = 0; $i < COLS-2; $i++) {
echo '<td> </td>';
}
}
?>
<td>
<span class="right">
<?php
// create a forwards link if more records exist
if ($startRow+SHOWMAX < $totalPix) {
echo '<a href="'.$_SERVER['PHP_SELF'].'?curPage='.($curPage+1).'">Next ></a>';
}
// otherwise leave the cell empty
else {
echo ' ';
}
?>
</span>
</td>
</tr>
</table>
<!-- directionsINC.php END -->Below is code that pulls data from the MySQL database and prints out the cells with data (see "alphaFirstINC.php"):
<!-- imageRepeatRankINC.php START -->
<!--This row needs to be repeated-->
<?php
// initialize cell counter outside loop
$pos = 0;
do {
// set caption if thumbnail is same as main image
if ($row['ID'] == $mainImage) {
$caption = $row['altName'];
}
?>
<td>
<a rel="lyteshow[vacation]" title="<?php echo $row['altName'].'<br />Full Name:<br />'.$row['name'].'<br />Top 100: #'.$row['rank'].'<br />Industry Group:<br />'.$row['group']; ?>" href="images/logos<?php echo $row['ID']; ?>.jpg">
<img src="images/logos<?php echo $row['ID']; ?>.jpg" alt="<?php echo $row['altName']; ?>" width="140" height="140" />
</a>
<?php
$repeat = basename($_SERVER['SCRIPT_NAME'], '.php');
$repeat = str_replace('_', ' ', $repeat);
if ($repeat == 'index') {
include('alphaFirstINC.php');
}
elseif ($repeat == 'index2') {
include('alphaFirstINC.php');
}
elseif ($repeat == 'alpha2') {
include('alphaFirstINC.php');
}
elseif ($repeat == 'alpha3') {
include('alphaFirstINC.php');
}
elseif ($repeat == 'rank') {
include('rankFirstINC.php');
}
elseif ($repeat == 'rank2') {
include('rankFirstINC.php');
}
elseif ($repeat == 'rank3') {
include('rankFirstINC.php');
}
//SEARCH FUNCTION IMAGE REPEAT
elseif ($repeat == 'index?go') {
include('rankFirstINC.php');
}
//SEARCH FUNCTION IMAGE REPEAT
elseif ($repeat == 'index2?go') {
include('rankFirstINC.php');
}
//SEARCH FUNCTION IMAGE REPEAT
elseif ($repeat == 'rank?go') {
include('rankFirstINC.php');
}
//SEARCH FUNCTION IMAGE REPEAT
elseif ($repeat == 'rank2?go') {
include('rankFirstINC.php');
}
?>
</td>
<?php
$row = mysql_fetch_assoc($result);
// increment counter after next row extracted
$pos++;
// if at end of row and records remain, insert tags
if ($pos%COLS === 0 && is_array($row)) {
echo '</tr><tr>';
}
} while($row); // end of loop
// new loop to fill in final row
while ($pos%COLS) {
echo '<td> </td>';
$pos++;
}
?>
<!-- imageRepeatRankINC.php END -->Also, below is my code that pulls data from the MySQL database and print out the tables:
<!-- alphaFirstINC.php START -->
<br /><?php echo $row['altName']; ?>
<br />Top 100: #<?php echo $row['rank']; ?>
<!-- alphaFirstINC.php END -->Any help would be appreciated!:p
)...