Looping through a DB for all information

Looping through a DB for all information - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Looping through a DB for all information (/showthread.php?tid=51344)

Pages: 1 2

Looping through a DB for all information - El Forum - 04-30-2012

[eluser]codemonkey[/eluser]
Hello, I need some help understanding how I would query a database table (using AR) and provide row counts for each different type of certain data.

Example:

A table named Toys has a column for id, name, type, and condition.

Types range from dolls, cars, and army men

How can I loop through (maybe loop through is the wrong word) and display how many rows contain each toy type excluding a condition of old.

I was thinking I would need to do multiple queries but I'm hoping I can reduce the amount with just one query and sort through the data.

Thanks

Looping through a DB for all information - El Forum - 04-30-2012

[eluser]NotDior[/eluser]
If your just looking for the number of types within your toys table you'll want to look at group by.

something like this:

Code:
SELECT type,count(id) as total FROM toys GROUP by type

That's sort of the SQL you'll need and you should be able to extract to AR from there.

Looping through a DB for all information - El Forum - 04-30-2012

[eluser]codemonkey[/eluser]
Then for each Type I can assign them to a variable? So if I wanted to build a table or something that had a row for each type and a column for the amount, how would that work?

Thanks

Looping through a DB for all information - El Forum - 04-30-2012

[eluser]CroNiX[/eluser]
The results would be:

type
total

type
total

So, yes, you can easily make a table from that by looping over the results or directly by using the HTML Table Class library in the user guide.

Looping through a DB for all information - El Forum - 04-30-2012

[eluser]codemonkey[/eluser]
I tried everything I could think of, I'm just spinning my wheels replacing random text and hoping for a good result.

I do not know how to convert your example to active records or how to implement as is into a working form.
I have no idea how to take the output and format it into something I can use.

I don't mind reading, I feel I have read the CI active records info a million times, can you provide a source of something that will help me understand?

I feel I should learn instead of asking for you to write the code but I got nothing and I feel like crap admitting it.

Thanks

Looping through a DB for all information - El Forum - 04-30-2012

[eluser]CroNiX[/eluser]

Code:
$data = $this->db

  ->select('type, COUNT(id) AS total')

  ->group_by('type')

  ->get('toys')

  ->result_array();

foreach($data as $d)

{

  echo 'Type: ' . $d['type'] . ', Total: ' . $d['total'] . '<br>';

}

Looping through a DB for all information - El Forum - 04-30-2012

[eluser]codemonkey[/eluser]
Thank you, maybe one day when I am better at this I can lend you a hand.

Is the table class limited to one table per view when using the $this->table->set_heading?

Looping through a DB for all information - El Forum - 04-30-2012

[eluser]InsiteFX[/eluser]
No not if you follow this:

Code:
// view first table

$this->table->generate();

$this->table->clear();

You need to use the clear method before creating a new table...

Looping through a DB for all information - El Forum - 05-01-2012

[eluser]codemonkey[/eluser]
It does not seem to work, the clear

Here is the view that generates the tables

Code:
<div id="stockview">Stock View</div> 

<h2>Stock</h2>

&lt;?php echo $this->table->generate($stockrecords); ?&gt;

&lt;?php $this->table->clear(); ?&gt;

<h2>History</h2>

<div id="table">

&lt;?php echo $this->table->generate($records); ?&gt;

&lt;?php echo $this->pagination->create_links(); ?&gt;

</div>

The table headings for the second table is still being displayed for the first.

Is it because of the variables?

Thanks!

Looping through a DB for all information - El Forum - 05-01-2012

[eluser]InsiteFX[/eluser]
Because you need to set the header for the second table!

Code:
$this->table->clear();

Lets you clear the table heading and row data. If you need to show multiple tables with different data you should to call this function after each table has been generated to empty the previous table information. Example:

Code:
$this->load->library('table');

$this->table->set_heading('Name', 'Color', 'Size');

$this->table->add_row('Fred', 'Blue', 'Small');

$this->table->add_row('Mary', 'Red', 'Large');

$this->table->add_row('John', 'Green', 'Medium');

echo $this->table->generate();

$this->table->clear();

$this->table->set_heading('Name', 'Day', 'Delivery');

$this->table->add_row('Fred', 'Wednesday', 'Express');

$this->table->add_row('Mary', 'Monday', 'Air');

$this->table->add_row('John', 'Saturday', 'Overnight');

echo $this->table->generate();