Do i insert / into database?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
merylvingien
Forum Newbie
Posts: 12
Joined: Mon Sep 07, 2009 9:05 am

Do i insert / into database?

Post by merylvingien »

Hi Fellas, i am just about to start building out a database for a new site and have been reading topics on here, trying to learn stuff as i go along (i am a bit thick so please bear with me)
I have noticed in a couple of topics that people have noted to NOT add slashes to a database.
The last site i worked on had a small database and when i tried to insert any ' into the database it wouldnt have it, so in the end i removed all of these from the data and just put up with it.
However this time the database is HUGE and i will need to have the ' in there.
So my question is: Do i insert data like this:

Code: Select all

INSERT INTO `tablename` (`row1`, `row2`, `row3`) VALUES (1, 'something', 'something with these[color=#FF0000]'[/color]', 'something more')
Or

Code: Select all

INSERT INTO `tablename` (`row1`, `row2`, `row3`) VALUES (1, 'something', 'something with these[color=#FF0000]/'[/color]', 'something more')
Loads more questions to come, but i think its best to tackle them one at a time.
Many thanks for any help.
merylvingien
Forum Newbie
Posts: 12
Joined: Mon Sep 07, 2009 9:05 am

Re: Do i insert / into database?

Post by merylvingien »

No one? 8O
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Do i insert / into database?

Post by califdon »

Just so you'll know, most of our users are turned off when somebody "bumps" their own post. If somebody is in a position to help you, they will do so without further comments, and sometimes they will deliberately skip your post if you have "bumped" it because it just clutters up the index, indeed it may look at first like somebody has already given you an answer. So for your own benefit, don't do it.

Now, to your question: yes, escape quote signs, but generally you will not do that by entering them manually, use either mysql_real_escape_string() or addslashes(). See http://www.w3schools.com/PHP/func_mysql ... string.asp
merylvingien
Forum Newbie
Posts: 12
Joined: Mon Sep 07, 2009 9:05 am

Re: Do i insert / into database?

Post by merylvingien »

My humble appoligies, i wont do that again.

Many thanks for your answer, i am still a little confused as to why i wouldnt add the escape manually?
I am building the database from excel and using a notepad to upload the database when its complete.
If i dont escape the said characters in the notepad, then when i try and upload i just get an error. Thats why i was confused when a few people were saying that i shouldnt escape characters in the database.

I will read up the info in the link, once again many thanks.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Do i insert / into database?

Post by califdon »

For one thing, you will need to unescape them with stripslashes() whenever you use the data, unless your MySQL engine is set up for magic_quotes_gpc. Your process for loading data should not depend on a manual operation, wherever possible. You will miss some occurrences if you do it manually, and there will be no documentation of how the data was added. These are important matters in database work. So you're using Notepad to read the Excel file and editing the data? I hope you don't have very many rows of data! You might want to read about loading CSV files (which Excel can create) directly into a MySQL data base, then perhaps review and fix any anomalies that you find. It's a matter of how much data you have and how good (or bad) the data discipline is. If it's only a small number of rows, the manual approach is probably acceptable. I wouldn't want to do it with thousands or even hundreds of rows! Read the PHP manual about addslashes, stripslashes, mysql_real_escape_string and magic_quotes_gpc: http://us3.php.net/manual/en/function.addslashes.php. It is, admittedly, nonintuitive, but the purpose of all this is to allow certain characters to be encoded within stored strings that may be enclosed by those characters, themselves. If you try to send this SQL statement to MySQL, it will produce an error, because it sees the apostrophe in "O'Neil" as the end of the lastname field value, then some invalid characters:

Code: Select all

$sql = "SELECT * FROM myTable WHERE lastname='O'Neil'";
Most instructions discourage dependence on magic_quotes_gpc, because results may surprise you in some circumstances, particularly if you upgrade MySQL or try to use the script on a different server.
Post Reply