check for duplicate records before DB entry

I am trying to check my DB before the data gets submitted for duplicates.  I copied a previous code i had written and tweaked with the new info, but i get the error result "Query is Empty"  

this should not show up as if the query is empty, it should proceed to insert into DB.

 
if (isset($_POST['submit'])) {
 
		// check for duplicate data in the database 
			
			$dupCheck_query = "SELECT qrtChurchID, qrtQrtID, qrtYrID FROM tblquartdata WHERE qrtChurchID = '".$row_rsUserChurch['churchID']."' , qrtQrtID = '".$_POST['qrtQrtID']."' , qrtYrID = '".$_POST['yearSelect']."'  ";
			mysql_select_db($database_con_db_local, $con_db_local);
			$dupCheck = mysql_query($dupCheck__query, $con_db_local) or die(mysql_error());
  			$dupFound = mysql_num_rows($dupCheck);
			
			if ($dupFound){
				$result = "<p style=\"color: #FF0000\">There is already information for that date, please try another date.</p>";
			}
			else { ?>
    
			<?php $result = "<h3 style=\"border: thin solid #ff9900; padding: 5px; \">Your Information has been submitted.  Thanks for taking the time to use District Connect.</h3>"; ?>

...Database input is here...

Open in new window

LVL 2
axessJoshAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
Try this.  I cannot test it, so please feel free to correct any parse errors, etc.  HTH, ~Ray
// DO THIS BEFORE ANY CONDITIONAL STATEMENTS
mysql_select_db($database_con_db_local, $con_db_local) or die( mysql_error() );

// IF THERE IS DATA POSTED
if (isset($_POST['submit'])) 
{
    // MAKE THE DATA SAFE TO USE IN A QUERY
    $cid = mysql_real_escape_string($row_rsUserChurch['churchID']);
    $qid = mysql_real_escape_string($_POST['qrtQrtID']);
    $yid = mysql_real_escape_string($_POST['yearSelect']);
    
    // CONSTRUCT THE QUERY USING SAFE VARS
    $dupCheck_query 
    = "SELECT  qrtChurchID, qrtQrtID, qrtYrID 
       FROM    tblquartdata 
       WHERE   qrtChurchID = '$cid'
       AND     qrtQrtID    = '$qid'
       AND     qrtYrID     = '$yid'
       LIMIT 1 "
       ;

    // RUN THE QUERY OR DIE ON ERROR
    $dupCheck = mysql_query($dupCheck_query) or die( mysql_error() );
    
    // IF ANY ROWS IT IS A DUPLICATE
    $dupFound = mysql_num_rows($dupCheck);
    if ($dupFound)
    {
        $result = "<p style=\"color: #FF0000\">There is already information for that date, please try another date.</p>";
    }
    else 
    { 
        $result = "<h3 style=\"border: thin solid #ff9900; padding: 5px; \">Your Information has been submitted.  Thanks for taking the time to use District Connect.</h3>";
    }
}

Open in new window

0
 
lisa_mcCommented:
Hi

on line 7 you have two __ instead of one try that

0
 
axessJoshAuthor Commented:
thanks,

now i get this error:

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 ' qrtQrtID = '1' , qrtYrID = '1'' at line 1
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Aaron TomoskySD-WAN SimplifiedCommented:
With mysql you can do insert ignore, Or insert on duplicate key update. As long as you have your unique columns specified it's super easy and can all be done in one query.
0
 
lisa_mcCommented:
hi again

replace your , with and/or as required in line 5

$dupCheck_query = "SELECT qrtChurchID, qrtQrtID, qrtYrID FROM tblquartdata WHERE qrtChurchID = '".$row_rsUserChurch['churchID']."' and qrtQrtID = '".$_POST['qrtQrtID']."' and qrtYrID = '".$_POST['yearSelect']."'  ";
0
 
Ray PaseurCommented:
The general design strategy I follow is not to check before -- I just mark the columns UNIQUE for those things that I only want to keep one of.  If you try to insert a duplicate value into a UNIQUE column, you will find musql_errno() = 1062.  You can trap that and handle the data accordingly.
0
 
Ray PaseurCommented:
But that said, please take a look at this teaching example and especially the man page references.  You need to learn about MySQL_REAL_Escape_String().

I'll try to straighten out the query syntax in a moment.
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

0
 
axessJoshAuthor Commented:
So if I understand correctly, functionally, my code is ok.   However, I need to use mysql_real_escape_string in my variables to sterilize them so they are ok to use in the query.  
0
 
Ray PaseurCommented:
Well, if the query fails, the code is not really OK.  MySQL_Real_Escape_String() will not correct the query syntax.  And if you are looking for a duplicate, using LIMIT 1 makes sense because you only need one duplicate data set.  Without the limit, the query will cause a table scan.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.