Databases
StackOverflow2013
Postsdbo
POcodeigniter join multiple rows where
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POcodeigniter join multiple rows where
    primarykey
    data
    text
    <p>I'm trying to code a search function for a site written on CodeIgniter but have trouble with the join statement in the model. </p> <p>A controller passes an array of search criteria to the model function: get_sales($search_criteria); </p> <pre><code>function get_sales($search_criterie){ $this-&gt;db-&gt;join('sales_prices', 'sales_prices.sale_id = sales.sale_id', 'left'); $this-&gt;db-&gt;join('sales_sizes', 'sales_sizes.sale_id = sales.sale_id', 'left'); $query = $this-&gt;db-&gt;get_where('sales', $search_criterie); $sale_data = $query-&gt;result_array(); } </code></pre> <p>I'm running into two problems:</p> <p>1) I need a WHERE clause in the sales_sizes join. Right now it joins sales_sizes.sale_id ON sales.sale_id but I want to be able to filter by size via the $search_criteria array. How can I do this?</p> <p>2) Every sales usually has multiple sizes and I want the output of the query formatted like:</p> <pre><code>Array ( [sale_id] =&gt; 1 [sale_category] =&gt; shoes [sale_price] =&gt; 29.99 [sale_sizes] =&gt; Array ( [0] =&gt; 10 [1] =&gt; 10.5 [2] =&gt; 11 ) ) </code></pre> <p>I've tried some foreach loops to format the output but can't get it to work. Hope somebody can help.</p> <p>Update:</p> <p>How do I process the following query result into a format like the one above?</p> <pre><code>Array ( [0] =&gt; Array ( [sale_id] =&gt; 1 [sale_category] =&gt; shoes [sale_price] =&gt; 29.99 [sale_size] =&gt; 10 ) [1] =&gt; Array ( [sale_id] =&gt; 1 [sale_category] =&gt; shoes [sale_price] =&gt; 29.99 [sale_size] =&gt; 10.5 ) [2] =&gt; Array ( [sale_id] =&gt; 1 [sale_category] =&gt; shoes [sale_price] =&gt; 29.99 [sale_size] =&gt; 11 ) ) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. US2083
    1. US2083
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COHave you tried grouping by sales_sizes?
      singulars
      1. POcodeigniter join multiple rows where
      1. USWill Sampson
    2. COyes, didn't work...
      singulars
      1. POcodeigniter join multiple rows where
      1. US2083
    3. COI think in order to return a number of sizes for each sale you would actually have to group by sale_id, sale_category and sale_price. This however would just return a number of records all with different sizes but the same other columns. In order to get the format of the array you want you would have to do some post processing and throw them into an array based on the returned results.
      singulars
      1. POcodeigniter join multiple rows where
      1. USWill Sampson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload