Solved

Why does num_rows not work?

Posted on 2013-01-26
22
1,480 Views
Last Modified: 2013-01-29
Here's my class:

class Test {

      function the_query(){
      $bruce=SqlQuery("","select * from registration where id='$_GET[id]'");
      return $bruce['first_name'];
      }
}

When I do this:

$dean = new Test();
echo $dean->the_query();

I get "Dustin" so the query works.

Now if this is my class:

class Test {

      function the_query(){
      $bruce=SqlQuery("","select * from registration where id='$_GET[id]'");
      return $bruce->num_rows;
      }
}

I get an error that says, "Trying to get property of non-object in C:\wamp\www\Showdown\adm\classes\songwriting_class.php on line 49." Line 49 being return $bruce->num_rows.

It seems to be that I have an "object" in that I can retrieve data from it. But when I ask for the number of rows, I'm told that I don't have an object.

What am I doing wrong?
0
Comment
Question by:brucegust
  • 7
  • 6
  • 5
  • +2
22 Comments
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 100 total points
ID: 38822941
'num_rows' is not a property of the dataset.  It is only available as a separate query.  And the way you have written that code, you are already 'out-of-scope'.  You could have gotten that value in the function where you actually did the query.  But at that point, you are no longer in that function and the only variables available to you are the ones that you have 'returned'.

Variables declared inside a function are not visible outside the function if they are not declared 'global'.  http://php.net/manual/en/language.variables.scope.php
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38822952
Simply $bruce is not an object. $dean is an object because it is the result of a class instantiation:

$dean = new Test();

Open in new window


$dean is an object of the class Test.

