[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

php 5 database access class question

So I brand new to OOPHP.  Been messing with procedural PHP for a bit but not an expert by any strech.

I am writing an internal app at work and since I will have to query the DB a lot I figured creating a db conn class was the way to go.  I found some example of it online and have been able to get a result set.  The problem is I want to use the same class for each DB object which, I think means that I will have to access the result set outside of the class, inline in my script?

At anyrate I am returning the data as an onbject in hope that I could access it it like this.... $stuff->prod_id but that does not work as it only returns the last row of the set.  I know if I were returning an array I could loop through but you can't do that with objects that I know of.

Here is my code.  I should mention that in my product table I have 2 rows each with two values.  prod_id and prod_name.

<?php include("dbconnect.php");


$dbconn = new database();

$dbconn->__construct;

$result=$dbconn->query("select * from product");

$stuff=$dbconn->fetch_rows($result);

mysql_free_result($result);

$dbconn->__destruct;

//echo $stuff;

echo $stuff->prod_id;
?>

../index.php

<?php
class database
{
private $connectlink;      //Database Connection Link
private $username = "removed for security";
private $password = "removed for security";
private $database = "removed for security";
private $hostname = "removed for security";
private $resultlink;      //Database Result Recordset link
private $rows;            //Stores the rows for the resultset
 
public function __construct() {
      $this->connectlink = mysql_connect($this->hostname,$this->username,$this->password);
      if(!($this->connectlink)) {
            //throw new DatabaseConnectionException("Error Connecting to the Database".mysql_error(),"101");
      }
      else {
            mysql_select_db($this->database);
      }
}
 
public function __destruct() {
      @mysql_close($this->connectlink);
}
 
public function query($sql) {
      $this->resultlink = mysql_query($sql);
      return $this->resultlink;
}
 
public function fetch_rows($result) {

            $obj = mysql_fetch_object($result);
            //var_dump($obj);
      
            return $obj;
}
}
?>


../dbconnect.php

0
lharrispv
Asked:
lharrispv
  • 5
  • 4
1 Solution
 
hieloCommented:
>>in hope that I could access it it like this.... $stuff->prod_id that does not work as it only returns the last row of the set
That's why the db classes typically implement a method that allows you to loop through a redordset  -ex:
while( $row = $dbObj->fetch(PDO::ASSOC) )
{
 print_r($row);
}

You are re-inventing the wheel. My suggestion to you is to use PDO. It works well and it supports multiple dbs, including mysql:
http://us.php.net/manual/en/book.pdo.php


0
 
hieloCommented:
>>but that does not work as it only returns the last row of the set
your method:
public function query($sql) {
      $this->resultlink = mysql_query($sql);
      return $this->resultlink;
}

is returning a resource.  Then in your fetch_rows you have:
 $obj = mysql_fetch_object($result);

mysql_fetch_object internally moves the pointer. So to get the next row item you just need to call
 fetch_rows() again. So, from what you posted, without making any changes to it, this is how you would iterate over a complete recordset
$dbconn = new database();
$result=$dbconn->query("select * from product");
if($result)
{
	while($row=$dbconn->fetch_rows($result) ){
		print_r($row);
	}
}

Open in new window

0
 
lharrispvAuthor Commented:
I am still going through the PDO stuff but I tried your second suggestion and that is closer but still not it.  This is what it returns:
stdClass Object ( [prod_id] => 2 [prod_name] => Adaptive Accounts ) stdClass Object ( [prod_id] => 1 [prod_name] => Adaptive Payments )

I want to be able to access each record individually.  Make sense?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
hieloCommented:
>>I want to be able to access each record individually.  Make sense?
Of course, but let's be clear about you are talking about. I suspect what you meant by "record" is a column at a time (as opposed to the entire row/recordset).

instead of print_r( $row )
print( $row->columnName);

ex:
 
$dbconn = new database();
$result=$dbconn->query("select name,description from product");
if($result)
{
        while($row=$dbconn->fetch_rows($result) ){
                print( $row->name . ": ". $row->description);
        }
}

Open in new window

0
 
lharrispvAuthor Commented:
Sorry, I should have mentioend I already tried that.  That gives me one long string consisting of each of the descriptions returned.

0
 
hieloCommented:
Let's say your query matches 10 records, then this:
$dbconn = new database();
$result=$dbconn->query("select name,description from product");
if($result)
{
        while($row=$dbconn->fetch_rows($result) ){
                print( "\n<div>" . $row->name . ": ". $row->description . "</div>");
        }

will print the data in 10 different lines.
}

Open in new window

0
 
lharrispvAuthor Commented:
one more question.. what if I knew there were 300 records returned but I wanted to only get at record 158?
0
 
hieloCommented:
>>one more question.. what if I knew there were 300 records returned but I wanted to only get at record 158?
you would need to add a method to your class to move the pointer internally:
...
	public function moveTo($rec){
		if($this->resultlink){
			mysql_data_seek($this->resultlink, $rec);
		}
	return $this;
	}
...

$dbconn = new database();
$result=$dbconn->query("select name,description from product");
if($result)
{
 $dbconn->moveTo(5);//advance 5 records
...

}

Open in new window

0
 
lharrispvAuthor Commented:
Thank you for your help!  This was exactly what I was looking for.  Sorry it took me so long to respond but I have been out sick for the past few work days.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now