SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Business Logic inside a Query

    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...

    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Is it okay to stick that "business logic" into my MySQL Query
    yes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes

    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Well, I have some hot-head PHP Developers that say I am flat out wrong for doing that...
    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


    Quote Originally Posted by DoubleDee View Post
    I suppose this comes down to the whole debate of where you store "Business Logic".
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    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.

    Quote Originally Posted by DoubleDee
    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?
    Using a switch(conditional) as shown isn't going to have any noticeable affect on query performance.
    The only code I hate more than my own is everyone else's.

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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...

    Code SQL:
    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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    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."
    Code:
    SELECT username
         , online_status
         , CASE WHEN photo_approved -- true
                THEN photo_name
                ELSE 'photo_pending_review.jpg'
            END AS photo
         , 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    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.
    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...

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


    And then my PHP Function is like this...
    PHP Code:
        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

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by DoubleDee
    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?
    Correct. When photo has not been approved merely change the photo in php, simple.
    The only code I hate more than my own is everyone else's.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    debbie, did you miss post #7 or ... ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    debbie, did you miss post #7 or ... ?
    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

  12. #12
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT username
         , online_status
         , CASE WHEN photo_approved -- true
                THEN photo_name
                ELSE 'photo_pending_review.jpg'
            END AS photo
         , 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

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    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.
    The only code I hate more than my own is everyone else's.

  14. #14
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    Nope just standard SQL. It will work on both MySQL and Oracle.
    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

  15. #15
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT username
         , online_status
         , CASE WHEN photo_approved -- true
                THEN photo_name
                ELSE 'photo_pending_review.jpg'
            END AS photo
         , 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!!

    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

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    So it is looking like this thread was for not, other than I learned something new in SQL!!
    not naught

    wanna see the COALESCE ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    not naught
    Oops! (I knew that!)


    wanna see the COALESCE ?
    Sure!


    Debbie

  18. #18
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,311
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)
    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.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    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 = ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    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.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •