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!

$this is used to access the object's variables, not local variables. You should also give a look at MySQLi and PDO. — maaudet
– maaudet, Commented Jun 17, 2011 at 4:18
You shouldn't die() in your class. Better to throw an exception upward — JohnP
– JohnP, Commented Jun 17, 2011 at 4:19
+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. — prodigitalson
– prodigitalson, Commented Jun 17, 2011 at 4:21

Fosco · Accepted Answer · 2011-06-17 13:11:24Z

11

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.

edited Jun 17, 2011 at 13:11

answered Jun 17, 2011 at 4:17

Fosco

38.6k7 gold badges90 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mixin Over a year ago

Thanks a lot man.this is what I'm looking for. Thanks a lot :) +100Likes :)

Mixin Over a year ago

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 Over a year ago

@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 Over a year ago

I'm new here and it says "Vote up requires 15 reputation" :(

prodigitalson Over a year ago

@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 :-)

|

Chris · Accepted Answer · 2011-06-17 04:33:14Z

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

Blake Basas · Accepted Answer · 2013-08-11 18:59:12Z

0

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

answered Aug 11, 2013 at 18:59

Blake Basas

891 silver badge5 bronze badges

Comments

Sahil Jangra · Accepted Answer · 2018-07-16 18:36:20Z

<?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'];

?>

Collectives™ on Stack Overflow

Fetch data from MySQL using OOP

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related