I am new in MySQL and I am using CodeIgniter. I have three tables called lead, document, and bank.
Now what I am doing is, In the lead table I am inserting the customer personal information which is unique. In the document table, I am inserting the user documents. In the bank table, I am inserting the bank records. It can be multiple.
There is no issue with insertion. I am getting the issue while fetching the data from the database.
Now table structure is
lead
id | name | mobile | email
1 | asdff |1234567831 |asd@gmail.com
2 | kjhgg |1231231231 |mnhg@gmail.com
3 | qwsde |1231233212 |oiuk@gmail.com
<!--many more-->
document
doc_id |doc_name | doc_date | lead_id |date_of_added
1 |asdasd |2019-06-24 | 1 |2019-06-16 17:31:07
2 |oiuytr |2019-06-24 | 2 |2019-06-16 17:31:07
3 |okjhyt |2019-06-25 | 3 |2019-06-20 20:12:09
<!--many more-->
bank
bank_id | bankname | lead_id |date_of_added
1 | sdasdas | 1 |2019-06-20 11:41:34
2 | asdasdasd | 1 |2019-06-21 10:41:34
3 | asdakjkh | 2 |2019-06-21 14:23:12
4 | qwerfgvf | 1 |2019-06-21 23:56:25
<!--many more-->
There is no connection between the document and the bank till now. should I connect using id?
Model
public function getconfirmLeadinfo($id){
return $result = $this->db->select('*')
->from('lead')
->join('document','lead.id=document.lead_id','LEFT')
->join('bank','lead.id=bank.lead_id','LEFT')
->where('lead.id',$id)
->get()
->result();
}
I am getting the output is
Array
(
[0] => stdClass Object
(
[id] => 1
[name] => asdff
[mobile] => 1234567831
[email] => asd@gmail.com
[doc_id] => 1
[doc_name] => asdasd
[doc_date] => 2019-06-24
[lead_id] => 1
[date_of_added] => 2019-06-16 17:31:07
[bank_id] => 1
[bankname] => sdasdas
[lead_id] => 1
[date_of_added] => 2019-06-20 11:41:34
)
[1] => stdClass Object
(
[id] => 1
[name] => asdff
[mobile] => 1234567831
[email] => asd@gmail.com
[doc_id] => 1
[doc_name] => asdasd
[doc_date] => 2019-06-24
[lead_id] => 1
[date_of_added] => 2019-06-16 17:31:07
[bank_id] => 2
[bankname] => asdasdasd
[lead_id] => 1
[date_of_added] => 2019-06-21 10:41:34
)
[2] => stdClass Object
(
[id] => 1
[name] => asdff
[mobile] => 1234567831
[email] => asd@gmail.com
[doc_id] => 1
[doc_name] => asdasd
[doc_date] => 2019-06-24
[lead_id] => 1
[date_of_added] => 2019-06-16 17:31:07
[bank_id] => 4
[bankname] => qwerfgvf
[lead_id] => 1
[date_of_added] => 2019-06-21 23:56:25
)
)
Now I am on edit page and I have to display the data.
if ($post){?>
<input type="text" name="name" value="<?php echo $post->name;?>">
<input type="text" name="mobile" value="<?php echo $post->mobile;?>">
<input type="text" name="email" value="<?php echo $post->email;?>">
<input type="text" name="doc_name" value="<?php echo $post->doc_name;?>">
<input type="text" name="doc_date" value="<?php echo $post->doc_date;?>">
how do I display my bank details here? should I use something like this
<?php
foreach ($result as $key => $value) {
<input type="text" name="bank[]" value="$value->bankname">
}
}?>
Would you help me out?
Use two separate query as below::
$data = [];
$lead = $this->db->select('*')->from('lead')->get()->row();
$data['lead'] = $lead;
if($lead){
$bank = $this->db->select('*')->where('lead_id', $lead->lead_id)->from('bank')->get()->result();
$data['bank '] = $bank ;
}
// you can retrive documet like bank details
return $data
Then you can pass $data to view and use lead and bank variable if it exist.
use foreach
<?php
foreach ($result as $key => $value) {
if ($value){?>
<input type="hidden" name="id[]" value="<?php echo $value->id;?>">
<input type="text" name="name[]" value="<?php echo $value->name;?>">
<input type="text" name="mobile[]" value="<?php echo $value->mobile;?>">
<input type="text" name="email[]" value="<?php echo $value->email;?>">
<input type="text" name="doc_name[]" value="<?php echo $value->doc_name;?>">
<input type="text" name="doc_date[]" value="<?php echo $value->doc_date;?>">
<?php }
}
?>
//controller: these arrays you can use in your view
function leadInfo($id){
$data=array();
$data['lead_detail']=$this->model_name->get_lead_detail_by_id($id);
$data['bank_details']=$this->model_name->get_bank_detail_by_lead_id($id);
$this->load-view('view_page_name',$data);
}
//model
function get_lead_detail_by_id($id){
$this->db->join('document','document.lead_id=lead.id');
return $this->db->where('lead.id',$id)->get('leads')->row_array();
}
function get_bank_detail_by_lead_id($id){
return $this->db->where('lead_id',$id)->get('bank')->row_array();
}