PHP MySql Search

Hi There,
Long time no speak. Hope all is well.
I have a small problem I hope you can help me with...
I have a MySql database with data. I am looking to write a simple lookup query in php  to look up a number (Text Input)-(register no in one of the tables) and if found display a table with data from the database related to that register number. Hope someone can help...
I have very very little php knowledge so please be patient&&
Regards,
Swannie
Martin-SwannieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrianMMCommented:
Here is a rough and ready 'stock search' i done ( I admit not my finest code but it works). Sounds like your requirements... feel free to hack it up to suit your data :)
<?php
// DB Configuration
define("DB_HOST", "localhost");
define("DB_USER", "amelec");
define("DB_PASS", "amelec"); 
define("DB_DB", "amelec"); 
define("STOCK_SHOW_TOTAL", false);
 
/*** Highlight search word(s) in a string ***/
function highlightWords($string, $words) {
    foreach ( $words as $word ) {
        $string = str_ireplace($word, '<span class="highlight_word">'.$word.'</span>', $string);
    }
    /*** return the highlighted string ***/
    return $string;
}
 
/*** Protect against SQL injection ***/
function cleanQuery($string) {
	if(get_magic_quotes_gpc()) {  // prevents duplicate backslashes
		$string = stripslashes($string);
	}
	if (phpversion() >= '4.3.0') {
		$string = mysql_real_escape_string($string);
	} else {
		$string = mysql_escape_string($string);
	}
	return $string;
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html lang="en">
<head>
	<meta http-equiv="content-type" content="text/html; charset=iso-8859-1">
	<title>STOCK SEARCH</title>
	<link rel = "stylesheet" type="text/css" href="amelec.css">
</head>
<body>
 
		<div align="center">
			<h3>ENTER A PART NUMBER:</h3>
			<form method="post" action="/stock.php">
				<input type="text" name="part_no">
				<input type="submit" value="GO">
			</form>
		</div>
<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
	if ($_POST['part_no'] != "") {
		// handle POST here
		mysql_connect(DB_HOST, DB_USER, DB_PASS) or die(mysql_error());
		mysql_select_db(DB_DB) or die(mysql_error());
 
		// Do we want to display total items?
		if (STOCK_SHOW_TOTAL) {
			$queryCount = "SELECT COUNT(part_id) AS PartCount FROM amelec_parts"; 
			$resultCount = mysql_query($queryCount) or die(mysql_error());
			if(mysql_num_rows($resultCount) > 0) {
				$row = mysql_fetch_array($resultCount);
				$totalParts = $row['PartCount'];
			} else {
				$totalParts = 0;
			}
		}
		
		// Get part(s) where the 'part no' contains string.
		$query = "SELECT * FROM amelec_parts WHERE part_no LIKE '%".cleanQuery($_POST['part_no'])."%'"; 
		$result = mysql_query($query) or die(mysql_error());
 
		// Display some friendly stats
		echo "<div align=\"center\" class=\"form-info\">Part No Requested: <b>".$_POST['part_no']."</b>";
		if(mysql_num_rows($result) > 0) {
			echo " - Parts Found: <b>".mysql_num_rows($result)."</b>";
		} else {
			echo " - Parts Found: <b>NONE</b>";
		}
		echo "</div>";
		// Do we have results?
		if(mysql_num_rows($result) > 0) {
			$words = explode(" ",$_POST['part_no']);
?>
		<hr>
		<table width="98%" align="center" style="padding:10px;">
			<tr>
				<td width="15%" align="left" class="menu-header">Part Number</td>
				<td>&nbsp;</td>
				<td width="90%" align="left" class="menu-header">Available Qty</td>
			</tr>
<?php
	
			while($row = mysql_fetch_array($result)){
				echo "<tr>";
				echo "<td class=\"result\">";
				echo highlightWords($row['part_no'], $words); ;
				echo "</td>";
				echo "<td>&nbsp;</td>";
				echo "<td class=\"result\">";
				echo $row['part_available_qty'];
				echo "</td>";
				echo "</tr>";
			}
?>
		</table>
<?php
			// Do we want to display total items?
			if (STOCK_SHOW_TOTAL) {
				echo "Total Parts: ".$totalParts;
			}
		} else {
			echo "<br><div align=\"center\"><b>No results for your query. Please try an alternative Part Number.</b></div><br>";
		}
	} else {
		echo "<div align=\"center\" class=\"form-warn\"><b>PLEASE ENTER A PART NUMBER TO SEARCH FOR</b></div>";
	}
} 
?>
</body>
</html>

