[eluser]Nica[/eluser]
hello,
Need your help, just want to ask how can I insert my mysql query to the codeigniter..?
This is my sample program for model.
Code: <?
class Sales_model extends Model {
function Sales_model()
{
parent::Model();
}
function restaurant_master_getall()
{
$this->load->database();
$query = $this->db->get('restaurant_master');
return $query->result();
}
function restaurant_master_get(){
$this->load->database();
$query = $this->db->query();
return $query->row_array();
}
}
?>
Thanks in advanced, have a great day ahead! 
[eluser]Andy Armstrong[/eluser]
hi it is very easy to insert data on using active records in CI, you read the CI guides Database Class.Anyways,here is my simple codes on inserting data using ACTIVE RECORDS
THIS IS FOR THE MODEL
Code: <?php
class Sales extends CI_Model
{
/*This is to insert data on database*/
public function insert_sale()
{
/*array('field'=>$this->input->post('name of your input form field'))*/
data = array('item_name'=>$this->input->post('item_name_field'),
'item_price'=>$this->input->post('item_price_field'));
$this->db->insert('sales_table',$data);
}
/*Display your current sales you inserted */
public function get_sales()
{
$query= $this->db->get('sales_table');
/*Check if there are rows returned*/
if($query->num_rows()> 0){
/*assign the result in a data array*/
foreach($query->result() as $row)
{
$data[] = $row;
}
return $data;
}
}
}
?>
THIS IS FOR CONTROLLER CLASS
Code: <?php
class My_sales extends CI_Controller
{
public function index()
{
/*fetch the data*/
$this->load->model('sales');
$data['my_sales']=$this->sales->get_sales();
/*pass the data to the views*/
$this->load->view('sales_page',$data);
}
/*this will the event happens, when the user submits form*/
public function process_add_sale()
{
/*call your model class which the SALES class*/
$this->load->model('sales');
/*do the process in inserting refer the method which is the insert_sale() funtion
within your sales model*/
$this->sales->insert_sale();
/*return to the form which index had load the views*/
$this->index();
}
}
?>
THIS IS FOR THE VIEWS
Code: <html>
<head>
<title>MY SALES</title>
</head>
<body>
<?php
/*check if there are rows returned*/
if(isset($my_sales))
{
foreach($my_sales as $row):
echo $row->item_name."-".$row->item_price;
endforeach;
}
?>
</body>
</html>
I hope it answers your question.. but you can read the CI guides or browse the wikis for tutorials..
[eluser]Nica[/eluser]
oh i'm sorry I think you misinterpret my question..
what I mean is I have my sql query like this..
Code: <?php
// db connection
$db = "mds_reports";
if($connect = mysql_connect("172.16.8.32", "mds_reports", "password"))
$connect = mysql_select_db($db);
else die("Unable to connect".mysql_error());
$date_from = $_POST['dfrom'];
$date_to = $_POST['dto'];
?>
<html>
<head>
</head>
<title>Sales Frequency Bracket</title>
<body>
<h1>Sales Per Sales Frequency Bracket</h1>
<form name="fetching" method="POST" action="Sales.php"></p>
<b>Date From:</b> <br><input type="text" name="dfrom" id="dfrom"></p>
<b>Date To:</b> <br><input type="text" name="dto" id="dto"><br /><br /><p>
<input name="submit" type="submit" value="Go">
</form>
<table>
<table border="2"
cellpadding="2"
cellspacing="1"
>
<tr>
<th>Restaurant Code</th>
<th>Restaurant Name</th>
<th>100 & Below</th>
<th>101-200</th>
<th>201-300</th>
<th>301-400</th>
<th>401-500</th>
<th>501-600</th>
<th>601-700</th>
<th>701-800</th>
<th>801-900</th>
<th>901-1000</th>
<th>1001 & Above</th>
<th>Total Gross Sales</th>
</tr>
<?php
if(isset($_POST['submit']))
{
echo " Date select from $date_from to $date_to"."</p>";
$sql = "SELECT restaurant_master.code, restaurant_master.name,
SUM(CASE WHEN mds_orders.GrossTotal < '100' THEN mds_orders.GrossTotal END) AS 'less100',
SUM(CASE WHEN mds_orders.GrossTotal >= '100' AND mds_orders.GrossTotal < '200' THEN mds_orders.GrossTotal END) AS 'less200',
SUM(CASE WHEN mds_orders.GrossTotal >= '200' AND mds_orders.GrossTotal < '300' THEN mds_orders.GrossTotal END) AS 'less300',
SUM(CASE WHEN mds_orders.GrossTotal >= '300' AND mds_orders.GrossTotal < '400' THEN mds_orders.GrossTotal END) AS 'less400',
SUM(CASE WHEN mds_orders.GrossTotal >= '400' AND mds_orders.GrossTotal < '500' THEN mds_orders.GrossTotal END) AS 'less500',
SUM(CASE WHEN mds_orders.GrossTotal >= '500' AND mds_orders.GrossTotal < '600' THEN mds_orders.GrossTotal END) AS 'less600',
SUM(CASE WHEN mds_orders.GrossTotal >= '600' AND mds_orders.GrossTotal < '700' THEN mds_orders.GrossTotal END) AS 'less700',
SUM(CASE WHEN mds_orders.GrossTotal >= '700' AND mds_orders.GrossTotal < '800' THEN mds_orders.GrossTotal END) AS 'less800',
SUM(CASE WHEN mds_orders.GrossTotal >= '800' AND mds_orders.GrossTotal < '900' THEN mds_orders.GrossTotal END) AS 'less900',
SUM(CASE WHEN mds_orders.GrossTotal >= '900' AND mds_orders.GrossTotal < '1000' THEN mds_orders.GrossTotal END) AS 'less1k',
SUM(CASE WHEN mds_orders.GrossTotal >= '1000' THEN mds_orders.GrossTotal END) AS 'gtr1k',
SUM(mds_orders.GrossTotal) AS 'total'
FROM mds_orders
JOIN restaurant_master
ON mds_orders.RestaurantID = restaurant_master.PKID
WHERE mds_orders.OrderDate BETWEEN '".$date_from." 00:00:00' AND '".$date_to." 23:59:59'
GROUP BY restaurant_master.code, restaurant_master.name";
$result = mysql_query($sql)
or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
$i = 0;
if (mysql_num_rows($result) >0) {
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)){
$csv .= $row['code'].",".
$row['name'].",".
$row['less100'].",".
$row['less200'].",".
$row['less300'].",".
$row['less400'].",".
$row['less500'].",".
$row['less600'].",".
$row['less700'].",".
$row['less800'].",".
$row['less900'].",".
$row['less1k'].",".
$row['gtr1k'].",".
$row['total'].","."<br>";
?>
<tr>
<td><?php echo $row['code'];?></td>
<td><?php echo $row['name'];?></td>
<td><?php echo $row['less100'];?></td>
<td><?php echo $row['less200'];?></td>
<td><?php echo $row['less300'];?></td>
<td><?php echo $row['less400'];?></td>
<td><?php echo $row['less500'];?></td>
<td><?php echo $row['less600'];?></td>
<td><?php echo $row['less700'];?></td>
<td><?php echo $row['less800'];?></td>
<td><?php echo $row['less900'];?></td>
<td><?php echo $row['less1k'];?></td>
<td><?php echo $row['gtr1k'];?></td>
<td><?php echo $row['total'];?></td>
</tr>
</tr>
</tr>
<?php
}}}
?>
</form>
</body>
</html>
then the output of this should be the same when I do it to CodeIgniter, how can I do this..?
thank you and sorry again for my mistake.
[eluser]Andy Armstrong[/eluser]
i have updated my post please check the above post
[eluser]Andy Armstrong[/eluser]
i have updated my post, please check above, i will not go reconstructing with your codes, but my sample codes has same approach :-)
[eluser]Nica[/eluser]
Ok I will try it first.,thank you for your response I will post back again if I still have question/s.
have a great day ahead! 
[eluser]Nica[/eluser]
I have another one question Andy,there's a two or more table we need to join to get the output of this..
Code: SUM(CASE WHEN mds_orders.GrossTotal < '100' THEN mds_orders.GrossTotal END) AS 'less100',
SUM(CASE WHEN mds_orders.GrossTotal >= '100' AND mds_orders.GrossTotal < '200' THEN mds_orders.GrossTotal END) AS 'less200',
SUM(CASE WHEN mds_orders.GrossTotal >= '200' AND mds_orders.GrossTotal < '300' THEN mds_orders.GrossTotal END) AS 'less300',
SUM(CASE WHEN mds_orders.GrossTotal >= '300' AND mds_orders.GrossTotal < '400' THEN mds_orders.GrossTotal END) AS 'less400',
SUM(CASE WHEN mds_orders.GrossTotal >= '400' AND mds_orders.GrossTotal < '500' THEN mds_orders.GrossTotal END) AS 'less500',
SUM(CASE WHEN mds_orders.GrossTotal >= '500' AND mds_orders.GrossTotal < '600' THEN mds_orders.GrossTotal END) AS 'less600',
SUM(CASE WHEN mds_orders.GrossTotal >= '600' AND mds_orders.GrossTotal < '700' THEN mds_orders.GrossTotal END) AS 'less700',
SUM(CASE WHEN mds_orders.GrossTotal >= '700' AND mds_orders.GrossTotal < '800' THEN mds_orders.GrossTotal END) AS 'less800',
SUM(CASE WHEN mds_orders.GrossTotal >= '800' AND mds_orders.GrossTotal < '900' THEN mds_orders.GrossTotal END) AS 'less900',
SUM(CASE WHEN mds_orders.GrossTotal >= '900' AND mds_orders.GrossTotal < '1000' THEN mds_orders.GrossTotal END) AS 'less1k',
SUM(CASE WHEN mds_orders.GrossTotal >= '1000' THEN mds_orders.GrossTotal END) AS 'gtr1k',
SUM(mds_orders.GrossTotal) AS 'total'
FROM mds_orders
JOIN restaurant_master
ON mds_orders.RestaurantID = restaurant_master.PKID
So in Code Igniter, how can I call this from the multiple table..?
Thanks again 
[eluser]Andy Armstrong[/eluser]
[quote author="Nica" date="1328231203"]I have another one question Andy,there's a two or more table we need to join to get the output of this..
Code: SUM(CASE WHEN mds_orders.GrossTotal < '100' THEN mds_orders.GrossTotal END) AS 'less100',
SUM(CASE WHEN mds_orders.GrossTotal >= '100' AND mds_orders.GrossTotal < '200' THEN mds_orders.GrossTotal END) AS 'less200',
SUM(CASE WHEN mds_orders.GrossTotal >= '200' AND mds_orders.GrossTotal < '300' THEN mds_orders.GrossTotal END) AS 'less300',
SUM(CASE WHEN mds_orders.GrossTotal >= '300' AND mds_orders.GrossTotal < '400' THEN mds_orders.GrossTotal END) AS 'less400',
SUM(CASE WHEN mds_orders.GrossTotal >= '400' AND mds_orders.GrossTotal < '500' THEN mds_orders.GrossTotal END) AS 'less500',
SUM(CASE WHEN mds_orders.GrossTotal >= '500' AND mds_orders.GrossTotal < '600' THEN mds_orders.GrossTotal END) AS 'less600',
SUM(CASE WHEN mds_orders.GrossTotal >= '600' AND mds_orders.GrossTotal < '700' THEN mds_orders.GrossTotal END) AS 'less700',
SUM(CASE WHEN mds_orders.GrossTotal >= '700' AND mds_orders.GrossTotal < '800' THEN mds_orders.GrossTotal END) AS 'less800',
SUM(CASE WHEN mds_orders.GrossTotal >= '800' AND mds_orders.GrossTotal < '900' THEN mds_orders.GrossTotal END) AS 'less900',
SUM(CASE WHEN mds_orders.GrossTotal >= '900' AND mds_orders.GrossTotal < '1000' THEN mds_orders.GrossTotal END) AS 'less1k',
SUM(CASE WHEN mds_orders.GrossTotal >= '1000' THEN mds_orders.GrossTotal END) AS 'gtr1k',
SUM(mds_orders.GrossTotal) AS 'total'
FROM mds_orders
JOIN restaurant_master
ON mds_orders.RestaurantID = restaurant_master.PKID
[/quote]
COULD YOU TRY THIS QUERY IN YOUR MODEL
Example codes:
Code: <?php
class Orders extends CI_Model
{
public function view_orders()
{
$query = $this->db->query("SELECT restaurant_master.code, restaurant_master.name,
SUM(CASE WHEN mds_orders.GrossTotal < '100' THEN mds_orders.GrossTotal END) AS 'less100',
SUM(CASE WHEN mds_orders.GrossTotal >= '100' AND mds_orders.GrossTotal < '200' THEN mds_orders.GrossTotal END) AS 'less200',
SUM(CASE WHEN mds_orders.GrossTotal >= '200' AND mds_orders.GrossTotal < '300' THEN mds_orders.GrossTotal END) AS 'less300',
SUM(CASE WHEN mds_orders.GrossTotal >= '300' AND mds_orders.GrossTotal < '400' THEN mds_orders.GrossTotal END) AS 'less400',
SUM(CASE WHEN mds_orders.GrossTotal >= '400' AND mds_orders.GrossTotal < '500' THEN mds_orders.GrossTotal END) AS 'less500',
SUM(CASE WHEN mds_orders.GrossTotal >= '500' AND mds_orders.GrossTotal < '600' THEN mds_orders.GrossTotal END) AS 'less600',
SUM(CASE WHEN mds_orders.GrossTotal >= '600' AND mds_orders.GrossTotal < '700' THEN mds_orders.GrossTotal END) AS 'less700',
SUM(CASE WHEN mds_orders.GrossTotal >= '700' AND mds_orders.GrossTotal < '800' THEN mds_orders.GrossTotal END) AS 'less800',
SUM(CASE WHEN mds_orders.GrossTotal >= '800' AND mds_orders.GrossTotal < '900' THEN mds_orders.GrossTotal END) AS 'less900',
SUM(CASE WHEN mds_orders.GrossTotal >= '900' AND mds_orders.GrossTotal < '1000' THEN mds_orders.GrossTotal END) AS 'less1k',
SUM(CASE WHEN mds_orders.GrossTotal >= '1000' THEN mds_orders.GrossTotal END) AS 'gtr1k',
SUM(mds_orders.GrossTotal) AS 'total'
FROM mds_orders
JOIN restaurant_master
ON mds_orders.RestaurantID = restaurant_master.PKID");
if($query->num_rows()> 0)
{
foreach($query->result() as $row)
{
$data[] = $row
}
return $data;
}
}
}
?>
[eluser]Nica[/eluser]
There's nothing display in a browser..
I'll show you my query of MVC..
This is my query for Model
Code: <?
class Sales_model extends CI_Model {
public function restaurant_master_getall() {
$this->load->database();
$query = $this->db->query("SELECT restaurant_master.code, restaurant_master.name,
SUM(CASE WHEN mds_orders.GrossTotal < '100' THEN mds_orders.GrossTotal END) AS 'less100',
SUM(CASE WHEN mds_orders.GrossTotal >= '100' AND mds_orders.GrossTotal < '200' THEN mds_orders.GrossTotal END) AS 'less200',
SUM(CASE WHEN mds_orders.GrossTotal >= '200' AND mds_orders.GrossTotal < '300' THEN mds_orders.GrossTotal END) AS 'less300',
SUM(CASE WHEN mds_orders.GrossTotal >= '300' AND mds_orders.GrossTotal < '400' THEN mds_orders.GrossTotal END) AS 'less400',
SUM(CASE WHEN mds_orders.GrossTotal >= '400' AND mds_orders.GrossTotal < '500' THEN mds_orders.GrossTotal END) AS 'less500',
SUM(CASE WHEN mds_orders.GrossTotal >= '500' AND mds_orders.GrossTotal < '600' THEN mds_orders.GrossTotal END) AS 'less600',
SUM(CASE WHEN mds_orders.GrossTotal >= '600' AND mds_orders.GrossTotal < '700' THEN mds_orders.GrossTotal END) AS 'less700',
SUM(CASE WHEN mds_orders.GrossTotal >= '700' AND mds_orders.GrossTotal < '800' THEN mds_orders.GrossTotal END) AS 'less800',
SUM(CASE WHEN mds_orders.GrossTotal >= '800' AND mds_orders.GrossTotal < '900' THEN mds_orders.GrossTotal END) AS 'less900',
SUM(CASE WHEN mds_orders.GrossTotal >= '900' AND mds_orders.GrossTotal < '1000' THEN mds_orders.GrossTotal END) AS 'less1k',
SUM(CASE WHEN mds_orders.GrossTotal >= '1000' THEN mds_orders.GrossTotal END) AS 'gtr1k',
SUM(mds_orders.GrossTotal) AS 'total'
FROM mds_orders
JOIN restaurant_master
ON mds_orders.RestaurantID = restaurant_master.PKID
WHERE mds_orders.OrderDate BETWEEN '".$date_from." 00:00:00' AND '".$date_to." 23:59:59'
GROUP BY restaurant_master.code, restaurant_master.name");
if($query->num_rows()> 0)
{
foreach($query->result() as $row)
{
$data[] = $row
}
return $data;
}
}
}
?>
This is my query for Controller
Code: <?php
class sales extends CI_Controller {
public function GetAll()
{
$this->load->model('sales_model');
$data['query'] = $this->sales_model->restaurant_master_getall();
$this->load->view('sales_viewall', $data);
}
function Get() {
$this->load->model('sales_model');
$data['query'] = $this->sales_model->restaurant_master_get();
}
}
?>
And this is my query for Views
Code: <html>
<head>
</head>
<body>
<h1>Sales Per Sales Frequency Bracket</h1>
<form name="fetching" method="POST" action=""></p>
<b>Date From:</b> <br><input type="text" name="dfrom" id="dfrom"></p>
<b>Date To:</b> <br><input type="text" name="dto" id="dto"><br /><br />
<input name="submit" type="submit" value="Go">
</form>
<table border="2"
cellpadding="2"
cellspacing="1"
>
<tr>
<th>Restaurant Code</th>
<th>Restaurant Name</th>
<th>100 & Below</th>
<th>101-200</th>
<th>201-300</th>
<th>301-400</th>
<th>401-500</th>
<th>501-600</th>
<th>601-700</th>
<th>701-800</th>
<th>801-900</th>
<th>901-1000</th>
<th>1001 & Above</th>
<th>Total Gross Sales</th>
</tr>
</table>
<?php
if(isset($_POST['submit']))
{
foreach($query as $row)
print $row->code;
print $row->name;
print $row->less100;
print $row->less200;
print $row->less300;
print $row->less400;
print $row->less500;
print $row->less600;
print $row->less700;
print $row->less800;
print $row->less900;
print $row->less1k;
print $row->gtr1k;
print $row->total;
print "<br>";
}
?>
</html>
[eluser]Andy Armstrong[/eluser]
forgotten to declare some variables, i guess this is the problem that there is nothing on your views..
In your model class
Code: WHERE mds_orders.OrderDate BETWEEN '".$date_from." 00:00:00' AND '".$date_to." 23:59:59'
On the top of your $query, after Code: $this->load->database();
or before your query statement.. Declare these two variables:
Code: $date_from = $this->input->post('dfrom');//the name of your input field :date from
$date_to = $this->input->post('dto');//the name of your input field :date to
Code: WHERE mds_orders.OrderDate BETWEEN '".$date_from." 00:00:00' AND '".$date_to('dto'." 23:59:59'
you haven't specified the action on the form.
here some roundups, you can do this if you haven't auto load some of your model class,helpers,libraries. If you have already done the auto loading then skip reading this..
Quote:Go to application>config>autoload.php
1.Go to the line 55 where auto loading libraries, declare in the array the libraries y
your want to load as for me i autoload this libraries
Code: $autoload['libraries'] = array('database','form_validation','session','pagination','email');
3. Go to line 67 on autoload.php in the auto load helper. load important helpers. As for
me I auto load these helpers.
Code: $autoload['helper'] = array('url','file','form','text');
Let's get back on your views. Inspect your form.... do it now. Have you seen the problem?
you haven't specified the action. Like i was saying awhile ago..
Code: <form name="fetching" method="POST" action="">
first we will do a very easy way on creating a form by the help of the auto loading the form helper we can have this functionality..
change your starting form tag to this one:
Code: <?php echo form_open('sales/getAll');?>
then close your form tag..
change </form> to this:
Code: <?php echo form_close();?>
I hope this works.. ^_^
one more thing your coding style is a bad practice, no offense im just here to help.When you declare a method:
like you name this method getAll you could change it to get_all, sometimes it could cause some problems.. anyway you use getAll() naming your method when you are creating your own libraries, but in a controller i don't think its a good practice. You can read in the guide of coding style.
|
