Page 1 of 2

Record manipulation

Posted: Fri Aug 01, 2008 12:50 am
by pritam79
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>";

Re: Record manipulation

Posted: Sat Aug 02, 2008 6:45 pm
by califdon
WOOPS!!! I apologize most earnestly to the first responder, whose reply I accidentally deleted!! :oops: 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.

:oops:

Re: Record manipulation

Posted: Sun Aug 03, 2008 1:10 am
by pritam79
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

Posted: Sun Aug 03, 2008 7:12 am
by ody3307
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"

Re: Record manipulation

Posted: Sun Aug 03, 2008 9:19 am
by pritam79
ody3307 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]'
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_STRING

Re: Record manipulation

Posted: Sun Aug 03, 2008 9:22 am
by Stryks
Also, from a security standpoint, you should be *at least* using mysql_real_escape_string() on those $_POST variables.

Re: Record manipulation

Posted: Sun Aug 03, 2008 11:33 am
by califdon
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);
 

Re: Record manipulation

Posted: Sun Aug 03, 2008 12:00 pm
by EverLearning
1) You can't put php functions in a string and expect them to work. This definatelly won't work

Code: Select all

$sql .= " first_name = mysql_real_escape_string($_POST['first_name']), ";
2) When accessing array values (like $_POST['first_name']), you either use string concatenation or suround the array with curly braces {}

Code: Select all

$sql .= " first_name = {$_POST['first_name']}, ";
// or
$sql .= " first_name = " . $_POST['first_name'] . "', ";
 
 
So reworked code that should work

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

Posted: Sun Aug 03, 2008 12:33 pm
by califdon
Thanks, EverLearning! Of course you're 100% correct. I must have gotten up too early this morning!

Re: Record manipulation

Posted: Tue Aug 05, 2008 6:41 am
by pritam79
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-

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]'";
the updated records get displayed on the page but do not get updated in mysql.

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>";
 
?>  
 

Re: Record manipulation

Posted: Tue Aug 05, 2008 7:01 am
by EverLearning
I missed a couple of parenthesis and a double quote(it was untested after all). Now its fixed

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");
pritam79 wrote:the updated records get displayed on the page but do not get updated in mysql.
That's because your form is submitted and $_POST is populated, but your query isn't good so nothing is written to the database.
Try with the query I wrote above.

Re: Record manipulation

Posted: Tue Aug 05, 2008 8:20 am
by pritam79
Thanks for the help, it worked. Cheers :)

Re: Record manipulation

Posted: Fri Aug 08, 2008 8:52 am
by pritam79
Hi, EverLearning,
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");
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

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");

Re: Record manipulation

Posted: Fri Aug 08, 2008 9:33 am
by EverLearning
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

Posted: Fri Aug 08, 2008 9:46 am
by pritam79
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.
I really forgot to include the error message. This is it.

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'