View Full Version : Check record in one database before inserting record into another

03-13-2009, 04:36 AM
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?

<?php require_once('Connections/connQuery.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";
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
return $theValue;

// *** Redirect if username exists
if (isset($_POST[$MM_flag])) {
$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
$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");

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

03-13-2009, 05:52 PM
Yes just run a select query on the first table and if you get a hit, bypass the insert into the second table.

09-23-2009, 05:15 PM
please, can anyone send me the code for the above question.


Old Pedant
09-23-2009, 09:07 PM
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.

Old Pedant
09-23-2009, 09:08 PM
Besides, that code was generated by DreamWeaer, and the last thing in the world you want to do is copy DumbWaster code.

Old Pedant
09-23-2009, 09:08 PM
Okay, that last thing in the world you want to do is die. Copying DW code only comes close.