Mysqli_stmt_num_rows and foreach


#1

Hi i've got the following code:

$user_id = 30;
// Array of ids
$id_gruppo= user_group_session($conn, $user_id);


    $query_last_tickets = mysqli_prepare($conn, "SELECT ticket_id, ticket_subject, ticket_body, ticket_time_stamp, ticket_status, ticket_user_id, ticket_group FROM user_tickets WHERE ticket_group = ? ORDER BY ticket_time_stamp DESC LIMIT 5");   
    mysqli_stmt_bind_param($query_last_tickets, 'i',  $data);

    foreach ($id_gruppo as $data) {

    	        mysqli_stmt_execute($query_last_tickets);
                mysqli_stmt_bind_result($query_last_tickets, $ticket_id, $ticketsubject, $ticketbody, $ticketdate, $ticketstatus, $ticketuserid, $ticket_group);
                mysqli_stmt_store_result($query_last_tickets);
                $numTicket = mysqli_stmt_num_rows($query_last_tickets);

echo $numTicket;


		             if ($numTicket > 0) {


					        while (mysqli_stmt_fetch($query_last_tickets)) {

					        	   echo $ticket_id;

					        }
		         
		    		}	

    }


    if ($numTicket < 1) {

    	echo "no rows found";

   	}

If i do echo $numTicket i get 2 and 0 beacuse the $id_gruppo array has got two values and for one of them i've got two rows and for the other i've got zero rows, as result the echo "no rows found" is shown. How can i fix this problem?

Many thanks for your help


#2

Can you expand on what the problem is? If you've got two group IDs, one has two rows and the other has zero, then it seems as if the code is behaving correctly. What do you want it to do that it does not, or not do that it does?


#3

As the saying goes: Never print inside a function, always return.

Don't echo at this stage. Rather collect all the data and then check if there is no result. you could also use the MySQL IN() clause to get all data with a single query, although in raw mysqli that is a bit of a PITA to set up.


#4

Hi thanks for your help i've attached a screen shot to better explain what is happening


#5

A really stupid fix were to reverse the ID array to have the last ID returning something. But that's likely to break with a new set of IDs.


#6

Uhm I'm not sure it will fix the problem, do you have any other ideas?


#7

sure, see post #2.


#8

Hi @Dormilich as you can see from the screen shot even if there is a result shown it still show the message no data to show and that is because it looks like in the foreach loop i've got two num_rows results you can see 1 and 0 and that is why is showing the message


#9

I gave you an explanation why and how to fix that already in post #2.


#10

Hi thanks sorry I thought post #2 was the one left by droopsnoot. I've tried your solution but still with no success


#11

So what did you try?


#12

The piece of code that decides whether to show that message occurs outside of your foreach loop, so any time the last row retrieved had a value less than one, the message will be shown. Surely if you don't want that message to appear, just remove it from the code? Or am I missing a bigger picture?


#13

Hi i've sorted it out in this way:

$user_id = 30;
// Array of ids
$id_gruppo= user_group_session($conn, $user_id);

$query_last_tickets = mysqli_prepare($conn, "SELECT ticket_id, ticket_subject, ticket_body, ticket_time_stamp, ticket_status, ticket_user_id, ticket_group FROM user_tickets WHERE ticket_group = ? ORDER BY ticket_time_stamp DESC LIMIT 5");   
    mysqli_stmt_bind_param($query_last_tickets, 'i',  $data);
$have_tickets = false ;
foreach ($id_gruppo as $data) {

    mysqli_stmt_execute($query_last_tickets);
    mysqli_stmt_bind_result($query_last_tickets, $ticket_id,      $ticketsubject, $ticketbody, $ticketdate, $ticketstatus, $ticketuserid, $ticket_group);
    mysqli_stmt_store_result($query_last_tickets);
    $numTicket = mysqli_stmt_num_rows($query_last_tickets);

    if ($numTicket > 0) {
        $have_tickets = true;
        while (mysqli_stmt_fetch($query_last_tickets)) {

            echo $ticket_id;

        }

    }   

}

if (!$have_tickets) {
    echo "no rows found";
}

closed #14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.