prepared statement within a for loop

[jimfog]

I am having trouble implementing a prepared statement within a for loop:

 for($j=0;$j < count($appdata);++$j)        {        $result1 = $connection->query('SELECT serviceID from services_list,appoint_servi_chosen        WHERE services_list.serviceID=appoint_servi_chosen.service_ID        and appoint_servi_chosen.app_ID= "'.$appdata[$j]['apID'].'"');}

As you see above I have to access an array in the statement above...how am I going to implement this with a prepared statement?

[Ingolme]

The same procedure as all prepared statements:

1. Prepare

2. Bind

3. Execute

 

I assume you're using MySQLi, you haven't specified. This code works if $connection is a MySQLi object, if it's PDO the syntax is a bit different.

$stmt = $connection->prepare('SELECT serviceID from services_list,appoint_servi_chosen    WHERE services_list.serviceID=appoint_servi_chosen.service_ID    and appoint_servi_chosen.app_ID=?'); for($j=0;$j < count($appdata);++$j) {    $stmt->bindParam('s', $appdata[$j]['apID']);    $result1 = $stmt->execute();}

Unless you need $j for something, you could just use a foreach() loop

foreach($appdata as $d) {$stmt->bindParam('s', $d['apID']);

[jimfog]

 

The same procedure as all prepared statements:

1. Prepare

2. Bind

3. Execute

 

I assume you're using MySQLi, you haven't specified. This code works if $connection is a MySQLi object, if it's PDO the syntax is a bit different.

$stmt = $connection->prepare('SELECT serviceID from services_list,appoint_servi_chosen    WHERE services_list.serviceID=appoint_servi_chosen.service_ID    and appoint_servi_chosen.app_ID=?'); for($j=0;$j < count($appdata);++$j) {    $stmt->bindParam('s', $appdata[$j]['apID']);    $result1 = $stmt->execute();}

Unless you need $j for something, you could just use a foreach() loop

foreach($appdata as $d) {$stmt->bindParam('s', $d['apID']);

I had no doubt I was going to use these 3 steps you mention...the only problem I had was accessing the array...but you also answered for it.

[jimfog]

the only part that is missing in the code above is the fetch() part...

a while loop specifically that will fetch the data from the database...

 

the question is where the while loop should be placed.

 

Just take a look here http://php.net/manual/en/mysqli-stmt.fetch.php in the first example.

[Ingolme]

The while loop would go inside the other loop. Each statement has its own set of results to go through:

$stmt = $connection->prepare('SELECT serviceID from services_list,appoint_servi_chosen    WHERE services_list.serviceID=appoint_servi_chosen.service_ID    and appoint_servi_chosen.app_ID=?'); foreach($appdata as $d) {    $stmt->bind_param('s', $d['apID']);    $result = $stmt->execute();    if($result) {        // The query was successful        // Make a variable $serviceID for the result        $stmt->bind_result($serviceID);        // Go through all the retrieved rows        while($stmt->fetch()) {            echo $serviceID;        }    } else {        echo 'Error: ' . $stmt->error . '<br>';    }}

[jimfog]

I want to a comment regarding this piece of code:

 $result = $stmt->execute();    if($result) {        // The query was successful        // Make a variable $serviceID for the result        $stmt->bind_result($serviceID);

Instead of the above I use this:

$stmt->execute();         if($stmt->errno!==0)         {printf("Error-execution failed-GET appt_failed: %s.n", $stmt->error);                 return false;        }

And another thing is that I do not see anywhere the code for closing the connection....stmt->close

Edited March 3, 2015 by jimfog

[Ingolme]

The execute() method returns true on success or false on failure. Checking the error number also works, use whichever method you prefer:

http://php.net/manual/en/mysqli-stmt.execute.php

 

It's not required to manually close the connection, it closes automatically when the script is done running. If you plan on preparing a lot of statements it would be a bit better to close one before opening the next one. You would call the close() function after the foreach() loop is finished.