Making simple allowed array in PHP? (CodeIgniter)

Question

I have some simple function to collect allowed array, but something is not ok, can somebody help me? Here is my code

public function getAllbyLink($table, $what, $url)
{
   $link=mysql_real_escape_string($url);
   $query = $this->db->query("SELECT * FROM ".$table." WHERE ".$what." = '{$link}' LIMIT 0 , 1");

    if ($query->num_rows() > 0)
    {
        return $query->result();
    }
        else redirect('');
}

Answer 1

Please read something about MVC pattern, question is clearly pointed on how to write a Model.

consider using this function

public function getTable($table, $where = array(), $select = '*', $order_by = '', $limit = '', $offset = '') {

    if ($order_by !== '' && $order_by != 'RANDOM') $this->db->order_by($order_by);
    if ($order_by == 'RANDOM') $this->db->order_by('id', 'RANDOM');
    if ($limit !== '') $this->db->limit($limit, $offset);
    $this->db->select($select);
    $q = $this->db->get_where($table, $where);

    return ($q->num_rows() > 0) ? $q->result() : FALSE;

}

for your purpose call the function like this:

getTable($talbe, array('what' => $link)); 
//returns FALSE if no data are selected, 
//or returns object with data, 

if you wish return array instead replace $q->result() with $q->array_result()

Please note that active record auto escapes.

---

After comments:

comment-1, you can simplify that function easily just delete what you do not need, for example

public function getTable2($table, $where = array(),  $limit = '', $offset = '') {

    if ($limit !== '') $this->db->limit($limit, $offset);
    $q = $this->db->get_where($table, $where);

    return ($q->num_rows() > 0) ? $q->result() : FALSE;

}

comment-2,when there is no data use this if-else statement

if (!$my_data = getTable2('table', array('where' => $link))) {
    //there is some DATA to work with
    echo "<pre>";
    var_dump($my_data);
    echo "</pre>";

} else {
    //no DATA do redirect or tell user that there is no DATA
    redirect(); //redirect to default_controller
}

comment-3, no comment;

comment-4, It also allows for safer queries, since the values are escaped automatically by the system. from this source. And another SO question about active record providing exact answer you are seeking.

Answer 2

My understanding of your code is:

• Read all rows from table
• Check if linkurl is in the list
• If so, return a random row for that value
• Else, redirect.

In this case, try this:

public function getAllbyLink($table,$url,$what)
{
    $query = $this->db->query("
          SELECT *
          FROM `".$table."`
          WHERE `".$what."` = '".mysql_real_escape_string($linkurl)."'
          ORDER BY RAND()
          LIMIT 1
    ");
    if( !$query) return redirect('');
    $result = $query->result();
    if( !$result) return redirect('');
    return $result;
}