Programming CRUD functions in PHP

Question

I'm writing some CRUD functions in PHP (not object oriented), and I ran into a little trouble. The function works just fine as is. Please review and read ahead for the actual issue I'm having. Here's the function:

<?php
function db_s(
            $table,
            $condition = array(),
            $limit = '',
            $order = array(),
            $group = array(),
            $return_query_string = false
) {
    $sql = '
        SELECT
            *
        FROM
            `'. $table .'`
    ';
    if (is_array($condition)) {
        if (count($condition) > 1) {
            $sql .= '
                    WHERE
                ';
            $sql .= implode(' AND ', $condition);
        }
        elseif (count($condition > 0)) { $sql .= ' WHERE '. $condition[0] .' '; }
    }
    elseif ($condition != '' && is_string($condition)) { $sql .= ' WHERE '. $condition .' '; }

    if (!empty($group)) {
        $sql .= '
                GROUP BY
            ';
        $sql .= implode(', ', $group);
    }
    if (!empty($order)) {
        $sql .= '
                ORDER BY
            ';
        $sql .= implode(', ', $order);
    }
    if ($limit != '') {
        $sql .= '
                LIMIT
                    '. $limit .'
            ';
    }
    //print '<pre>'. $sql .'</pre>';
    $query = mysql_query($sql) OR die(mysql_error() .'<p><pre>'. $sql .'</pre></p>');
    if ($return_query_string) { return $sql; }
    else { return $query; }
} // end db_s() function
?>

And here are some examples of how you would use it:

<?php
$results = db_s('users', 'active = 1');
while($row = mysql_fetch_assoc($results)) {
    // do cool stuff here
}
?>

---

<?php
$results = db_s('messages', array('user_id = 1512', 'date < "2014-05-01"'), 10);
while($row = mysql_fetch_assoc($results)) {
    // do cool stuff here
}
?>

---

<?php
$results = db_s('messages', array('user_id = 1512', 'date < "2014-05-01"'), 10, 'date DESC');
while($row = mysql_fetch_assoc($results)) {
    // do cool stuff here
}
?>

---

<?php
$results = db_s('posts', 'unread = 1', '', 'date ASC', 'user_id', true);
print '<pre>'; print_r($results); print '</pre>';
?>

All of this works just fine (pending any syntax errors above but the function works fine). The problem is in the way I want to use the function. As you can see above, I must first establish the $results variable. Then I have to put the whole thing into a while loop with $row = mysql_fetch_assoc($results). That seems like too much work for me. In order to really make this function useful to me, I'd really like to rewrite it so I can use it like this:

<?php
while ($row = db_s('messages', array('user_id = 1512', 'date < "2014-05-01"'), 10, 'date DESC')) {
    // do cool stuff here
}
?>

---

Or like this:

<?php
while ($row = db_s('users', 'active = 1')) {
    // do cool stuff here
}
?>

In order to do that, I've tried rewriting the end of the function (the part that actually returns the query), but I can't seem to get it to work correctly. I've tried something like this

    }
    //print '<pre>'. $sql .'</pre>';
    $query = mysql_query($sql) OR die(mysql_error() .'<p><pre>'. $sql .'</pre></p>');
    if ($return_query_string) { return $sql; }
    else { return mysql_fetch_array($query); }  //<--- this line is modified
} // end db_s() function
?>

But it just doesn't work like that. Unfortunately the results are... spotty... when used like this. Often it returns more results than it should, and it only spits out the first result several times... I did rewrite the while() loop so it would use the function db_s() correctly. Can anyone provided me any insight as to why it wouldn't work like this? Or perhaps, how to return the results so I can use the function as I intend to? Any help is greatly appreciated. Thanks in advance!

Answer 1

You need to get on PDO or MySQLI now. The solution would be similar or you could use the *_fetch_all() for those libraries.

For the function return:

$query = mysql_query($sql) OR die(mysql_error() .'<p><pre>'. $sql .'</pre></p>');

if($return_query_string) {
    return $sql;
} else { 
    while($result[] = mysql_fetch_array($query)) {}
    return $result;
}

Then use it like this:

foreach(db_s('users', 'active = 1') as $row) {
    //do cool stuff here
}

I'm not endorsing the function or the code concept, just answering the question.