Record manipulation
Moderator: General Moderators
Record manipulation
HI,
I have contacts for different users in mysql and have display them in a tabular format in php pages. I need to include the option for deleting and editing/updating the displayed records. I am not sure whether to use two links in each row of the records for deletion and updation or delete using checkboxes. If i use links for both the tasks, how can i make sure that on pressing the delete or update link, the record corresponding to row is deleted? Will this be the link reference to the scripts that deletes the record? echo "<a href='mydelete.php?rec=$N_ID'>DELETE</a>";
I have contacts for different users in mysql and have display them in a tabular format in php pages. I need to include the option for deleting and editing/updating the displayed records. I am not sure whether to use two links in each row of the records for deletion and updation or delete using checkboxes. If i use links for both the tasks, how can i make sure that on pressing the delete or update link, the record corresponding to row is deleted? Will this be the link reference to the scripts that deletes the record? echo "<a href='mydelete.php?rec=$N_ID'>DELETE</a>";
Re: Record manipulation
WOOPS!!! I apologize most earnestly to the first responder, whose reply I accidentally deleted!!
It was a dumb blunder on my part! I'll try to identify the responder and repeat what he said, as well as I can.
Edit: jaoudestudios responded that using checkboxes does allow the user to delete mutliple items at once, but that it complicates the script because you would have to loop through all of them to see which ones to delete.
I agree, and added that checkboxes also may be awkward because what do you do if the user checks more than one Edit, or checks some Edits and some Deletes?
I was trying to quote jaoudestudios in my response, but apparently clicked Edit instead of Quote. Really dumb! If I were not a moderator, I wouldn't have the ability to edit someone else's post, and as a moderator I should be more careful. Very sorry.

