Avoiding duplicate rows, using multiple columns, when Inserting via query...

[Jim R]

My table primarily stores rows (basketball players) of First Name, Last Name, Grade and School.  Obviously, with over 1,000 there are many players with similar last names and similar first names, even a couple of instances over the years of players with same first and last name.  

 

I've done some searches here and elsewhere and found "on duplicate key" or "insert ignore into", and they don't seem to apply or I'm not able to get it to work.  My intention is to check first name, last name and grade before inserting a row from a form submission.

 

Do I need to SELECT the information I want to check concat(nameFirst, nameLast, grade) then compare it to the data I'm looking to insert?

"INSERT INTO a_players (school, grade, nameFirst, nameLast)
	VALUES ('".$data['school']."', '".$data['grade']. "', '" .$data['nameFirst']. "', '" .$data['nameLast']."')";
 

[requinix]

You probably don't need to worry about concurrency much (multiple players being added at the same time) so you should be able to simply do a SELECT for a matching player and then do the INSERT if you don't find a record.

[Jim R]

Correct.  These are one at a time, and the people doing the inputting won't be at the same events/games.  Am I going down the correct path, in terms of SELECT the fields I need to compare, concatenating them, then putting them in a WHERE on my INSERT?  

 

I'm not sure of the syntax of that.  Something like this?

"INSERT INTO a_players (school, grade, nameFirst, nameLast)
	VALUES ('".$data['school']."', '".$data['grade']. "', '" .$data['nameFirst']. "', '" .$data['nameLast']."')";

WHERE concat(grade,nameFirst,nameLast) != concat($data['grade'],$data['nameFirst'],$data['nameLast']

Or would it some sort of nested query? 

[requinix]

Sounds like you're making this more complicated than it needs to be.

 

The name and grade are what make a player unique, right? They don't in the real world but I guess they do in your application...

 

If you want to avoid adding a player twice then you can do a SELECT to find a matching player with that information. Name and grade. It's a query just like any other SELECT query you might do, except this time you don't really care about getting the data back but simply knowing that the data exists (which you can do by attempting to get the data).

 

With that query you know if the player exists, and if they do not then you add them. You they do then you should probably present a warning to the user: the duplicate suggests maybe there's a problem with what the user is doing.

[Jim R]

I'm saying I've never done a Select within an Insert to find matching values.  I was wondering what the syntax of that would be.  Even if you just point me to where I can find it.

Edited January 2, 2018 by Jim R

[requinix]

Two. Separate. Queries.

[taquitosensei]

You could create a unique key and then it wouldn't let you insert the duplicates. You'd have to do some error checking on the result, so you could display errors to the end user. 

alter table a_players add UNIQUE player(grade,nameFirst,nameLast)

[Jim R]

The intention is to get information from a form, compare it to data in a_players, and if it doesn't already exist, insert a new row.  

 

I'm not entirely sure I'm getting the data out of the form I need just yet, but I'm wondering are the $query and IF statements accurate?  It's not showing any errors, but it also didn't insert the row in my testing.

 add_action('quform_post_process_1', function (array $result, Quform_Form $form){
    $data = array(
        'f_school' => $form->getValue('quform_1_7'),
        'f_grade' => $form->getValue('quform_1_5'),
        'f_nameFirst' => $form->getValue('quform_1_3'),
        'f_nameLast' => $form->getValue('quform_1_4')
    );
 
 
			$query = "SELECT nameFirst,nameLast,grade 
						FROM a_players
						WHERE nameFirst = '" . $data['f_nameFirst'] ."' and nameLast = '" . $data['f_nameLast'] . "' and grade = '" . $data['f_grade'] ."'";
			
			$results = mysql_query($query);
			echo mysql_error();
		
		//	while($check = mysql_fetch_assoc($results)) {			
  
					if (mysql_num_rows($results) == 0) {
			 			"INSERT INTO a_players (school, grade, nameFirst, nameLast)
						VALUES ('".$data['f_school']."', '".$data['f_grade']. "', '" .$data['f_nameFirst']. "', '" .$data['f_nameLast']."')";
					}			

			
 
}, 10, 2);

[benanamen]

Since no one is mentioning it...

 

You are using dangerous, obsolete code that has been completely removed from current versions of Php. Your code will not work at all in current versions.  You need to use PDO with prepared statements. Never put variables in your query. It appears you may be allowing user supplied data to go directly in your query which opens you up to an SQL Injection Attack. Also, do not output internal system errors to the user. That info is only good to hackers.

 

Here is a tutorial to get you going. https://phpdelusions.net/pdo

 

As far as your question, if you want to avoid duplicates, set a UNIQUE constraint on the relative columns and then capture the duplicate error if any. There is no need for two query's. This will also avoid creating a race condition if you were to have simultaneous submissions of the same data.

Edited January 3, 2018 by benanamen

[Jim R]

Since no one is mentioning it...

 

You are using dangerous, obsolete code that has been completely removed from current versions of Php. Your code will not work at all in current versions.  You need to use PDO with prepared statements. Never put variables in your query. It appears you may be allowing user supplied data to go directly in your query which opens you up to an SQL Injection Attack. Also, do not output internal system errors to the user. That info is only good to hackers.

 

Here is a tutorial to get you going. https://phpdelusions.net/pdo

 

As far as your question, if you want to avoid duplicates, set a UNIQUE constraint on the relative columns and then capture the duplicate error if any. There is no need for two query's. This will also avoid creating a race condition if you were to have simultaneous submissions of the same data.

 

If felt like I knew any of what you're talking about, I wouldn't need to come here for help.  I have a pretty large number of files incorporating mysql connections and syntax.  I tried switching over some of this mysqli, and I couldn't get it all sync'd up to work.  Am I going to have to set up a PDO based connection?  The form is from a plugin that likely isn't using that method.  Is the data derived from that form going to be compatible?  

 

The queries look simple enough, but keep in mind, as much as I've been doing, I've never really gotten more than a layman's understanding of it.  I don't know the difference between fetch_assoc and fetch_array.      I've been porting query syntax from one project to another since 2004.

 

 

The form used to supply data will only be accessible by four or five people.  I'm not worried about someone hacking me in that way.  There won't be a race condition because they won't be at the same place at the same time writing about the same subjects.  

 

 

I'm not sure how to set up a unique key utilizing multiple columns.  Again, that's why I'm here.  

[Jim R]

What's the difference between using PDO and mysqli ?

[benanamen]

If you want to upload all your project files to Github or BitBucket I will see about getting you going in the right direction. It sounds like you more just want it to work and are not really interested in learning programming which is ok. (By me in this case)

Edited January 3, 2018 by benanamen

[Jim R]

I like learning and enjoy my interactions on here, but at 47, I'm self taught as an adult after programming a lot with Basic and Fortran in the 80's.  I never pursued it beyond that until about 2003.  I've learned since then my brain doesn't handle coding very well, but I can find the logic in code, port it and tweak it for my needs.

 

 

No matter what happens, I'd like to have a working set of queries (or just one), which checks for an existing row, and if one doesn't exist, it inserts it.  

 

I'm going to create a separate connection file for this POD or mysqli and play around with it.  I saw mysql is being deprecated out, completely unsupported by version 7.0.

[Jim R]

PDO syntax:  Should I focus on execute() or fetch()?