Checking for duplicate entries in a database, and handling it accordingly.

[NiTx]

Hi All!

 

New to forum altohugh I have followed it for a we while never really posted anything. Decided I will need a bit of help for one of my current projects. At the same time I intend to help others to my best of my ability.

 

So I'm busy making a review website at the moment. Like reviews for lollies, fruit.. what ever! What ever has a brand. I do have a few Lynda tutorials I am following, however, they don't always explain or teach what I need to know! So I'll need some expert advice from this board.

 

So:

 

I have two tables in my database: reviews, brands

 

What I want to do is:

 

When a user submits a review for a brand that already exist in my database it want it to go, ok... someone has reviewed this brand before so I'm going to submit the review into the reviews table (as per usual) but I'm not going to treat this brand as being new and add it to the brands table. Instead I'm going to alter data on the brand that already exist. I want it to change the review count for that brand by adding an extra 1.

 

So I need code that checks to see if the brand exists in my data base already, if not, add it. If it does, tally up all the reviews that have been done on this brand and add 1.

 

It sucks because I know what I need to do, I just dont know the code to get there AND I KNOW ITS PROBLY SIMPLE. So, by all means if you can answer my question by giving me a tool or link to a site that can show me the SQL queries and php code I need, then please do so! Otherwise your response will be very much so appriciated.

 

Thanks in advance! Look forward to learn and share my knowledge!

 

 

[fugix]

so you dont have any code at all yet?

[floridaflatlander]

Big  picture

 

If I had to guess, you'll probably need to control the names in brands.

 

I say you because if you set it up to be automatic and someone types in redwing shoes for a brand and then someone else types in red wing shoes when you run a guery like

 

$query = SELECT name FROM brands WHERE name = "red wing".

 

Your query will come back with the results that there is no red wing in your brands table and its Ok to include red wing as a brand, then you'll have double enteries.

[NiTx]

Sorry I don't have any data, I didn't know where to start.

 

If anyone can show me where to start that will be awesome. I know i need to do a mysql query, how eer i felt it pointless to try make a code because i wouldnt even come close.

 

 

 

[fugix]

well to learn php better go to www.php.net and to learn mysql go to dev.mysql.com

[NiTx]

Thank you fugix,

 

I have used my current knowlegde of php and sql and this is what I'm coming up with.

 

		
$company = mysql_prep($_POST['company']);

$company_set = mysql_query("SELECT cmp_name FROM companies WHERE cmp_name = $company", $connection);

	if(!$company_set) {

	$query = "INSERT INTO companies ( cmp_name ) VALUES (
			'{$company}') ";

		if (mysql_query($query, $connection)) {
			//Success?
		} else {
		//Display error
			echo "<p>Subject creation failed.</p>";
			echo "<p>" . mysql_error() . "</p>";
		}

	}

 

Error code: Resource id #6

 

As you can see, I am trying to find out if $company already exists in the table companies under the row cmp_name. If it doesn't exist then continue as normal and add the new company to the database.

 

 

 

[fugix]

You would use mysql_num_rows() to check if there are any rows where your criteria is met

[floridaflatlander]

I basically do it like this

 

if (($_POST['submit'])) {
$company = ($_POST['company']);
// Check to see if this profile already exist on the companies table
$query = "SELECT cmp_name FROM companies WHERE cmp_name = '$company'";
$r = mysqli_query($dbc, $query); 
$n_rows = mysqli_num_rows($r);

                if ($n_rows == 0) {
               do this
               } else {redirect or echo "This company already exist";}

} // end of if submit

[joe92]

As floridaflatlander said, if someone posts 'red wing' and another posts 'redwing' they will both get seperate entries in your table which will become quite messy for you. After checking for a direct match under mysql, I would suggest using the levenshtein function (a gem pointed out to me by another freak ) to compare to the nearest entries already in the table.

 

Run:

if(!mysql_num_rows($theDirectMatchQuery))
{
do the levenshtein function
if the levenshtein function doesn't return any close match's THEN insert without question into database
}

 

To work levenstein with a set of mysql results do the following:

 

$checkingCompany = mysql_query("SELECT name FROM companies");
$checkingCompanyNumRows = mysql_num_rows($checkingCompany);
$companyArray = array();
for($i=0;$i<$checkingCompanyNumRows;++$i) //this will run the loop for as many times as results were returned
{
$checkingCompanyFetchRow = mysql_fetch_row($checkingCompany);
array_push($companyArray, $checkingCompanyFetchRow);
}

 

On the link provided above, where they are using an array, use the $companyArray that you just pushed all the results into. Then after the levenstein is complete, if another result is say only 25% different to the one being submitted, send the submitted result and the closest match result back to the user asking if they are sure they are different and if not, please change it. Else they can continue onto submitting their review. This will not completely prevent duplicates from being entered into the database as it relies on the good will of the person changing their submission. However, most people will change it so this will save you a lot of time and make your site look much neater too.

 

As for starting out in the complete basics of mysql and php, I would just tell you to get yourself a good book, one that I used and recommend is Learning PHP, MySQL, and JavaScript by Robert Nixon .

 

Good luck, Joe

[joe92]

Oops, just noticed an error in the for loop and thanks to the 10 minute edit window, I can't edit it It should say:

array_push($companyArray, $checkingCompanyFetchRow[0]);

 

You have to tell it which row to return.