Open in new window

0
Martin-SwannieAuthor Commented:
Thanks for the quick response........

Am correct in assuming that the php portion will be stock.php and the html will be the search page or is it all one form?
0
BrianMMCommented:
all one page... self-submitting form.

The will be displayed then posted to itself and form displayed with results under... then they can just quickly change search without needing to reload a new page.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Martin-SwannieAuthor Commented:
Give me a couple of minutes...... I'll test it now
0
Martin-SwannieAuthor Commented:
I get the following error:

Not Found

The requested URL /search1.php was not found on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.
Apache/2.2.8 (Unix) mod_ssl/2.2.8 OpenSSL/0.9.8b mod_auth_passthrough/2.1 mod_bwlimited/1.4 FrontPage/5.0.2.2635 PHP/5.2.5 Server at www.castdcssa.co.za Port 80

Any Ideas??

Swannie
<?php
// DB Configuration
define("DB_HOST", "localhost");
define("DB_USER", "castdcss_admin");
define("DB_PASS", "t1nk3rb3ll123"); 
define("DB_DB", "castdcss"); 
define("STOCK_SHOW_TOTAL", false);
 
/*** Highlight search word(s) in a string ***/
function highlightWords($string, $words) {
    foreach ( $words as $word ) {
        $string = str_ireplace($word, '<span class="highlight_word">'.$word.'</span>', $string);
    }
    /*** return the highlighted string ***/
    return $string;
}
 
/*** Protect against SQL injection ***/
function cleanQuery($string) {
        if(get_magic_quotes_gpc()) {  // prevents duplicate backslashes
                $string = stripslashes($string);
        }
        if (phpversion() >= '4.3.0') {
                $string = mysql_real_escape_string($string);
        } else {
                $string = mysql_escape_string($string);
        }
        return $string;
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html lang="en">
<head>
        <meta http-equiv="content-type" content="text/html; charset=iso-8859-1">
        <title>REF SEARCH</title>
        
</head>
<body>
 
                <div align="center">
                        <h3>ENTER A REF NUMBER:</h3>
                        <form method="post" action="search1.php">
                                <input type="text" name="NDMA_Ref">
                                <input type="submit" value="Search">
                        </form>
                </div>
<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
        if ($_POST['NDMA_Ref'] != "") {
                // handle POST here
                mysql_connect(DB_HOST, DB_USER, DB_PASS) or die(mysql_error());
                mysql_select_db(DB_DB) or die(mysql_error());
 
                // Do we want to display total items?
                if (STOCK_SHOW_TOTAL) {
                        $queryCount = "SELECT COUNT(lastname) AS NameCount FROM authorize"; 
                        $resultCount = mysql_query($queryCount) or die(mysql_error());
                        if(mysql_num_rows($resultCount) > 0) {
                                $row = mysql_fetch_array($resultCount);
                                $totalParts = $row['NameCount'];
                        } else {
                                $totalParts = 0;
                        }
                }
                
                // Get part(s) where the 'part no' contains string.
                $query = "SELECT * FROM authorize WHERE lastname LIKE '%".cleanQuery($_POST['NDMA_Ref'])."%'"; 
                $result = mysql_query($query) or die(mysql_error());
 
                // Display some friendly stats
                echo "<div align=\"center\" class=\"form-info\">Ref No Requested: <b>".$_POST['NDMA_Ref']."</b>";
                if(mysql_num_rows($result) > 0) {
                        echo " - Names Found: <b>".mysql_num_rows($result)."</b>";
                } else {
                        echo " - Names Found: <b>NONE</b>";
                }
                echo "</div>";
                // Do we have results?
                if(mysql_num_rows($result) > 0) {
                        $words = explode(" ",$_POST['NDMA_Ref']);
?>
                <hr>
                <table width="98%" align="center" style="padding:10px;">
                        <tr>
                                <td width="15%" align="left" class="menu-header">Part Number</td>
                                <td> </td>
                                <td width="90%" align="left" class="menu-header">Available Qty</td>
                        </tr>
<?php
        
                        while($row = mysql_fetch_array($result)){
                                echo "<tr>";
                                echo "<td class=\"result\">";
                                echo highlightWords($row['NDMA_Ref'], $words); ;
                                echo "</td>";
                                echo "<td> </td>";
                                echo "<td class=\"result\">";
                                echo $row['part_available_qty'];
                                echo "</td>";
                                echo "</tr>";
                        }
?>
                </table>
<?php
                        // Do we want to display total items?
                        if (STOCK_SHOW_TOTAL) {
                                echo "Total Names: ".$totalParts;
                        }
                } else {
                        echo "<br><div align=\"center\"><b>No results for your query. Please try an alternative Ref Number.</b></div><br>";
                }
        } else {
                echo "<div align=\"center\" class=\"form-warn\"><b>PLEASE ENTER A REF NUMBER TO SEARCH FOR</b></div>";
        }
} 
?>
</body>
</html>

