PHP and MySQL - An overlap in duties?

MySQL database
#1

Hi all,

I know PHP but am new to database design ( I usually work with some CMS and use PHP to build out features, pages, etc.). I’m building an app from scratch and I find myself wondering “should I let MySQL handle this task or should I code it out using PHP?”.

So for example, I have a tagging feature. I can tag contacts so they are organized. I don’t want a user to create the same tag. I can require the tag name to be unique in the database and upon inserting a duplicate tag name the database will simply ignore it (not create a duplicate record). Or I can code up some PHP to check to see if the submitted tag exists and prompt them that no duplicates are allowed.

See what I mean here? It seems like you get into the situation where you can handle things on the coding end (PHP) or at the datasource (MySQL). And perhaps this is where triggers and views on the MySQL side come into light too although I know nothing about those currently.

Just wanted some thoughts on this topic.

#2

Actually, no. For concurrent requests, a race condition exist where the requests can all find that the data doesn’t exist and will try to insert/update it.

You would define the column(s) as a unique (composite) index in the table, just attempt to insert/update the data, then detect in the php code if a duplicate key error occurred, and notify the user about the duplicate value.

1 Like
#3

Gotcha. Thanks for the reply @phdr

#4

I concur with @phdr. It is exactly as he has stated.

#5

Thanks for the input @benanamen.

So it’s better to set as much of the business logic/rules within your MySQL instance and let MySQL decide whether a entry create/remove/update happens. Then communicate what MySQL just did to your user using code.

Life without foreign keys can exist. Right?
#6

Think of it this way, what is a Database for? It purpose built to manage Data. Let it do what it does best. Of course there are times when you will code, but if the DB can do it, then let the DB do it.

#7

Roger that.

I think what’s happening is I’m leaning on php to do things mysql can do because I’m not fully aware of mysql’s abilities.

Lots to learn. I’m a frontend designer/developer with a growing interest in backend development. Sometimes it’s overwhelming but little steps will get me there.

Sponsor our Newsletter | Privacy Policy | Terms of Service