$bruce = SqlQuery(...

Open in new window


$bruce is the result of a method not an object. You need to have a method in the Test class whcih returns the nume of rows and then call it this way:
$dean = new Test();
$dean->GetNumRows($query);

Open in new window


and in the Test class:
class Test {

      function GetNumRows($query){
      $bruce=SqlQuery($query);
      return $bruce->num_rows;
      }
//this code could not work depending on how is implemented in your code SqlQuery 
//function
}

Open in new window


Your actual function the_query must be rewritten this way:

      
    function the_query(){
        $bruce=SqlQuery("","select * from registration where id='$_GET[id]'");
        return $bruce->result; // again, this depends on how SqlQuery function is implemented
    }

Open in new window


If you want to post here your full code for class Test (SqlQuery code included) I'll be happy to help you. About Php object oriented programming, let me suggest this book (http://www.apress.com/9781590599099/): it's not so easy tor ead, but it makes you to  jump forward in php oop.

Cheers
0
 

Author Comment

by:brucegust
ID: 38822990
OK, I see.

So what if I did this in my SqlQuery function (I added what I'm hoping to be a "count" property.

function SqlQuery($database,$sql){

$dbhost = "localhost";    // Your database server 192.168.0.10
$dbuser = "root";      // Your db username
$dbpass = "";      // Your db password
$dbname = "Showdown";      // Your database name

$dbc = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

$query_results = mysqli_query($dbc,$sql);
$row = mysqli_fetch_assoc($query_results);
$count=mysqli_num_rows($query_results);
return $row;
return $count;

}

Open in new window


Does that solve the problem of the count not being a property of the dataset? I want to think that I'm on to something but now I don't know how to retrieve it.

When I try return $bruce[count] I get an error.

Ahh, I'm close, but I'm still missing it.
0
 

Author Comment

by:brucegust
ID: 38823006
Sorry guys, there was some posting going on while I was making my way through this. Here's the whole code, top to bottom just so I'm doing everybody the courtesy of letting them see the whole mess and not just a puddle...

songwriting_class.php...

function SqlQuery($database,$sql){

$dbhost = "localhost";    // Your database server 192.168.0.10
$dbuser = "root";      // Your db username
$dbpass = "";      // Your db password
$dbname = "Showdown";      // Your database name

$dbc = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

$query_results = mysqli_query($dbc,$sql);
$row = mysqli_fetch_assoc($query_results);
$count=mysqli_num_rows($query_results);
return $row;
return $count;

}

class Test {

	function the_query(){
	$bruce=SqlQuery("","select * from registration where id='$_GET[id]'");
		if($this->count==0)
		{
		echo "nope";
		}
		else
		{
		return $bruce['first_name'] ;
		}
	}
}

Open in new window


... and then the page where I'm attempting to retrieve the results...

	<?php 
							require_once('classes/songwriting_class.php'); 
							$dean = new Test();
							//var_dump($dean);
							echo $dean->the_query();
							?>

Open in new window

0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 38823092
You can have Only one return statement in a function.  And it can only return One variable or object like an array.
0
 
LVL 30

Assisted Solution

by:Marco Gasi
Marco Gasi earned 200 total points
ID: 38823701
As I said, you must implement a function to get the num of rows: as Dave said, both functions and methods can return only one value.

But I see you have a bad design pattern: it seems you are too lazy to do all you need to do to have a correct OOP system :-)

First, you should create at least one class to interact with database: in this class you should put all methods to connect to the database and execute SELECT, INSERT, DELETE, UPDATE and all other queries you think to need (getNumRows(), geColumnsName() and so on);

Second, within Test class you should refer to db class methods to retrieve all data you need:

DBManager.class.php
public $dbhost;
public $dbuser;
public $dbpass;
public $dbname;
public $dbc;

function __construct(){
    $this->dbhost = "localhost";    // Your database server 192.168.0.10
    $this->dbuser = "root";      // Your db username
    $this->dbpass = "";      // Your db password
    $this->dbname = "Showdown";      // Your database name
   $this->dbconnect();
}

function dbconnect(){
    $dbc = new mysqli($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname);
}

function SqlQuery($sql){
    $query_results = mysqli_query($this->dbc,$sql);
    $row = mysqli_fetch_assoc($query_results);
    return $row;
}
function SqlNumRows($sql){
    $query_results = mysqli_query($this->dbc,$sql);
    $count=mysqli_num_rows($query_results);
    return $count;
}

Open in new window


Test.class.php

class Test {

	function getUserName(){
        $dbman = new DBManager();
	$bruce = $dbman->SqlQuery("","select * from registration where id='$_GET[id]'");
		if($this->count==0)
		{
		return "nope";
		}
		else
		{
		return $bruce['first_name'] ;
		}
	}
}

Open in new window


<?php 
	require_once('classes/Test.class.php'); 
	$dean = new Test();
	//var_dump($dean);
	echo $dean->getUserName();
?>

Open in new window


Keep in mind this is only an example to show you the generaic design pattern to use in oop: it should be improved, but it can tell you how to proceed with classes and objects.

Cheers
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 38824209
Looking at this...

$bruce=SqlQuery("","select * from registration where id='$_GET[id]'");
return $bruce['first_name'];

Open in new window

... would tell us that the SQLQuery() function probably runs a query, acquires a single row from the results set (which immediately makes me wonder why the query string did not contain LIMIT 1) and assigns the single row, in the form of an array, to the $bruce variable.  Then the next statement assumes that the array contains a value at the index first_name and it returns that value.  If these assumptions are correct, then the information at $bruce['first_name'] most likely is a character string.  

Looking at this...

$bruce=SqlQuery("","select * from registration where id='$_GET[id]'");
return $bruce->num_rows;

Open in new window

... would tell us that the SQLQuery() function probably runs a query and returns a pointer to the the results set Resource.  This Resource is then used as an object to acquire and return the property named num_rows.

It may be easier to understand this process if you look at it from a purely procedural standpoint.  Assuming you have a good connection and selection of the data base, here are the essential moving parts of a SELECT query, in order of operation:

1. You run the SELECT query by invoking mysql_query().  The function takes as input a query string, and makes a request to the MySQL Engine.  The MySQL Engine runs the query and presents some kind of result indication back to the PHP Engine.  If the query fails, mysql_query() returns FALSE and you embark on a journey of debugging.  If the query succeeds, it returns a Resource, which is your key to retrieving the results from the MySQL Engine.

2. Subsequent requests  to the MySQL Engine can get you the number of rows, or the actual rows of data, etc.  All such requests must contain the Resource, because the MySQL Engine may serve several PHP Engine queries simultaneously, and the Resource is the only way of knowing what data set the MySQL Engine should use to respond to PHP requests for data.

3. PHP might choose to retrieve the row(s) from the MySQL data set.  To do that you would call mysql_fetch_assoc() or mysql_fetch_object(), passing the Resource to the MySQL Engine.  The MySQL Engine will respond by presenting an array or object containing the row of the results set, or FALSE if there are no more rows to return.

Hope that explanation helps, and if it's not clear yet, get this book and work through the examples.  It's about procedural PHP, not OOP, but it is very clear on the ways of using the SQL extensions.  The OOP part of things is not really very different in concept; there are just a few differences in notation which actually simplify the code.
http://www.sitepoint.com/books/phpmysql5/

Once you're comfortable with procedural MySQL and you see the analogies in OOP MySQLI, you'll be ready to tackle PDO, which is a substantially different notation.  But you may not need to go there at all.  The MySQLI extension will likely be supported for a long time to come.

Best to all, ~Ray
0
 
LVL 33

Expert Comment

by:Slick812
ID: 38824501
hey, , you seem to be on the right track in setting up your code, but there are things that can help you, for instance , in your -

function SqlQuery($database,$sql){

$dbhost = "localhost";    // Your database server 192.168.0.10
$dbuser = "root";      // Your db username
$dbpass = "";      // Your db password
$dbname = "Showdown";      // Your database name

$dbc = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

$query_results = mysqli_query($dbc,$sql);
$row = mysqli_fetch_assoc($query_results);
$count=mysqli_num_rows($query_results);
return $row;
return $count;

}

Open in new window


you do not seem to have used something to get "Multiple" returns from a function, the most common is the Array, as is returned by many PHP functions, , as in dis code -

function SqlQuery($database,$sql){

$dbhost = "localhost";    // Your database server 192.168.0.10
$dbuser = "root";      // Your db username
$dbpass = "";      // Your db password
$dbname = "Showdown";      // Your database name

$dbc = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

$query_results = mysqli_query($dbc,$sql);
$row = mysqli_fetch_assoc($query_results);
$count=mysqli_num_rows($query_results);
return array($row,  $count);

}

//used like -
$dataRe = SqlQuery($db ,$query);
echo "Returned Row Count is ".$dataRe[1];

Open in new window


you may or may Not want to have an overall ObjOrein code base, if so you can also return an object wid properties

$re = new stdClass;
$re->count =  $count;
$re->row =  $row;
return $re;
// or in one line -
//return (object) array('count' => $count, 'row' => $row);
// = = = = =

//used like -
$dataRe = SqlQuery($db ,$query);
echo "Returned Row Count is ".$dataRe->count;

Open in new window


you may try and consider the Class you write, as a code location for ALL the Database Code you use for the song stuff, a code container for all data base values and functions, if you look for song database code, you will know it's in that Class.
0
 

Author Comment

by:brucegust
ID: 38827242
Slick, Dave, Ray, Marcus - y'all rock, it's just that simple.

Here's what you've explained thus far and I want to explain it back to you just to make sure I understand "why" as well as "what."

Marcus, I like your paradigm and between you and Dave I understand now that, unlike a procedural approach (which I've been doing since before 2008) where I would often preface the mysqli_fetch_assoc with a mysqli_num_rows and make sure there was data in the recordset before I attempted to retrieve it, there now has to be a separate function to see how many rows there are in the database.

Got it.

Marcus, I understand the logic of everything you've written save line 12 of the DBManager class. Tell me if I'm understanding this correctly: You begin by declaring your variables and then, using your construct function, assign values to those variables, which would actually be referred to as "properties" in that they're occurring within a class. But at line 12, you're not assigning a value. Rather it appears that you're saying programmatically that all of the properties that you've defined within your construct function are now being cumulatively represented by "dbconnect." I'm thinking that "dbconnect" will now be recognized as the collection of properties you've defined in the construct function every time that "dbconnect" is referenced within the DBManager class.

And that's going to be handy given the title of your next method which is the "dbconnect" function. Because you've prefaced this with the previous construct function, when the dbconnect function kicks into gear, it is automatically bringing to the table all of the credentials that you're going to need to login to the database and now doing so in the context of a mysqli connection.

Correct?
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38827733
I'm thinking that "dbconnect" will now be recognized as the collection of properties you've defined in the construct function

More precisely, dbconnect is a method (that is a function which is part of a class) whose goal is to establish a connection to the database using as parameters the properties defined in the constuctor. You can do this even in the constructor itself and Usually I do it, since this class has no reason to exist without a database connection:

<?php
class DBManager {
	public $dbhost;
	public $dbuser;
	public $dbpass;
	public $dbname;
	public $dbc;
	public $msg;

	function __construct(){
		$this->dbhost = "localhost";    // Your database server 192.168.0.10
		$this->dbuser = "root";      // Your db username
		$this->dbpass = "cavallo";      // Your db password
		$this->dbname = "Showdown";      // Your database name
		$dbc = new mysqli($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname);
	}

	function dbconnect(){
			$dbc = new mysqli($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname);
	}
		
}

Open in new window


Anyway, if you're skilled in procedural Php, I suggest to read (before the book a suggested above) this book: PHP Master Write Cutting-Edge Code It offers a good introduction to Object oriented programming in Php (plus several others interesting things) and I'm sure you'll appreciate it to help you in understanding this approach to the programming. It'll help you also to understand what is more deeply treated in the Matt Zandstra book PHP Objects, Patterns, and Practice - strongly recommended :-)

Hope this clarify your doubts

Cheers
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38827746
Ooops, sorry, in the snippet above i leaved the no more used function dbconnect():

<?php
class DBManager {
	public $dbhost;
	public $dbuser;
	public $dbpass;
	public $dbname;
	public $dbc;
	public $msg;

	function __construct(){
		$this->dbhost = "localhost";    // Your database server 192.168.0.10
		$this->dbuser = "root";      // Your db username
		$this->dbpass = "cavallo";      // Your db password
		$this->dbname = "Showdown";      // Your database name
		$dbc = new mysqli($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname);
	}


}
                                            

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 38827833
Note that 'num_rows' needs to be called immediately after the query and stored if necessary.  It will not be valid even if you do get a value at any other time.  It is tied to the query but is not returned with it.
0
 

Author Comment

by:brucegust
ID: 38828117
Alright, I think I've got it, but I'm getting an error that says I'm not able to connect to the database.

The login credentials are correct. Do you see anything with the way that I've implemented your suggestions that would result in a bad database connection?

Here's the code:

<?php
class DBManager {
	public $dbhost;
	public $dbuser;
	public $dbpass;
	public $dbname;
	public $dbc;
	public $msg;

	function __construct(){
		$this->dbhost = "localhost";    // Your database server 192.168.0.10
		$this->dbuser = "root";      // Your db username
		$this->dbpass = "";      // Your db password
		$this->dbname = "showdown";      // Your database name
		$dbc = new mysqli($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname);
	}

	function SqlQuery($sql) {
	$query_results=mysqli_query($this->dbc, $sql);
	$row=mysqli_fetch_assoc($query_results);
	return $row;
	}
	
	function SqlNumRows($sql) {
	$query_results=mysqli_query($this->dbc, $sql);
	$count=mysqli_num_rows($query_results);
	return $count;
	}

}

class Test {

	function getUserName(){
        $dbman = new DBManager();
	$bruce = $dbman->SqlQuery("","select * from registration where id='2545'");
		if($this->count==0)
		{
		return "nope";
		}
		else
		{
		return $bruce['first_name'] ;
		}
	}
}

?>

Open in new window


.and here's the error that I'm getting...


Warning: mysqli_query() expects parameter 1 to be mysqli, null given in C:\wamp\www\Showdown\adm\classes\songwriting_class.php on line 19

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, null given in C:\wamp\www\Showdown\adm\classes\songwriting_class.php on line 20

Notice: Undefined property: Test::$count in C:\wamp\www\Showdown\adm\classes\songwriting_class.php on line 37
nope
0
 
LVL 33

Expert Comment

by:Slick812
ID: 38828289
I think this may help ? Looks to me as if in marqusG class code a  $this  is missing for the class property of $dbc, as in -
$this->dbc = new mysqli($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname);

and just for clarity, there is a difference between how the new msqli operates in OO and in it's procedure mode
I beleive the procedure mode goes like

$this->dbc = mysqli_connect($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname);

$query_results = mysqli_query($this->dbc, $sql);


OO mode as -
$this->dbc = new mysqli($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname);
$query_results = $this->dbc->query($sql);

- - - - -
I really think that the advantage of Object code in php is to make the developers code work "Less" less lines of code to write, less time to do work for results. So brucegust , even though you are learning Class now, see if you can detect, how the organization and connected relation ships of the Class can make programming "Better" for your web output, for me, instead of making a million different Class functions for every little separate thing that needs to be done (as the num-Rows thing), I save time by seeing in my page output I need three or four things from DB, instead of calling 4 object methods, I have One method that returns the 4 things I need (in array or object) so I make one call.
Also in DB selects, you can do that select once, output a portion of the return Data in method1, store in array property of Class the other select return data, and in method2 you can get that class property data later, and not do another select. But a main advantage (to me) of Class is the internal property storage of needed info for that object, to be used several times in several different methods, and having  methods that can change the output and relation ship of several other class methods, , , as in there is a connection for things in a Class due to shared $this properties, that's rough to do in code outside of a class.
0
 

Author Comment

by:brucegust
ID: 38828646
Hey, Slick!

I so appreciate your time and I do see the "prize" as far as mastering this and getting to the point where I'm actually writing less code.

I'm still not where I need to be with my current dilemma however. Code is attached. I'm getting the following errors:

Warning: mysqli::query() [mysqli.query]: Empty query in C:\wamp\www\Showdown\adm\classes\songwriting_class.php on line 19

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\Showdown\adm\classes\songwriting_class.php on line 20

Notice: Undefined property: Test::$count in C:\wamp\www\Showdown\adm\classes\songwriting_class.php on line 37

So, I'm now getting a connection, but now I'm being told I don't have a query and then my connection seems to be an issue once again in line 20.

Thoughts?

<?php
class DBManager {
	public $dbhost;
	public $dbuser;
	public $dbpass;
	public $dbname;
	public $dbc;
	public $msg;

	function __construct(){
		$this->dbhost = "localhost";    // Your database server 192.168.0.10
		$this->dbuser = "root";      // Your db username
		$this->dbpass = "";      // Your db password
		$this->dbname = "showdown";      // Your database name
		$this->dbc = new mysqli($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname);
	}

	function SqlQuery($sql) {
	$query_results = $this->dbc->query($sql);
	$row=mysqli_fetch_assoc($query_results);
	return $row;
	}
	
	function SqlNumRows($sql) {
	$query_results = $this->dbc->query($sql);
	$count=mysqli_num_rows($query_results);
	return $count;
	}

}

	class Test {

	function getUserName(){
		$dbman = new DBManager();
		$bruce = $dbman->SqlQuery("","select * from registration where id='2545'");
		if($this->count==0)
		{
		return "nope";
		}
		else
		{
		return $bruce['first_name'] ;
		}
	}
}

?>

Open in new window

0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38828819
Oooops, Slick812 is right, I forgot a $this: I'm very sorry for this - for $this :)

@Slick812: I think you're expert enough in oop to choose to not follow basic rules of OOP programming: you know that a class has to provide 4 infos and you decide to pack all that infos in a single array returned by one method: personally, I don't like it but who am I to judge? The beautyful thing of programming is we are all lone wolfs and everyone can do what he thinks is better for him. But if you have no experience in oop maybe it is better to begin following all rules literally and then you'll can adjust your programming style as you wish: you can't run if you don't know how to walk - hope it's clear what I mean...

@brucegust: now your method SqlQuery requires only one parameter (I've deleted the one - $database - because it was unneeded since the database name is set in the class constructor): consequently, you mast call it this way:

$bruce = $dbman->SqlQuery("select * from registration where id='2545'");

instead of

$bruce = $dbman->SqlQuery("","select * from registration where id='2545'");
0
 

Author Comment

by:brucegust
ID: 38829274
Sweet!

I got it all down to one last error. I'm being told...

Notice: Undefined property: Test::$count in C:\wamp\www\Showdown\adm\classes\songwriting_class.php on line 37
nope  

I understand that a property within a class is a variable, but I don't see where that is NOT being defined.

The other errors are gone, so yeah, baby! But this last thing is still alluding me.

What am I missing?
0
 
LVL 33

Assisted Solution

by:Slick812
Slick812 earned 100 total points
ID: 38829494
OK not sure I should display this, it's from an educational thing I did, to show how to use mysqli properly (or maybe just my way ?)
This is tested and works on 2 different PHP versions 5.2 an 5.3
Please notice, that maybe beginners in mysqli should set up proper "Error Checks" to help them debug and refine their code


$instID = $_POST['inst-ID'];// value here is 'mathSmith2' for instuctor ID
// Always verify that the POST and GET values are within acceptable limits for the DataBase
if ((strlen($instID) > 5) && // all Instuctor ID are 6 or more in length
    (strlen($instID) < 15) && // all Instuctor ID are less than 15 in length (VARCHAR (14))
    (preg_match('/[^a-zA-Z0-9]/', $instID) == 0)) // instID MUST be only Alfa Numeric
    {
// with MySLIi the database is sected in the creation
    $mysqli = new mysqli('localhost', 'user', 'password', 'eduDB');
    if (!mysqli_connect_errno()) // test to see if there is a mysql connection
        {
        echo '<br />mysqli QUERY RESULT BELOW -<br />';
// IMPORTANT if using the query of mysqli you still MUST escape ALL user input just like the Old MySQL
        $instID = $mysqli->real_escape_string($instID); // protect from injection

// the SQL for SELECT is Identical for the query( ) as the Old Style of MySQL
            $query = 'SELECT date1, time1 FROM examkit WHERE instrucID = "'.$instID.'"';
            if ($result = $mysqli->query($query)) { // result from SELECT query is used similar to Old MySQL
            echo 'Number returned rows from examkit: '. $result->num_rows.'<br />';
            while ($row = $result->fetch_object()) // get Object for each Row
                echo 'date1= ', $row->date1,' time1= ', $row->time1, '<br />';

            $result->close(); // release mysqli references as soon as possible
            } // bracket correspons to - if ($result = $mysqli->query($query))
            else echo 'SELECT error ',$mysqli->error;
        $mysqli->close(); // release mysqli references as soon as possible

        }  // bracket correspons to - if (!mysqli_connect_errno())
        else echo '<b>BIG MySQL ERROR - did NOT Connect to database ', mysqli_connect_error() ,'</b>';
    }  // bracket correspons to - if ((strlen($instID)
    else echo 'ERROR - Instuctor ID is NOT a valid Value<br />';

Open in new window


= = = = = =
as far as following the rules, no I do not, but I have seen so many coders in sites like www.phpclasses.org and others that have php Classes to see and use, and they obviously take the Manual examples and use those ideas to have some bloated class code (My Dumb opinion), sure they have to start somehow, and get better as they go, but in procedure design (functions), most do not make 4 functions, that you can place into One function with improved (less) code writes. But you really need to see where you are going with writting any code, as in goal oriented design, do only code to get you to your page output, But as you say there are thousand of different ways to do that, LOL, no one size fits all in Class design ! !
0
 
LVL 33

Expert Comment

by:Slick812
ID: 38829508
since this is for brucegust  learning, I'll give more to chew on, I HIGHLY recommend the prepare() and execute() for ALL user input in the mysqli query code.

Below is a rewrite of the above code using prepare and execute, this is more or less what's in the PHP manual, and most every tutorial. I have the exact same code for the Post verification and - new mysqli( ) -  but instead of using query( ), I use the prepare( ) , bind_param( ), execute( ), and bind_result( ) methods.


      using mysqli->prepare( ) for a SELECT query
// get user Input from page Form
$instID = $_POST['inst-ID'];// value here is 'mathSmith2' for instuctor ID
// Always verify that the POST and GET values are within acceptable limits for the DataBase
if ((strlen($instID) > 5) && // all Instuctor ID are 6 or more in length
    (strlen($instID) < 15) && // all Instuctor ID are less than 15 in length
    (preg_match('/[^a-zA-Z0-9]/', $instID) == 0)) // instID must be only Alfa Numeric
    {
// with MySLIi the database is sected in the creation
    $mysqli = new mysqli('localhost', 'user', 'password', 'eduDB');
    if (!mysqli_connect_errno())
        {
        $numRows = 0;
// In the prepare, use the ? for a PlaceHolder to Bind the Paramerts later
        $query = 'SELECT date1, time1 FROM examkit WHERE instrucID = ?';
        $stmt = $mysqli->prepare($query);// prepare returns a mysqli Statement Object
        if($stmt) {// if the SQL language in prepare is Wrong,  $stmt is false
// IMPORTANT, unlike Old and the mysqli->query( ) you should NOT escape the User Input with real_escape_string()
// because the Value of $instID is NOT used as a php string in the SQL prepare string

// you MUST assign a variable NAME to every ? in the prepare( ) SQL
            $stmt->bind_param('s', $instID); // assign $instID to the ? for SQL test 'WHERE instrucID = ?'

// whenever execute() is used, the C code underneth PHP reads the Value in the $instID variable
// and sends it to the MySQL server, where the prepare() SQL methods are actually run at that time,
// with the execute DATA in the ? positions.
// this happens without any visible code method for the php developer to see, which is strange for many using PHP
            if($stmt->execute())
               {
// IMPORTANT, after the statement execute( ), the $stmt does NOT contain any Returned Query Data
// the $stmt is ONLY a reference object to the returned Query data still on the MySQL server
// which has NOT been returned to PHP at this point.

// You get the SELECT prepare( ) Result Data by the statement->bind_result( ) for Returned Row Values
// you MUST assign a variable NAME to every column in the SELECT, here it's two as 'SELECT date1, time1'
// the variable assignment is ALWAYS the exact order in the SELECT text, one after the other, from left to right
            $stmt->bind_result($date1, $time1);
            echo 'Statement FETCH OUTPUT BELOW -<br />';
            while ($stmt->fetch()) // fetch() will place the ROW values into the Bound variables
                {
//unlike regular PHP, the $date1 and $time1 values will change without any visable code assignment
// on each while loop of fetch(), the C code underneth PHP changes the Value assignment of $date1 and $time1
                echo 'date1= ',$date1,' |time1= ',$time1,'<br />';
                ++$numRows; //a way to get the number of rows
                }
            echo '<br />Num Rows in Fetch ',$numRows,'<br />';
            } // bracket for if($stmt->execute())
            else echo 'ERROR - execute  FAILED= ',$mysqli->error,'<br />';
            $stmt->close();// release mysqli references as soon as possible
// IMPORTANT, close $stmt to release all the query Result return data still on the MySQL server
            } // bracket for if($stmt)
            else echo 'ERROR in PREPARE= ',$mysqli->error,'<br />';
        $mysqli->close(); // release mysqli references as soon as possible
        } // bracket for if (!mysqli_connect_errno())
        else echo '<b>BIG MySQL ERROR - did NOT Connect to database ', mysqli_connect_error() ,'</b>';
    } // if ((strlen($instID)
    else echo 'ERROR - instID is NOT a valid Value<br />';

Open in new window


Some Differences in code operations for using execute( )
In the SELECT used above, when execute( ) is used, the returned Statement Object, does NOT have any DATA returned from the selection out of the Table, these are keep on the MySQL server untill you close the Statement Object, here I use the $stmt->bind_result( ) and do while loop with $stmt->fetch(), this gets ONE row of DATA from the MySQL pipeline on each loop, this allows you to BREAK the loop and stop, when you find the DATA you are looking for, and not have useless DATA crowd the MySQL pipeline. Although this is seldom any concern for php developers, it can be used if you know whats happening and have big DATA server slowdowns. Although not in this code example, you can transfer ALL SELECTED DATA to "php storage" through the MySQL pipeline, with the use of  $stmt->store_result(); , many in php that do not understand where and how the data is stored, often misunderstand the use of this function,
0
 
LVL 30

Accepted Solution

by:
Marco Gasi earned 200 total points
ID: 38829922
Look at this code:

      class Test {

      function getUserName(){
            $dbman = new DBManager();
            $bruce = $dbman->SqlQuery("select * from registration where id='2545'");
            if($this->count==0)
            {
            return "nope";
            }
            else
            {
            return $bruce['first_name'] ;
            }
      }

$count is not declared as a class property so it is an undefined property :-). Being a local variable you can only use $count. In addition you have to givi it a value:

      class Test {

      function getUserName(){
            $dbman = new DBManager();
            $bruce = $dbman->SqlQuery("select * from registration where id='2545'");
                $count = $bruce->SqlNumRows("select * from registration where id='2545'");
            if($count==0)
            {
            return "nope";
            }
            else
            {
            return $bruce['first_name'] ;
            }
      }

But it seems you're simply copying and pasting this code in with just a little changes in your environment: my code is only for example purpose and it should be  greatly improved before to be used in a real application.

Cheers
0
 

Author Comment

by:brucegust
ID: 38831762
Marcus, your last suggestion did work after I changed this:

$count = $bruce->SqlNumRows("select * from registration where id='2545'");

to this...

$count=$dbman->SqlNumRows("select * from registration where id='2545'");

So, it's done! I got this piece completed and given the enormity of the information that's been disseminated in terms of theory, I'm confident I have a great headstart in terms of being able to continue building using an OOP approach.

Thanks for the help!
0
 
LVL 33

Expert Comment

by:Slick812
ID: 38831905
just for an "alternate" view, (no points please) you might look at this, it is untested and may have formatting text errors, , but hopefully it may give you a little more possiblities for Class design, , please consider that when you call -
$query_results = $this->dbc->query($sql);
this takes any $sql  string, and there are many strings that Will Not Work.


class DBManager {
// it has always helped me in debugging and use, to start CLASS development
//with ALL properties given a DEFAULT value here
	public $dbhost = "localhost";
	public $dbuser = "root";
	public $dbpass = "passw";
	public $dbname = "showdown";
	public $dbc = NULL;
	public $msg = '';

	function __construct(){
		$this->errors = array(); //You do not need to have properties 
//declared above, but it helps reading code later
// be aware that PHP Classes are dymanmic, you can add and delete public class properies at any time
// inside the class code OR in the Object - $dbman->newProperty
		$this->dbc = new mysqli($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname);
	}

	public function SqlQuery($sql) {
	if ($query_results = $mysqli->query($sql)) {
		$re = new stdClass;
		if ($query_results->num_rows < 1) {
			$re->count = 0;
			$re->row = array();// you can leave this out or have a null, 
//I like to have consistant returns so if the $re->row is expected to be an array do an array

			return $re;
			}
		$row = $query_results->fetch_array(MYSQLI_ASSOC);
		$re->count = $query_results->num_rows;
		$re->row = $row;
		return $re;
		} 
		else
		{
		$this->errors['mysqli-query'] = $mysqli->error;
		return false;
		}
	}
	
	public function SqlNumRows($sql) {
	if ($query_results = $mysqli->query($sql)) {
		return $query_results->num_rows;
		} 
		else 
		{
		$this->errors['mysqli-query'] = $mysqli->error;
		return false;
		}
	}

}

	class Test {

	public function getUserName(){
		$dbman = new DBManager();
		if($bruce = $dbman->SqlQuery("","select * from registration where id='2545'")){
		if($bruce->count==0)
		{
		return "nope";
		}
		else
		{
		return $bruce->row['first_name'] ;
		}
		} else return false;// just for errors
// to debug
//foreach($dbman->errors as $k => $v) echo ' dbmanError: ',$k,' - ', $v;
	}
}

Open in new window


just give it a look, and see if anything clicks for you.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now