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.
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'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.
I'll need to see if "submit" was hit to be able to run the update
PHP Code:
$sql="UPDATE $tbl_name SET tn='$tn', acct_number='$acct_number', port='$port', fqdn='$fqdn'";
Thank you for any help!
working w/ code that searches mySQL db but want to update as well
?foru,
just to verify, you want to have an update next to the displayed results?
ex. Results..
Results "update"
Results "update"
Results "update"
Results "update"
then when you click on "update" it takes you to a form where you can change the values of that result, then submit the form to update the values?
please let me know if this is correct
working w/ code that searches mySQL db but want to update as well
well lucky for you i use what you are looking for, except when they click on "update" i go to another page, but i will give you what i have.
this is all on one page, displayed in order. Please let me know if this doesnt work.
Information that is displayed in table with Update and delete.
PHP Code:
<?php
mysql_connect("host","user","pass");
mysql_select_db("db");
$color1 = ""; // color of row 1
$color2 = "#FFFFFF";// color of row 2
$row_count = 0;
$result = mysql_query("SELECT * FROM tblname ORDER BY whatever");
while($row = mysql_fetch_array($result)) {
$row_color = ($row_count % 2) ? $color1 : $color2; //alternate row color
$row_count++;
$id = $row['id']; // gets the id for the row that you want to edit when you click "update"
echo"
<table cellSpacing='0' cellPadding='0' width='100%' border='0'>
<tbody>
<tr bgcolor='$row_color'>
<td>Name:</td>
<td>Email:</td>
<td>Testimonial:</td>
<td>Status:</td>
<td><a href=\"page.php?id=$id\">Update</a></td>
<td><a onClick=\"return confirm("Are You Sure?")\" href=\"page.php?action=del&id=$id\">Delete</a></td>
</tr>
</tbody>
</table>";// closing of echo
}
if($_REQUEST['action']=="del") {
mysql_query("DELETE FROM $table WHERE id={$_REQUEST['id']};");
}
?>
The update form
PHP Code:
<?php
$con = mysql_connect("host","user","pass");
if(!$con) {
die('Could not connect: ' . myslq_error());
}
mysql_select_db("db", $con);
$sql = "SELECT * FROM table WHERE id=$_GET[id]"; // gets in id and table name from Update click
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
?>
<form action="page.php" method="post">
<table cellSpacing="0" cellPadding="3" width="100%" border="0">
<tbody>
<tr>
<td>ID:</td>
<td><input size="65" type="text" name="id" value"<?php echo $row['id'] ?>"></td>
</tr>
<tr>
<td>Name:</td>
<td><input size="65" type="text" name="tn" value"<?php echo $row['tn'] ?>"></td>
</tr>
<tr>
<td>Email:</td>
<td><input size="65" type="text" name="acct_number" value"<?php echo $row['acct_number'] ?>"></td>
</tr><tr>
<td>Testimonial:</td>
<td><input size="65" type="text" name="port" value"<?php echo $row['port'] ?>"></td>
</tr><tr>
<td>Status:</td>
<td><input size="65" type="text" name="fqdn" value"<?php echo $row['fqdn'] ?>"></td>
</tr>
<tr>
<td>Submit</td>
<td><input type="submit" name="submit" value="Submit"></td>
</tr>
</tbody>
</table>
The submit section
PHP Code:
<?php
if(isset($_POST['submit'])) {
$con = mysql_connect("host","user","pass");
if(!$con) {
die('Could not connect: ' . myslq_error());
}
mysql_select_db("db", $con);
mysql_query("UPDATE table SET tn = '$_POST[tn]', acct_number = '$_POST[acct_number]', port = '$_POST[port]', fqdn= '$_POST[fqdn]' WHERE id = '$_POST[id]'");
mysql_close($con);
}
?>
working w/ code that searches mySQL db but want to update as well
im sorry you did bring out a couple of mistakes that i had.
1) this is the correct way to display your table.
PHP Code:
<tr bgcolor='$row_color'>
<td>".$row['tn']."</td>
<td>".$row['acct_number']."</td>
<td>".$row['port']."</td>
<td>".$row['fqdn']."</td>
<td><a href=\"page.php?id=$id\">Update</a></td>
<td><a onClick=\"return confirm("Are You Sure?")\" href=\"page.php?action=del&id=$id\">Delete</a></td>
</tr>
2) In this one i had "$table", but just put your table name there
PHP Code:
if($_REQUEST['action']=="del") {
mysql_query("DELETE FROM tablename WHERE id={$_REQUEST['id']};");
}
And yes there shouldnt be (2) == signs because that would mean nothing
