Solved

All fields unique in table

Posted on 2014-01-30
14
223 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
  • 6
  • 4
  • 4
14 Comments
 
LVL 108

Expert Comment

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

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
 
LVL 108

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 108

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 42

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 108

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 42

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 42

Expert Comment

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

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 108

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmakerā€¦

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now