working w/ code that searches mySQL db but want to update as well
I am working with a mySQL database and there are 4 fields at the moment. The script works to search the database, and I adapted it to display the results in form fields so the values can be changed to update the records. My issue is where to incorporate the UPDATE statement in the following code.
I've worked with UPDATE on something else but it was where ALL the records were pulled with a "while" loop then you had to choose which one to update from a long list.PHP Code:<html>
<head>
<title>database search script</title>
</head>
<body>
<form name="form" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />
</form>
<?php
//set your table name here
$table_name="your_data";
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
$limit=10;
// check for an empty string and display a message.
if ($trimmed == "")
{
echo "<p>Please enter a search...</p>";
exit;
}
// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("your_host", "your_username","your_password"); //(host, username, password)
//specify database ** EDIT REQUIRED HERE **
mysql_select_db("your_databasename") or die("Unable to select database"); //select which database we're using
// Build SQL Query
$query = "select * from $table_name where tn like \"%$trimmed%\"
order by tn"; // EDIT HERE and specify your table and field names for the SQL query
$query2 = "select * from $table_name where fqdn like \"%$trimmed%\"
order by fqdn"; // EDIT HERE and specify your table and field names for the SQL query
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
$numresults2=mysql_query($query2);
$numrows=mysql_num_rows($numresults2);
// no results
if ($numrows == 0)
{
echo "Results<br>";
echo "<p>Your search for: "" . $trimmed . "" returned zero results</p>";
}
// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}
// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");
$query2 .= " limit $s,$limit";
$result2 = mysql_query($query2) or die("Couldn't execute query");
// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";
// begin to show results set
echo "Results<br>";
$count = 1 + $s ;
// now you can display the results returned
while ($row= mysql_fetch_array($result) OR ($row= mysql_fetch_array($result2))) {
$tn = $row["tn"];
$acct_number = $row["acct_number"];
$port = $row["port"];
$fqdn = $row["fqdn"];
echo "<form><table cellspacing=\"0\" cellpadding=\"3\" rules=\"all\" bordercolor=\"Black\" border=\"1\" style=\"border-color:Black;border-width:1px;border-style:solid;font-family:Verdana;font-size:8pt;border-collapse:collapse;\">
<tr>
<td>TN</td>
<td>AccountNumber</td>
<td>Port</td>
<td>FQDN Domain</td>
</tr>
<tr>
<td><input name=\"tn\" type=\"text\" id=\"tn\" value=\"$tn\" size=\"15\"></td>
<td><input name=\"acct_number\" type=\"text\" id=\"acct_number\" value=\"$acct_number\" size=\"17\"></td>
<td><input name=\"port\" type=\"text\" id=\"port\" value=\"$port\" size=\"2\"></td>
<td><input name=\"fqdn\" type=\"text\" id=\"fqdn\" value=\"$fqdn\" size=\"30\"></td>
</tr><input type=\"submit\" name=\"Update\" value=\"Update Database\" />
</table></form>" ;
$count++ ;
}
$currPage = (($s/$limit) + 1);
//break before paging
echo "<br />";
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
Prev 10</a>  ";
}
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
// not last page so give NEXT link
$news=$s+$limit;
echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
?>
</body>
</html>
I'll need to see if "submit" was hit to be able to run the update
Thank you for any help!PHP Code:$sql="UPDATE $tbl_name SET tn='$tn', acct_number='$acct_number', port='$port', fqdn='$fqdn'";
CSS Codes
Reply With Quote
Bookmarks