mysql_result and mysql_fetch_assoc as method
I'm attempting to do some OO programming in PHP and I want to create a DB class with a database connector method, along with a query method.
Here's what I've come up with:
<?php
class DB
{
var $hostname = 'example.com';
var $db = 'Beta';
var $username = 'myusername';
var $password = 'mypassword';
function connect()
{
mysql_connect($this->hostname, $this->username, $this->password);
mysql_select_db($this->db);
}
function query($sql)
{
return mysql_fetch_assoc(mysql_query($sql));
}
}
$db = new DB;
$db->connect();
while($row = $db->query(\"SELECT * FROM User WHERE UserID = 9 OR UserID = 4\"))
{
print $row['Logon'];
}
?>When I run that code, I get "jaredjjaredjjaredjjaredj..." (which is the Logon where the UserID is 4 repeating over and over again). What's up with that?
Does anyone have a good example of a database object that I can study?
kaykays posted this at 11:31 — 6th November 2007.
He has: 17 posts
Joined: Oct 2007
IMO, your logic is not correct. You are currently repeatedly calling the function to run the query.
I think you require three functions:
1) Connect - this is ok
2) Query - Just to run the query
3) Fetch - To return the results.
Connect and Query should be run once before the while loop.
Fetch should be run as the while condition.
teammatt3 posted this at 15:18 — 6th November 2007.
He has: 2,102 posts
Joined: Sep 2003
Yeah, realized that. For example what if the query is just an update query and I don't need to fetch anything? When I was testing I was just running select queries, I didn't even think about updating, inserting or deleting.
Any ideas of what else should go in a DB object? I should look through some CMSs and see what they do...
I probably need a result counter with mysql_num_rows and one for mysql_affected_rows too.
pr0gr4mm3r posted this at 18:42 — 6th November 2007.
He has: 1,502 posts
Joined: Sep 2006
I've been working on something like this as well. I've been working on it as a side note, so I haven't gotten that far.
I figure to have one class per table in a database and have each table class inherit a main database class. The main database class can handle global stuff like connections and also methods for raw queries and such. The table can have more table-specific methods. For example, if I have a table of registered site users, I would have table methods like AddUser(), RemoveUser(), changePassword(), etc. My goal is to completely remove raw SQL queries from my main code. Replacing raw queries with custom methods like that allows you to tweak your SQL queries for the whole website and even make it relatively easy to migrate to a different SQL database system like PrestegeSQL or something similar if needed.
I can post actual code of what I am talking about once I get it written.
JeevesBond posted this at 19:13 — 6th November 2007.
He has: 3,956 posts
Joined: Jun 2002
<?phpwhile($row = $db->query(\"SELECT * FROM User WHERE UserID = 9 OR UserID = 4\"))
?>
The problem is in this line. Look at it closely and think what it's doing (it might help to cross your legs and make Yoga-like Ommmmmmmm noises at this point).
It's constantly running the same query over and over, without an end. You need to get the result from a mysql_query call and pass it to something like mysql_fetch_assoc().
Being a Drupal fan-boy I'll point you at their solution, they have a thin database abstraction layer (it's not done with objects, but it is object orientated-ish).
a Padded Cell our articles site!
teammatt3 posted this at 15:47 — 9th November 2007.
He has: 2,102 posts
Joined: Sep 2003
Yeah, that makes sense. I thought because I returned the results of mysql_fetch_assoc it would use that, but each time through the loop it re-runs the query, producing the exact same associative array each time. Got it.
I'd like that, because I really don't know what I'm doing
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.