Validate SQL row result in php

Question

Hello I was trying to do this...

When my query returns 0, 1 or 2 do different things.

    SELECT status FROM event_auth WHERE perm = true AND id_event = 1

and it returns

   status 
  --------
     0
     0
     2

then I did it

 SELECT status FROM event_auth WHERE perm = true AND id_event = 1 GROUP BY status

   status 
  --------
     0
     2

It might be more than 3 rows, I just want to get those rows. I want to check this... Php Code... (explaning)

    if ( (rows == 1) == 1){
     // do something
     } 
    if ( (rows => 1) == 0){
     // do something
     } 
    if ( rows > 1 ) == 2{
     //do something
     }
    if 

I have an authorize movements module where

  0 = pending,
  1 = accepted,
  2 = rejected.

if all my authorizer are in 0 or one of them is 0, don't do anything.

if one of them put 2, reject it.

if all them put 1, do something... (change an status in another table)

Exist something that I can do ?

Thank you.

Answer 1

Here is some tested code that i think does what you require.

Change the $eventId variable to test it.

There are some changes:

The query returns the counts of the different 'status'es, makes it easier when testing for failure / acceptance.

It displays the reason why it decided what happens.

Here is the output using your supplied test data:

boolean false
string 'This event is Rejected: 1 : counts: 1' (length=37)
string 'P:\developer\xampp\htdocs\testmysql\index.php' (length=45)

The code:

<?php
   // assume that the STATUS column MUST be one of these values
   define('PENDING',  0);
   define('ACCEPTED', 1);
   define('REJECTED', 2);

   // connect to the database
   $dsn = 'mysql:host=localhost;dbname=testmysql';
   $username = 'test';
   $password = 'test';
   $options = array(
   PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
   );
   $db = new PDO($dsn, $username, $password, $options);
   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


   // testing -- change this id to ensure it works
   $eventId = 2;
   // -------------

   // return the counts for each status - will make it easier to check later
   $sql =  " SELECT ea.status as `status`, COUNT(ea.status) AS status_count "
          ." FROM event_auth AS ea "
          ." WHERE ea.perm = TRUE AND ea.id_event = :eventId "
          ." GROUP BY ea.status "
          ." ORDER BY ea.status DESC ";

   $stmt = $db->prepare($sql);
   $allOk = $stmt->execute(array(':eventId' => $eventId));

   $statusList = $stmt->fetchAll(PDO::FETCH_ASSOC); // column names only

   // assume that we will NOT accept the event
   $acceptEvent = false;
   $theReason = ''; // why we did what we did ...

   // check the return statuses and counts
   foreach( $statusList as $details ) {
       if (   $details['status'] == REJECTED
           && $details['status_count'] >= 1)  { // will stop the loop
           $theReason = "This event is Rejected: {$eventId} : counts: {$details['status_count']}";
           $acceptEvent = false;
           break;
       }

       if (   $details['status'] == PENDING
            && $details['status_count'] >= 1)  { // will stop the loop
           $theReason = "This event is Pending: {$eventId} : counts: {$details['status_count']}";
           $acceptEvent = false;
           break;
       }

       // this never stops the loop
       if (   $details['status'] == ACCEPTED
           && $details['status_count'] >= 1)  {
           $theReason = "This event is Accepted: {$eventId} : counts: {$details['status_count']}";
           $acceptEvent = true;
       }
   }

   var_dump($acceptEvent, $theReason, __FILE__);