How to query and fetch


#1

Hello.

version: 1.5.9

Unless it would be a security vulnerability, recommend adding a tutorial in wiki on a brief overview of IPs style of querying/fetching. It’s open source so I dont think it would be a risk. I’ve studied multiple scrips within IP to get a feel and learned how to query but now i need to learn how to fetch based on $this->dbquery method.

The following returns one result instead of the 5 limit. When i build my own DB connection and query it in my own style it works but I’d love to keep all add-on stuff organic. I’m used to using while($row = $bcSql->fetch()) { method after the query but i see IP uses the foreach method mostly.

As you can see, this is our christmas card list. :sunglasses:

//Top Customers
	$bcSql = $this->db->query("SELECT ip_clients.client_name, COUNT(*) AS Inv, SUM(ip_invoice_amounts.invoice_total) AS TtlSales
				FROM ip_invoices, ip_clients, ip_invoice_amounts
				WHERE ip_invoices.client_id=ip_clients.client_id AND ip_invoices.invoice_id=ip_invoice_amounts.invoice_id
				GROUP BY client_name  
				ORDER BY Inv  DESC, TtlSales DESC
				LIMIT 5");
				
		$topCust = '';
		$row = $bcSql->row();
			$topCust .= '<tr>
							<td>'.$row->client_name.'</td>
							<td>'.number_format($row->Inv).'	Invoices   |  $'.number_format($row->TtlSales, 2).' Sales
							</td>
						</tr>';

I also noticed IP has the following methods:

  • $this->db->select*
  • $this->db->get*
  • $this->db->where*
  • $this->db->join*

Can a simple tutorial with a couple simple examples for each be created for us rookies?

Also, I saw a post about adding modules where it was implied Composer and other code machines are the preferred way of coding. I understand the conveniences of composer but should we have to use that vs. manual page flipping hand writing the code? I tried learning composer before but it seems I’m more aware of what’s going on and learn more when i manually code.


#2

I will suggest you study the models first. The models hides away a lot of the database interaction, so that you do not have to submit a full query like the one you supplied above.

For example, a simple model is the file
application/modules/payments/models/Mdl_payment_logs.php

While it may seem like a lot of functionality is not there, the class borrows the functionality to select, save, delete, edit etc from its parent class Response_Model, which in turns borrows from the Code Igniter models.


#3

Thank you Crafter. I have now learned some more about classes (a weak area for me). I have successfully gotten a query to work through a class. Can you please look at it and tell me if there is a better way to streamline the call of it?

model function

public function phonecnt(){
		$qryPhone = $this->db->query("SELECT COUNT(client_id) AS Cnt
				FROM `ip_clients`
				WHERE client_phone = '' AND client_name != 'vacant'");
		$cntPhone = $qryPhone->row();
		echo $cntPhone->Cnt;
	}

view callback (do i really need the ‘new class()’ part of the call)

 <?php $cnt = new Mdl_Market(); $cnt->phonecnt();?>

#4

Well done.

You don’t need the new()
The model classes are refrenced as in the following example.

   $this->load->model('mymodule/mdl_market');
            :
  $cnt => $this->mdl_market->get_by_id($invoice_id),

#5

Thanks but…assuming yours was purely example and not completely relative to my query since you tossed in an invoice variable, I got errors trying to translate it. I’m completely ignorant when it comes to classes.

from what i gather, classes have to do with objects and all associated features of the objects (i.e. clients, invoices, quotes). My query is not a feature of the clients object…its a count so in my mind its a compilation of features. I event looked up and read up on code igniter tutorials. seems easy but no results when i use it.

with my example, there is no id associated with the count return. so, in your example, what is the ‘get by id’ method about? how does it call my function (method)? The function will echo a number (count) if successfully called. The only way i have successfully called it is by using the new().

also, I still cannot echo more than one row of several from a query. The foreach method baffles me in IP. I see variables come out of nowhere such as foreach ($payments as $payment)…$payments is no where to be found in any model or controller. I thought I figured it out by reading up on codeignighter but using their examples, the methods are not defined in IP.

Any other ideas on where to study? Otherwise, i find it easier to revert back to basic PHP and just use an include dbconfig file to run simple queries/whiles/fetches/etc. I want to learn but cant seem to find any relative examples to learn from.

Thanks.


#6

It was an example and I make it clear in my post. The important thing is for you to understand how models are loaded, which the example shows.

For you, you would have something like :

  $cnt => $this->mdl_market->phonecnt();

#7

Crafter, thank you for your patience…perseverence paid off. I watched a couple youtube tuts on codeigniter and learned a few more things. I am now passing great things from my modles through the controller to the view with keys so I can now do the foreach method.

Can I put a small wiki together on this for other future rookie codeigniter users? All i need is the criteria for what goes into a wiki and will put it together. can PM it to you unless other delivery methods are preferred.

my success story
The model

public function test(){
		$qry = $this->db->select("client_id, client_name, client_phone, client_email");
		$qry = $this->db->from("ip_clients");
			$where = "client_phone = '' OR client_email = ''";
		$qry = $this->db->where($where);
		$qry = $this->db->get();
		return $qry->result();

The controller

public function contact()
    {
        $this->load->model('clients/mdl_market');
		$data["records"] = $this->mdl_market->test();
        $this->layout->buffer('content', 'clients/contact', $data)->render();
    }

The view

<div class="col-xs-12">
			 <h1>Customers with missing Phone # or Email</h1>
			 <?php 
			 foreach ($records AS $rec){
				 echo $rec->client_id. '--'. $rec->client_name. '--'. $rec->client_phone. '--'. $rec->client_email. '<br>';
			 }
			 
			 ?>
		</div>