dynamic query class

aharrover

I'm kinda new to oo php and I need a pointer. I've built a class that creates a database (mysql) connection and I can update, select and return data. Problem is that I need for the query's that return data to be dynamic or else I'll have 100 functions. For example, my ssnquery function queries just for a matching ssn. I have another function that queries on first name/last name combo. The data coming for these queries is coming from forms where the form element names are the same as the db field names. Is there a way to write a generic function so it reads those form names and then constructs the query?? For example, form "A" has a text field in it called ssn and also has an associated value. Can php read this incoming $post array and build a query or am I barking up the wrong tree.....

eoghain

You can do this, but it takes alot of thought and preperation. You'll also have to give alot of thought to security. But here is a simple example of this:

<!-- HTML Form for automatic query processing -->
<form action="query.php" method="post">
   <input type="hidden" name="table" value="users" />
   <input type="hidden" name="cols[]" value="fname" />
   <input type="hidden" name="cols[]" value="lname" />
   <input type="hidden" name="constraints[]" value="ssn" />
   <input type="text" name="ssn" value="" />
   <input type="submit" name="submit" value="submit" />
</form>

There is a simple form with 3 hidden fields and 1 exposed field. This form will be processed by the below code:

<?php
// query.php

// query variables
$table = "";
$selCols = "";
$where = "WHERE ";

foreach ($_POST as $key => $value) {
   switch ($key) {
      case "table":
         $table = $value;
         break;
      case "cols":
         $selCols = implode(",", $value);
         break;
      case "constraints":
         $sep = "";
         foreach ($value as $constraint) {
            $where .= $sep . $constraint . " = " . $_POST[$constraint];
            $sep = "AND ";
         }
         break;
   }
}

$query = "SELECT " . $selCols . " FROM " . $table . $where;

// Run $query and do whatever with the results.
?>

Now that is a very simple query processing structure that you control by the data that is in your forms. Personally I don't think this is a good way for you to go about building any database quering code. You are giving way too much informaiton out to the world, they will be able to know the name of your table, and the name of the columns in it, if someone wanted to they could write there own form to select everything from the table. So whatever you do don't use this code it isn't safe, it's just there to show you an example.

aharrover

is it better to simply have a bunch of functions? I do see your point about security tho. Hadn't thought of that.

eoghain

It all depends on what you are trying to acomplish. What you overall design is.

Personally I have a database class that I created years ago (before PEAR) and I use that to do my work for me. But that class is very generic, I pass in my query that I've built in my business logic specific code and it returns me the results of that query.

// Sample Usage of my DB class
$db = new DBConnection($username, $password, $database, $host);

$sql = "SELECT * FROM MyTable";
$data = array();
$count =$db->doQuery($sql, $data);

print_r($data);

As you can see my database code has no idea what my business logic is. It just deals with connecting to and running queries against the database. When I need to use it, I instantiate it and use it (typically at the beginning of my pages).