Fetch data from MySQL using OOP

Question

I'm a beginner in OOP PHP. I'm trying to make a class that will connect,query and fetch data I done the below coding

class MySQL {
 private $set_host;
 private $set_username;
 private $set_password;
 private $set_database;
 public function __Construct($set_host, $set_username, $set_password){
 $this->host = $set_host;
 $this->username = $set_username;
 $this->password = $set_password;
 $con= mysql_connect($this->host, $this->username, $this->password);
 if(!$con){ die("Couldn't connect"); }
 }
 public function Database($set_database)
 { 
 $this->database=$set_database;
 mysql_select_db($this->database)or die("cannot select Dataabase");
 }
 public function Fetch($set_table_name){
 $this->table_name=$set_table_name;
 $query=mysql_query("SELECT * FROM ".$this->table_name); 
 $result= mysql_fetch_array($query);
 }
}
$connect = new MySQL('localhost','root','');
$connect->Database('cms');
$connect->Fetch('posts');

what I'm trying to achieve is this

$connect = new MySQL('localhost','root','');
$connect->Database('cms');
$connect->Fetch('posts');

and I want to fetch the data using a format like this

echo $result[0];

but I'm not getting that logic to make this happen please help

Thanks!

• maaudet: $this is used to access the object's variables, not local variables. You should also give a look at MySQLi and PDO.
• JohnP: You shouldn't die() in your class. Better to throw an exception upward
• prodigitalson: +1 for Manhim's comment about PDO and Mysqli. Start with these and then extend them instead of constructing something around the old mysql extension. In fact unless you have to support an old version of PHP, or need to work around legacy code you shouldnt even be using the mysql extension.

Answer 1

Your Fetch function is only pulling one row from the database, and you aren't returning the results...

The method isn't the best, but in order to achieve what you're trying to do:

public function Fetch($set_table_name){
 $query=mysql_query("SELECT * FROM ".$set_table_name); 
 $result = array();
 while ($record = mysql_fetch_array($query)) {
 $result[] = $record;
 }
 return $result;
}

This will make each row a part of $result, but you'll have to access it like this:

You would call the fetch function like this:

$result = $connect->Fetch('posts');
echo $result[0]['columnName']; // for row 0;

Or in a loop:

for ($x = 0; $x < count($result); $x++) {
 echo $result[$x][0] . "<BR>"; // outputs the first column from every row
}

That said, fetching the entire result set into memory is not a great idea (some would say it's a very bad idea.)

Edit: It also looks like you have other issues with your class... I have to run but will check back tomorrow and if others haven't set you straight I will expand.

Edit2: Ok, going to do a once-over on your class and try to explain a few things:

class MySQL {
 //declaring the private variables that you will access through $this->variable;
 private $host; 
 private $username;
 private $password;
 private $database;
 private $conn; // Adding the connection, more on this later.
 public function __Construct($set_host, $set_username, $set_password){
 $this->host = $set_host;
 $this->username = $set_username;
 $this->password = $set_password;
 // Combining the connection & connection check using 'or'
 // Notice that I removed the semi-colon after the mysql_connect function
 $this->conn = mysql_connect($this->host, $this->username, $this->password)
 or die("Couldn't connect");
 }
 public function Database($set_database)
 { 
 $this->database=$set_database;
 // Adding the connection to the function allows you to have multiple 
 // different connections at the same time. Without it, it would use
 // the most recent connection.
 mysql_select_db($this->database, $this->conn) or die("cannot select Dataabase");
 }
 public function Fetch($table_name){
 // Adding the connection to the function and return the result object instead
 return mysql_query("SELECT * FROM ".$table_name, $this->conn); 
 }
}
$connect = new MySQL('localhost','root','');
$connect->Database('cms');
$posts = $connect->Fetch('posts');
if ($posts && mysql_num_rows($posts) > 0) {
 echo "Here is some post data:<BR>";
 while ($record = mysql_fetch_array($posts)) {
 echo $record[0]; // or a quoted string column name instead of numerical index.
 }
} else {
 echo "No posts!";
}

I hope this helps... It should get you started at least. If you have more questions you should ask them separately.

• Mixin: Thanks a lot man.this is what I'm looking for. Thanks a lot :) +100Likes :)
• Mixin: I have a lot similar kind of questions to ask and how much do I need to give you to answer my ridiculous crazy question? I don't want to annoy you ,that's why :)
• prodigitalson: @asker: ITs great that this is an snwer to your question and you should give Fosco an upvote and the answer points (click the check mark graphic) but PLEASE look into using Mysqli or PDO for what you are trying to do. See My and Manhim's comments to your question.
• Mixin: I'm new here and it says "Vote up requires 15 reputation" :(
• prodigitalson: @Fosco: If he is really trying to learn OOP you should teach him how to throw/try/catch instead of using the die statements in the class :-)

Answer 2

That's because $result is set inside the Fetch function. It won't be available outside the Fetch function.

Instead of this line $result= mysql_fetch_array($query);, you'll want something like return mysql_fetch_array($query);.

And then in your code

$row = $connect->Fetch('posts');
// do something with $row

On another note, your Fetch function only applies to database queries that return one row. You'll have to separate the mysql_query & mysql_fetch_array calls into another function if you want to loop through rows in a query result.

And on another note, you shouldn't need to encapsulate this code into a class. PHP already provides OOP-based database classes called PDO, or PHP Data Objects. There's a learning curve to it, but it's best practice, and will help secure your code against things like SQL injection.

This is a pretty good tutorial: http://www.giantflyingsaucer.com/blog/?p=2478

Answer 3

I would make a function that returns the value of the object.

public function returnData($data){
 return $this->$data;
}

Then within the page you wish to get the data on just initiate the class and call the function within that class.

$post->returnDate("row name here");

Answer 4

<?php 
 //database.php 
 class Databases{ 
 public $con; 
 public function __construct() 
 { 
 $this->con = mysqli_connect("localhost", "root", "", "giit"); 
 if(!$this->con) 
 { 
 echo 'Database Connection Error ' . mysqli_connect_error($this->con); 
 } 
 } 
 public function insert($table_name, $data) 
 { 
 $string = "INSERT INTO ".$table_name." ("; 
 $string .= implode(",", array_keys($data)) . ') VALUES ('; 
 $string .= "'" . implode("','", array_values($data)) . "')"; 
 if(mysqli_query($this->con, $string)) 
 { 
 return true; 
 } 
 else 
 { 
 echo mysqli_error($this->con); 
 } 
 } 
 public function selectmulti($selecttype,$table_name) 
 { 
 $array = array(); 
 $query = "SELECT ".$selecttype." FROM ".$table_name.""; 
 $result = mysqli_query($this->con, $query); 
 while($row = mysqli_fetch_assoc($result)) 
 { 
 $array[] = $row; 
 } 
 return $array; 
 } 
 public function selectsingle($selecttype,$table_name) 
 { 
 $query = "SELECT ".$selecttype." FROM ".$table_name.""; 
 $result = mysqli_query($this->con, $query); 
 $row = mysqli_fetch_assoc($result);
 return $row; 
 } 
 } 
 ?> 
<?php
$data = new Databases; 
$post_data = $data->selectmulti('*','centerdetail'); 
$n = 1;
foreach($post_data as $post) 
{ 
 echo $n.'.'.$post['CenterCode'].'___';
 $n += 1;
}
echo '<br>';
$post_data = $data->selectsingle('*','centerdetail'); 
echo $post_data['CenterCode'];
?>