So ive been trying to figure out a way to create a MySQLi CRUD class that i can recycle in my work. Unfortunately the purpose is completely defeated if you arent able to use prepared statements in it. And everything ive tried hasn't worked. Does anyone have any advise or examples that i could use to write it?
Stumped on creating a mysqli CRUD class..
I had a pretty involved discussion with Jazz Snob about DAOs and DTOs which sort of touches upon this. I think what you are after is a noble cause -- it really sucks writing PHP from scratch sometimes.
The approach basically involves creating a data object class which has a class variable for each field in some table. Then you create a mapper class which can take a given data object class and dynamically create SQL statements or prepared statements to create/update/delete things.
Interesting post. Ive been looking at other methods of accomplishing it including using PDO instead of mysqli. Primarily because PDO allows you to insert prepared statements in an array. The only problem is that after researching the stability of PDO ive seen some blogs harping on how it was the biggest mistake in php. Does anyone have any first hand knowledge on PDO indicating any possible security risks or faults in the programming?
zypher11;10954283 wrote:Primarily because PDO allows you to insert prepared statements in an array.
Not sure what you mean. Got a code sample?
zypher11;10954283 wrote:The only problem is that after researching the stability of PDO ive seen some blogs harping on how it was the biggest mistake in php. Does anyone have any first hand knowledge on PDO indicating any possible security risks or faults in the programming?
I still haven't really gotten very deep into DB abstraction. Having read this rant, I'm starting to think it's probably important to examine your reasons for wanting a db abstraction layer. I'm not entirely convinced they are always necessary because although there is such a thing as standardized SQL, the various DBMS' all support slightly different language features and you may find yourself having to change all your SQL unless you are VERY VERY careful when building your SQL statements.
I have not used PDO nor read much about it so I can't comment on security risks.
I have been thinking it would be nice to write a PHP script that could build a DataObject class for an arbitrary table based on the output of an EXPLAIN statement. One could then create a DataMapper class to manipulate any of these auto-generated DataObject classes to do all the crud you need as well as some validation. Writing this sort of thing for any arbitrary DBMS sounds hard, but writing it once for MySQL sounds very do-able.
Prepared statements in PDO using an array looks like this:
$sql = "SELECT * FROM table where id = ? AND id2 = ?";
$params("param1", "param2"); //replaces the ?s
$this->mysql = new PDO("....");
$stmt = $this->mysql->prepare($sql);
$stmt->execute($params);
makes it a bit easier to implement into a class as you would imagine instead of having to count the variables and adjust the sql accordingly and using prepared statements adds an additional layer to protect against injections.
I see no reason why one couldn't create a class for each table for which you need CRUD and define some class vars -- one for each column in the table and then auto-generate the $params array and also the $sql. The functions [man]get_class_vars[/man] and [man]get_object_vars[/man] could make this quite easy I think.
Yes that would be relatively easy to do. However i wanted to take it a step further and make the class more portable. Was hoping to come up with a recyclable class so i could submit, prepare, execute and fetch a statement with one call to the object regardless of the environment or database. So far PDO looks like it would be the most viable but am still trying to find a workaround to accomplish it with mysqli.
I'm not sure I see the obstacle to portability here as long as you generate SQL that is compatible with any DBMS.
Ya with a standard method of submitting a query it would be easy. But the problem lies in prepared statements which is what i want to use for the added security. Prepared statements are run as such:
$mysqli = new mysqli(......);
$sql = "SELECT id, username, password FROM users WHERE username = ? AND password = ?";
$stmt = $mysqli->stmt_init();
if($stmt->prepare($sql)){
$stmt->bind_param('ss', $username, $password); //replaces the ?s
$stmt->execute();
$stmt->bind_result($id, $username, $password);
while($stmt->fetch()){
echo "$id, $username, $password<br />";
}
$stmt->close();
}
a relatively long process to do a single query which is why i wanted to modularize it to allow me to run it in a single function call. it's the "how do i declare all the variables and tell it which ones are for the params and which ones are for the results" that's putting my head through a ringer lol. If i spent more than 20 min on it im sure i could figure it out but wanted to see if anyone had already done it that could give me some pointers
zypher11 wrote:how do i declare all the variables and tell it which ones are for the params and which ones are for the results
Brainstorming an idea here...
Send the function two parameters - $params, $results. Both are an array of variables (perhaps $results should be passed-by-reference? makes sense to me..). You could use something like [man]gettype/man to determine the type of data each $params variable contains when binding them.
You know, about 6 seconds before you posted that, i started thinking about passing 2 arrays just like that lol.. think after work i might give that a shot and see how it works out.
Well to me the problem is always that I'm getting put from a <form> and I have to do this tedious mapping between form input names and the fields in my db. How do we expect this amazing class to function?
I've always hoped for something that validates user input as automatically as possible. I realize that I can't expect a DBMS to know that I want to put a valid email address in a VARCHAR field, but I can expect an automatic code generator to know that INT fields have no alpha chars in them.
I'm sort of imagining a system wherein I can create a new object that corresponds to the table I want and just feed it $GET or $POST (or some subarray of $GET or $POST) and the constructor will take care to extract all its instance var values from the supplied array.
Then I can use a data mapper which knows how to handle all the CRUD.
Suppose I have a table called my_table, then I could do omething like this:
require 'classes/MyTable.php'; // this php file could be auto-generated from an EXPLAIN statement and is a trivial class with one class var for each table column
$mt = new MyTable($_POST); // could this auto-validate? Could I hang some custom validation on it?
require 'classes/Mapper.php'; // a carefully constructed class to handle DB access in a DBMS-agonostic way
$mapper = new Mapper(DB_HOST, DB_USER, DB_PASS, DB_NAME);
// then one of these:
$mapper->create($mt);
$mapper->update($mt);
$mapper->delete($mt);
OH...almost forgot...to fetch an object, the mapper class could have a fetch function:
$mt = mapper->fetch('my_table', 'WHERE id=23');
or something like that...
a class like that wouldnt be too incredibly difficult to write. Im trying to write a relatively long class that would pretty much handle any possible situation with ease.
To accomplish what i 'think' your trying to talk about you could probably just submit an array that looks similar to this:
$table = array(
"row1" => array(
"value for row 1 col 1",
"value row 1 col 2"
ect...
),
"row2" => array(
"value for row 2 col 1",
"value row 2 col 2"
ect...
),
"row3" => array(
"value for row 3 col 1",
"value row 3 col 2"
ect...
)
);
then use a foreach($table) loop to submit them all. Well see what i can come up with when i have a little time on my hands
8 years later
- Edited
It shouldn't be that difficult. To create CRUD in PHP with prepared statement, first select the data from MySQL database using single query execution method. Now, write function for update and delete query using prepared statement.
Links removed by Site Administrator so it doesn't look like you're spamming us. Please don't post them again.
NOTE that this thread is Eight YEARS OLD...
Steve_R_Jones locked the discussion.