Solved

All fields unique in table

Posted on 2014-01-30
14
229 Views
Last Modified: 2014-02-18
Just looking for some logic behind how to approach this.
I have about 5000 url's in table - which are built by prettifying the url (remove spaces etc).
Obviously all url's need to be unique and to achieve this I append a counter to the end of the url as new products are added.
But I want to add in an approach where I can do a check on all urls and make sure there are no duplicates.  This may happen because of a product name change.
So basically select all product names and urls and throw in an array - now how would you approach this?
Run through the array and do a forward lookup for the url maybe?
Newer entries should be sacrificed in favour of older urls (ID is auto)
0
Comment
Question by:Gary
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
14 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39822531
PHP array_unique() is your likely friend here.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39822538
If you're looking to overwrite the older URLs and you can order the URLs by date, you can use the URL as both the key and the value of the array.  Matching keys will overwrite the data from prior entries.
0
 
LVL 58

Author Comment

by:Gary
ID: 39822559
Cannot use array_unique() - if a url exists more than once then I need to append it -1,-2 etc.
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39822575
If I understand the data set correctly, maybe create an array of objects and usort() the array by the URL, then walk the array numbering them by adding a property to each object.  Once done you can go back and recreate the URLs.
0
 
LVL 58

Author Comment

by:Gary
ID: 39822594
Only append where there is a duplicate and append the duplicate with -1, and where there is another duplicate append with -2 and so on -3, -4 etc
I have multiple products that have the same name.
The lower the ID the older the product and the higher its url preservation order.  Otherwise I could end up with circular redirects to the wrong product - if a product was originally /product-4 because there was already a /product-1,-2,-3 but product-2 and product-3 have been removed then product-4 cannot be renamed as product-2 since product-2 may have a redirect url assigned since it was removed.

Think I'm confusing myself.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39822758
Please show me a handful of URLs that would be representative of the collection.  I think if we put our heads together a good design will emerge.
0
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 333 total points
ID: 39822919
Can you not make use of a unique index in the sql table. It'll generate a 1062 mysql error if you try and insert a duplicate, and you can handle that in a try / catch block. Avoids having to read all the data into an array first.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39822973
@Chris: Yes, of course, but I have a feeling there is at least one more layer to this question that will only come out when we start developing the test data set.
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39822979
For sure - just thought I'd put it out there early on :)
0
 
LVL 58

Author Comment

by:Gary
ID: 39822994
Ok, I'll give you an idea of what I currently do when adding new products

select id,CAST(REPLACE(url, '/".prettyurl($product->NAME)."-', '') AS UNSIGNED) as urls from url where url REGEXP '^/".prettyurl($product->NAME)."(-[0-9]+)?$' ORDER BY urls DESC

bit convoluted but does the job, what it does is if no rows then the new url is unique so I can just insert it.  If a row is returned what I actually get back is the last incremented value. So say for example I was inserting my-product and my-product-23 existed I would get back 23, I would then add 1 and make the new url my--product-24

So as far as sample data is concerned this is what I would end up with in the db.
my-product
my-product-2
my-product-3
...
my-product-23
my-product-24

Every url is unique to a product and will never be repeated even if that product is deleted.  So if my-product-2 is deleted it can never be used again for a new product even though the url is no longer used (the url will still exist in the table)

So in my url checking I want to make sure no url is repeated, chances of it happening should be 0 but I want a way to check it.  The table has an auto increment ID so if there are two entries for the same ID then the newer entry should be appended by +1 to whatever the last count is for that
0
 
LVL 58

Author Comment

by:Gary
ID: 39823004
Can you not make use of a unique index in the sql table. It'll generate a 1062 mysql error if you try and insert a duplicate, and you can handle that in a try / catch block. Avoids having to read all the data into an array first.
C'mon that's a bit too obvious and simple! There must be an harder way to do this...
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39823011
:)
0
 
LVL 43

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 333 total points
ID: 39823656
Hey Gary,

Here's my take on it (the working parts at least). Make sure your URL has a unique INDEX in your table:

<?php 
$product = "NewProduct";

try {
	
	$insert = "INSERT INTO Q_28352755 (url) VALUES (:product)";

	$insertStmt = $dbh->prepare($insert);
	$insertStmt->bindParam(':product', $product, PDO::PARAM_STR);

	$insertStmt->execute();
	
} catch (PDOException $ex) {
		
	switch ($ex->errorInfo[1]):
		case 1062:
			//we have a duplicate
			echo "<p>We have a dupe</p>";
		
			//get the next available counter
			$sql = "SELECT CAST(SUBSTRING(url, (:length+1)-CHAR_LENGTH(url)) AS SIGNED) + 1 AS counter
			FROM Q_28352755
			WHERE LEFT(url, :length) = :product
			ORDER BY ID DESC
			LIMIT 1;";
	
			$selectStmt = $dbh->prepare($sql);
			$selectStmt->bindParam(':product', $product, PDO::PARAM_STR);
			$selectStmt->bindParam(':length', strlen($product), PDO::PARAM_INT);
			$selectStmt->execute();
			
			$counter = $selectStmt->fetchColumn();
			
			//new product name
			$product = $product . '-' . $counter;
			
			//run the insert statement again
			$insertStmt->execute();
			
			if ($insertStmt->rowCount()) {
				printf('<p>New Record Inserted: %s</p>', $product);
			}
			
			break;
			
		default:
			//handle any other PDO errors
		
	endswitch;

}
?>

Open in new window

0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 167 total points
ID: 39826208
Storage is cheap.  You might think about keeping the unnumbered URL in one column and the number itself in another column.  A SELECT MAX() query would tell you the current high value for a given URL, and you just add one to the number column when you INSERT.

You might want to consider something like a transaction or locking logic to prevent race conditions in the following INSERT query.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

710 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