 |
|
|
02-02-2006, 05:31 PM
|
Edit DB records with PHP
|
Posts: 87
Name: Colin Roy
Location: Dallas, TX
|
Hello,
I am trying to write a page that will allow me to edit records in my database.
First, let me post the code for teh page that is passing along the ID variable.
PHP Code:
<?php
include("dbconnection.php");
$search_results = FALSE;
if (isset($_POST['Submit'])) {
$search = $_POST['search'];
$date = $_POST['date'];
$query = "SELECT customerID,firstname,lastname,city,state,email FROM customers WHERE firstname = '$search' OR lastname = '$search' OR city = '$search' OR state = '$search' OR email = '$search' AND date >= '$date' ORDER BY date asc";
$result = odbc_exec($cnx, $query);
$i = 0;
$color1 = "#CADEFA"; // light slate gray
$color2 = "#8298AE"; // gainsboro
$search_results = '<div id=\"table1\">
<center>
<TABLE CELLPADDING=0 CELLSPACING=1 width=\"100%\"><tr align=center bgcolor=lightsteelblue>
<th><font face=\"Tahoma\" size=\"1\"><b>First Name</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Last Name</font></th>
<th><font face=\"Tahoma\" size=\"1\">Customer City</font></th>
<th><font face=\"Tahoma\" size=\"1\">Customer State</font></th>
<th><font face=\"Tahoma\" size=\"1\">Email</font></th>
<th><font face=\"Tahoma\" size=\"1\">Update</font></th>
</tr>';
while ($row = odbc_fetch_array($result)) {
$color = ($i%2==0 || $i==0)?$color1:$color2;
/*
echo "<p>SQL: $query</p>";
if ($result === false)
echo "<p>odbc_exec returned false, msg: " . odbc_errormsg() . "</p>";
else echo "<p>odbc_exec returned " . odbc_num_rows($result) . " rows.</p>";
*/
$search_results .= "
<tr align=center bgcolor=".$color.">
<td><font face=\"Tahoma\" size=\"1\">$row[firstname]</font></td>
<td><font face=\"Tahoma\" size=\"1\">$row[lastname]</font></td>
<td><font face=\"Tahoma\" size=\"1\">$row[city]</font></td>
<td><font face=\"Tahoma\" size=\"1\">$row[state]</font></td>
<td><font face=\"Tahoma\" size=\"1\"><a href=\"mailto:$row[email]\">$row[email]</a></font></td>
<td><font face=tahoma size=1><form action='cust_edit.php'><input type='hidden' name='customerID' value='$row[customerID]'><input type='submit' value='Edit'></form></font></td>
</tr>";
$i++; // for alternating colors per row...
} // End of WHILE loop
$search_results .= "
</table>
</center>
</div>";
}
?>
ok, now hitting teh edit button should pass on the customerID variable to cust_edit.php
This it does, because the url that comes up when i hit edit is http://sliquid.com/protected/cust_ed...customerID=xxx xxx being the ID of that record. Only thing, the page remains blank
ok, so now for my edit page. This is where i have trouble.
PHP Code:
<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 'On');
include("dbconnection.php");
$id=$_GET['customerID'];
echo "$id";
$result = odbc_query("SELECT * FROM customers WHERE customerID='$id' ",$cnx);
while($row = odbc_fetch_assoc($result))
{
echo"
$firstname= $row[firstname];
$lastname= $row[lastname];
$address1= $row[address1];
$address2= $row[address2];
$city= $row[city];
$state= $row[state];
$zip= $row[zip];
$email= $row[email];
$referral= $row[referral];
$lubricant= $row[lubricant];
<!-- Your Form Stuff here -->
<input type=\"hidden\" name=\"customerID\" value=\"<? echo $id; ?>\">
First Name:<br>
<input type=\"text\" name=\"firstname\" value=\"<? echo $firstname; ?>\">
<br>
Last Name:<br>
<input type=\"text\" name=\"lastname\" value=\"<? echo $lastname; ?>\">
"
}
?>
all i get is a blank page
maybe i shouldnt be echoing the html, but im not sure. any ideas?
Thanks,
Colin
Last edited by croix; 02-10-2006 at 12:58 PM..
|
|
|
|
|
02-03-2006, 02:40 PM
|
|
Posts: 87
Name: Colin Roy
Location: Dallas, TX
|
OK, i took out all the html and I am trying to just get the PHP to echo one result first.
PHP Code:
<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 'On');
include("dbconnection.php");
$id=$_GET['customerID'];
echo "$id";
$result = odbc_query("SELECT * FROM customers WHERE customerID='$id' ",$cnx);
while($row = odbc_fetch_assoc($result))
{
echo"
$firstname= $row[firstname];
$lastname= $row[lastname];
$address1= $row[address1];
$address2= $row[address2];
$city= $row[city];
$state= $row[state];
$zip= $row[zip];
$email= $row[email];
$referral= $row[referral];
$lubricant= $row[lubricant];
<!-- Your Form Stuff here -->
<input type=\"hidden\" name=\"customerID\" value=\"<? echo $id; ?>\">
First Name:<br>
<input type=\"text\" name=\"firstname\" value=\"<? echo $firstname; ?>\">
<br>
Last Name:<br>
<input type=\"text\" name=\"lastname\" value=\"<? echo $lastname; ?>\">
"
}
?>
this still leaves me with a blank page and no errors reported.
Last edited by croix; 02-10-2006 at 12:58 PM..
|
|
|
|
|
02-04-2006, 09:05 PM
|
|
Posts: 46
|
Well this here should work - I scaled it to showing you example of the one textfield - also I always do my database stuff a tad bit different with $result doing the query and connecting so that part is a bit different then what you had
PHP Code:
<?
$result = mysql_query("SELECT * FROM customers WHERE customerID='$id' ",$connect);
while($myrow = mysql_fetch_assoc($result))
{
echo"
$firstname= $row[firstname];
$lastname= $row[lastname];
$address1= $row[address1];
$address2= $row[address2];
$city= $row[city];
$state= $row[state];
$zip= $row[zip];
$email= $row[email];
$referral= $row[referral];
$lubricant= $row[lubricant]];
?>
<!-- Your Form Stuff here -->
<input type=\"hidden\" name=\"customerID\" value=\"<? echo $id; ?>\">
First Name:<br>
<input type="text" name=\"firstname\" value=\"<? echo $firstname; ?>\">
<br>
Last Name:<br>
"
}
?>
That there SHOULD work, its the same wasy I have my backend system done and it worked for that... |
|
|
|
|
02-09-2006, 03:31 PM
|
Re: Edit DB records with PHP
|
Posts: 87
Name: Colin Roy
Location: Dallas, TX
|
this does not seem to work for me either:
again, blank page, no error reported. viewing source gives me
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=windows-1252"></HEAD>
<BODY></BODY></HTML>
PHP Code:
<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 'On');
include("dbconnection.php");
$id=$_GET['customerID'];
echo "$id";
$result = odbc_query("SELECT * FROM customers WHERE customerID='$id' ",$cnx);
while($row = odbc_fetch_assoc($result))
{
echo"
$firstname= $row[firstname];
$lastname= $row[lastname];
$address1= $row[address1];
$address2= $row[address2];
$city= $row[city];
$state= $row[state];
$zip= $row[zip];
$email= $row[email];
$referral= $row[referral];
$lubricant= $row[lubricant];
<!-- Your Form Stuff here -->
<input type=\"hidden\" name=\"customerID\" value=\"<? echo $id; ?>\">
First Name:<br>
<input type=\"text\" name=\"firstname\" value=\"<? echo $firstname; ?>\">
<br>
Last Name:<br>
<input type=\"text\" name=\"lastname\" value=\"<? echo $lastname; ?>\">
"
}
?>
Last edited by croix; 02-10-2006 at 12:58 PM..
|
|
|
|
|
02-10-2006, 01:10 PM
|
Re: Edit DB records with PHP
|
Posts: 87
Name: Colin Roy
Location: Dallas, TX
|
OK, heres the update.
727
Fatal error: Call to undefined function: odbc_query() in d:\inetpub\sliquid\wwwRoot\protected\cust_edit.php on line 8
727 is the correct customerID
the error line (8) is:
$result = odbc_query("SELECT * FROM customers WHERE customerID='$id' ",$cnx);
$cnx is defined in the include, and it works for several other pages, so Im assuming that my problem is the odbc_query, that it is not a good translation from mysql_query
any ideas? also, if thats the problem, then I would bet that odbc_fetch_assoc is going to be wrong too. anyone know where I can find correct translations for these functions?
oh, and heres the latest on the code:
PHP Code:
<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 'On');
include("dbconnection.php");
$id=$_GET['customerID'];
echo "$id";
$result = odbc_query("SELECT * FROM customers WHERE customerID='$id' ",$cnx);
while($row = odbc_fetch_assoc($result))
{
echo"
$firstname= $row[firstname];
$lastname= $row[lastname];
$address1= $row[address1];
$address2= $row[address2];
$city= $row[city];
$state= $row[state];
$zip= $row[zip];
$email= $row[email];
$referral= $row[referral];
$lubricant= $row[lubricant];
<!-- Your Form Stuff here -->
<input type=\"hidden\" name=\"customerID\" value=\"<? echo $id; ?>\">
First Name:<br>
<input type=\"text\" name=\"firstname\" value=\"<? echo $firstname; ?>\">
<br>
Last Name:<br>
<input type=\"text\" name=\"lastname\" value=\"<? echo $lastname; ?>\">
";
}
?>
also, Ive been clued in that I do not need a loop since there will be only one result, but my attempts to remove the loop only return my favorite - the blank page.
Last edited by croix; 02-10-2006 at 01:32 PM..
|
|
|
|
|
02-10-2006, 03:49 PM
|
Re: Edit DB records with PHP
|
Posts: 60
Location: Romania
|
PHP Code:
<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 'On');
include("dbconnection.php");
$id=$_GET['customerID'];
echo "$id";
$result = odbc_query("SELECT * FROM customers WHERE customerID='$id' ",$cnx);
while($row = odbc_fetch_assoc($result))
{
$row['firstname'] = $firstname;
$row['lastname'] = $lastname;
$row['address1'] = $address1;
$row['address2'] = $address2;
$row['city'] = $city;
$row['state'] = $state;
$row['zip'] = $zip;
$row['email'] = $email;
$row['referral'] = $referral;
$row['lubricant'] = $lubricant;
echo"
".$firstname."
".$lastname."
".$address1."
".$address2."
".$city."
".$state."
".$zip."
".$email."
".$referral."
".$lubricant."";
echo "
<!-- Your Form Stuff here -->
<input type=\"hidden\" name=\"customerID\" value=\"".$id."\">
First Name:<br>
<input type=\"text\" name=\"firstname\" value=\"".$firstname."\">
<br>
Last Name:<br>
<input type=\"text\" name=\"lastname\" value=\"".$lastname."\">
" ;
}
?>
try this,i think it should work this way... |
|
|
|
|
02-10-2006, 03:51 PM
|
Re: Edit DB records with PHP
|
Posts: 60
Location: Romania
|
and also make sure you have the corect extensions installed for using the ODBC functions!!!
|
|
|
|
|
02-10-2006, 04:32 PM
|
Re: Edit DB records with PHP
|
Posts: 87
Name: Colin Roy
Location: Dallas, TX
|
727
Fatal error: Call to undefined function: odbc_query() in d:\inetpub\sliquid\wwwRoot\protected\cust_edit.php on line 9
and yes, i must have the correct extensions installed, because I have 10 or 15 other pages that are working, all with odbc functions
also, i believe odbc_query is wrong, should be odbc_do
and odbc_fetch_assoc should be odbc_fetch_array
but when I switch to these, I get
727
Warning: odbc_do(): supplied argument is not a valid ODBC-Link resource in d:\inetpub\sliquid\wwwRoot\protected\cust_edit.php on line 9
Warning: odbc_fetch_array(): supplied argument is not a valid ODBC result resource in d:\inetpub\sliquid\wwwRoot\protected\cust_edit.php on line 10
|
|
|
|
|
02-10-2006, 04:43 PM
|
Re: Edit DB records with PHP
|
Posts: 87
Name: Colin Roy
Location: Dallas, TX
|
OK, i switched some stuff around, and heres the error im getting now.
727
Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in d:\inetpub\sliquid\wwwRoot\protected\cust_edit.php on line 10
Warning: odbc_fetch_array(): supplied argument is not a valid ODBC result resource in d:\inetpub\sliquid\wwwRoot\protected\cust_edit.php on line 11
heres the code:
PHP Code:
<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 'On');
include("dbconnection.php");
$id=$_GET['customerID'];
echo "$id";
$query = "SELECT * FROM customers WHERE customerID= '$id' ";
$result = odbc_exec($cnx, $query);
while($row = odbc_fetch_array($result))
{
$row['firstname'] = $firstname;
$row['lastname'] = $lastname;
$row['address1'] = $address1;
$row['address2'] = $address2;
$row['city'] = $city;
$row['state'] = $state;
$row['zip'] = $zip;
$row['email'] = $email;
$row['referral'] = $referral;
$row['lubricant'] = $lubricant;
echo"
$firstname= $row[firstname];
$lastname= $row[lastname];
$address1= $row[address1];
$address2= $row[address2];
$city= $row[city];
$state= $row[state];
$zip= $row[zip];
$email= $row[email];
$referral= $row[referral];
$lubricant= $row[lubricant];
<!-- Your Form Stuff here -->
<input type=\"hidden\" name=\"customerID\" value=\"<? echo $id; ?>\">
First Name:<br>
<input type=\"text\" name=\"firstname\" value=\"<? echo $firstname; ?>\">
<br>
Last Name:<br>
<input type=\"text\" name=\"lastname\" value=\"<? echo $lastname; ?>\">
";
}
?>
|
|
|
|
|
02-10-2006, 06:09 PM
|
Re: Edit DB records with PHP
|
Posts: 47
Name: Eric
Location: Florida
|
This probably won't fix your problem but will give you (and some others, apparently) a better idea on how to code PHP.
PHP Code:
<?php
error_reporting(E_ALL);
ini_set('display_errors', 'On');
include 'dbconnection.php';
$request = odbc_exec($cnx, 'SELECT * FROM customers WHERE (customerID=\''.clean($_GET['customerID']).'\') LIMIT 1');
$result = odbc_fetch_array($result);
echo '
<input type="hidden" name="customerID" value="'.$result['customerID'].'">
First name:<br>
<input type="text" name="firstname" value="'.$result['firstname'].'">
<br>
Last name:<br>
<input type="text" name="lastname" value="'.$result['lastname'].'">';
/**
* @return string
* @param string $toClean
* @desc Cleans the supplied string for sql use. $commas is for comma seperated fields
*/
function clean($toClean)
{
if (!is_array($toClean))
{
if (!get_magic_quotes_gpc() && !get_magic_quotes_runtime())
{
$toClean = mysql_real_escape_string($toClean);
}
}
else
{
foreach ($toClean AS $var => $val)
{
$toClean[$var] = clean($val);
}
}
return $toClean;
}
This would be a good read: http://www.securiteam.com/securityre...DP0N1P76E.html
__________________
PHP / mySQL Developer
Last edited by ermau; 02-10-2006 at 06:12 PM..
|
|
|
|
|
02-13-2006, 12:59 PM
|
Re: Edit DB records with PHP
|
Posts: 87
Name: Colin Roy
Location: Dallas, TX
|
thanks, i'll give it a read
|
|
|
|
|
08-20-2009, 10:29 AM
|
Re: Edit DB records with PHP
|
Posts: 1
Name: VAMP Research
|
Quote:
Hello Everyone,
I want to populate a form so a user can edit a record.
Currently my PHP looks like so
PHP Code:
<?php
// Connecting, selecting database
$link = mysql_connect('HOST', 'USER', 'PASSWORD')
or die('Could not connect: ' . mysql_error());
//echo 'Connected successfully';
mysql_select_db('DATABASE') or die('Could not select database');
$user = $_GET['caseid'];
$search_results = FALSE;
$query = "SELECT * FROM Investigations WHERE CaseID = '$user'";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$i = 0;
$color1 = "#CADEFA"; // light slate gray
$color2 = "#8298AE"; // gainsboro
$search_results = '<div id=\"table1\">
<center>
<TABLE CELLPADDING=0 CELLSPACING=1 width=\"100%\"><tr align=left bgcolor=Gray>
<th><font face=\"Tahoma\" size=\"1\">CaseID</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Investigation Type</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Location</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Date</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Time</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Weather</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Investigators</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Equipment</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">History</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Activity</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Investigation Notes</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Findings</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Status</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Images Folder</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Audio Folder</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Video Folder</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Case Password</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">Confidentiality Level</b></font></th>
</tr>';
while ($row = mysql_fetch_array($result)) {
$color = ($i%2==0 || $i==0)?$color1:$color2;
echo $search_results .= "
<tr align=center bgcolor=Silver>
<th><font face=\"Tahoma\" size=\"1\">$row[CaseID]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[InvestigationType]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[Location]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[Date]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[Time]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[Weather]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[Investigators]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[Equipment]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[History]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[Activity]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[InvestigationNotes]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[Findings]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[Status]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[ImagesFolder]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[AudioFolder]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[VideoFolder]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[CasePassword]</b></font></th>
<th><font face=\"Tahoma\" size=\"1\">$row[ConfidentialityLevel]</b></font></th>
</font></td>
</tr>";
echo $search_results .= "<form action='insert.php' method='post'>
Case ID: <input type='text' name='CaseID' size='50' value='$row[CaseID]'><br>
Investigation Type: <input type='text' name='InvestigationType' size='50' value='$row[InvestigationType]'><br>
Location: <input type='text' name='Location' size='50' value='$row[Location]'><br>
Date: <input type='text' name='Date' size='50' value='$row[Date]'><br>
Time: <input type='text' name='Time' size='50' value='$row[Time]'><br>
Weather: <input type='text' name='Weather' size='50' value='$row[Weather]'><br>
Investigators: <input type='text' name='Investigators' size='50' value='$row[Investigators]'><br>
Equipment: <input type='text' name='Equipment' size='50' value='$row[Equipment]'><br>
History: <input type='text' name='History' size='50' value='$row[History]'><br>
Activity: <input type='text' name='Activity' size='50' value='$row[Activity]'><br>
Investigation Notes: <input type='text' name='InvestigationNotes' size='50' value='$row[InvestigationNotes]'><br>
Findings: <input type='text' name='Findings' size='50' value='$row[Findings]'><br>
Status: <input type='text' name='Status' size='50' value='$row[Status]'><br>
Images Folder: <input type='text' name='ImagesFolder' size='50' value='$row[ImagesFolder]'><br>
Audio Folder: <input type='text' name='AudioFolder' size='50' value='$row[AudioFolder]'><br>
Video Folder: <input type='text' name='VideoFolder' size='50' value='$row[VideoFolder]'><br>
Case Password: <input type='text' name='CasePassword' size='50' value='$row[CasePassword]'><br>
Confidentiality Level: <input type='text' name='ConfidentialityLevel' size='50' value='$row[ConfidentialityLevel]'><br>
<input type='Submit'>
</form>";
$i++; // for alternating colors per row...
} // End of WHILE loop
$search_results .= "
</table>
</center>
</div>";
?>
I am getting the above to display into editable fields flanked by the original data in tables. My next issue would be how do i update this data in my table when the user is complete?
Thanks
|
I did it! If anyone needs any help i can show what i did!
Last edited by VAMPResearch; 08-20-2009 at 02:38 PM..
Reason: Add PHP Code Wrapping
|
|
|
|
|
|
« Reply to Edit DB records with PHP
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
