jimfog Posted February 13, 2015 Share Posted February 13, 2015 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? Link to comment Share on other sites More sharing options...
Ingolme Posted February 13, 2015 Share Posted February 13, 2015 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']); Link to comment Share on other sites More sharing options...
jimfog Posted February 13, 2015 Author Share Posted February 13, 2015 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. Link to comment Share on other sites More sharing options...
jimfog Posted March 2, 2015 Author Share Posted March 2, 2015 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. Link to comment Share on other sites More sharing options...
Ingolme Posted March 3, 2015 Share Posted March 3, 2015 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>'; }} Link to comment Share on other sites More sharing options...
jimfog Posted March 3, 2015 Author Share Posted March 3, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
Ingolme Posted March 3, 2015 Share Posted March 3, 2015 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now