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!

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! Hello World: WebSphere Service Registry and Repository Manage, govern, and share services across your organization by using WebSphere Service Registry and Repository. Follow the hands-on exercises to learn how to navigate the Web interface to publish, find, reuse, and update services. FREE! Go There Now!

NEW! Whitepaper: Delivering SOA solutions: service lifecycle management The unprecedented scope of a service-oriented architecture (SOA) initiative brings to the forefront a number of management and governance issues that were sidestepped in the past. The key to a successful SOA implementation is managing and governing activities throughout the entire SOA delivery lifecycle by ensuring that services conform to the needs of all of the business’s stakeholders. Learn how service lifecycle management allows the business to ensure that the process by which services are defined, created, tested, deployed, optimized and retired is manageable, repeatable and auditable. FREE! Go There Now!

NEW! Develop Systems Software Assets with IBM Rational Asset Manager Join us for this on demand webcast to learn about developing complex systems more quickly and efficiently. We'll cover market drivers for developing, governing and reusing systems software assets and how you can develop system software assets with Rational Asset Manager. FREE! Go There Now!

NEW! Project and Portfolio Management Executive Resource Kit Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle. FREE! Go There Now!

NEW! Download IBM Rational Developer for System z Download a free trial version of IBM Rational Developer for System z, software that can help you deliver core development capabilities; the power of Java Platform, Enterprise Edition (Java EE); and rapid application development support to diverse enterprise application development teams. With comprehensive development tools to help create, deploy and maintain traditional enterprise and composite applications, Rational Developer for System z enables developers with different technical backgrounds to easily participate in important technology projects. FREE! Go There Now!

Be the first to hear about i5/OS V6R1! Hold your calendar on January 30, 2008 for this free webcast on the new i5/OS. Rational's Enterprise Modernization products will be discussed at this webcast as they help to drive the application development environment for this new System i OS. <br />And learn how i5/OS will take you to the next step of efficient, resilient business processing. You will hear about the new i5/OS capabilities as it will be the most significant i5/OS release in years. If you cannot join the webcast on 1/30/08 you can still use this link to listen to the replay.<br /> FREE! Go There Now!

NEW! Download the free Web Application Security eKit Discover how IBM Rational AppScan Standard Edition can help you detext vulnerabilities in your web applications in the Web Application Security eKit. IBM Rational AppScan is a leading suite of automated web application security solutions that scan and test for common Web application vulnerabilities. The new Web Application Security eKit provides you with valuable resources, including white papers, demos, and additional information on the benefits of testing your Web applications. FREE! Go There Now!

NEW! Understanding Web application security challenges As businesses grow increasingly dependent upon Web applications, these complex entities grow more difficult to secure. Most companies equip their Web sites with firewalls, Secure Sockets Layer (SSL), and network and host security, but the majority of attacks are on applications themselves – and these technologies cannot prevent them. This paper explains what you can do to help protect your organization, and it discusses an approach for improving your organization’s Web application security. FREE! Go There Now!

NEW! Best Practices: The Integrated Project and Portfolio Management Platform. Hear how IBM Rational Project and Portfolio Management integrated solutions help teams put the right tools and processes in place to maximize the effectiveness and efficiency of project teams and ensure that the business vision is being executed correctly. Learn how to automate and integrate requirements prioritization, top-down project planning, communications and controls, and methodology deployment to keep your scope, costs, and schedules under control. Tackle with an end-to-end approach the management of scope and scope changes, usage of methodology to control and empower project teams, and optimization of resources to align activity costs with the overall project plan. FREE! Go There Now!

All FREE IBM® developerWorks Tools!