Tikkie Posted January 24, 2014 Share Posted January 24, 2014 I've just started with classes and mysql with PDO but having trouble to understand it correctly. I established a working connection in connection.php with the following code: class dbConnect { public function connect(){ try{ $conn = new PDO ("mysql:host=localhost;dbname=database", 'root', ''); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { echo 'ERROR: ' . $e->getMessage(); } } } Now what I want to do is create a function in functions.php to execute query's, but this is not working: require_once ('connection.php'); function getUserId($name){ $query = $conn->prepare('SELECT id FROM users WHERE name = :name'); $query->bindParam(':name', $name); $query->execute(); while($row = $query->fetch()) { echo($row[0]);} } It gives me the following errors: Notice: Undefined variable: conn in C:\xampp\htdocs\d\functions.php on line 8Fatal error: Call to a member function prepare() on a non-object in C:\xampp\htdocs\d\functions.php on line 8 I know i can't call variables from functions unless if it's a global variable. I tried so many things but nothing seems to help me. Can somebody give me a simple solution? Quote Link to comment https://forums.phpfreaks.com/topic/285655-pdo-query/ Share on other sites More sharing options...
kicken Posted January 24, 2014 Share Posted January 24, 2014 You need to pass the connection into the function when you call it as a parameter. If you currently only define $conn within your class as shown, then you'll need to change that so you can get a reference to it from outside the class. For example, make your connect method return $conn. function getUserId($conn, $name){ ... } $db = new dbConnect(); $conn = $db->connect(); $id = getUserId($conn, 'blah'); Quote Link to comment https://forums.phpfreaks.com/topic/285655-pdo-query/#findComment-1466451 Share on other sites More sharing options...
objnoob Posted January 25, 2014 Share Posted January 25, 2014 class dbConnection { private $_conn = null; // the connection public $error = null; // the last exception caught public function connect(){ try{ $this->_conn = new PDO ("mysql:host=localhost;dbname=database", 'root', ''); $this->_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); return $this->_conn; // connection made! return connection }catch(PDOException $ex) { /* caught PDOException on new PDO()! to avoid possibly displaying your mysql password you will construct your own PDOException with a safe message....... to store in $this->error */ $this->error = new PDOException('Couldn\'t connect to database!', $ex->getCode() ); return null; // connection failed! return null } } public function getConnection(){ return $this->_conn; } } $db = new dbConnection(); if( ! $conn = $db->connect() ){ echo $db->error->getMessage(); exit; } Quote Link to comment https://forums.phpfreaks.com/topic/285655-pdo-query/#findComment-1466455 Share on other sites More sharing options...
Tikkie Posted January 25, 2014 Author Share Posted January 25, 2014 Thanks for your answers. The best result I can get at this moment is when I put the query in de connection.php. I can't get this working in the functions, always get an error on ->prepare Now my connection.php looks like this: <?php class dbConnect { public function connect($id, $column){ try{ $pdo = new PDO ("mysql:host=localhost;dbname=database", 'root', ''); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $query = $pdo->prepare('SELECT * FROM users WHERE id = :id'); $query->bindParam(':id', $id); $query->execute(); while($row = $query->fetch()) { echo ($row[$column]);} } catch(PDOException $e) { echo 'ERROR: ' . $e->getMessage(); } } } ?> and functions.php looks like this: <?php require_once ('connection.php'); function getUserName($id, $column){ $pdo = new dbConnect(); $pdo->connect($id, $column); } $id = '1'; $column = 'username'; getUserName($id, $column); function getFullName($id, $column){ $pdo = new dbConnect(); $pdo->connect($id, $column); } $id = '1'; $column = 'fullname'; getUserName($id, $column); ?> It makes me able to create functions for returning every seperate column. Is this the best way to do it? I think it also would be better if I just query on the column, so user "username" in stead of *. But when I replace the * for :column and bindParam :column it only shows me "username" of "fullname" in stead of my name. Quote Link to comment https://forums.phpfreaks.com/topic/285655-pdo-query/#findComment-1466495 Share on other sites More sharing options...
mac_gyver Posted January 25, 2014 Share Posted January 25, 2014 Is this the best way to do it? no. the code you posted last is NOT a proper way of making and using a database connection in your application or of writing a class. a database connection is something your application is dependent on and is used multiple times throughout the code on any one page. your application should form one database connection, then use that one connection everywhere it is needed. you should not be forming a database connection inside each function, just to run the code inside that function, then to have php (automatically) close that connection when the function ends/returns. the first reply in this thread showed you an example of passing the variable holding the database connection into your function. your first goal would be to assign the $conn variable in your main code with an instance of the pdo class. pdo is already a class, wrapping your own class definition around it, unless your class adds something useful to the pdo class, doesn't accomplish anything. hard-coding your database connection details inside of a class definition is also not good coding. also, by wrapping your class definition around the pdo class, you must now write your own methods in that class to handle every pdo method you call. it is much better to have your class extend the pdo class, provided your class is going to add something useful to the pdo class at all, so that you can directly use the pdo methods in your code. if your class doesn't add anything useful to the pdo class, you might as well just use the pdo class directly without wrapping your own class around it. a useful thing you can add to the pdo class, would be an arbitrary prepared query method that takes the query statement, any optional bound input parameters, runs the query with error handling, then returns the result set (select/show queries) or number of affected rows (insert/update/delete queries) or perhaps just returns the pdo PDOStatement object to the calling code so that it can test/use the result from the query. lastly, your list of functions appear to be related to things having to do with a user. wouldn't writing a user class that contains methods that perform the getUserName, getFullName, ... operations be something to do for learning how to write and use classes? in this case, you would pass the instance of your database class/pdo into an instance of a user class using dependency injection. Quote Link to comment https://forums.phpfreaks.com/topic/285655-pdo-query/#findComment-1466521 Share on other sites More sharing options...
Tikkie Posted January 26, 2014 Author Share Posted January 26, 2014 Thanks for your answer. At this moment i'm very confused and having many questions. First I will read some more about classes and PDO to understand what the best solution is to put multiple query's in my code. Quote Link to comment https://forums.phpfreaks.com/topic/285655-pdo-query/#findComment-1466595 Share on other sites More sharing options...
ginerjm Posted January 26, 2014 Share Posted January 26, 2014 pdo is already a class, wrapping your own class definition around it, unless your class adds something useful to the pdo class, doesn't accomplish anything. hard-coding your database connection details inside of a class definition is also not good coding. At Last! Somebody has come forth and said what I was thinking every time some 'noob' (and others) posted their own idea of how to utilize the PDO interface. Classes, definitions & functions all written to simply run a couple of php functions to get some data. What a colossal waste of energy. Quote Link to comment https://forums.phpfreaks.com/topic/285655-pdo-query/#findComment-1466608 Share on other sites More sharing options...
objnoob Posted January 26, 2014 Share Posted January 26, 2014 At Last! Somebody has come forth and said what I was thinking every time some 'noob' (and others) posted their own idea of how to utilize the PDO interface. Classes, definitions & functions all written to simply run a couple of php functions to get some data. What a colossal waste of energy. It's quite obvious the OP is learning...... ideas will be conjured and dispelled. mistakes will be made and corrected. the learning process is full of trials and tribulations. A better understanding of OOP and the PDO class. Such a colossal waste, right? Lighten up, man. I can list 10 reasons why wrapping PDO is a great idea. I'd love to hear 10 to the contrary. Quote Link to comment https://forums.phpfreaks.com/topic/285655-pdo-query/#findComment-1466622 Share on other sites More sharing options...
ginerjm Posted January 26, 2014 Share Posted January 26, 2014 objnoob: You pick on my post when I was only expressing my agreement with the original sentiment expressed by the guru mac_gyver??? My post explicitly referenced multiple occurrences of this action, not just this particular individual. Additionally, I don't know what you are referring to with: "A better understanding of OOP and the PDO class." - it is not anything I posted and was in no way the target of my post. Frankly, I think you should lighten up and be sure you are flaming the right poster Quote Link to comment https://forums.phpfreaks.com/topic/285655-pdo-query/#findComment-1466624 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.