Check value of a field?

I have a very simple query, i.e.


	  $b = "SELECT profiel_id, bericht, operator_id
	          FROM profiel_berichten
			 ORDER
			    BY profiel_id";

Before I execute the query:


	  $ber = $pdo->prepare($b);
	  $ber->execute();

I need to check if the value of $row->operator_id is GT 0. Because if that is the case I need to add a WHERE statement to the query, i.e.


       WHERE operator_id = $_SESSION[user_id]

I tried it the following way:


	  $b = "SELECT profiel_id, bericht, operator_id 
	          FROM profiel_berichten
			 ORDER
			    BY profiel_id";
				
	  $berichten = $pdo->prepare($b);	  
	  $berichten->execute(); 				
				
      $row = $berichten->fetch();
	  if ($row['operator_id'] > 0){
	      $b .= "WHERE operator_id = $_SESSION[user_id] ";  
	  }; 
	  
	  while($row = $berichten->fetch()) {
		   echo $row['bericht'];
	   }

But that doesn’t give me the right result

You’re changing the query, but not executing it again, so the second fetch() will just retrieve the next row from the original query.

So I need to add


$berichten = $pdo->prepare($b);      
 $berichten->execute(); 

again after:


 $row = $berichten->fetch();
      if ($row['operator_id'] > 0){
          $b .= "WHERE operator_id = $_SESSION[user_id] ";  
      }; 
      $berichten = $pdo->prepare($b);      
      $berichten->execute(); 

?

I would think that if the only way you can decide whether that clause needs adding is by checking the first row that comes from your first query, then you have no option but to run the updated query afterwards. I’m not entirely certain whether you need to call CloseCursor() on the first query before you run the second one.

Hi droopsnoot. Thanks for the replies. I am completely new to PDO, and have really no clue how it should look like because what I asked/thought in post #4 is not working. Hope you can help me out with this

Can you expand on “not working”?

Two problems I can see:

  1. When you add the WHERE clause, it doesn’t look as if you start the string with a space (but it might just be the screen making it hard to see) so your query ends “ORDER BY profile_idWHERE operator_id=1” not “ORDER BY profile_id WHERE operator_id=1”.

  2. On my MySQL, I have to put the WHERE before the ORDER BY clause, otherwise it throws an SQL error. If you type in your query in phpmyadmin or whatever you use, does it work?

Hi droopsnoot, thank you again for the reply :tup: I know that the ORDER BY clause should come under the Where clause, and that is exact what my problem is because in PHP you can’t use a IF statement within a query. Before I used Coldfusion and there you could use an IF statement in your queries:


    SELECT profiel_id,
           operator_id,
           bericht 
      FROM profiel_berichten
    <cfif admin_id NEQ 0>
     WHERE operator_id = SESSION.user_id
    </cfif>
     ORDER
        BY profiel_id LIMIT 1

In other words. If the the WHERE clause was executed the ORDER clause would be under the WHERE clause if the WHERE clause was not executed the query would still give me results. So how do I accomplish the same in PHP?

Thank you in advance.


      $b1 = "SELECT profiel_id, bericht, operator_id
              FROM profiel_berichten";
      $b2 = " ORDER BY profiel_id";

      $berichten = $pdo->prepare($b1 . $b2);
      $berichten->execute();

      $row = $berichten->fetch();
      if ($row['operator_id'] > 0){
          $berichten->CloseCursor();   // might not need this
          $berichten = $pdo->prepare($b1 . " WHERE operator_id = $_SESSION[user_id] " . $b2);
          $berichten->execute();
      };

      while($row = $berichten->fetch()) {
           echo $row['bericht'];
       }

Maybe there’s a better way of doing it directly in the query, but I can’t get my head around how you’d compare the value of a field returned in the first row of the query to decide how the query is structured.

In your CF example above, where is the admin_id variable coming from? It’s not one of the vars you select in your basic query, does CF automatically add it in if you’ve used it as a conditional?

Hi droopsnoot. Thank you very much for the reply, It is highly appreciated. :tup: I am gonna give it a try right away.

For the CF example. That was/is a typo. It should have been:


    <cfif admin_id NEQ 0>
     WHERE operator_id = SESSION.admin_id
    </cfif>

Sorry for the confusion :frowning:

Yes, but where does the admin_id in your cfif clause come from? This one:


<cfif admin_id NEQ 0>

Sorry again should have been:


    <cfif operator_id NEQ 0>
     WHERE operator_id = SESSION.user_id
    </cfif>

I have been looking at it to long I guess :frowning:

Well, me too, and I don’t know Coldfusion so I’m only asking out of interest. I still can’t imagine why you’d want to alter the query based on the operator-id that comes out of the first row returned by the query - I could understand if you only wanted to add the WHERE clause if your $_SESSION[‘user_id’] was non-zero. But then I don’t know your database or what it’s doing.

Hi droopsnoot… Ok let me try to explain it even better! Let say it is an ongoing conversation between the profile (profiel_id) and the staff (lets say 10 messages in total) Then when the profile send a new message, then first I would like to check who was/is the operator who answered the last question/suggestion from that profile. Lets say that was the operator with operator_id = 10. Then I would like to check if that particular operator is online. If that is the case send the conversation to that operator. If that operator is not online send the conversation to any available operator.

Does this make any sence?