CodeIgniter join() does not show comma separated values

Question

I am using CodeIgniter and I am facing the problem with join method.

I want services from db_services table by service_id which is also stored in db_orders within the same column name service_id, I have service_id as a primary key in db_services table.

Now user add services by clicking the checkbox of the required services with the help of implode() function which puts the data correctly into database db_orders table under service_id column i.e. 1,2,3,5

Now when I retrieve data from db_orders I get comma-separated value from the database which works fine when I remove this line $this->db->join( $this->db->dbprefix('db_services'), $this->db->dbprefix('db_services').'.service_id = '.$this->db->dbprefix('db_orders').'.service_id' ); within my Order_model -> view_orders() function everything works like a charm but when I add that line it only show one record (first record) for instance if two (2) service_id are inserted so I get only the one which I guess the first number which is inserted during the database.

<?php $services = explode(",", $value->service_id);
foreach ( $services as $service ): ?>
<a href="" class="btn btn-success btn-flat btn-xs"><?php echo $service; ?></a>
<?php endforeach; ?>

That was the problem I have solved but the actual problem is when I use join table method it does not work let me copy-paste my code which helps you to better under the problem well I have mentioned every little details if you need more I can elaborate further.

Order_model -> view_orders()

<?php
public function view_orders()
{
    $this->db->select( '*' );
    $this->db->from( $this->db->dbprefix('db_orders') );
    $this->db->join( $this->db->dbprefix('db_order_status'), $this->db->dbprefix('db_order_status').'.order_status_id = '.$this->db->dbprefix('db_orders').'.order_status_id' );
    $this->db->join( $this->db->dbprefix('db_users'), $this->db->dbprefix('db_users').'.user_id = '.$this->db->dbprefix('db_orders').'.user_id' );
    $this->db->join( $this->db->dbprefix('db_services'), $this->db->dbprefix('db_services').'.service_id = '.$this->db->dbprefix('db_orders').'.service_id' );
    $query = $this->db->get();
    return $query->result();
}
?>

Order Controller -> index()

public function index()
{
    $data['orders'] = $this->order->view_orders();
    $data['title'] = "View All Orders";
    $this->load->template('orders/view_all_orders', $data);
}

orders/view_all_orders.php

<?php
$services = explode(",", $value->service_id);
foreach ( $services as $service ): ?>
<a href="" class="btn btn-success btn-flat btn-xs"><?php echo $service; ?></a>
<?php endforeach; ?>

Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|189.9445) and normalize the schema! — sticky bit, Feb 16 '20 at 17:37
I need to use this because I have a separate table for services `db_services` which store all the related data about services like `price` so I store `services_id` in `db_orders` so I can fetch related data about services. — shaz3e, Feb 16 '20 at 18:15
No you don't. Keeping the service table is not a problem at all in a normalized schema, it's even the opposite. Use a linking table to model the relationship between orders and services. I think you should do some research of how to model and normalize in relational databases. — sticky bit, Feb 16 '20 at 18:24
I guess relational column just works fine is there is something wrong with the code because when I remove one line `$this->db->join( $this->db->dbprefix('db_services'), $this->db->dbprefix('db_services').'.service_id = '.$this->db->dbprefix('db_orders').'.service_id' );` I get the ids but the problem is I cannot get the service name with their id in this case so I have to get the service name and price in this case — shaz3e, Feb 16 '20 at 19:16
Once again and for the last time: You're using the database in a wrong way! The problems you have are just the symptom of that. The reasons is the wrong model. Fix this as mentioned before, then you can simply join the three tables. — sticky bit, Feb 16 '20 at 19:21
Should I create 2 rows for one order with each service_id in a different table, that's what you are saying — shaz3e, Feb 16 '20 at 19:30
Have you read the question I linked in the first comment? You should create a linking table that holds one row for each service of an order by storing the respective order ID and the service ID in a row (in two columns). Not always two rows but as many rows as an order has services. — sticky bit, Feb 16 '20 at 19:45

score 0 · Answer 1 · answered Feb 17 '20 at 15:33

Alright here is the solution I have come with.

tweaked with my function just by remove this line -> //$this->db->join( $this->db->dbprefix('db_services'), $this->db->dbprefix('db_services').'.service_id = '.$this->db->dbprefix('db_orders').'.service_id' );

public function view_orders()
{
  $this->db->select( '*' );
  $this->db->from( $this->db->dbprefix('db_orders') );
  $this->db->join( $this->db->dbprefix('db_order_status'), $this->db->dbprefix('db_order_status').'.order_status_id = '.$this->db->dbprefix('db_orders').'.order_status_id' );
  $this->db->join( $this->db->dbprefix('db_users'), $this->db->dbprefix('db_users').'.user_id = '.$this->db->dbprefix('db_orders').'.user_id' );
  //$this->db->join( $this->db->dbprefix('db_services'), $this->db->dbprefix('db_services').'.service_id = '.$this->db->dbprefix('db_orders').'.service_id' );
  $query = $this->db->get();
  return $query->result();
}

I have created another function under Order_model -> get_services() as below.

public function get_services()
{
    $query = $this->db->get( $this->db->dbprefix('db_services') );
    return $query->result();
}

now my controller looke like this

public function index()
{
  $data['get_services'] = $this->order->get_services(); // added this line to get all services and show it view by respective id
  $data['orders'] = $this->order->view_orders();
  $data['title'] = "View All Orders";
  $this->load->template('orders/view_all_orders', $data);
}

and my view looks like this now.

<?php foreach( $orders as $order ): ?>
  <tr>
    <td><a href="/orders/view/<?php echo $order->order_id; ?>"><?php echo $order->order_id; ?></a></td>
    <td><?php echo $order->status; ?></td>
    <td>
      <a href="/user/view/<?php echo $order->user_id; ?>"><?php echo $order->name;?></a>
    </td>
    <td>
      <?php
        $service_ids = explode(',', $order->service_id);
        foreach ($service_ids as $service_id) {
          foreach ($get_services as $row) {
            if ($service_id == $row->service_id) {
              echo $row->service_name;
            }
          }
        }
      ?>
    </td>
    <td><?php echo gmdate('l jS F, Y',$order->created); ?></td>
    <td>
      <a class="btn btn-xs btn-flat btn-info" href="/orders/view/<?php echo $order->order_id; ?>"><i class="fas fa-eye"></i></a>
      <a class="btn btn-xs btn-flat btn-success" href="/orders/edit/<?php echo $order->order_id; ?>"><i class="fas fa-edit"></i></a>
      <a class="btn btn-xs btn-flat btn-danger" href="/orders/delete/<?php echo $order->order_id; ?>"><i class="fas fa-trash"></i></a>
    </td>
  </tr>
<?php endforeach; ?>

As far as this helped me solve my problem but I am still confused am I following the right direction

CodeIgniter join() does not show comma separated values

1 Answers1