Solved

MySQL Double Entry

Posted on 2012-03-29
2
333 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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.

679 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