Edit: jaoudestudios responded that using checkboxes does allow the user to delete mutliple items at once, but that it complicates the script because you would have to loop through all of them to see which ones to delete.
I agree, and added that checkboxes also may be awkward because what do you do if the user checks more than one Edit, or checks some Edits and some Deletes?
I was trying to quote jaoudestudios in my response, but apparently clicked Edit instead of Quote. Really dumb! If I were not a moderator, I wouldn't have the ability to edit someone else's post, and as a moderator I should be more careful. Very sorry.
Re: Record manipulation
Hi there, i have a php script which issues this query to update records. But i am getting this error message- "Could not update record". This means my query is not able to update records. Is there anything wrong with the query?
Code: Select all
$result = mysql_query("UPDATE contacts SET
first_name = '$_POST[first_name]',
last_name = '$_POST[last_name]',
c_title = '$_POST[c_title]',
company = '$_POST[company]',
c_email = '$_POST[c_email]',
home_phone = '$_POST[home_phone]',
work_phone = '$_POST[work_phone]',
cell_phone = '$_POST[cell_phone]',
website = '$_POST[website],
street = '$_POST[street]',
city = '$_POST[city]',
state = '$_POST[state]',
country = '$_POST[country]',
zip = '$_POST[zip]'
where C_ID = '$_POST[C_ID]'");
if(!$result)
{
echo "Could not update record";
exit;
}
Re: Record manipulation
Your syntax is off a bit. The $_POST variables are all wrapped in single quotes.
It should be like below:
$_POST['variable_name']
NOT this:
'$_POST[variable_name]'
Also, change your "where" to "WHERE"
It should be like below:
$_POST['variable_name']
NOT this:
'$_POST[variable_name]'
Also, change your "where" to "WHERE"
Re: Record manipulation
I tried your idea but i got this error- Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRINGody3307 wrote:Your syntax is off a bit. The $_POST variables are all wrapped in single quotes.
It should be like below:
$_POST['variable_name']
NOT this:
'$_POST[variable_name]'
Re: Record manipulation
Also, from a security standpoint, you should be *at least* using mysql_real_escape_string() on those $_POST variables.
Re: Record manipulation
In addition to the things said by others, what you need to do is display the specific reason for the query failing, which you can do by using the die() function in connection with mysql_error(). It is also a good practice to form the SQL string separately from the actual command. I would suggest this:
Code: Select all
$sql = "UPDATE contacts SET ";
$sql .= " first_name = mysql_real_escape_string($_POST['first_name']), ";
$sql .= " last_name = mysql_real_escape_string($_POST['last_name']), ";
$sql .= " c_title = mysql_real_escape_string($_POST['c_title']), ";
$sql .= " company = mysql_real_escape_string($_POST['company']), ";
$sql .= " c_email = mysql_real_escape_string($_POST['c_email']), ";
$sql .= " home_phone = mysql_real_escape_string($_POST['home_phone']), ";
$sql .= " work_phone = mysql_real_escape_string($_POST['work_phone']), ";
$sql .= " cell_phone = mysql_real_escape_string($_POST['cell_phone']), ";
$sql .= " website = mysql_real_escape_string($_POST['website']), ";
$sql .= " street = mysql_real_escape_string($_POST['street']), ";
$sql .= " city = mysql_real_escape_string($_POST['city']), ";
$sql .= " state = mysql_real_escape_string($_POST['state']), ";
$sql .= " country = mysql_real_escape_string($_POST['country']), ";
$sql .= " zip = '$_POST[zip]' ";
$sql .= " WHERE C_ID = '$_POST[C_ID]";
$results = mysql_query($sql) or die("Update query failed: <br>" . mysql_error() . "<br>$sql);
- EverLearning
- Forum Contributor
- Posts: 282
- Joined: Sat Feb 23, 2008 3:49 am
- Location: Niš, Serbia
Re: Record manipulation
1) You can't put php functions in a string and expect them to work. This definatelly won't work
2) When accessing array values (like $_POST['first_name']), you either use string concatenation or suround the array with curly braces {}
So reworked code that should work
Code: Select all
$sql .= " first_name = mysql_real_escape_string($_POST['first_name']), ";Code: Select all
$sql .= " first_name = {$_POST['first_name']}, ";
// or
$sql .= " first_name = " . $_POST['first_name'] . "', ";
Code: Select all
$sql = "UPDATE contacts SET ";
$sql .= " first_name = '" . mysql_real_escape_string($_POST['first_name']). "', ";
$sql .= " last_name = '" . mysql_real_escape_string($_POST['last_name']). "', ";
$sql .= " c_title = '" . mysql_real_escape_string($_POST['c_title']). "', ";
$sql .= " company = '" . mysql_real_escape_string($_POST['company']). "', ";
$sql .= " c_email = '" . mysql_real_escape_string($_POST['c_email']). "', ";
$sql .= " home_phone = '" . mysql_real_escape_string($_POST['home_phone']). "', ";
$sql .= " work_phone = '" . mysql_real_escape_string($_POST['work_phone']). "', ";
$sql .= " cell_phone = '" . mysql_real_escape_string($_POST['cell_phone']). "', ";
$sql .= " website = '" . mysql_real_escape_string($_POST['website']). "', ";
$sql .= " street = '" . mysql_real_escape_string($_POST['street']). "', ";
$sql .= " city = '" . mysql_real_escape_string($_POST['city']). "', ";
$sql .= " state = '" . mysql_real_escape_string($_POST['state']). "', ";
$sql .= " country = '" . mysql_real_escape_string($_POST['country']) . "', ";
$sql .= " zip = '" . mysql_real_escape_string$_POST['zip'] . "'";
$sql .= " WHERE C_ID = '" . mysql_real_escape_string($_POST['C_ID']) . "'";
$results = mysql_query($sql) or die("Update query failed: <br>" . mysql_error() . "<br>$sql);
Re: Record manipulation
Thanks, EverLearning! Of course you're 100% correct. I must have gotten up too early this morning!
Re: Record manipulation
Hi EverLearning,
I tried the thing you suggested but i am getting this error-
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\wamp\www\PDMS\do_modcontact.php on line 22(second line of the update query).
But when i try this- the updated records get displayed on the page but do not get updated in mysql.
this is my current which does so-
I tried the thing you suggested but i am getting this error-
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\wamp\www\PDMS\do_modcontact.php on line 22(second line of the update query).
But when i try this-
Code: Select all
$sql = "UPDATE contacts SET
first_name = '$_POST[first_name]',
last_name = '$_POST[last_name]',
c_title = '$_POST[c_title]',
company = '$_POST[company]',
c_email = '$_POST[c_email]',
home_phone = '$_POST[home_phone]',
work_phone = '$_POST[work_phone]',
cell_phone = '$_POST[cell_phone]',
website = '$_POST[website]',
street = '$_POST[street]',
city = '$_POST[city]',
state = '$_POST[state]',
country = '$_POST[country]',
zip = '$_POST[zip]'
WHERE C_ID = '$_POST[C_ID]'";this is my current which does so-
Code: Select all
$sql = "UPDATE contacts SET
first_name = '$_POST[first_name]',
last_name = '$_POST[last_name]',
c_title = '$_POST[c_title]',
company = '$_POST[company]',
c_email = '$_POST[c_email]',
home_phone = '$_POST[home_phone]',
work_phone = '$_POST[work_phone]',
cell_phone = '$_POST[cell_phone]',
website = '$_POST[website]',
street = '$_POST[street]',
city = '$_POST[city]',
state = '$_POST[state]',
country = '$_POST[country]',
zip = '$_POST[zip]'
WHERE C_ID = '$_POST[C_ID]'";
// $results = mysql_query($sql) or die("Update query failed: <br>" . mysql_error() . "<br>",$sql);if(!$sql)
if(!$sql)
{
echo "Could not update records";
exit;
}
echo "<br><b><center>Contact modified sucessfully</center></b><br>";
echo "<table style='height: 304px'>";
echo "<tr><td style='width: 458px; height: 43px'><b>First Name</b> : $_POST[first_name]</td>";
echo "<td style='width: 537px; height: 43px'><b>Cell Phone</b> : $_POST[cell_phone]</td></tr>";
echo "<tr><td style='width: 458px; height: 44px'><b>Last Name</b> : $_POST[last_name]</td>";
echo "<td style='width: 537px; height: 44px'><b>Website</b> : $_POST[website]</td></tr>";
echo "<tr><td style='width: 458px; height: 44px'><b>Title</b> : $_POST[c_title]</td>";
echo "<td style='width: 537px; height: 44px'><b>Street</b> : $_POST[street]</td></tr>";
echo "<tr><td style='width: 458px; height: 44px'><b>Company</b> : $_POST[company]</td>";
echo "<td style='width: 537px; height: 44px'><b>City</b> : $_POST[city]</td></tr>";
echo "<tr><td style='width: 458px; height: 44px'><b>E-mail</b> : $_POST[c_email]</td>";
echo "<td style='width: 537px; height: 44px'><b>State</b> : $_POST[state]</td></tr>";
echo "<tr><td style='width: 458px; height: 44px'><b>Home Phone</b> : $_POST[home_phone]</td>";
echo "<td style='width: 537px; height: 44px'><b>Country</b> : $_POST[country]</td></tr>";
echo "<tr><td style='width: 458px; height: 44px'><b>Work Phone</b> : $_POST[work_phone]</td>";
echo "<td style='width: 537px; height: 44px'><b>Zip</b> : $_POST[zip]</td></tr>";
echo "</table>";
?>
- EverLearning
- Forum Contributor
- Posts: 282
- Joined: Sat Feb 23, 2008 3:49 am
- Location: Niš, Serbia
Re: Record manipulation
I missed a couple of parenthesis and a double quote(it was untested after all). Now its fixed
Try with the query I wrote above.
Code: Select all
$sql = "UPDATE contacts SET ";
$sql .= " first_name = '" . mysql_real_escape_string($_POST['first_name']). "', ";
$sql .= " last_name = '" . mysql_real_escape_string($_POST['last_name']). "', ";
$sql .= " c_title = '" . mysql_real_escape_string($_POST['c_title']). "', ";
$sql .= " company = '" . mysql_real_escape_string($_POST['company']). "', ";
$sql .= " c_email = '" . mysql_real_escape_string($_POST['c_email']). "', ";
$sql .= " home_phone = '" . mysql_real_escape_string($_POST['home_phone']). "', ";
$sql .= " work_phone = '" . mysql_real_escape_string($_POST['work_phone']). "', ";
$sql .= " cell_phone = '" . mysql_real_escape_string($_POST['cell_phone']). "', ";
$sql .= " website = '" . mysql_real_escape_string($_POST['website']). "', ";
$sql .= " street = '" . mysql_real_escape_string($_POST['street']). "', ";
$sql .= " city = '" . mysql_real_escape_string($_POST['city']). "', ";
$sql .= " state = '" . mysql_real_escape_string($_POST['state']). "', ";
$sql .= " country = '" . mysql_real_escape_string($_POST['country']) . "', ";
$sql .= " zip = '" . mysql_real_escape_string($_POST['zip']) . "' ";
$sql .= " WHERE C_ID = '" . mysql_real_escape_string($_POST['C_ID']) . "'";
$results = mysql_query($sql) or die("Update query failed: <br>" . mysql_error() . "<br>$sql");That's because your form is submitted and $_POST is populated, but your query isn't good so nothing is written to the database.pritam79 wrote:the updated records get displayed on the page but do not get updated in mysql.
Try with the query I wrote above.
Re: Record manipulation
Thanks for the help, it worked. Cheers 
Re: Record manipulation
Hi, EverLearning,
Your update statement
worked.
But when i tried to do the same with another page it didn't work. This is the update statement for a different page
Your update statement
Code: Select all
$sql = "UPDATE contacts SET ";
$sql .= " first_name = '" . mysql_real_escape_string($_POST['first_name']). "', ";
$sql .= " last_name = '" . mysql_real_escape_string($_POST['last_name']). "', ";
$sql .= " c_title = '" . mysql_real_escape_string($_POST['c_title']). "', ";
$sql .= " company = '" . mysql_real_escape_string($_POST['company']). "', ";
$sql .= " c_email = '" . mysql_real_escape_string($_POST['c_email']). "', ";
$sql .= " home_phone = '" . mysql_real_escape_string($_POST['home_phone']). "', ";
$sql .= " work_phone = '" . mysql_real_escape_string($_POST['work_phone']). "', ";
$sql .= " cell_phone = '" . mysql_real_escape_string($_POST['cell_phone']). "', ";
$sql .= " website = '" . mysql_real_escape_string($_POST['website']). "', ";
$sql .= " street = '" . mysql_real_escape_string($_POST['street']). "', ";
$sql .= " city = '" . mysql_real_escape_string($_POST['city']). "', ";
$sql .= " state = '" . mysql_real_escape_string($_POST['state']). "', ";
$sql .= " country = '" . mysql_real_escape_string($_POST['country']) . "', ";
$sql .= " zip = '" . mysql_real_escape_string($_POST['zip']) . "' ";
$sql .= " WHERE C_ID = '" . mysql_real_escape_string($_POST['C_ID']) . "'";
$results = mysql_query($sql) or die("Update query failed: <br>" . mysql_error() . "<br>$sql");But when i tried to do the same with another page it didn't work. This is the update statement for a different page
Code: Select all
$sql = "UPDATE notes SET ";
$sql .= " n_date = '" . mysql_real_escape_string($_POST['n_date']). "', ";
$sql .= " n_notes = '" . mysql_real_escape_string($_POST['n_notes']). "', ";
$sql .= " WHERE N_ID = '" . mysql_real_escape_string($_POST['N_ID']) . "'";
$results = mysql_query($sql) or die("Update query failed: <br>" . mysql_error() . "<br>$sql");- EverLearning
- Forum Contributor
- Posts: 282
- Joined: Sat Feb 23, 2008 3:49 am
- Location: Niš, Serbia
Re: Record manipulation
OK. I could just fix this, but instead, I'll give you pointers. With what error does your script die? It should be something along the lines: "You have incorrect syntax in your SQL statement near ...". Also, echo the $sql varible. You should see where the error is.
Re: Record manipulation
I really forgot to include the error message. This is it.EverLearning wrote:OK. I could just fix this, but instead, I'll give you pointers. With what error does your script die? It should be something along the lines: "You have incorrect syntax in your SQL statement near ...". Also, echo the $sql varible. You should see where the error is.
Code: Select all
Update query failed:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE N_ID = '3'' at line 1
UPDATE notes SET n_date = '', n_notes = 'q', WHERE N_ID = '3'