Here's some more MySQL/php help. For a script that inserts a record, click here. Below, we'll pull records from a database and display them on a table. There will be an open search. The first search pulls data from a table that matches two search fields to two columns using OR (one or the other matches). If you want the condition to be that they both must match, just replace the OR with AND in the query statement. The action of the form is the same page. The script will re-print out the form in a convenient place at the top of the results page, ready for another search. It will also show the number of records. Notice, after the print statements, the backslashes in front of all quotes, except those that surround what's printed. Items you need to change are in bold. Items in color are so for clarity, and probably don't need to be changed. These are open, loose searches. For a 'tight' search (exact, I mean EXACT match), replace the "LIKE" WITH "=" and remove the "%" from the search string. If I forgot anything, let me know ;
    One more thing. In the SELECT (query) statement, you can put (SELECT * FROM TableName AS "Friendly Table Name".....) if you want to print your table out on the page in a friendly fashion. These scripts and more are at work at my database. I'll put more here as I have time.

The script below will search multiple columns and return a table of the recordset.  It matches BOTH with an AND statement.  You can make it OR for a more open search.  Note this top search will return all of the fields by "SELECT * FROM...".  See bottom of page for a bit more info.
Click Here for a page where you can make a script like this on-the-fly.

<?php
if (isset($_POST['submit'])):
$db = mysql_connect("localhost", "user_name", "password");
mysql_select_db ("database_name") or die ("Sorry, database Problems. <a href=\"javascript:history.go(-1);\">Please try again</a>.");

$SearchBox=$_POST['SearchBox'];
$SearchBox2=$_POST['SearchBox2'];


// Above collecting search data from form fields
and putting it into regular named variables
//now they are plugged into the query below:

$result = mysql_query ("SELECT * FROM TableName WHERE Field_1 LIKE '%$SearchBox%' AND Field_2 LIKE '%$SearchBox2%'") or die ("Sorry no results. <a href=\"javascript:history.go(-1);\">Please try again</a>.");
$numrs = mysql_num_rows($result);
if ($numrs ==0){
print ("<font face=\"Arial\">Your search yielded 0 results. <a href=\"javascript:history.go(-1);\">Please try again</a>.</font>");}
else{
print ("<p><font face=\"Arial\"><b>Here are your results: $numrs records.</b><form method \"POST\" action=\"$PHP_SELF\">Search Again:");
print ("Search Parameter 1: <input name=\"SearchBox\" size=\"40\" maxlength=\"255\"><br>");
print ("Search Parameter 2: <input name=\"SearchBox2\" size=\"40\" maxlength=\"255\">");
print ("<input type=submit name=\"submit\" value=\"Go\"></form><hr width=\"400\" align=\"left\">");
print "<table></font>\n";
while ($line = mysql_fetch_assoc($result)) {
$TableLine = $line["Field_1"];
$TableLine1 = $line["Field_2"];
$TableLine2 = $line["Field_3"];
print "<tr><td><p>$TableLine</td><td><p>$TableLine1</td><td><p>$TableLine2</td></tr>\n";  <= Keep adding one for each field you need to show in the results.
}
print "<tr><td align=center><p><b><a href=\"#top\">Back To Top</a></td></tr>";
print "</table>\n";}
mysql_close($db);
?>
<?php else: ?>
<p><font face="Arial">Search our database. Submit a word or a phrase below and you'll get
back the records that match your search. Leave blank for all records.<br>
<form method "POST" action="<?php echo($PHP_SELF); ?>">
Search Parameter 1: <input name="SearchBox" size="40" maxlength="255">
Search Parameter 2: <input name="SearchBox2" size="40" maxlength="255">
<input type=submit name="submit" value="Go">
</form>
</font>
<?php endif; ?><p><--- Anything below this stays on the page no matter what.


The script below will search a single column and return a table of the recordset. It uses a somewhat different method of arranging and displaying returned data, so that you can see these different ways.  Also, this query only returns ONE FIELD (it doesn't have to be the one you're searching) by "SELECT Field_X FROM....".  To make it return ALL fields just make it "SELECT * FROM..." like the query above.

<?php
if ($submit):
$db = mysql_connect("localhost", "user_name", "password");
mysql_select_db ("database_name") or die ("Sorry, database Problems. <a href=\"javascript:history.go(-1);\">Please try again</a>.");
$result = mysql_query ("SELECT Field_X FROM TableName WHERE Field_1 LIKE '%$SearchBox%'") or die ("Sorry no results. Please try again.");
$numrs = mysql_num_rows($result);
print ("<p><b>Here are your results: $numrs records.</b><form method \"POST\" action=\"$PHP_SELF\">Search Again: <input name=\"SearchBox\" size=\"40\" maxlength=\"255\"><input type=SUBMIT name=\"submit\" value=\"Go\"></form><hr width=\"400\" align=\"left\">");
print "<table>\n";
while ($line = mysql_fetch_array($result)) {
print "\t<tr>\n";
while(list($col_name, $col_value) = each($line)) {
print "\t\t<td>$col_value</td>\n";
}
print "\t</tr>\n";
}
print "</table>\n";
}
print "<tr><td align=center><p><b><a href=\"#top\">Back To Top</a></td></tr>";
print "</table>\n";
mysql_close($db);
?>
<?php else: ?>
<p>Search our database. Submit a word or a phrase below and you'll get
back the records that match your search. Leave blank for all records.<br>
<form method "POST" action="<?php echo($PHP_SELF); ?>">
<input name="SearchBox" size="40" maxlength="255">
<input type=SUBMIT name="submit" value="Go">
</form>
<?php endif; ?> <p><--- Anything below this stays on the page no matter what.


Note:  Both of these queries use LIKE statements.  The parenthesis either side of the form field names in the Query Statement are saying that it's okay for that field to have data BEFORE and AFTER what matches the search.  So, a search for "car" will bring up "cardboard", "my car is the best", "pericardium", etc.  For an EXACT match query, replace the LIKE with an equals sign (=) and remove the parentheses.

Press H for TWebMan Home | J - JavaScript | P - Make A Perl Script |A - Autorepaircd.Com |T - Tsoft