A simple function to search through a table in a MySQL database. Just fill in the variables when you call the function.           Try it out with ?userquery=i  

By : bigphish

<?php
function searchtable($database,$tablename,$userquery,$host,$user,$pass){
$link = mysql_connect($host, $user, $pass) or die("Could not connect: " . mysql_error());
$db = mysql_select_db($database, $link) or die(mysql_error());
$fields = mysql_list_fields($database, $tablename, $link);
$cols = mysql_num_fields($fields);
$content = "";
for ($i = 0; $i < $cols; $i++) {
$allfields[] = mysql_field_name($fields, $i);
}
foreach ($allfields as $myfield) {
$result = mysql_query("SELECT * FROM $tablename WHERE $myfield like '%$userquery%' ");
if (mysql_num_rows($result) > 0){
$content .= "<h3>search <i>$database</i> for <i>$userquery</i>, found match(es) in <i>$myfield</i>: </h3>\n";
$content .= "<table border=1 align=\"center\">\n\t<tr>\n";
for ($i = 0; $i < $cols; $i++) {
$content .= "\t\t<th";
if ($myfield == mysql_field_name($fields, $i)){
$content .= " bgcolor=\"orange\" ";
}
$content .= ">" . mysql_field_name($fields, $i) . "</th>\n";
}
$content .= "\t</tr>\n";
$myrow = mysql_fetch_array($result);
do {
$content .= "\t<tr>\n";
for ($i = 0; $i < $cols; $i++){
$content .= "\t\t<td";
if (preg_match("/$userquery/i", $myrow[$i])){
$mysplit = preg_split("/($userquery)/i", $myrow[$i], -1,PREG_SPLIT_DELIM_CAPTURE);
$content .= " bgcolor=\"orange\">";
for ($si=0;$si<count($mysplit);$si++){
if (preg_match("/$userquery/i", $mysplit[$si])){
$content .= "<font color=\"green\"><b>$mysplit[$si]</b></font>";
}else{
$content .= "$mysplit[$si]";
}
}
} else {
$content .= ">$myrow[$i]";
}
$content .= "&nbsp;</td>\n";
}
$content .= "\t</tr>\n";
} while ($myrow = mysql_fetch_array($result));
$content .= "</table>\n";
}
}
mysql_close($link);
return $content;
}

$page_content = searchtable(yourdb,your table,$userquery,yourhost,youruser,yourpass);
echo $page_content;


?>


//Database used as demo on my site

#
# Table structure for table `thistest`
#

CREATE TABLE thistest (
firstname varchar(20) NOT NULL default '',
lastname varchar(20) NOT NULL default '',
phone varchar(20) NOT NULL default '',
city varchar(20) NOT NULL default ''
) TYPE=MyISAM;

#
# Dumping data for table `thistest`
#

INSERT INTO thistest VALUES ('Joe', 'Smith', '(888) 555-1234', 'Mexico City');
INSERT INTO thistest VALUES ('Jane', 'Jiggles', '(888) 555-2345', 'Las Vegas');
INSERT INTO thistest VALUES ('Waldo', 'Wiggum', '(888) 555-3355', 'Tuktoyuktuk');
INSERT INTO thistest VALUES ('Big', 'Phish', '(888) 555-5555', 'San Diego');
INSERT INTO thistest VALUES ('Captian', 'Crunch', '(888) 888-8888', 'Mississippi');

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

More Search Code Articles
More By Codewalkers

 

developerWorks - FREE Tools!

Check out the new Jazz space on developerWorks <a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts. FREE! Go There Now!

NEW! Download a free trial of Lotus Quickr 8.0 Visit IBM developerWorks to download a free trial version of Lotus Quickr 8.0, which enables collaboration by transforming the way everyday business content such as documents, rich media, photos, and video can be shared. Lotus Quickr makes it faster and easier to share content of all types (not just documents) within virtual teams. It is designed to make it easier to collaborate across organizational boundaries, while continuing to work within the context of familiar desktop applications. FREE! Go There Now!

NEW! Innovate don't duplicate! Asset reuse strategies for success Asset Reuse is a key strategy for companies looking to create innovative solutions to solve complex software development problems. Searching for, identifying, updating, using and deploying software assets can be a difficult challenge. Listen to this webcast, to learn about strategies and tools that you can leverage for a successful project, including Rational Asset Manager, Rational Software Architect and WebSphere Service Registry and Repository. FREE! Go There Now!

NEW! Using Rational Business Developer to enhance your developer productivity Join this Rational Talks to You teleconference, to hear how Enterprise Generation Language (EGL) eliminates the need for tedious and error-prone low level coding, so developers can focus on business requirements. EGL extends the Rational software development platform with a simplified programming language that enables developers who have little or no experience with Java, Web technologies or Service Oriented Architecture, to create enterprise-class applications and services quickly and easily. It also allows developers who may have little or no mainframe programming experience to quickly create traditional mainframe components. FREE! Go There Now!

Role of Integrated Requirements Management in Software Delivery As organizations integrate software into every aspect of business, they are constantly pressured to deliver faster, better, and cheaper results. Unfortunately, a “dis-integrated” software delivery approach reduces returns while increasing costs. This IBM Rational White Paper shows how Integrated Requirements Management aligns organizations around maximizing value and keeping pace with change. FREE! Go There Now!

NEW! Krugle, developerWorks, and code search Ken Krugler, co-founder of code search company Krugle, and Laura Merling, vice president of Marketing and Business Development for Krugle, join to talk about the ins and outs of code search and what it means as a new feature for developerWorks users. FREE! Go There Now!

NEW! Trial download: IBM Lotus Forms V3.0 Get a free trial download of IBM Lotus Forms V3.0 (formerly Workplace Forms), which provides a zero-footprint eForms solution to help you automate and move forms-based business processes off the desktop and onto the Web. With Lotus Forms, you can extend applications beyond the firewall by creating a single electronic form document ready for use in both thick and Web 2.0 thin client format. FREE! Go There Now!

NEW! Evaluate WebSphere Extended Deployment Compute Grid V6.1 Visit IBM developerWorks to download a free trial version of WebSphere Extended Deployment Compute Grid, which lets you schedule, execute, and monitor batch jobs. Because online transaction processing and batch jobs execute simultaneously on the same server resources, you can avoid costly duplication of resources. Compute Grid supports job types of Java transactional batch, compute-intensive and a new type called "native execution", which enables non-Java workloads to run on distributed end points. FREE! Go There Now!

NEW! Trial download: IBM Rational Functional Tester V7.0.1 Get a free trial download of the latest version of IBM Rational Functional Tester V7.0.1. Rational Functional Tester is an automated functional and regression testing solution for QA teams concerned with the quality of their Java, Microsoft Visual Studio .NET, and Web-based applications. FREE! Go There Now!

NEW! Trial download: IBM Rational Performance Tester V7.0.1 Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads. FREE! Go There Now!

All FREE IBM® developerWorks Tools!