0

I'm designing a fairly complex web application using MySQL and PHP, and I need to write a CRUD interface for the database. I have many tables, with various one-to-many and many-to-many relationships between them. To keep things simple, let's focus on three tables: "students", "tutors", and "sessions". The operations for accessing/modifying records in individual tables are fairly straightforward, i.e.:

createStudent($name, $email, ...)
fetchStudent($student_id)
updateStudent($student_id, $name, $email, ...)
deleteStudent($student_id)

And similar functions for the other tables. I also have some browsing functions, with optional filter parameters:

fetchStudents($limit, $sort, ...)
fetchSessions($limit, $sort, ...)

These also seem fairly straightforward. However, I am struggling with a "sane" design for organizing the browsing functions that involve more than one table. My current approach is something like this:

// Fetch a list of tutors for a specified student
fetchStudentTutors($student_id, $limit, $sort, ...) 

// Fetch a list of students for a specified tutor
fetchTutorStudents($student_id, $limit, $sort, ...) 

// Fetch a list of sessions for a specified student
fetchStudentSessions($student_id, $limit, $sort, ...) 

// Fetch a list of sessions with a specified tutor for a specified student
fetchStudentSessionsWithTutor($student_id, $tutor_id, $limit, $sort, ...)

However, the possible types of queries, and thus necessary functions, theoretically grow exponentially (or perhaps factorially) with each new table (in reality some relations will not exist, like mapping multiple tutors to a single session). My question is, is there a better approach? Should I try to create a single function, like:

// $tutor_id, $student_id are also optional parameters
fetchSessions($tutor_id, $student_id, $limit, $sort, ...)

where the constraints on other tables are part of the filtering? In this case, should I try to built a single, monster query inside said function that considers all of these filters? Or, should I code a bunch of if-else blocks to handle all possible permutations?

Better yet, is there an existing project that can make it easier to tackle this kind of problem in PHP and SQL?

alexw
  • 8,468
  • 6
  • 54
  • 86

2 Answers2

0

Use a framework, like Yii. It has all that stuff done already.

CRUD and relations are easy to do with it.

Sarke
  • 2,805
  • 2
  • 18
  • 28
  • Does a framework really make it any easier to structure the type of complex queries I'm describing, though? From what [other posts are saying](https://stackoverflow.com/questions/18814279/yii-working-with-multiple-tables?lq=1), at least in Yii, you still end up basically writing the query. The only difference as far as I can tell, is that you have to deal with the extra layer of the Yii `CDbCriteria` interface, which may as well just be string concatenation of different pieces of the query. – alexw Aug 07 '14 at 03:09
  • It's much easier! Just look at the relations in CActiveRecord. http://www.yiiframework.com/doc/guide/1.1/en/database.arr you Also look at the beforeSave, afterSave, and afterDelete events. – Sarke Aug 07 '14 at 03:12
  • Hmm, interesting, that actually sounds promising. I especially like the "statistical query" option that they offer, which sounds much better than writing `COALESCE(SUM())` statements all over the place. I am a little apprehensive about reworking my entire codebase into a framework, though. Especially considering the horrible experience I had with the .NET framework when I was a C++ programmer ;) – alexw Aug 07 '14 at 03:28
  • Also look at the admin section of the blog demo http://www.yiiframework.com/demos/blog/index.php/post/admin it has a full crud. It's included in the Yii source in the demo folder. – Sarke Aug 07 '14 at 03:33
0

It turns out that a full-fledged framework is not necessary. What I was looking for is an "object relation mapper (ORM)". Most frameworks have a built-in ORM, but there are some standalone ORMs such as RedBean and Propel as well.

alexw
  • 8,468
  • 6
  • 54
  • 86