PHP - Check For Duplicate Entries - Mysql Insert

I dont even know where to begin doing this. I am supposed to remove duplicate entries in this 2 dimensional array but I just can't find any answers anywhere.

Code: [Select]
<?php
/*
In the following two dimensional array (an array of arrays) you will notice that
there are several duplicate entries (David and Patricia).

1) Write a basic function which will go through $input and remove the duplicate
entries. A "duplicate" is an entry which has the same FirstName and LastName
as another entry. Ignore the other fields. Call your function "dedupe1". It
should return a two-dimensional array without these duplicates

2) Using your dedupe1 function, write one called dedupe2 which will detect empty
DOB's and DOB's of '00/00/0000'. When an empty or zeroed DOB is detected, your
code should use the entry with a complete DOB (if available). Like dedupe1
your code should return a two-dimensional array without duplicates. If you
use the provided $input function, all of the returned entries should have a
complete DOB listed.

*/

$input = array(
array(
'FirstName' => 'Daniel',
'LastName' => 'Anderson',
'Phone' => '614-123-4568',
'Address' => '123 Main St',
'SSN' => '001-01-0001',
'DOB' => '01/11/1922'
),
array(
'FirstName' => 'Aaron',
'LastName' => 'Williams',
'Phone' => '937-321-3993',
'Address' => '933 N Park St',
'SSN' => '992-23-1192',
'DOB' => '04/21/1965'
),
array(
'FirstName' => 'David',
'LastName' => 'Taylor',
'Phone' => '223-293-9921',
'Address' => '123 Main St',
'SSN' => '003-19-2992',
'DOB' => '12/14/1995'
),
array(
'FirstName' => 'Patricia',
'LastName' => 'Anderson',
'Phone' => '614-123-4568',
'Address' => '123 Main St',
'SSN' => '123-32-3123',
'DOB' => '00/00/0000'
),
array(
'FirstName' => 'David',
'LastName' => 'Taylor',
'Phone' => '223-293-9921',
'Address' => '123 Main St',
'SSN' => '003-19-2992',
'DOB' => ''
),
array(
'FirstName' => 'Patricia',
'LastName' => 'Anderson',
'Phone' => '614-123-4568',
'Address' => '123 Main St',
'SSN' => '123-32-3123',
'DOB' => '02/22/1957'
),
);

// Get results from dedupe1 and print them
$result1 = dedupe1($input);
var_dump($result1);

// Get results from dedupe2 and print them
$result2 = dedupe2($input);
var_dump($result2);

print_r($input);//just to see the information in the web page

function dedupe1($in_array) {
// your code here
}

function dedupe2($in_array) {
// your code here
}

?>

MySQL returns an error in the form of a number, a state, and a message. Without parsing the message you will not be able to determine what column is duplicated.While parsing the error code, I have also notice that, if you have multiple unique fields as duplicates, only the first duplicate encountered will be returned in the message. This is not very helpful to the end user.   Is there any way to parse the returned error code to reflect all duplicate fields, please see sample code below?

$error=array();
$sql = 'INSERT INTO staff(username, email, phone)
VALUES (?, ?, ?)';
$stmt = $conn->stmt_init();
$stmt = $conn->prepare($sql);
// bind parameters and insert the details into the database
$stmt->bind_param('sss', $username, $email, $phone);
$stmt->execute();
if ($stmt->errno == 1062) {
$errors[] = "One of the fields is already in use.";
}

Hi all,   Its been a long time since last help request from real professional from here but I'm again in trouble with a much more spectacular plan I'm working on.

For those who are interested in the plan then here it is:
My Idea was to make a new build starting from scratch and make it as dynamical as possible. So my goal is not to make almost anything fixed in the code. I have made a decision to make a one supper large table for multiple different entries so no more joining and no more views for me.! In this help request I'm having trouble with Posting values to a page processing page lets call it record_changer.php The sole purpose of this file is to get form posts and decide what to do. Either update, delete, or insert.

record_changer.php
 

<?php

include '../../config/config.inc.php';

