Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check record in one database before inserting record into another

    I have a database with two tables: 'members' and 'comp_entrants'. I need to run a query to check whether someone is in the 'members' table, and if they are, they can insert a new entry in to the 'comp_entrants' table. If not, I want to display an error. I have all the code (below) for inserting the record (and checking it is not a duplicate entry) into the 'comp_entrants' table, but cannot figure out how to insert the first validation point.
    Should the queries be run as two separate queries (select and then insert) or do they have to be wrapped in an 'if/else' statement?

    Code:
    <?php require_once('Connections/connQuery.php'); ?>
    <?php
    if (!function_exists("GetSQLValueString")) {
    function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
    {
      $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
    
      $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
    
      switch ($theType) {
        case "text":
          $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
          break;    
        case "long":
        case "int":
          $theValue = ($theValue != "") ? intval($theValue) : "NULL";
          break;
        case "double":
          $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
          break;
        case "date":
          $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
          break;
        case "defined":
          $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
          break;
      }
      return $theValue;
    }
    }
    
    // *** Redirect if username exists
    $MM_flag="MM_insert";
    if (isset($_POST[$MM_flag])) {
      $MM_dupKeyRedirect="comp_exist.php";
      $loginUsername = $_POST['entry_id'];
      $LoginRS__query = sprintf("SELECT entry_id FROM comp_entries WHERE entry_id=%s", GetSQLValueString($loginUsername, "int"));
      mysql_select_db($database_connQuery, $connQuery);
      $LoginRS=mysql_query($LoginRS__query, $connQuery) or die(mysql_error());
      $loginFoundUser = mysql_num_rows($LoginRS);
    
      //if there is a row in the database, the username was found - can not add the requested username
      if($loginFoundUser){
        $MM_qsChar = "?";
        //append the username to the redirect page
        if (substr_count($MM_dupKeyRedirect,"?") >=1) $MM_qsChar = "&";
        $MM_dupKeyRedirect = $MM_dupKeyRedirect . $MM_qsChar ."requsername=".$loginUsername;
        header ("Location: $MM_dupKeyRedirect");
        exit;
      }
    }
    
    $editFormAction = $_SERVER['PHP_SELF'];
    if (isset($_SERVER['QUERY_STRING'])) {
      $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
    }
    
    if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
      $insertSQL = sprintf("INSERT INTO comp_entries (entry_id, entrant_name, entrant_phone, entrant_email) VALUES (%s, %s, %s, %s)",
                           GetSQLValueString($_POST['entry_id'], "int"),
                           GetSQLValueString($_POST['entrant_name'], "text"),
                           GetSQLValueString($_POST['entrant_phone'], "text"),
                           GetSQLValueString($_POST['entrant_email'], "text"));
    
      mysql_select_db($database_connQuery, $connQuery);
      $Result1 = mysql_query($insertSQL, $connQuery) or die(mysql_error());
    
      $insertGoTo = "compconfirm.php";
      if (isset($_SERVER['QUERY_STRING'])) {
        $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
        $insertGoTo .= $_SERVER['QUERY_STRING'];
      }
      header(sprintf("Location: %s", $insertGoTo));
    }
    ?>

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Yes just run a select query on the first table and if you get a hit, bypass the insert into the second table.

  • #3
    New to the CF scene
    Join Date
    Sep 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    please, can anyone send me the code for the above question.

    Thanks,
    Grace

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    Well, the database was poorly designed, in the first place.

    The original poster was DUPLICATING information--entrant_name, entrant_phone, entrant_email--in the second table.

    Instead, he should have just used a single "memberid" field as a reference to the members table. And kept all that kind of info in the single members table, not scattered in various tables.

    So I wouldn't pay any attention to this thread.

    Design a *good* database and you won't even have this problem.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    Besides, that code was generated by DreamWeaer, and the last thing in the world you want to do is copy DumbWaster code.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    Okay, that last thing in the world you want to do is die. Copying DW code only comes close.


  •  

    Posting Permissions

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