?
Solved

check for duplicate records before DB entry

Posted on 2011-10-13
9
Medium Priority
?
210 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:axessJosh
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 3

Expert Comment

by:lisa_mc
ID: 36962984
Hi

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

0
 
LVL 2

Author Comment

by:axessJosh
ID: 36963045
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
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36963054
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:lisa_mc
ID: 36963207
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36964124
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36964164
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
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1000 total points
ID: 36964251
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
 
LVL 2

Author Comment

by:axessJosh
ID: 36964821
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36965743
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month17 days, 11 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question