Skip to main content
Home

query dari 2 atau lebih tabel

April 27, 2010 by rhoalq

rhoalq's picture

para master ci, bagaimana yah cara untuk menampilkan query dari 2 atau lebih tabel...???

gambarannya sprti ini...

saya mempunyai tabel category, type, product

di tabel category :
ada field CategoryId, ParentCategoryId, CategoryName, TypeId
tabel type :
ada field TypeId, TypeName
tabel product :
ada field ProductId, ProductName, Specification, CategoryId

dan yang ingin saya tampilkan adalah ProductId, ProductName, Specification, CategoryName, TypeName

yg jadi pertanyaan saya, querynya bagaimana? Model, control dan view nya bagaimana?

terima kasih banyak...

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

BLS:

April 28, 2010 by syabac, 32 weeks 6 days ago
Comment: 6654

syabac's picture

kira2 SQL-nya seperti ini..
SELECT p.ProductId, p.ProductName, p.Spesification, c.CategoryName, t.TypeName
FROM product p
JOIN category c ON c.CategoryId=p.CategoryId
JOIN type t ON t.TypeId=c.TypeId

bila diimplementasikan menggunakan Active Record di CI...
Model:

class Product_model extends Model{
    function detail_product(){
        $this->db->select('p.ProductId, p.ProductName, p.Spesification, c.CategoryName, t.TypeName')
                 ->from('product p')
                 ->join('category c', 'c.CategoryId=p.CategoryId')
                 ->join('type t', 't.TypeId=c.TypeId');
        return $this->db->get()->result();
    }
}

Controller:

class Product extends Controller{
    function lists(){
        $this->load->database();
        $this->load->model('product_model');
 
        $data['products']    = $this->product_model->detail_product();
 
        $this->load->view('products', $data);
    }
}

View:

echo '<table>';
    foreach($products as $product):
        echo '<tr>';
        echo '<td>' . $product->ProductId . '</td>';
        echo '<td>' . $product->ProductName . '</td>';
        echo '<td>' . $product->Spesification . '</td>';
        echo '<td>' . $product->CategoryName . '</td>';
        echo '<td>' . $product->TypeName . '</td>';
        echo '</tr>';
    endforeach;
echo '</table>';

Semoda membantu..

#1 Balas

April 28, 2010 by Firu, 32 weeks 6 days ago
Comment: 6643

Firu's picture

ini langkah - langkahnya :

1. u join dulu query nya sehingga membentuk data yg u inginkan.
2. buat modelnya, contohnya :

function getData(){
                // query data
		$string_query = 'SELECT * FROM jobvacancy jv INNER JOIN jobcategory jc ON jc.categoryid = jv.categoryid 
						INNER JOIN jobtype jt ON jt.typeid = jv.typeid WHERE jv.status = 1';
 
		$query = $this->db->query($sqlQuery ." ORDER BY JobID DESC,PostDate DESC");
 
		if ($query -> num_rows()> 0){
			foreach ($query->result_array() as $row){
					$data[] = $row;
			}
				$query->free_result();
				return $data;
			} 			
		}

3. buat controllernya , contohnya :

function getDefaultData(){
$this->load->model('data_model');
$data['jobRec'] = $this->data_model->getDefaultData();

$data['main_content'] = 'job_listing';
$this->load->view('site_template/template',$data);
}

4. buat viewnya, contohnya :

 $row = 0;
			if (!empty($jobRec)){	
			foreach ($jobRec as $jobRow) :

 if($row == 0){

 echo base_url();job_listing/job_detail/ echo $jobRow['JobID']; "> echo $jobRow['Position'];  echo $jobRow['CompanyName'];
 echo $jobRow['Location'];  echo $jobRow['TypeName'];
 echo $jobRow['PostDate'];

5. Pelajari lagi konsep CI n baca - baca di http://net.tutsplus.com/?s=code+igniter.
yah kurang lebih seperti itu tahap - tahapnya, coba u pelajari lagi konsep CI....

semoga membantu....

re: query dari 2 atau lebih tabel

April 28, 2010 by rhoalq, 32 weeks 6 days ago
Comment: 6660

rhoalq's picture

terima kasih banyak mas2...

tp, stlh maslah trsbut trslesaikan muncul masalh lain mas, yaitu cara mengambil id dari hasil query baru di eksekusi...

sbgai gambaran klo d pemrograman php yg biasa, saya menggunakan sprti ini :

/*
...................  coding lain
 
*/
mysql_query($query1);  // <--------  ini query pertama
$query="SELECT * FROM detailtransaction order by DetailTransactionId desc limit 1";
$hasil=mysql_query($query);
while($rec=mysql_fetch_array($hasil)){
if(isset($rec[0])){
 
		$_SESSION['DetailTransactionId']= $rec[0];
 
}
 }
 
$query2="INSERT INTO  `transaction` (
`TransactionId` ,
`ProductId` ,
`DetailTransactionId` ,
`UserId`,
`Direction`
)
VALUES (
'$TransactionId', ' $ProductId','$_SESSION[DetailTransactionId]', '$UserId','Direction')";
 
 
mysql_query($query2);

nah, klo di ci bagaimana? mengambil session itu sprti apa?

#2 Balas

April 29, 2010 by Firu, 32 weeks 5 days ago
Comment: 6664

Firu's picture

coba baca disini dulu bos http://codeigniter.com/user_guide/libraries/sessions.html.

re: query dari 2 atau lebih tabel

April 29, 2010 by rhoalq, 32 weeks 5 days ago
Comment: 6665

rhoalq's picture

justru itu mas, saya sudah baca... tp, tdk mngerti...

tolong bantu saya dong mas...!!

saya sangat membutuhkan bantuan untuk saat ini...

Intinya pake

April 29, 2010 by mul14, 32 weeks 5 days ago
Comment: 6667

mul14's picture

Intinya pake $this->session->set_userdata() dan $this->session->userdata()

re:session

April 29, 2010 by rhoalq, 32 weeks 5 days ago
Comment: 6675

rhoalq's picture

ok, mas2... terima kasih banyak...

Premium Drupal Themes by Adaptivethemes