Im new to stackoverflow. So hopefully you understand my question
At the moment I write my MySQLI calls like this - but depends what I'm trying to achieve.
function getArticles($dbh) {
$query = "SELECT article_id,article_name,article_text FROM Articles";
$result = mysqli_query($dbh,$query);
if(!mysqli_errno($dbh)) {
if(mysqli_num_rows($query) > 0) {
$articles = array();
while($rows = mysqli_fetch_object($query) {
$articles[] = $rows;
}
}
mysqli_free_result($dbh)
} else {
echo mysqli_error($dbh);
}
return ($articles);
}
Then I fetch them and display using a foreach() in my calling page. Can I make my MySQL calls better in any way?, Or how would you write your Mysql calls.
I would choose the OO way too, but apart from that, I would wrap calling the query in a separate class or function. You can then write a function that takes an SQL query and an array of parameters as input, and returns an array with results or an error code or error object (or array). That way, functions like getArticles, of which you probably have dozens, contain only a single line of code and you don't have to implement the loop and the error handling each time.
Something like this:
function executeQuery($dbh, $sql)
{
$result = mysqli_query($dbh, $sql);
if ($result !== false)
{
while($row = mysqli_fetch_object($query) {
$data[] = $row;
}
return $data;
}
return false;
// Or maybe return some error code or error information?
// Maybe throw an exception?
// You could return an array with info, but then you'd have to check
// if the resulting array contains data or error information...
}
function getArticles($dbh) {
return executeQuery(
$dbh,
"SELECT article_id,article_name,article_text FROM Articles");
}
See how it pulls all logic from getArticles and generalizes it in executeQuery?
There are a million different ways to do it, so it is really a matter of taste. The method you use is a pretty standard "PHP for beginners" way of doing things, but many would consider it cumbersome, especially as the way you have it you would need to rewrite the query function for every SQL query you do. Most people would want to abstract things so that all the different things that your code does are covered within a simple resuseable function.
At it's simplest, that could be a matter of changing your code to something like this:
function query($dbh, $query) {
if($resource = mysqli_query($dbh,$query)) {
while($rows = mysqli_fetch_object($resource) {
$result[] = $rows;
}
}
} else {
echo mysqli_error($dbh);
}
return ($result);
}
That's not much of an improvement to be honest, but the point is that now you have a global function that you can reuse all across the site with as few as two lines of code like this:
$sql = "SELECT * FROM MYTABLE";
$result = query($dbh,$sql);
It would be much better done as an object that also instantiates the database connection, etc, so that you call one function or probably class that does all the database connection and query parsing stuff.
I kinda-sorta depends. For one-off scripts whether you go with the OO or the procedural API the end result is pretty much the same either way: in one case you use $dbh->method(...) in the other you get mysqli_method($dbh, ...). Syntactic sugar is all it is.
On slightly bigger roll-my-own SQL code I tend to resort to my current version of the re-invented SQL<-> PHP wheel which is as follows:
The result of step 3 is that instead of doing SQL you do lazy-loading of resources. You can construct objects of the query type and loading (executing the query) may be deferred to some later point in the overall control flow of the PHP code. That property helps to cleanly separate the data from the presentation & control flow. It also makes it easier to do things like proper content negotiation transparently as that would amount to merely plugging in a different version of your presentation objects.