Business Logic inside a Query

DoubleDee · June 7, 2012, 12:55am

My website allows a Member to upload one Photo to his/her Profile, however I want to Approve/Decline each uploaded file before it appears.

When I display a Member’s Photo on a page, I usually am grabbing other Member Details (e.g. Username, First Name, Location, Gender, etc) from the database. And since I am trying to avoid having to call a PHP Function - with another SELECT query inside of it - to grab the “member_photo” and determine whether it was approved or not, I am wondering if I can handle all of this logic in one query?

The logic of what I need to do is pretty simple…


For a given "member.id",
if "photo_approved" = TRUE
then return "photo_name"
else return "photo_pending_approval.jpg"

Is it okay to stick that “business logic” into my MySQL Query, or should I leave that to a PHP Function??

Thanks,

Debbie

r937 · June 7, 2012, 2:53am

yes

DoubleDee · June 7, 2012, 3:44pm

Well, I have some hot-head PHP Developers that say I am flat out wrong for doing that…

Care to expound on why it is okay?

I suppose this comes down to the whole debate of where you store “Business Logic”.

If you did everything in MySQL and then switched to Oracle you’d feel the pain.

Then again, if you stored everything in PHP and switched to .Net the same thing would happen?

The only thing I can see, is that they always say the DATABASE is the “bottle-neck” on almost every system, so maybe that is why it is better to stick things in a PHP Function? :-/

Debbie

r937 · June 7, 2012, 3:55pm

there is a word for those types of developers, and that word is incorrect

also, LEFT OUTER JOIN and COALESCE are standard sql, so no worries if you switch to oracle

where would you store the business logic that an order has to be associated with a known customer (relational integrity, enforced by foreign key)

where would you store the business logic that a customer needs to be identified uniquely (entity integrity, enforced by primary key)

and the toughest question of all… where would you store the business logic that a product must have certain known attributes (tables with defined columns for specific items of data)

anybody that thinks you should do “all” business logic in the application (php) is fooling themselves, and you can verify this by taking the database away from them and telling them to implement with text files and excel sheets

as for the database being a “bottleneck” you might ask yourself which types of people encounter more bottlenecks – developers who know what they’re doing or developers who are, shall we say, uninformed about what business rules actually belong in the database

sorry, this sounded like a rant, didn’t it

wasn’t aimed at you, debbie

oddz · June 7, 2012, 5:56pm

I don’t understand the problem with using a conditional in php swapping the image. Much less intimidating for a designer who might need to change it. That is how I would handle it.

Using a switch(conditional) as shown isn’t going to have any noticeable affect on query performance.

DoubleDee · June 7, 2012, 6:23pm

also, LEFT OUTER JOIN and COALESCE are standard sql, so no worries if you switch to oracle

Well, since you are diving into SQL, please allow me to describe what I need to do again…

Scenario:
When a Member uploads a Photo, I store the physical Photo in a directory on my webserver, and in my database I have the fields “photo_name” and “photo_approved” (default=0).

Usually when I grab a Photo, I need to grab other Member Info and would have something like…


SELECT username, online_status, photo_name, photo_label, first_name, location
FROM member
WHERE id=?;

So, in SQL, how would I say…

“When you query the database for DoubleDee’s info (id=19), check to see if “photo_approved”==TRUE, and if it is, then return the “photo_name”, otherwise return “photo_pending_review.jpg” so a dummy photo can be shown.”

I have a PHP Function that does that, but the problem is I query the database once - like in the example above - and then I have to run a SELECT a second time in my PHP Function to do the search and conditional I just described. So you have 2 database hits for only 1 bang?!

where would you store the business logic that an order has to be associated with a known customer (relational integrity, enforced by foreign key)

where would you store the business logic that a customer needs to be identified uniquely (entity integrity, enforced by primary key)

and the toughest question of all… where would you store the business logic that a product must have certain known attributes (tables with defined columns for specific items of data)

anybody that thinks you should do “all” business logic in the application (php) is fooling themselves, and you can verify this by taking the database away from them and telling them to implement with text files and excel sheets

I agree.

as for the database being a “bottleneck” you might ask yourself which types of people encounter more bottlenecks – developers who know what they’re doing or developers who are, shall we say, uninformed about what business rules actually belong in the database

Well, I’m sure that is a complicated topic, and one no one can address without a specific problem, but I have heard that consistently over the years, making me at least conclude that database calls are expensive, so query wisely…

