PHP and MySQL

My FAST way to enter massive amounts of array data on MySQL tables using PHP, InDesign, Excel, Frontpage, Dreamweaver and Notepad

I’m sure this is for the novice, because people who work with PHP, MySQL and others eventually find ways to enter data formatted as arrays in a really fast manner. This is my way that does the job.

Let’s say you need to make a MySQL table that has a lot of information, like from old database files, or the entire County, City, State, Zip code, Longitude, Lattitude of the United States.

For PHP and MySQL, the data needs to be in this form


"insert into nameoftable (first_name, job, city) values ('Stan','doctor','Chicago'), ('Charlie','engineer','New York'), ('Alfonso','consultant','Miami')";


It looks confusing, but using tabs, the statements looks better…


"insert into nameoftable (first_name, job, city) values
('Stan','doctor','Chicago'),
('Charlie','engineer','New York'),
('Alfonso','consultant','Miami')
";


If you have thousands of imported data, or even hundreds, this could take weeks to format and check.

I use old versions of Microsoft Excel, Adobe InDesign and Notepad or Frontpage.

First, select all of your data and paste it to Excel. See if the carriage returns and the tabs are there. The data should be in many rows and many columns. If they are just in one long row, then try pasting the data in InDesign.

In InDesign, see if the data appears as if they have been separated by tabs, or commas, and hopefully, in separate lines.

Before you can assign the single quotation marks ('), the commas (,) and the parentheses - ( ) , you need to clean your data first.

How I do this really fast

1. In InDesign, make each line the row of entries for the MySQL table. Use the Enter button to assign new lines to your information. Don’t worry if you miss an entry or make a mistake. Just do this as fast as you need to so.

2. Tab to separate the data, if the tabs are not there. If your information is separated by commas, then don’t do this step. Don’t worry about the spaces in between the data. You will clean it up later

3. Clean up the possible spaces between tabs and new lines using Search and Replace. The good thing about InDesign is that you can search for tabs using “^t". So to search for the extra space before a tab,
type in ” ^t” in the search box, and
type in “^t” in the replace box.
Click Replace All. Do this several times, until all of them are gone. You may have had something like
” ^t” - several spaces before the tab,
so keep clicking until a message that says nothing more was found appears. Then do the same thing with spaces after the tabs by
searching for “^t ” and
replacing it with “^t".

4. Do the same thing with the paragraphs. Clean up the possible spaces before and after paragraphs by
searching for ” ^p” and
replacing it with “^p".
Then do it again with “^p ” and
replacing it with “^p”

5. Make sure you save your work once in a while. Your computer could crash or freeze anytime with all this search and replace procedures.

6. Everything still looks jumbled up, so what you now need to do, is to see if they are tabbed correctly. Copy all, or a manageable amount and paste them on an Excel table. If you missed a tab or carriage return, you will spot it here. Make the necessary corrections.

7. Use Excel to rearrange your columns or take information off, or insert new columns with additional information, like if you want to put a “paid, unpaid” column, etc. Or if you want to just sort the rows according to last name, or city or whatever, of if you want to sort and separate the data to be put in different tables.

8. Copy and paste the data back to Indesign. Now you need to replace the tabs and carriage returns, but you’re still not safe. Save the data.

9. Look for single quotes first. If they are there, they will confuse PHP. You need to replace “‘” with “\’". However, don’t fully trust this yet… Look for a symbol that is not in the data. For example, look for an exclamation point “!". If your data has none, then search and replace your single quotes with five or more exclamation points - “!!!!!!!!". Save the data.

10. Search and replace tabs. Search for “^t” and replace them with something like “#######, %%%%%%%” - several sharp symbols - a comma - a space - several percentage symbols. Save the data.

11. Search and replace the new paragraph lines or returns. Search for “^p” and replace them with “&&&&&&&&&amp,^p(++++++++” - several ampersands - a closing parenthesis - a comma - ^p - an opening parenthesis and several pluses. Save the data.

12. Make a new file on Frontpage, Dreamweaver or Notepad. In Frontpage, click the HTML tab, and erase all the HTML code. In Dreamweaver, click the Code button, and erase all the HTML code. Copy and paste the data. The drawback about Notepad is that it can be limited in what it can take, and cut off your data, so make sure that the last line on Notepad is the last line you have on the InDesign file.

13. Replace the “!!!!!!!!” in (8) above, with “\’". Replace the “#######” in (9) above with “‘", and do the same thing with “%%%%%%%", “&&&&&&&&", and “++++++++", replacing them with “‘".

14. The reason we are not doing (12) above in Indesign is that InDesign will give you opening and closing single quotes, not the vertical, simple single quotes (I don’t know what you call them).

15. Look at the last line, and take out the comma if there is one. Now you have formatted data that you can enter into your MySQL table.

16. This is a really fast tutorial. Sorry for grammar mistakes or inexact commands for the programs I mentioned, but you get the gist. You may need to look at (12). Depending on how you set up your MySQL, you might want to put in a different unicode for the “‘".

Val Zubiri

Programming by Val Zubiri
Art by Val Zubiri
Arts Global Impact.org / Original Art and Art Books for Collectors for sale
Chicago Art Movement.com
Get a Massage Contact and Shop using your mobile phone

When I finally began to understand PHP and MySQL, I realized that a lot of people who would also like to learn PHP and MySQL do not need to go through the same, or worse, difficult ordeal that I went through.

There were times when I had to go to Borders Bookstore, hang out in the Computers section, and wait for a person to pick up a PHP or MySQL book. I would then approach them and ask them if they can sold my PHP/MySQL problem.

Online, I have also looked for member profiles which have the keywords PHP and MySQL mentioned. I would email them my questions, and I got answers back most of the time.

By doing this, and also mentioning in my own profile that I liked PHP and MySQL, I also encountered people online who needed help.

The best way I was able to teach anyone PHP and MySQL, later on, was to ask them to first buy the book that I also already have. If my chat buddies got stumped, I would refer them to the book page, and proceed from there.

Val Zubiri

Programming by Val Zubiri
Art by Val Zubiri
Arts Global Impact.org / Original Art and Art Books for Collectors for sale
Chicago Art Movement.com
Get a Massage Contact and Shop using your mobile phone

CASE: The Client wants the website to have a login page.

What this means is that your life just got more complicated.

You will need the following pages:

1. Registration Page
2. Login Page
3. Logout Page (or Button)
4. Page that automatically emails a forgotten username or password

and: A database of username, password and email

The Registration Page
Should check the database so that the username and the email are not repeated (be unique).

The Login Page
Should either use sessions or cookies which the Logout Page clears

The Forgot Username / Password Page
Should have the PHP mail() function. You might also consider making the forgot password email look better than just having regular text. (You will need to know about html headers in the mail function.)

Actually, this analysis continues…

The client surprises you with such a request, but he or she has valid reasons for such a request. Chances are you would have to make more tables and more columns in the database.

Then you get more creative…

Elements the Registrant would not know your are recording
It would also not hurt to have a column that would tell the date the person registered. It might not hurt to know when the member last logged in. It might not hurt to know how many times the website was used by the member. It might also not hurt to know the IP address of the registrant.

Elements the Registrant would know your are recording
During the registration process, you might want to know age, city, interests, sex, etc. Whatever you do, don’t scare off the registrant by asking too many questions.

Val Zubiri

Programming by Val Zubiri
Art by Val Zubiri
Arts Global Impact.org / Original Art and Art Books for Collectors for sale
Chicago Art Movement.com
Get a Massage Contact and Shop using your mobile phone

First of all, the best way to get into making websites, is to make a website for yourself.

The best way get into this is to make a website of your hobby.

For example, if you just had a baby, you might want to make a website of your baby’s pictures. In time, you will discover ways to present your baby pictures, and then ease yourself into discovering how to program your own picture albums, etc.

This example is a little weak, because the idea is picture-based, and there are many ways to show a picture album to visitors without getting into PHP and MySQL.

So let’s move on to a more complicated hobby…

Let’s say you have a comicbook collection. Then you might want the visitors to see your collection LISTED in different ways. Some might want to see your collection arranged according to the date of publication, or according to the superheroes or villains involved, or according to the current market value.

So now you are looking at making a database using MySQL, and commands using PHP to dip into the database and present the information to your visitors.

Because you are so into your hobby, you will eventually attempt to do your best to learn PHP and MySQL. As you discover the capabilities of PHP and MySQL, you will eventually make more expert modifications to your codes.

Val

Programming by Val Zubiri
Art by Val Zubiri
Arts Global Impact.org / Original Art and Art Books for Collectors for sale
Chicago Art Movement.com
Get a Massage Contact and Shop using your mobile phone

If you are new to MySQL, chances are you are still confused with join and the need to have many tables, and how to look for information from different tables using the join and other confusing commands.

What you can do, which is what I did for at least a couple of years, is to just make one huge, wide table, that looked like a spreadsheet. Even if you have to repeat information in certain columns, just try to live with that. Adjust it later on. Of course, when you have a client with a lot of requirements, this might not be advisable.

If you are placing PHP codes in many pages, to dip into the database, you will need to come back to each page to change your code if your database, tables and the way you arrange your information have changed.

Val Zubiri

Programming by Val Zubiri
Art by Val Zubiri
Arts Global Impact.org / Original Art and Art Books for Collectors for sale
Chicago Art Movement.com
Get a Massage Contact and Shop using your mobile phone