Open in new window

0
Martin-SwannieAuthor Commented:
I fixed the error above and get the following result:

Ref No Requested: Swanepoel - Names Found: 1

But how do I show the data related to the result? I think it must go here somewhere..
if(mysql_num_rows($result) > 0) {
                        $words = explode(" ",$_POST['NDMA_Ref']);
?>
                <hr>
                <table width="98%" align="center" style="padding:10px;">
                        <tr>
                                <td width="15%" align="left" class="menu-header">Part Number</td>
                                <td> </td>
                                <td width="90%" align="left" class="menu-header">Available Qty</td>
                        </tr>
<?php
        
                        while($row = mysql_fetch_array($result)){
                                echo "<tr>";
                                echo "<td class=\"result\">";
                                echo highlightWords($row['NDMA_Ref'], $words); ;
                                echo "</td>";
                                echo "<td> </td>";
                                echo "<td class=\"result\">";
                                echo $row['part_available_qty'];
                                echo "</td>";
                                echo "</tr>";
                        }
?>
                </table>
<?php
                        // Do we want to display total items?
                        if (STOCK_SHOW_TOTAL) {
                                echo "Total Names: ".$totalParts;
                        }
                } else {
                        echo "<br><div align=\"center\"><b>No results for your query. Please try an alternative Ref Number.</b></div><br>";
                }
        } else {
                echo "<div align=\"center\" class=\"form-warn\"><b>PLEASE ENTER A REF NUMBER TO SEARCH FOR</b></div>";
        }
} 
?>

Open in new window

0
BrianMMCommented:
well visiting: http://www.castdcssa.co.za/ i don't see the file! is it uploaded?
0
Martin-SwannieAuthor Commented:
0
BrianMMCommented:
exactly...

1) set up what columns you want...

         <table width="98%" align="center" style="padding:10px;">
                        <tr>
                                <td width="15%" align="left" class="menu-header">Part Number</td>
                                <td> </td>
                                <td width="90%" align="left" class="menu-header">Available Qty</td>
                        </tr>


2) in the while loop, build up the data you want to display...:

$row['your_field_name'] will get the value for than column name, so it's a case of wrapping a <td> </td> around each bit.. making sure your first thing is the <tr> and last the </tr>:


                     while($row = mysql_fetch_array($result)){
                                echo "<tr>";
                                echo "<td>";
                                echo $row['field1'];
                                echo "</td>";
                                echo "<td>";
                                echo $row['field2'];
                                echo "</td>";
                                echo "<td>";
                                echo $row['field3'];
                                echo "</td>";
                                echo "<td>";
                                echo $row['field...etc'];
                                echo "</td>";
                                echo "</tr>";
                        }


btw you dont need  echo highlightWords($row['field1'], $words); but handy bit of code for you!
0
Martin-SwannieAuthor Commented:
Lets say field1 is the name and field2 is the lastname(columns in my Table)

.. do I put that database "firstname" for field1 and "lastname" for field2?

Apologies if this sounds like a stupid question..


Swannie
0
Martin-SwannieAuthor Commented:
I got it to work - you are a star....

Thank you


Swannie
0
BrianMMCommented:
No such thing as stupid question... we all have to begin somewhere!

Simply put the names of the columns as per MySQL's names :)

So if your two columns are "firstname" and "lastname", the you would write $row['firstname'] and $row['lastname']
0
BrianMMCommented:
as I said apologies not the tidyest code... but a good bit of code for example and showing how it works so good for this occasion :)

Feel free to keep in touch if you need any more guidance... either thru here i think my profile will have details or something - forget how expert-exchange works sometimes!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrianMMCommented:
If you want to close a Q you should award the points if you got a solution! :)
0
Martin-SwannieAuthor Commented:
Thank You
0
Martin-SwannieAuthor Commented:
Sorry - awarded the points.
Still a newby here...
0
BrianMMCommented:
no probs.. any other help.. give me a shout!
0
Martin-SwannieAuthor Commented:
Hi Brain - I posted another question regarding this matter. Do you mind having a look?

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_24429484.html?cid=239#a24447244
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.