SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot TCJeter's Avatar
    Join Date
    Jun 2002
    Location
    Anchorage, AK
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with insert/update syntax

    Hello all.

    I'm having a little trouble getting my SQL functions to operate correctly - any help would be appreciated.

    Currently, I have a form for a user 'profile'. I want the profile info to be inserted if it's a new record, or to be updated if it exists - based on the user e-mail address.

    Here's what I have so far....
    PHP Code:
    $result = @mysql_query("SELECT * FROM $division WHERE email_id = '$email_id'");
    if(!
    $result) { 
    $sql "INSERT INTO $division 
        SET time_id = NOW(),
         email_id = '"
    .$email_id."',
         first_name = '
    $first_name',
         last_name = '
    $last_name',
         phone_id = '
    $phone_id',
         cost_center = '
    $cost_center',
         employee_id = '
    $employee_id',
         title_id = '
    $title_id',
         department_id = '
    $department_id'";
    if (!
    mysql_query($sql)) {
    echo (
    "Insert failed:".mysql_error());
    } else {
    echo (
    "Insert successful");
    }
    } else {
    $sql "UPDATE $division 
        SET time_id = NOW(),
         email_id = '"
    .$email_id."',
         first_name = '
    $first_name',
         last_name = '
    $last_name',
         phone_id = '
    $phone_id',
         cost_center = '
    $cost_center',
         employee_id = '
    $employee_id',
         title_id = '
    $title_id',
         department_id = '
    $department_id
        WHERE email_id = '"
    .$email_id."'";
    if (!
    mysql_query($sql)) {
    echo (
    "Insert failed:".mysql_error());
    } else {
    echo (
    "Insert successful");
    }

    It will update records properly and display 'Update Successful', but when I enter a new record it says 'Insert Successful', but the record does not show up....any suggestions on the next step in trouble-shooting?

    Thanks,
    TC

  2. #2
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The first thing you have to fix is this
    PHP Code:
    $result = @mysql_query("SELECT * FROM $division WHERE email_id = '$email_id'");
    if(!
    $result
     
    // insert
    else
     
    // update 
    The fact that mysql_query didn't return FALSE doesn't mean there are any matching rows. This just means your query was correct. You should use mysql_num_rows to test if the record exists:
    PHP Code:
    $result mysql_query("SELECT * FROM $division WHERE email_id = '$email_id'") or die(...);
    if(
    mysql_num_rows($result))
      
    // update
    else
      
    // insert 
    Another (and simpler) solution would be to use REPLACE.

  3. #3
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try mysql_affected_rows();
    becuase query can execute successfully, even though it might not insert data.

    and just to be more clear i would do
    PHP Code:
    $result mysql_query($sql);
    if(!
    $result)
    {
     echo 
    mysql_error());
    }
    elseif(
    mysql_affected_rows()>0)
    {
      echo 
    $result;//JUST FOR DEBUGGING
      
    echo 'INSERT/UPDATE sucessful.';
    }
    else
    {
      echo 
    $result;//JUST FOR DEBUGGING
      
    echo 'Cannot isnert data.';

    hope this helps

    EDIT: change in code
    ---------------------------
    Errors = Improved Programming.
    My Site

  4. #4
    SitePoint Zealot TCJeter's Avatar
    Join Date
    Jun 2002
    Location
    Anchorage, AK
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WOW.
    Thanks for the prompt responses.
    stereofrog - thanks. That fix is working for me. Much appreciated.

    jaswinder_rana - thank you also. I will incorporate the affected row query to be more precise.

    -TC


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •