PHP/MySQL: The right way creating a big website database based...

Question

Hi, I'm creating a movies website, IMDB.com like.. I'm really new to PHP and programming at all but I have some books and StackOverflow of course :)

I have already done lot of the work, but now I have more than 600 lines of code (PHO only) per page and more than 20 database tables only for storing and geting the movie data (many-to-many relationships)

Most of the code are MySQLi queries (prepared statements) to insert/get data and loops. Nothing fancy, just basic PHP/MySQL.

I have some questions:

• It is recommended to use MySQLi prepared statements for every SQL query or is better just to use old MySQL for internal PHP/database queries and leave prepared statements for user input only?

• To get all the movie data and show it I need to get the data from more than 16 different tables. I use one MySQL query per table (somethimes more) and lots of loops. This is the correct way a website need to work?? I mean.. this is normal???

• How can I simplify the code to simplify the reading? Can I separete all those queries to external files?? Can I create functions to handle each table query??

Hope you can give me a hand and maybe you have some more suggestions for correctly building such a website.

Thanks!!

Answer 1

Consider looking at or using a web framework for your website.

symfony CakePHP CodeIgniter

Are some of the more mainstream ones. Learn from them if anything.

Answer 2

1. Prepared statements are just fine for your own internal queries to. You'll have a strutured approach for all queries.

2. Well it depends on what you're showing. But I would say that you normally could use joins to get the data you need from more tables. You'll have a lot less quering to get the correct data, and it sounds like all your data is connected somehow to the one movie you're showing.

3. As Peter D comments, I would recommend using a web framework to learn how to seperate out the database handling from the view. Are you using an object oriented approach now? Look at the MVC pattern that some of these frameworks implement, that'll get you going.

Answer 3

Like Peter D mention before add this one to the list of framework to use. Zend Framework http://framework.zend.com Open source and free.

1. It is recommended to use MySQLi ...

Definitely MySQLi, but it's a big question by itself, if you start coding you'll need to grasp the basic of T-SQL to understand the difference.

1. To get all the movie data ...

It depends on alot of things. Size of database. Wanted results i.e. the information that need to be displayed, response time of the queries vs displaying in user view. Do you know about JOIN, UNION?

1. How can I simplify the code to ...

Yes to all theses questions. www.w3schools.com/php/ if it can be of any help and learn the MVC pattern. Useful to alot of programming language these days. Maybe a framework would help you here

Answer 4

As mentioned in the anwsers above I would also point you to using a framework that impliments the MVC design pattern. Along with that most of the frameworks have an ORM built in but if they do not you can look at Symphony or EZPDO is another good ORM to tie into your Model in (M)VC.

CodeIgniter is a very fast and light weight MVC framework that would allow you to bootstrap pretty quickly but you may also want to look into ZF (ZendFramework). ZF has a great framework feature set and is pretty flexible overall.

Beyond that be sure to seperate your reads and your writes in either or Model or your calls to the ORM of choice. This will allow you to slave data to multiple MySQL Boxes for bigger performance but allows you to use one database engine to start out.

Add in the ability to use Memcached so that you can cache your data/objects vs hitting the database.

When using cache put some thought into how you would expire cache for a database update. In other words if your selecting data from a database to display in a view and that data is has not changed you should not need to hit the database every time but rather pull it from memory. Once the data actually does change you'd want to invalidate that cache so it's not stale and then re-cache the new data.

memcached: http://www.danga.com/memcached/'

-facebook also has a version

CodeIgniter - http://codeigniter.com/

EZPDO - http://www.ezpdo.net/blog/?p=2

ZendFramework -http://framework.zend.com/

Answer 5

To this questions:

"To get all the movie data and show it I need to get the data from more than 16 different tables. I use one MySQL query per table (somethimes more) and lots of loops. This is the correct way a website need to work?? I mean.. this is normal???"

No. If I understand you correctly, you should be using some type of JOIN depending on the data you're retrieving from the database. Getting results for huge amounts of data, and then picking out only the pieces you want in PHP is much slower than letting the database do the work of sorting/retrieving only the records/info you want to show.

I highly recommend a somewhat dated but very easy to grasp book that covers PHP and MySQL/Databases in general: http://www.dmcinsights.com/phpmysql2/ - It covers a lot of "in practice" techniques along with the code, so it'd be great to learn from.

There is a apparently a third edition with updated info, but I have not looked at it.