Calling DB variables through a class

jmbrink26

I apologize for the rather ambiguous subject, but wasn't sure whats the best wording for my issue at hand.

What I am trying to accomplish, is to call all the DB connection variables through a class, but I want my query to be different depending on the page it is on.

Let me put it in context. I am writing this 2 page form that ultimately outputs to a 3rd page. When the first page is submitted, the HTML form data gets passed into variables, which I then place into session variables. However at the same time, I am storing those variables into a table on my MySQL DB.

The user then fills out the fields on the 2nd page, then hits another button, and that either shows an error or gives you a thank you message. However on the backend of it, it stores the data from both page 1 and page 2, and that goes into a separate table on the same DB.

Which because the insert statement I use on page 2 does not contain all the columns I have in my insert statement on page 3, my insert statements are writing a different table, and of course the 2nd table contains more columns.

So, the way I have it right now, is I define the connection variables in a class in an external .inc file like so:

class Database {
   public $host = 'myhost.example.com';
   public $db = 'mydb';
   public $user = 'johndoe';
   public $pass = 'mypass';

   function mysql() {
      $connect = mysql_connect($host, $user, $pass);
      if(!$connect) {
         die('Error: ' . mysql_error());
      }
      mysql_select_db($db, $connect);
      if(!mysql_query($insert, $connect) {
          die('Error: ' . mysql_error());
      }
      mysql_close();
       }
}

So let's say on page two, I write to a table I called request_part_one. So I call my variable insert, and then call my function inside my class.

$insert = "INSERT into request_part_one (fname, lname, campus, aostudy, program) VALUES ($first, $last, $phone, $campus, $aoStudy, $program)";
$database = new Database();
$database->mysql();

And then comes page 3, which data is being passed now from page 1 and 2. But the table I am using for this insert statement is one, a different table, and two, this table has the same data as the first table, but also has the data passed from page 2.

$insert = "INSERT into requests (fname, lname, phone, campus, aostudy, program, email, cell, address, city, state, zip) VALUES ($first, $last, $campus, $phone, $aoStudy, $program, $email, $cell, $address, $city, $state, $zip)";
$database = new Database();
$database->mysql();

So, with that laid out, the problem I having is it is not performing the insert statement. It connects to the database just fine as expected, but it doesn't ever run the query.

Now, one thing I tried doing is created a variable in my .inc file inside the class and changed the conditional to apply to query

public $query;
if(!$query, $connect) {
   die('Error: ' . mysql_error());
}

And then assign the value of query to the value of insert on both pages 2 and 3

$database = new Database();
$database->query = $insert;
$database->mysql();

But that didn't seem to make a big difference.

What am I doing wrong here? I'm sure I'm just going about this the wrong way.

Thanks in advance for your help. :queasy:

Weedpacket

Why do you have the request_part_one table at all? Or, if you have to have that, why do you duplicate all its data in requests?

In your class you mention the variable $insert but you never give it any value (it's not the same variable as the one named $insert outside the class).

public $query;
if(!$query, $connect) {
   die('Error: ' . mysql_error());
}

This isn't even valid syntax. Presumably you have "public $query;" appearing along with the other public properties; the if(!$query, $connect) would be something like if(isset($this->query)) mysql_query($this->query, $connect);.

Meanwhile, all this looks like reinventing the wheel. [man]PDO[/man] already provides an object-oriented interface to database connections.

jmbrink26

Hi Weedpacket,

Well to answer your question why I have two tables of which one containing "duplicate" data, the reason behind that is because the form this is interacting with is 2 parts, and our corporate people want the first part saved to a database, even if the person never completes the 2nd part so they can be contacted.

So I thought it could be handled in two different ways; Either it can be saved in different tables, or use an UPDATE statement on the 2nd part of the form, and when they want the first part of the form, I could just write a query for them that only includes the data from the first part of the form.

My biggest concern initially was time since I was attempting to complete the task before my boss had a meeting with some of our corporate people, and then it took so long writing the javascript that it just didn't get done in time anyway, so now time isn't so much a factor, so I suppose it would be better to use an UPDATE statement on the 3rd page.

In any case, regardless of it being two INSERT's or an INSERT and an UPDATE, I'll still need to run two separate queries. So even if I call both of the query strings to use in mysql_query(), they will still be two different strings.

Where I am caught up is getting the class to see the strings.

Now, I have tried accomplishing this two ways; first I tried just declaring $insert inside my class

class Database() {
function mysql() {
if(!mysql_query($insert, $connect) {
die('Error: ' . mysql_error());
   }
}

And I was hoping the class would just see the value of $insert on each different page, but of course that didn't work.

So then I tried to declare a variable named $query inside the class, and have the function call query, and then try to assign the value of $query to the value of $insert on that page when I am calling the object.

//External .inc file
class Database() {
   $query;
   mysql() {
     if(!mysql_query($query, $connect) {
       die('Error: ' . mysql_error());
       }
     }
}

//Page 2
$database = new Database();
$database->query = $insert;
$database->mysql();

And I would call it the exact same way on page 3, but the string on page 3 is different of course. So, how should I go about this? I am totally stumped. I mean of course putting the $insert or $query would work inside the class itself, but that would require for the insert statement to be the same both times, and it's not.

Thanks for letting me know about PDO. I'll read up on that. Would PDO really help in choosing different queries when calling your DB from a class though?

I had the whole thing working before I put it in a class, and I had my connection strings in both pages, but then I thought it would be best to use the DRY method, so that's why I decided to write the class to begin with. It connects fine, just doesn't run the query.

laserlight

jmbrink26 wrote:
I could just write a query for them that only includes the data from the first part of the form.

That makes sense to me. With just one table, you insert only the first part's data, leaving the second part's data NULL so that after the second part is submitted you can update them.

jmbrink26 wrote:
Would PDO really help in choosing different queries when calling your DB from a class though?

Yes and no. Yes, because the common interface means that you only have to match the SQL statement with the database vendor, assuming that the SQL statement cannot be made generic enough; no, because you might actually need to make this matching instead of having it done transparently for you as with a database abstraction layer. In other words, with the PDO extension, sometimes you would need to implement the database abstraction yourself.

Weedpacket

jmbrink26 wrote:
Would PDO really help in choosing different queries when calling your DB from a class though?

I was actually suggesting using PDO instead of hand-tooling your own class to do the donkey-work.

Your own database object would then be able to concern itself with the task of turning submitted form data into database statements (and so hide from the rest of the application the question of whether the form data is recorded in one table or two or fifteen).

jmbrink26

Thanks for the clarification on PDO. I'll play around with that.

So I guess my question in general terms is: how to assign a value of a public variable to the value of a private local variable on any given page.

So obviously going about it like

$database->query = $insert;

doesn't work.

So, what is the best way to go about that?

Weedpacket

If setting and getting the value ad lib wouldn't break anything (i.e., there are no dependencies or conditions that have to be satisfied by any value being added) then you might as well make the property public.

An alternative would be to write public methods that do the getting and setting for you.

And an alternative to that would be to use [man]get[/man] and [man]set[/man] methods to construct an interface that stands between the private properties and public appearances.

jmbrink26

So if I understand _set properly, I would write something like this into my class:

public function _set($query, $insert) {
$this->query = $insert;
}

And that would take on the value of whatever the value of $insert is at that time, right?

Weedpacket

$this->$query

but you wouldn't be gaining much because you can write $object->some_brand_new_never_seen_before_property = "foo"; already, which this would be equivalent to. Even though it is poor practice.