Search Box Pulling from MySQL

[risingPhoenix]

Hi,

I'm creating a website for my web design internship that lists the top 100 of the 2008 Fortune 500 companies. The website's server is running PHP 5.2.5 and MySQL 4.1.22.

I want to create a search function where people can type in words of numbers into a text box and result are pulled from a MySQL database. For example, if someone types "wal" into a search box then the logos of "Walgreens" and "Wal-Mart" and their names will come up. I need help with writing the PHP, MySQL, and XHTML.

I've entered the company names and their rankings in a MySQL database and built two web pages that list the top 100 Fortune 500 companies alphabetically and by rank:

Alphabetically:
http://celebritas.com/logos/template/alpha3.php

Rank:
http://celebritas.com/logos/template/rank3.php

I want to create a search box written in PHP and MySQL that I can put into both web pages so users can search the MySQL by comany name (like "wal" would pull up "Wal-Mart" and "Walgreen") or by ranking (like "1" would pull "Wal-Mart"). I need help writing the PHP, MySQL, and XHTML.

The PHP code that gets my MySQL tables prepared is below:

$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
//$sql = "SELECT * FROM logos_captions2 LIMIT $startRow,".SHOWMAX;
//$sql = "SELECT * FROM logos_captions2 ORDER BY cap2BizName ASC LIMIT $startRow,".SHOWMAX;
$title = basename($_SERVER['SCRIPT_NAME'], '.php');
$title = str_replace('_', ' ', $title);

if ($title == 'alpha2') {
$sql = "SELECT * FROM logos_caps ORDER BY altName ASC LIMIT $startRow,".SHOWMAX;
}

elseif ($title == 'alpha3') {
$sql = "SELECT * FROM logos_caps ORDER BY altName ASC LIMIT $startRow,".SHOWMAX;
}

elseif ($title == 'rank2') {
$sql = "SELECT * FROM logos_caps ORDER BY ID ASC LIMIT $startRow,".SHOWMAX;
}

elseif ($title == 'rank3') {
$sql = "SELECT * FROM logos_caps ORDER BY ID 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
?>

The PHP and MySQL code that pulls data from my MySQL table repeats table columns and rows for it is below (for "alpha3.php" only):

<!-- imageRepeatINC.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>
<img src="images/logos<?php echo $row['ID']; ?>.jpg" alt="<?php echo $row['altName']; ?>" width="140" height="140" />
<br /><?php echo $row['altName']; ?>
<br />Top 1-100: #<?php echo $row['rank']; ?>
</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>&nbsp;</td>';
$pos++;
}
?>
<!-- imageRepeatINC.php END -->

Thank you!

[Phill Pafford]

Sounds like AJAX to me, here are some good examples:

Example #1 (http://www.w3schools.com/PHP/php_ajax_database.asp)

Example #2 (http://www.w3schools.com/PHP/php_ajax_livesearch.asp)

[MyWebsiteAdvise]

Hi,

I can recommend you this article: Using MySQL Full-text Searching (http://devzone.zend.com/node/view/id/1304)

[risingPhoenix]

Thank you for the help. I've almost go the search box completed. You can see it at the website below:

http://celebritas.com/logos/template/index2.php

There's still a few bugs to work out but I'm close to completing it.

[MyWebsiteAdvise]

Nice :)

[BenignEnvy.com]

Sounds like AJAX to me, here are some good examples:

Example #1 (http://www.w3schools.com/PHP/php_ajax_database.asp)

Example #2 (http://www.w3schools.com/PHP/php_ajax_livesearch.asp)


I tried Ex 1 and didn't get my mySQL database results.. It looks like nothing happened. My hosting plan allows me to use only one such database.. Is that the problem ..

i.e.:

http://adrie.awardspace.com/htmlpage.html

HELP!

[BenignEnvy.com]

Thank you for the help. I've almost go the search box completed. You can see it at the website below:

http://celebritas.com/logos/template/index2.php

There's still a few bugs to work out but I'm close to completing it.


Teach ME?!

~Adrie

[cbVision]

Well you'd just need to create a search form that calls a php file that outputs the results.

The search form would be as simple as:
<form action="search.php" method="post">
<input type="text" name="searchfor" />
<input type="submit" />
</form>

search.php would include:
..include your connection strings
extract $_POST;
$query = 'SELECT * FROM tablename WHERE companyname LIKE "$searchfor"';
$results = mysql_query($query);
while($row=mysql_fetch_assoc($result)) {
print($row['companyname']);
print("<br />");
}

This should print a list of all records containing whatever was searched for.