sorry, this sounded like a rant, didn’t it

wasn’t aimed at you, debbie

Oh, I know. And the 20-something year old punk, who has both enough naiveté and arrogance to fill up Texas makes me shake my head…

For my simple case, I think using the database or a PHP Function is about equal.

But I am on an eternal quest to learn how to do things multiple ways, and learn how to do things the best way possible, so that is why I am asking.

Thanks,

Debbie

r937 · June 7, 2012, 6:31pm


SELECT username
     , online_status
     , [COLOR="#0000FF"]CASE WHEN photo_approved -- true
            THEN photo_name
            ELSE 'photo_pending_review.jpg'
        END AS photo[/COLOR]
     , photo_label
     , first_name
     , location
  FROM member
 WHERE id=?;

note that in this simple case of columns in the member table, i too might use an application conditional

when photos are in a separate table, that’s when i’d use a LEFT OUTER JOIN with COALESCE

DoubleDee · June 7, 2012, 6:33pm

As I have been typing, a little light went off in my head… (Although I still hope r937 show me a solution in SQL.)

I was complaining because I have a query like this…


SELECT username, online_status, photo_name, photo_label, first_name, location
FROM member
WHERE id=?;

And then my PHP Function is like this…


	function validatePhoto($dbc, $photoName){

		// Check for Image.
		if (is_null($photoName)){
			$safeImage = "NoImageAvailable_100x76.png";
		}

		// Build query.
		$q1 = "SELECT photo_approved
						FROM member
						WHERE photo_name=?";

		// Prepare statement.
		$stmt1 = mysqli_prepare($dbc, $q1);

		// Bind variable to query.
		mysqli_stmt_bind_param($stmt1, 's', $photoName);

		// Execute query.
		mysqli_stmt_execute($stmt1);

		// Store results.
		mysqli_stmt_store_result($stmt1);

		// Check # of Records Returned.
		if (mysqli_stmt_num_rows($stmt1)>0){
			// Record Found.

			// Bind result-set to variables.
			mysqli_stmt_bind_result($stmt1, $photoApproved);

			// Fetch record.
			mysqli_stmt_fetch($stmt1);

			// ********************
			// Return Photo name.	*
			// ********************
			if ($photoApproved==1){
				// Approved Photo.
				$safeImage = $photoName;
//				return $photoName;
			}else{
				// Pending Photo.
				$safeImage = "PhotoPendingApproval_100x76.png";
			}

		}else{
			// Record Not Found.
/*
			$_SESSION['resultsCode'] = 'FUNCTION_MEMBER_ID_NOT_FOUND_5001';

			// Set Error Source.
			$_SESSION['errorPage'] = $_SERVER['SCRIPT_NAME'];

			// Redirect to Outcome Page.
			header("Location: " . BASE_URL . "/account/results.php");

			// End script.
			exit();
*/
		}

		return $safeImage;
	}//End of validatePhoto

But I guess there is no reason why I can’t just go update all of my queries in my web pages to be like this…

SELECT username, online_status, photo_name, photo_label, photo_approved, first_name, location
FROM member
WHERE id=?;

…so there is only ONE Database Call, and then my PHP Function just does the swapping of the “photo_name” as needed, right?

Debbie

oddz · June 7, 2012, 9:21pm

Correct. When photo has not been approved merely change the photo in php, simple.

r937 · June 7, 2012, 9:26pm

debbie, did you miss post #7 or … ?

DoubleDee · June 7, 2012, 9:46pm

Apparently it crossed in the mail.

I haven’t seen anything yet.

Just spotted it now, after sitting back down at my laptop? (Is that even proper English?!) :-/

Debbie

DoubleDee · June 7, 2012, 9:49pm

r937:

SELECT username
     , online_status
     , [COLOR="#0000FF"]CASE WHEN photo_approved -- true
            THEN photo_name
            ELSE 'photo_pending_review.jpg'
        END AS photo[/COLOR]
     , photo_label
     , first_name
     , location
  FROM member
 WHERE id=?;
note that in this simple case of columns in the member table, i too might use an application conditional

when photos are in a separate table, that’s when i’d use a LEFT OUTER JOIN with COALESCE

Wow, that is simple?!

So is that code something I can run through my Prepared Statement?

Or is that a “Stored Procedure”?

What exactly is it called?

