Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL Double Entry

Posted on 2012-03-29
2
Medium Priority
?
341 Views
Last Modified: 2012-04-03
I need a way to keep entrys into a database from being doubled. The issue is that on something like

%7B%7D that is the same as {} in html. So if one is entered in with the percentages and one is entered in normal, the data base thinks they are two different items even though they could be the exact same thing.

Now when I insert the new item into the data base the value ='s mysql_real_escape_string(htmlentities(item)) is how I do it now. This is wrong im guessing cause it could go with either of the above examples and puts them in as given.

So my question here is kinda two parted.

 1) how do I get it to insert the info one way or the other but never both being allowed. So {} will always show %7B%7D when inserted or visa versa.

and

2) How do I take an existing database and sort threw it to make sure they are all the same as 1 codes the new ones in, and take out any possible dupilications due to the difference?
0
Comment
Question by:mimoser
2 Comments
 
LVL 80

Assisted Solution

by:arnold
arnold earned 1000 total points
ID: 37786608
Instead of only item, you need htmlenities (item,null,"utf8")
http://stackoverflow.com/questions/405684/php-mysql-with-encoding-problems
Not sure what are you storing so it is hard to now what you are writing into the database.
http://www.php.net/manual/en/function.html-entity-decode.php
http://www.php.net/manual/en/function.htmlentities.php
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1000 total points
ID: 37786951
Wow, this is like comparing apples and (not oranges, but) fish.  There are a lot of "interesting" parts to the question.  Let's step through the data flow.

Getting it from client input to data base storage
When external data arrives in the PHP script, perhaps from client input via a form or from a web service, etc., it needs to be "escaped" so the data can be put into a data base.  For this purpose, you use mysql_real_escape_string().  You can read the man page to learn what it does.  Escaped data is safe to put into the data base.  The escape characters are almost always backslashes.  They are removed by MySQL when the data is written.  If this entire process is done correctly you wind up with exactly the same information in the data base.  Your client typed O'Reilly and your data base contains O'Reilly.  That's the expected process. The most common error condition arises when magic quotes has escaped the data and the script also calls mysql_real_escape_string().  Then you get things that look like O\'Reilly in the data base.  This article explains a little bit of what can go wrong with magic quotes.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_6630-Magic-Quotes-a-bad-idea-from-day-one.html

Getting it from data base storage to client output
Now that we have received external data and stored it, it's time to regurgitate it for browser output.  We want to be careful that we do not send something "toxic" to one of our clients.  Imagine if one bad client put a dangerous piece of JavaScript into our data base.  The last thing we want to do is cause that JavaScript to run on all the other client computers!  This is where the entity functions come into play.  To quote the online man pages, "... useful in preventing user-supplied text from containing HTML markup, such as in a message board or guest book application"
http://php.net/manual/en/function.htmlspecialchars.php
http://php.net/manual/en/function.htmlentities.php

These entity functions have no place in the logic flow prior to data base storage.  They should be used once and only once immediately before you echo the data or add the data to the browser output string.

A possible fix?
If you have entities in your data base, they probably got there because of a logic error, such as using htmlentities() on the input side of the process, rather than on the output side.  You may be able to correct the problem by reading the data base, using this:http://php.net/manual/en/function.html-entity-decode.php

I say may because once the data has been munged and stored, un-munging the data is not a certain thing.  Character encoding is one of the most complicated and nettlesome parts of information processing, so proceed with caution (and lots of data visualization) if you try to apply an automated solution to correct your data base.

best of luck, ~Ray
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

782 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