if(is_ajax()){
# Checks if action value exists
  if(isset($_POST["action"]) && !empty($_POST["action"])){
    $action = $_POST["action"];
# Switch case for value of action
    switch($action){
			case "insert": datatable_insert_function();
			break;
			case "update": datatable_update_function();
			break;
			case "delete": datatable_delete_function();
			break;
    }
  }
}

# Function to check if the request is an AJAX request
function is_ajax(){ return isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest'; }

function datatable_insert_function(){
}

function datatable_update_function(){
}

function datatable_delete_function(){
}

?>

The problem.

The problem is that the $insert places two entries to the DB. I cant seem to understand why.?

	# Test _POST values
	$_POST['UserID'] = '2';
	$_POST['WorkID'] = '22';
	$_POST['Status'] = '1';
	$_POST['Code'] = '1';
	$_POST['Title'] = '1';


	$columns = array();
	foreach(array_keys($_POST) as $name){
		# Exclude Action and ID
		if($name == 'Action' || $name == 'ID' || $name == 'submit' ){ continue; }
		$columns[] = $name;
	}

	print_r($columns);

	echo "<br>";

	$data = array_fill_keys($columns, 'NULL');

	print_r($data);

	foreach($data as $key => $value){
		$data[$key] = empty($_POST[$key]) ? 'NULL' : "'".mysql_real_escape_string($_POST[$key])."'";
	}

	echo "<br>";

	print_r($data);

	$insert = mysql_query('INSERT INTO datatable (ID, '.implode(', ',$columns).')VALUES (null, '.implode(',',$data).')') or die(mysql_error());

No errors no nothing. Just two entries of correct data. 
PS. Sorry for a lot of prints in the code it is work and idea in the progress. The posts at the moment are fixed in the code so it is easier to refresh and debug.

Please help if you spot the problem. Im really out of ideas. Some fresh eyes might make a difference.

And Please for those who want to say it is a bad idea and why and why and so on.. Move a long.!!! Im not interested in whinging i have a great use for this and just having trouble with the two entries.

Thanks.
Edited by ztimer, 14 January 2015 - 03:27 PM.

Hola Everybody !!

I'm putting together a web app similar to Digg and was wondering if there is a function or code example I can use to avoid users submitt the same url.

For example:
Right now the user can submit;
1. http://www.example.com?post01
2. http://example.com?post01
3. www.example.com?post01

I want the web app to check if the link has been submitted by the user first and look for duplicate submission.

Any ideas ?

Thank you,

V.

I'm trying to prevent the reinsertion of data if a user refreshes a page. Tried using header() to take them to a new page but I'm using sessions, and it gives an error. Now I'm doing a basic check to see if the field exists before insert...

but I'm inserting arrays. Not sure if I've got this right. Would this work.

Code: [Select]

if(sizeof($_POST['fname'])) {
// loop through array
$number = count($fname);
for ($i=0; $i<$number; $i++)
{
   $fnames = $fname[$i];
    $lnames = $lname[$i];
    $phones = $phone[$i];
    $emails = $email[$i];
    $bdates = $bdate[$i];
    $bdates2 = $bdate2[$i];
    $bdates3 = $bdate3[$i];
    $wids = $wid[$i];
    $rids = $rid[$i];

$number_of_rows = mysql_num_rows(mysql_query("SELECT * FROM tbl_attendees WHERE attendee_fname='$fnames' AND  attendee_lname='$lnames' AND attendee_registrationid='$rid'"));

if($number_of_rows > 0) { // nothing to do. Just want to reload the page without running query insert

} else {

$query_insertItemWorkshop = "INSERT INTO tbl_attendees (attendee_fname, attendee_lname, attendee_registrationid, workshop_id, attendee_email, attendee_telephone, attendee_bday, attendee_bmonth, attendee_byear) VALUES ('$fnames', '$lnames', '$reg_alone', '$wids', '$emails', '$phones', '$bdates', '$bdates2', '$bdates3')";


$dberror = "";
$ret = mysql_query($query_insertItemWorkshop);


}


<!-- rest of page -->

Hi...

Before I have no problem in using On Duplicate Key, but now i have because of using time function.

Before I only have insert query:

Code: [Select]
$result = mysql_query("INSERT INTO regular_sum_hours(EMP_NO, Hours) SELECT EMP_NO, sec_to_time(SUM(time_to_sec(Rendered))) FROM regular_dtr_total GROUP BY EMP_NO") or die(mysql_error());   

And now I think that I need to add a syntax for Update, so i revise my code:
Code: [Select]
$result = mysql_query("INSERT INTO regular_sum_hours(EMP_NO, Hours) SELECT EMP_NO, sec_to_time(SUM(time_to_sec(Rendered))) FROM regular_dtr_total GROUP BY EMP_NO ON DUPLICATE KEY EMP_NO = EMP_NO, Hours = sec_to_time(SUM(time_to_sec(Rendered)))") or die(mysql_error());

and I got a problem in this part:

 Hours = sec_to_time(SUM(time_to_sec(Rendered)))")


Thank you

Hello,

I have looked on the forum, but haven't found the answer to this.

I have a basic [name, email, phone, event] web form and want to make sure there person is not submitting twice by doing duplicate check of the mail.  I looked at other solutions that suggested setting up unique IDs for each user, but it would be possible for a user to sign up for more then one event.

Any info would be helpful. Not a newbie, but not an expert.

I'm working on a directory component and have kind of a tricky thing I need to do.

The list of results pop through ajax on the left side along with it's google map number tag, and the google map pops on the right side. To make it even trickier the results are popped through a radius search by proximity.

The problem is if there are multiple results with the same address you end up having all of the number tags on the map buried under the last tag with that address, plus it makes for kind of a long list.

What I need to do is run a check to see if the previous result is the same address, and if yes not pop that result in a new table cell, rather stick it in a jquery drop down div so that all of the following results with the same address are stuffed in the jquery drop down in the same table cell under the first result with that address.

In a perfect world all of the listings with the same addresses would be submitted in order so I could just do a check for the previous itemid's address, but of course that's not the case. I thought about running a query to grab ALL of the addresses and then check the item's address against it to see if it matches and if yes echo the jquery div instead of the <td>, but obviously that won't work because that will cause the first item with that address to be put in the div also. Plus I don't know if that's a very efficient way to go about it anyway.

Here's kind of the basic thing I'm working with:
Code: [Select]
$query = "SELECT data_txt FROM jos_sobi2_fields_data WHERE fieldid = 76 AND itemid = $mySobi->id";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$myaddress = $row['data_txt'];
}
echo "<td $style>";

Any ideas?

Hi, I need help to check multiple rows for many columns, so that the script won't re-insert a duplicate entry. For eg:


$isRowExist=mysql_query("SELECT (field1,field2) from table1 WHERE field1='$f1' AND field2='$f2' ");

if(mysql_num_rows($isRowExist)==1)
   print "already in table1!";
else
   //proceed to insert into table table1


*BUT I tried this and it says mysql_num_rows expects resource, and this warning only shows when I have multipel columns I want to compare (it works just fine with one column in the WHERE clause above)

Please help!

Dear All,

  I have attached here with a schema, of MySql DB format i am refering to for the topic i need help on.

I want to know what is the best way to achieve this problem / task.

I have a profile table, and a category table, and a table to map profiles to category.
Which allows me to define/add into the category_profile_mapping table, profile ids against a category id.

Each category can have anywhere between 2 to 1,00,000 profiles mapped to it.

I have another table called sms_out. hence on the site, when i select a category and submit the form value with message, i want to know what is the best way to populate sms_out with MSISDN from the table "Profile" and the message from the form.
The list of MSISDN will be based on the category id selected, hence the profile id's will be available in the category_profile_mapping table.

I am bad at explaining the problem, however incase you folks need more info do let me know. i shall be glad to provide the same to .. all of you guys who are willing to help me

PS:  I am asking this question, cuz i want to knw the best solution to achieve this, when there is entries in millions to be done at one form submit.

[attachment deleted by admin]

Hello. Many thanks for your help.

I am writing a PHP/MySQL dating-site and have hit a programming impass.

I have a database full of members and a search form consisting of checkboxes.

So to search, a member ticks say...gender: female; age: 21,22,23,24,25,26; height: 5'4",5'5",5'6",5'7"; county: cornwall,devon,somerset

How can a run a check on the database selecting all entries that fall into the selected criteria. For example a 23 year old female of 5'5" living in Cornwall and a 26 year old female of 5'4" living in Somerset?

The key index of my database is 'id' and the fields a age,height,county
The names of the form checkboxes a
Gender: male, female; Age: 21,22,23,24 etc; Height: 5_4,5_5,5_6 etc; county: cornwall,devon etc

(Another quite newbie...)
I have already an online booking form. The Form works fine now and I would like to add on one more issue, but the code ignores what I want to check. I have 4 fields: arrival, departure, no. of persons and comments to check.

Scenario 1:

All field mentioned above are emtpty: Workes fine and message appears: "You have not provided any booking details".

Scenario 2:

If arrival (date_start) and departure (date_end) is entered, there should be at least an entry either in the field "comment", or in the field "pax". If not, there should be a message: "You have not provided sufficient booking details".

INSTEAD: The booking request is sent, which should not be the case.

The code is currently:

      # all fields empty : arrival, departure, pax and comments - error

if(empty($data_start) && empty($data_end) && empty($pax)&& empty($comment)){
    exit("You have not specified any booking details to submit to us.
    <br>Please use your browser to go back to the form.
    <br>If you experience problems with this Form, please e-mail us");
    exit;
}
      #If arrival  and departure date is entered, there should be at least an entry

either in the field "comment", or in the field "pax".
      
if(!isset($data_start) && !isset($data_end) && empty($pax) && empty($comment)){
    exit("You have not provided sufficient booking details.
    <br>Please use your browser to go back to the form.
    <br>If you experience problems with this Form, please e-mail us");
    exit;
}

The form can be tested at http://www.phuket-beachvillas.com/contact-own/contact-it.php

Can someone please check and tell me what's wrong with the code ? Thanks to anyone for any hint.

Here is one of those purely conceptual questions, which involves no code.  I'm trying to create a select query which among other things, allows a user of a website to search other members who fall within a particular age range.  I have a column in the table where the members' information are stored which records their ages.  Which brings us to the problem.  On any given day, a member's age may increase by one year compared to what it was the previous day, hence the need to update this column periodically.  I can't think of any way to automatically update this column on a daily basis.  So my solution is to run an initial update query every time a member tries to search other members based on age, which updates the age column for all other members, before running the select query which eventually retrieves the desired age range.  This leads to the second problem.  Imagine there are thousands of users using the website.  At any given instance, there could be hundreds of members, trying to search others based on age.  This means hundreds of users will be updating a single column (the age column) in one table at the same time.  Is this feasible? Can it cause the server to crash? Or is there really a more reasonable way to do all of this? Thank you all for taking your time to read this. Appreciate any responses.

Hello everyone.

I'd like to know how to duplicate a product through a php back-office.

That product consists of an ID in the "products" table of my DB. Then we have another table called "sub_products" that links its sub-products through a column named "product_id".

So I have product with ID "1000", and sub-products associated with it on another table with ID's "2021", "2022", "2023".

What's the routine I can use to duplicate both the product and its sub-products? I need to duplicate them because they fit 2 categories so I'm changing the category column during this duplicate.

Could really use some help. Thank you in advance! 

When I move threads on my forum, I want there to be a old thread in the section it was moved from saying "Moved: new link". To do this, I'm inserting a new thread in the database with the value moved equal to the new location, so when threads are being displayed users can see the thread in the new section.

Is there a quick function in MySQL that will allow me to create a duplicate row in MySQL, and then just run a quick update statement?

I don't want to have to insert a whole new thread like this (unless it's the only way):


//insert new thread
mysql_query("INSERT INTO `threads` VALUES (null, '$id','$title', '$content', '". getUsername() ."', NOW(), '". qfc() ."', NOW(), '". getUsername() ."', '','{$_SERVER['REMOTE_ADDR']}', '0', '0', '0', '', '0')");