And is that MySQL specific, or would that work in SQL Server or Oracle too?

Thanks!

Debbie

oddz · June 7, 2012, 10:21pm

Nope just standard SQL. It will work on both MySQL and Oracle. What won’t work on both is IF() though supported by mySQL. Which is probably why r937 used a case statement instead – more portable. I really wouldn’t worry about portability to much. Going from one db to another is always a major expense any way you slice it – developers might as well get a piece of the pie. Also, how often do you think your going to change database providers. Especially Oracle considering it has a hefty price tag.

DoubleDee · June 7, 2012, 10:35pm

Cool.

What won’t work on both is IF() though supported by mySQL. Which is probably why r937 used a case statement instead – more portable.

Duly noted.

I really wouldn’t worry about portability to much. Going from one db to another is always a major expense any way you slice it – developers might as well get a piece of the pie. Also, how often do you think your going to change database providers. Especially Oracle considering it has a hefty price tag.

Just playing devil’s advocate.

Debbie

DoubleDee · June 7, 2012, 10:37pm

r937:

SELECT username
     , online_status
     , [COLOR="#0000FF"]CASE WHEN photo_approved -- true
            THEN photo_name
            ELSE 'photo_pending_review.jpg'
        END AS photo[/COLOR]
     , photo_label
     , first_name
     , location
  FROM member
 WHERE id=?;
note that in this simple case of columns in the member table, i too might use an application conditional

when photos are in a separate table, that’s when i’d use a LEFT OUTER JOIN with COALESCE

Thanks for the help, r937!! :tup:

I had no clue you could do that in SQL, and learned something new!!

Ironically, the consensus I am getting from others is that I should NOT be pre-screening Uploaded Member Photos, and should just trust people?! (:

So it is looking like this thread was for not, other than I learned something new in SQL!!

Thanks,

Debbie

r937 · June 8, 2012, 1:08am

not naught

wanna see the COALESCE ?

DoubleDee · June 8, 2012, 3:17am

Oops! (I knew that!)

wanna see the COALESCE ?

Sure!

Debbie

Jeff_Mott · June 8, 2012, 5:03am

If you did everything in MySQL and then switched to Oracle you’d feel the pain.

Then again, if you stored everything in PHP and switched to .Net the same thing would happen?

Especially considering that not long ago you were just discovering how functions work, your analysis is very astute.

Given the choice to put your business logic in the DB or in PHP, I think neither is inherently better or worse than the other. My only advice is to be consistent. Try to mix-and-max as little as possible.

But I guess there is no reason why I can’t just go update all of my queries in my web pages to be like this…

SELECT username, online_status, photo_name, photo_label, photo_approved, first_name, location
FROM member
WHERE id=?;

…so there is only ONE Database Call, and then my PHP Function just does the swapping of the “photo_name” as needed, right?

It’s great that you answered your own question.

I have just one comment. When you said “update all of my queries,” that set off alarm bells in my head. That probably means you should refactor to avoid repeating the same code. Maybe a function like getMemberById to write the query once and call it when you need it.

function validatePhoto($dbc, $photoName){

…
// ********************
// Return Photo name. *
// ********************
if ($photoApproved==1){
// Approved Photo.
$safeImage = $photoName;
// return $photoName;
}else{
// Pending Photo.
$safeImage = “PhotoPendingApproval_100x76.png”;
}
…

I’d actually suggest moving this kind of logic to the templating layer. The business logic only needs to answer: Is the photo approved? Then the templating layer gets to decide how to render that information – maybe a simple message, maybe a popup notification, or maybe, like this case, a dummy image. But whichever you choose, it’s still a templating decision.

r937 · June 8, 2012, 5:23am

assume the photos are in a separate table, members are allowed more than one photo, and you want to retrieve the photo that the member has marked as primary


SELECT member.username
     , member.online_status
     , COALESCE(photos.photo_name
            , 'photo_pending_review.jpg') AS photo
     , COALESCE(photos.photo_label
            , 'Pending') AS photo_label
     , member.first_name
     , member.location
  FROM member
LEFT OUTER
  JOIN photos
    ON photos.member_id = member.id
   AND photos.primary = 1
   AND photos.photo_approved = 1
 WHERE member.id = ?

r937 · June 8, 2012, 5:26am

this is good advice, except that certain business rules are inherently better in the database (see post #4) and should never be done with php