jiros1 Posted July 5, 2016 Share Posted July 5, 2016 Hi, Thanks for taking the time. I'm trying PDO for the first time and I'm trying to make a CRUD codebase. I can insert but I have trouble with the select statement and iterate through the data, Apache gives me this error: Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in index.php:38 This is line 38: $getName = $result; while( $row = $getName->fetch_assoc() ){ echo $row['name']; } This is my code: $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // set the PDO error mode to exception $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $pdo->prepare("SELECT name FROM users"); $stmt->execute(); // set the resulting array to associative $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); $getName = $result; //Iterate through the data // line 38: while( $row = $getName->fetch_assoc() ){ echo $row['name']; } Quote Link to comment https://forums.phpfreaks.com/topic/301429-starting-with-pdo-select-statement/ Share on other sites More sharing options...
requinix Posted July 5, 2016 Share Posted July 5, 2016 You're trying to call fetch_assoc on whatever the setFetchMode function returned. 1. You need to call it on a statement object. 2. You might be confusing PDO with mysqli. PDO does not have a "fetch_assoc". Use one of these instead. 1 Quote Link to comment https://forums.phpfreaks.com/topic/301429-starting-with-pdo-select-statement/#findComment-1534235 Share on other sites More sharing options...
Solution Muddy_Funster Posted July 5, 2016 Solution Share Posted July 5, 2016 (edited) Here's an example PDO Select statement for reference: $con = new PDO('lib:host=host;dbname=dbname','usr','pwd') $sql = "SELECT name FROM user WHERE UID = :uid"; $stmt = $con->prepare($sql); $stmt->bindParam(':uid',$id, PDO::PARAM_INT); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); var_dump($result); There are variants on how to bind parameters and how to fetch results, but this is the simplest way that is closest to what you already tried. I also altered the query to show one way of how you would bind a parameter to a prepared statement. Hope it helps some. Edited July 5, 2016 by Muddy_Funster 1 Quote Link to comment https://forums.phpfreaks.com/topic/301429-starting-with-pdo-select-statement/#findComment-1534236 Share on other sites More sharing options...
Jacques1 Posted July 5, 2016 Share Posted July 5, 2016 You can actually omit the fetch() calls altogether. PDO is rather “clever”: If you simply iterate over the PDOStatement object with a foreach loop, you automatically get the rows as associative arrays. Also note that prepared statements are useless when there's no external input. Simply use a static query: <?php const DB_HOST = '...'; const DB_USER = '...'; const DB_PASSWORD = '...'; const DB_NAME = '...'; const DB_CHARSET = 'UTF8'; $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHARSET; $databaseConnection = new PDO($dsn, DB_USER, DB_PASSWORD, [ PDO::ATTR_EMULATE_PREPARES => false, // important: Disable emulated prepared statements PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, ]); $userStmt = $databaseConnection->query(' SELECT name FROM users '); foreach ($userStmt as $user) { echo 'The name is '.htmlspecialchars($user['name'], ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8').'<br>'; } 1 Quote Link to comment https://forums.phpfreaks.com/topic/301429-starting-with-pdo-select-statement/#findComment-1534237 Share on other sites More sharing options...
jiros1 Posted July 5, 2016 Author Share Posted July 5, 2016 Thanks a lot, I've got it working now! A question though, how come we can do a foreach iteration now? And why doesn't that work for MYSQLi? Quote Link to comment https://forums.phpfreaks.com/topic/301429-starting-with-pdo-select-statement/#findComment-1534240 Share on other sites More sharing options...
Jacques1 Posted July 5, 2016 Share Posted July 5, 2016 A question though, how come we can do a foreach iteration now? A PDOStatement implements the Traversable interface. And why doesn't that work for MYSQLi? It does, at least partially. The mysqli_result class implements Traversable as well. You get a mysqli_result either through the mysqli::query() method or by calling mysqli_stmt::get_result() in the case of a prepared statement (this requires the native MySQL driver, though, so it's not available on all systems). mysqli is a more low-level interface, so it tends to be harder to use. Quote Link to comment https://forums.phpfreaks.com/topic/301429-starting-with-pdo-select-statement/#findComment-1534243 Share on other sites More sharing options...
jiros1 Posted July 6, 2016 Author Share Posted July 6, 2016 Awesome, thanks! A PDOStatement implements the Traversable interface. It does, at least partially. The mysqli_result class implements Traversable as well. You get a mysqli_result either through the mysqli::query() method or by calling mysqli_stmt::get_result() in the case of a prepared statement (this requires the native MySQL driver, though, so it's not available on all systems). mysqli is a more low-level interface, so it tends to be harder to use. Quote Link to comment https://forums.phpfreaks.com/topic/301429-starting-with-pdo-select-statement/#findComment-1534253 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.