Manali Naik
Reputation: 61
Foreign Key In Php to get data from another table
function show_username($connect)
{
$output = '';
$query = "SELECT * from users";
$res = mysqli_query($connect, $query);
while($row = mysqli_fetch_array($res))
{
$output .= '<option value="'.$row["id"].'">'.$row["name"].'</option>';
{
}
return $output;
}
function show_offer($connect)
{
$output = '';
$query = "SELECT * FROM add_offer ORDER BY id DESC";
$res = mysqli_query($connect, $query);
while($row = mysqli_fetch_array($res))
{
$output .= '<div class="col-md-3 col-sm-4 col-lg-6">';
$output .= '<div class="panel panel-default">';
$output .= '<div class="panel-body">';
$output .= '<div style="padding:1px;float:left;font-weight:bold;">'.$row["part_no"].'</div>';
$output .= '<div style="padding:1px;float:left;">'.$row["make"].'</div>';
$output .= '<div style="padding:1px;float:left;">'.$row["date_code"].'</div>';
$output .= '<div style="padding:1px;float:left;">'.$row["qty"].'</div>';
$output .= '<br>';
$output .= '<div style="float:left;font-size:9px;">'.$row["time"].'</div>';
$output .= '</div>';
$output .= '</div>';
$output .= '</div>';
}
echo $output;
}
<div class="container">
<div class="row">
<div class="col-lg-offset-3 col-lg-6">
<div class="panel">
<div class="panel panel-default" style="border: 1px solid #66512c;">
<div class="panel-heading" style="background-color: #66512c;color: white;">
Market Offers
</div>
<div class="panel-body" style="padding: 0px;padding-left: 5px;border-bottom: 1px solid #66512c;">
<div class="nav nav-pills nav-stacked">
<select name="category" id="user" class="form-control">
<option selected="" value="" class="form-control">All User Offers</option>
<?php echo show_username($connect); ?>
</select>
<div class="panel panel-body" id="show_offer">
<?php echo show_offer($connect);?>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="col-lg-3"></div>
</div>
<script>
$(document).ready(function(){
$('#user').change(function(){
var user_id = $(this).val();
$.ajax({
url:"load_data.php",
method:"POST",
data:{user_id:user_id},
success:function(data){
$('#show_offer').html(data);
}
});
});
});</script>
<?php
//load_data.php
$connect = mysqli_connect("localhost", "root", "", "customer");
$output = '';
if(isset($_POST["user_id"]))
{
if($_POST["user_id"] != '')
{
$query = "SELECT * FROM add_offer WHERE user_id = '".$_POST["user_id"]."' ORDER BY id DESC";
}
else
{
$query = "SELECT * FROM add_offer ORDER BY id DESC";
}
$res = mysqli_query($connect, $query);
while($row = mysqli_fetch_array($res))
{
$output .= '<div class="col-md-3 col-sm-4 col-lg-6">';
$output .= '<div class="panel panel-default">';
$output .= '<div class="panel-body">';
$output .= '<div style="padding:1px;float:left;font-weight:bold;">'.$row["part_no"].'</div>';
$output .= '<div style="padding:1px;float:left;">'.$row["make"].'</div>';
$output .= '<div style="padding:1px;float:left;">'.$row["date_code"].'</div>';
$output .= '<div style="padding:1px;float:left;">'.$row["qty"].'</div>';
$output .= '<br>';
$output .= '<div style="float:left;font-size:9px;">'.$row["time"].'</div>';
$output .= '</div>';
$output .= '</div>';
$output .= '</div>';
}
echo $output;
}?>
In this above code I have used functions to take data from 2 seperate table and I want to display the table in the following format as name from users table part_no,make,date_code,qty from add_offers table the image shows my output market_offers page
I want the data not to be selected by dropdown but has to display whole data as first with name from users table and followed by users part_no from add_offers table.
Also the timestamp display as 2018-03-22 12.40.55 which i want as 22-03-2018 12.40.55
Upvotes: 1
Views: 87
Answers (1)
Tayyab Khan
Reputation: 283
You've to change your code as bellow:
function show_username($connect)
{
$output = '';
$query = "SELECT * from users order by id asc";
$res = mysqli_query($connect, $query);
while($row = mysqli_fetch_array($res))
{
$output .= '<option value="'.$row["id"].'">'.$row["name"].'</option>';
{
}
return $output;
}
function show_offer($connect)
{
$output = '';
$user_query = "SELECT * from users order by id asc";
$user_res = mysqli_query($connect, $user_query );
$user_row = mysqli_fetch_array($user_res);
$query = "SELECT * FROM add_offer where user_id = ".$user_row['id']." ORDER BY id DESC";
$res = mysqli_query($connect, $query);
while($row = mysqli_fetch_array($res))
{
$output .= '<div class="col-md-3 col-sm-4 col-lg-6">';
$output .= '<div class="panel panel-default">';
$output .= '<div class="panel-body">';
$output .= '<div style="padding:1px;float:left;font-weight:bold;">'.$row["part_no"].'</div>';
$output .= '<div style="padding:1px;float:left;">'.$row["make"].'</div>';
$output .= '<div style="padding:1px;float:left;">'.date('d-m-Y H:i:s',strtotime($row["date_code"])).'</div>';
$output .= '<div style="padding:1px;float:left;">'.$row["qty"].'</div>';
$output .= '<br>';
$output .= '<div style="float:left;font-size:9px;">'.$row["time"].'</div>';
$output .= '</div>';
$output .= '</div>';
$output .= '</div>';
}
echo $output;
}
Html Code:
<div class="container">
<div class="row">
<div class="col-lg-offset-3 col-lg-6">
<div class="panel">
<div class="panel panel-default" style="border: 1px solid #66512c;">
<div class="panel-heading" style="background-color: #66512c;color: white;">
Market Offers
</div>
<div class="panel-body" style="padding: 0px;padding-left: 5px;border-bottom: 1px solid #66512c;">
<div class="nav nav-pills nav-stacked">
<select name="category" id="user" class="form-control">
<option selected="" value="" class="form-control">All User Offers</option>
<?php echo show_username($connect); ?>
</select>
<div class="panel panel-body" id="show_offer">
<?php echo show_offer($connect);?>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="col-lg-3"></div>
</div>
Java Script Code:
<script>
$(document).ready(function(){
$('#user').change(function(){
var user_id = $(this).val();
$.ajax({
url:"load_data.php",
method:"POST",
data:{user_id:user_id},
success:function(data){
$('#show_offer').html(data);
}
});
});
});</script>
PHP Code:
<?php
//load_data.php
$connect = mysqli_connect("localhost", "root", "", "customer");
$output = '';
if(isset($_POST["user_id"]))
{
if($_POST["user_id"] != '')
{
$query = "SELECT * FROM add_offer WHERE user_id = '".$_POST["user_id"]."' ORDER BY id DESC";
}
else
{
$query = "SELECT * FROM add_offer ORDER BY id DESC";
}
$res = mysqli_query($connect, $query);
while($row = mysqli_fetch_array($res))
{
$output .= '<div class="col-md-3 col-sm-4 col-lg-6">';
$output .= '<div class="panel panel-default">';
$output .= '<div class="panel-body">';
$output .= '<div style="padding:1px;float:left;font-weight:bold;">'.$row["part_no"].'</div>';
$output .= '<div style="padding:1px;float:left;">'.$row["make"].'</div>';
$output .= '<div style="padding:1px;float:left;">'.$row["date_code"].'</div>';
$output .= '<div style="padding:1px;float:left;">'.$row["qty"].'</div>';
$output .= '<br>';
$output .= '<div style="float:left;font-size:9px;">'.$row["time"].'</div>';
$output .= '</div>';
$output .= '</div>';
$output .= '</div>';
}
echo $output;
}?>
Upvotes: 1
Related Questions
- How to use foreign key when querying from two tables
- MySQL + PHP: fetching data using foreign keys
- PHP Database Query foreign key data from database
- Get values from second table using foreign keys
- Get data from other table with foreign key
- PHP/mysql, Using foreign keys to get data from another table
- Getting related data from another table
- How can I retrieve data from a table and using a foreign key retrieve data from another table?
- Fetch data from mysql Foreign key table in php
- try to fetch information of specific foreign key