[ciapug] On or Off ...

[Carl Olsen]

I got really deep into object oriented PHP this weekend and decided to start
with something a little more simple, so I'm creating a class for each table
with select, insert, update, and delete functions that all use prepared
statements.  I agree that the security blanket is great.  I'm also using
exceptions to send error messages back to the page with messages I can
display to the user (right now, I have it sending the page name and line
number, but I'll remove that when the site goes into production).

My slimmed down version is not as cool as the examples in the book, but I
need to take a small step forward before I try everything at once.  I'm
going to look for places where I can use some of the advanced classes from
the book.  They have some classes for handling collections of objects that
look very useful.  PHP5 can now do a foreach loop on an array of objects,
but you have to implement the built-in iterator and interatoraggregate
classes in collection and collectioniterator classes to get there.  I got
all the code to run (the examples are all in PostgreSQL, so I had to convert
them all to mysqli), but I can't say I'm comfortable with it.  The book says
you have to use them a few times before you will get comfortable with them,
so I'm not experiencing anything unusual.  I need to get my project going,
so I'm going to take a few of the simpler classes and start with them (my
project is not extremely data intensive, so I can get by with less).  I've
been doing object oriented programming with C# for several years, so at
least that makes it easier to learn than if I didn't know anything about
OOP.

As I look at the prepared statements, I can vaguely visualize feeding in the
parameters as arrays so that a generic set of select, insert, update, and
delete functions could be written to handle any table in the database, but
I'm going to take the longer route this time.

Thanks for the timer!

Carl 

-----Original Message-----
From: ciapug-bounces at cialug.org [mailto:ciapug-bounces at cialug.org] On Behalf
Of Tony Bibbs
Sent: Monday, August 22, 2005 9:15 AM
To: carl-olsen at mchsi.com
Cc: ciapug at cialug.org
Subject: Re: [ciapug] On or Off ...

Sorry for the delay, I was on vacation.

Well, given you have already written both a raw SQL and prepared 
statement version, I would simply use a timer to see how they do.  I've 
attached a simple timer class for you to use (not that you'd really need 
it).

My guess is the prepared statements, if not supported by your DBMS, will 
take a little longer but the security blanket it gives will probably 
offset any performance concerns.

--Tony

Carl Olsen wrote:
> I'm just wondering about performance.  I just wrote a class for a table in
> my MySQL database and I'm using mysqli for the connections and queries.
> Will it increase my performance to add prepared statements?  Here's my
code:
> 
> <?php
> 
> class faqs
> {
> 	private $id;
> 	private $question;
> 	private $answer;
> 	private $order;
> 	private $conn;
> 	private $needsupdating = false;
> 	
> 	public function __construct($faqid)
> 	{
> 		$this->conn = mysqli_connect("host", "username", "password",
> "database");
> 		if(!$this->conn)
> 		{
> 			throw new Exception("Unable to connect to the
> server: " . mysqli_connect_error());
> 		}
> 		$sql = "SELECT faq_question, faq_answer, faq_order FROM
> faq_faq WHERE faq_id = " . $faqid;
> 		$rs = mysqli_query($this->conn, $sql);
> 		if(!$rs)
> 		{
> 			throw new Exception("An error occurred selecting
> from the database: " . mysqli_error($this->conn));
> 		}
> 		if(!mysqli_num_rows($rs))
> 		{
> 			throw new Exception("The specified faq does not
> exist!");
> 		}
> 		$data = mysqli_fetch_array($rs);
> 		$this->id = $faqid;
> 		$this->question = $data['faq_question'];
> 		$this->answer = $data['faq_answer'];
> 		$this->order = $data['faq_order'];
> 		mysqli_free_result($rs);
> 	}
> 	
> 	public function getquestion()
> 	{
> 		return $this->question;
> 	}
> 	public function getanswer()
> 	{
> 		return $this->answer;
> 	}
> 	public function getorder()
> 	{
> 		return $this->order;
> 	}
> 	public function setquestion($question)
> 	{
> 		if(!is_string($question) || strlen($question) == 0)
> 		{
> 			throw new Exception("Invalid question value");
> 		}
> 		$this->question = $question;
> 		$this->needsupdating = true;
> 	}
> 	public function setanswer($answer)
> 	{
> 		if(!is_string($answer) || strlen($answer) == 0)
> 		{
> 			throw new Exception("Invalid answer value");
> 		}
> 		$this->answer = $answer;
> 		$this->needsupdating = true;
> 	}
> 	public function setorder($order)
> 	{
> 		if(!is_integer($order) || strlen($order) == 0)
> 		{
> 			throw new Exception("Invalid order value");
> 		}
> 		$this->order = $order;
> 		$this->needsupdating = true;
> 	}
> 	
> 	public function __destruct()
> 	{
> 		if(!$this->needsupdating)
> 		{
> 			return;
> 		}
> 		$sql = "UPDATE faq_faq SET ";
> 		$sql .= "faq_question = '" .
> mysqli_real_escape_string($this->conn, $this->question) . "', ";
> 		$sql .= "faq_answer = '" .
> mysqli_real_escape_string($this->conn, $this->answer) . "', ";
> 		$sql .= "faq_order = '" .
> mysqli_real_escape_string($this->conn, $this->order) . "' ";
> 		$sql .= "WHERE faq_id = " . $this->id;
> 		$rs = mysqli_query($this->conn, $sql);
> 		if(!$rs)
> 		{
> 			throw new Exception("An error occurred updating the
> database: " . mysqli_error($this->conn));
> 		}
> 		mysqli_free_result($rs);
> 		mysqli_close($this->conn);
> 	}
> }
> 
> ?>
> 
> -----Original Message-----
> From: Tony Bibbs [mailto:tony at tonybibbs.com] 
> Sent: Friday, August 12, 2005 2:05 PM
> To: carl-olsen at mchsi.com; ciapug at cialug.org
> Subject: Re: [ciapug] On or Off ...
> 
> Are you talking about cleaning it as far as escaping arguments to your 
> prepared statements?  If so, 4.1.x should be doing it on the server for 
> you.  Older ones would have to be done by your database abstraction layer.
> 
> I think it's safe to black-box that much of it.  As long as you are 
> using prepared statements you are safe(r).
> 
> --Tony
> 
> 
> 
>