PDO query

[Tikkie]

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 8
Fatal 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?

[kicken]

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');

[objnoob]

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;

}

[Tikkie]

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.

[mac_gyver]

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.

[Tikkie]

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.

[ginerjm]

 

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.

[objnoob]

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.

[ginerjm]

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