cannot use prepared statement

[jimfog]

If the rest of the code that's in the function depends on the result of the prepared statement then you should return. If it is not dependent then it's better not to leave the function so that the program can run as normally as possible.

What is interesting to note here is that when the prepare statement fails(and thus producing an error) the function execution stops anyway....so a return statement might not needed anyway....of course I do not know why this happens...here is the function:

function insert_appnt($connection,$name,$start,$end,$origin,$staff,$services,$email)  {          //μηπως στα παρακάτω queries να γίνει χρήση transaction     $connection->set_charset("utf8");     $result2=$connection->query('select crID from business_users,users where users.user_ID=business_users.crID and users.email="'.$email.'"');     if(!$result2)     {echo 'Aδυναμία σύνδεσης με την βάση.';     return false;}     elseif($result2->num_rows>0)     {         $crID=$result2->fetch_object();         $bookedfor= $crID->crID;     }            $staf=(is_array($staff)==true)?$staff[0]:$staff;      //WHEN THE BELOW STATEMENT FAILS..THE FUNCTION DOES NOT PROGRESS.        if ($stmt = $connection->prepare('INSERT into  appointments (Bookfrom,startDate,endDate,apps_origin,staffID,bookedfor) VALUES( ?, ?, ?, ?, ?, ? )'))        {             $stmt->bind_param('siisii',$name,$start,$end,$origin,$staf,$bookedfor);             $stmt->execute();             if($stmt->errno!==0)             {printf("Error-execution failed : %s.n", $stmt->error);}             $stmt->close();        }        else        {printf("Error-prepare statement failed: %s.n", $connection->error);}              $lastid=$connection->insert_id;                  if (is_array($services)==true)         {         //2ND PREPARED STATEMENT             foreach ($services as $value) {          if ($stmt = $connection->prepare('INSERT into  appoint_servi_chosen (app_ID,service_ID) VALUES( ?, ? )'))             {             $stmt->bind_param('ii',$lastid,$value);               $stmt->execute();             if($stmt->errno!==0)             {printf("Error-execution failed appoint_servi_chosen table: %s.n", $stmt->error);}             $stmt->close();             }             else             {printf("Error-prepare statement failed appoint_servi_chosen table: %s.n", $connection->error);}            }                }         else         {             $result1=$connection->query('insert into appoint_servi_chosen(app_ID,service_ID)              values("'.$lastid.'","'.$services.'")');         }         {return $lastid;}        }

I have made a comment about which prepared statement I make it to deliberately to fail....

If you look at the code the second prepared statement depends on the first but as I said execution is halted anyway when the first prepare statement produces an error.

[Ingolme]

I'm not sure, but I think you're referring to the fact that PHP is showing an error message on the screen. That doesn't necessarily mean that the program halted, the program continues running unless the error message is labeled "Fatal error".

 

Which statement is throwing the PHP error? I can't find any information in the PHP manual that any of the mysqli functions throw an error.

[jimfog]

Yes...you are right program execution continues...and in this case since the 2nd prepared statement is depended on the 1st then I should use

return.

 

The question of course is where should I use it?I do not think of course this is something to difficult to implement.

I just wanted your comment though...thanks.

[Ingolme]

Return from the function when something fails, returning false or -1 is a good way to let the person who called the function know that it failed. -1 is probably better in this case because the person that's calling the function expects an integer.

 

If you're getting a PHP error message, what does the message say?

 

 

Unrelated to the topic:

You have an efficiency problem on this line:

foreach ($services as $value) {if ($stmt = $connection->prepare('INSERT into appoint_servi_chosen (app_ID,service_ID) VALUES( ?, ? )'))

You only need to prepare a statement once, if you prepare the very same statement every time you want to use it you're missing out on one of its main advantages. Prepare it outside the loop, then execute it as often as you want to inside the loop, like this:

if ($stmt = $connection->prepare('INSERT into  appoint_servi_chosen (app_ID,service_ID) VALUES( ?, ? )')) {    // If the statement was prepared properly, execute it as many times as needed    foreach ($services as $value) {        $stmt->bind_param('ii',$lastid,$value);          $stmt->execute();        if($stmt->errno!==0)        {printf("Error-execution failed appoint_servi_chosen table: %s.n", $stmt->error);}        $stmt->close();    }} else {    // Show an error    printf("Error-prepare statement failed appoint_servi_chosen table: %s.n", $connection->error);    // We can show the person that's calling the function that it failed by returning -1 instead of $lastid    return -1;}

Lastly, I'm not sure why this is wrapped in curly braces:

{return $lastid;}

Some languages let you create a local scope by wrapping code in curly braces, but PHP doesn't behave like that. In the best case, PHP ignores then, in the worst case it will consider it a syntax error. I don't know because I've never attempted this.

[jimfog]

regarding the curly braces...you are right...they are not needed.

 

Regarding the code you gave that addresses the efficiency problem...I get many warnings with this content:

 

Couldn't fetch mysqli_stmt...

 

The above warning is displayed both on the line that has the bind_param command both and also the line that has the execute command...probably this has to do with the fact that the prepared statement is outside the loop,so there may be a problem with the code you gave.

 

The error I am getting is an error I am causing deliberately...and this depends on what I want to test.

 

As such...I cannot understand how is this going to help our discussion.

[Ingolme]

The reason you're getting that error is probably because you're calling $stmt->close() inside the loop. I didn't notice that at first, you can take it out of the loop.

 

The reason why I'm concerned with the error message is that PHP shouldn't necessarily show a verbal error message when the statement fails, you should just detect the error by looking at the statement's error code. What's the point in testing ($stmt->errno != 0) if PHP throws an error anyways?

[jimfog]

First of all...you were right about $stmt->close() ...it had to be outside the loop.

 

Regarding the error message.

If for example there is a mismatch between type definition string and bind variables I get this:

<b>Warning</b>: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables in <b>C:Apache24htdocsAppointmentsAdministratoradmin_db_code.php</b> on line <b>91</b><br />

Error-execution failed appoint_servi_chosen table: No data supplied for parameters in prepared statement

[thescientist]

Sounds like your count is off. Are you sure the number of parameters your binding matches the number of parameters you are preparing?

[Ingolme]

Sounds like your count is off. Are you sure the number of parameters your binding matches the number of parameters you are preparing?

 

The error was done deliberately to test the program.

 

It does seem like the $stmt->errno part is working properly.

[jimfog]

Ι Have 2 last question...the first is towards igolme.

 

1. Why not use return also here

    if($stmt->errno!==0)        {printf("Error-execution failed appoint_servi_chosen table: %s.n", $stmt->error);}

2. I suppose when going into production this error must be written into a log.I know the issue is huge but can give me some jumpstart by telling me what the first step could be achieving this.