combine functions ?

[eluser]zztemp[/eluser]
Hi,

So its been i while since ive coded, i restarted. Creating some small order tool.
The language you'll see is dutch. The function bekijkDetails() gets al the info of an order.
But i want to retrieve all the comments posted on an order aswell, and i would like to do that in the same function. How can i do this?

Code:

public function bekijkDetails()
    {
      $bestellingsid = $this->uri->segment(3);
        
      $sql = "SELECT bestelling_id,bestelling_besteller,bestelling_materiaal,bestelling_status,bestelling_datum,bestelling_bestemming,bestelling_opmerking,
      bestelling_email FROM tbl_bestelling WHERE bestelling_id = '".$bestellingsid."'";
      
      $query = $this->db->query($sql);  
             if($query->num_rows() > 0){
                    foreach ($query->result_array() as $row) {
                            $id = $row['bestelling_id'];
                            $besteller = $row['bestelling_besteller'];
                            $bestemming = $row['bestelling_bestemming'];
                            ...
                       $data = array(
                                'id' => $id,
                                'besteller' => $besteller,
                                'bestemming' => $bestemming,
                                .....
                        );  
                        return $data;  
                    }                        
                }        
    }

Following is the function to retrieve the comments on an order.
I know i could do it in one sql command, but that it only retrieves 1 comment
(cause of the for each)

Code:

public function HaalBerichtOp(){
    $sql = "SELECT comments_comment FROM tbl_comments WHERE comments_bestellingid = '".$bestellingsid."'";
            
            $query = $this->db->query($sql);    
            if($query->num_rows() > 0){
                foreach ($query->result_array() as $row) {
                    $comment = $row['comments_comment'];
                    $data = array(
                        'comment' => $comment,  
                    );      
                }
                
            }    return $data;    
    }

I hope someone could clarify this for me.
If you would like to see how it works:

example

hit the application logo to view the details of an order

[eluser]neilmcgann[/eluser]
Nooooo! SQL injection alert! Passing an unfiltered part of a URL string into a SQL query is disastrous.

If you must build the SQL this way use a parameterised query, or use active record which does escaping for you.

To address the main question - do a join between the tables on the id to fetch all the comments in one db query.

[eluser]zztemp[/eluser]
I know that there is possibility for SQL injection.
THanks for mentioning it. But i know the 20 people that will be using this tool (on our intranet)
So i'm not concerned with it. But you are right, it is an issue.

I tried the join, but i couln't get it to werk properly so i started looking for other possibilities

[eluser]zztemp[/eluser]
So i got it to work but sadly, with "old" php :

Code:

public function HaalBerichtOp(){
            $id = $this->uri->segment(3);
            $sql = "SELECT comments_comment FROM tbl_comments WHERE comments_bestellingid = '".$id."' order by comments_id DESC";
            $query = mysql_query($sql) or die('<div>'.mysql_error().'</div>');
            $i=0;
            while($line= mysql_fetch_array($query,MYSQL_NUM)) {
               $data[$i] = $line[0];
            }
            return $data;          
    }

how do i do this in codeigniter?
Would be helpfull in furter functions

so i re-wrote it to:

Code:

public function HaalBerichtOp(){
            $id = $this->uri->segment(3);
            $sql = "SELECT comments_comment FROM tbl_comments WHERE comments_bestellingid = '".$id."' order by comments_id DESC";
            $query = $this->db->query($sql);    
            $i = 0;                                
            foreach ($query->result_array() as $row) {
                $data[] = $row['comments_comment'];
                $i++;
            }  
            return $data;  
            print_r($data);                      
    }

I just post this, maybe other people (beginners) will find it usefull someday

[eluser]neilmcgann[/eluser]
I couldn't resist replying. The last function could be re-coded as:

Code:

public function HaalBerichtOp()
{
$id = $this->uri->segment(3);

//create parameterised query where parameters replaced by question marks
$sql = "SELECT comments_comment FROM tbl_comments WHERE comments_bestellingid = ? order by comments_id DESC";
//execute query with 1 parameter
$query = $this->db->query($sql,array($id));
    
  //get results as numerically keyed array
$data = $query->result_array();

//return $data (array will be empty if there is no data returned from query)
return $data;
}

Or in active record as:

Code:

public function HaalBerichtOp()
{
$id = $this->uri->segment(3);

$this->db->select('comments_comment');
$this->db->where('comments_bestellingid',$id); //$id is auto-escaped

$query = $this->db->get('tbl_comments');

$data = $query->result_array();

return $data;
}

I'm presuming that you don't actually need to loop through the returned results for any reason, so I just send them back in the same numerically-keyed array format as result_array returns them. You can make the active record version even more concise by using get_where or chaining the sections together to remove most of the $this->db parts.

Thinking about the overall problem, I'd probably not bother with the join as it would make it messier to handle the results. I'd just retrieve the product and then do something like:

Code:

$data['comments'] = HaalBerichtOp();

...to add an array of comments in the product data assembled in $data.

Or something like that

[eluser]zztemp[/eluser]
Dear neilmcgann,

Why wouldn't you reply? I'm thankfull that you did, i didn't know that trick with the questionmark. Adding to this is the fact that i only created one or two small projects in CI before i "stopped coding". So i don't realy know my way around CI that well.

As for your solution, i figured that out myself this is what i did (i'll rewrite my sql after this post ) :

i have two functions in my controller:
This one is to get my messages:

Code:

public function HaalBerichtOp(){
  $this->load->model('M_bestellen');
  $data = $this->M_bestellen->HaalBerichtOp();  
  return $data;
  //print_r($data);
}

and than this one is to get the details of the order + the messages.

Code:

public function bekijkDetails(){
        $id = $this->uri->segment(1);
        $this->load->model('M_bestellen');
        $data = $this->M_bestellen->bekijkDetails();
        $comment = $this->HaalBerichtOp($id);
        $data['comment'] = $comment;
        $data['title'] = "Detailweergave";
        $data['views'] = array('v_detailsbestelling');
        $this->load->view('v_template',$data);    
    }

And in my view (v_detailsbestelling) i loop true it:

Code:

for($i=0; $i<count($comment);$i++){
echo $comment[$i]."<br/>";
}

The only problem with this is, i was thinking of refreshing my div with jquery if someone added a comment to an order. But now i'll have to refresh all the details + the comments.
So it's not ideal i guess.

Thanks for the reply!