Solved

MySQL Double Entry

Posted on 2012-03-29
2
334 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
[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
2 Comments
 
LVL 78

Assisted Solution

by:arnold
arnold earned 250 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 110

Accepted Solution

by:
Ray Paseur earned 250 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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.
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
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

738 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