featching into column data

[Yohanne]

Hi coders,

 

Need help to display database recods. now i have 2 table like below.

 

 

 

and i need to display exactly like below. and i think, i need to featch the country to display and i need it to display by column not row. please help, give me an idea. how it to do. while im solving as will here. please help.. 

 

[Yohanne]

hello?

[jasmeet]

<table>

<tr>

<?php

$query=mysql_query("select * from table1");

while($result=mysql_fetch_array($query)){ ?>

<td><?php echo $result['country']; ?></td>

<table>

<?php

$query_1=mysql_query("select * from table2 where id='$result[id]'");

while($result_1=mysql_fetch_array($query_1)){

?>

<tr><td><?php echo $result_1[''city"]; ?></td></tr>

<?php } ?>

</table>

<?php } ?>

</tr>

</table>

[jcbones]

Make sure to change your database credentials, and table names.  You will have to do the table styling also.

 

<?php
//create the sql query string, take note that the order by clause sorts the countries in alphabetical order
$sql = "SELECT a.country, b.city FROM country AS a JOIN city AS b ON a.country_id = b.country_id ORDER BY a.country";
 
//create a mysqli object
$db = new mysqli("localhost", "my_username", "my_password", "my_database");
//query the database
$result = $db->query($sql);
//create a variable to hold the last known country name.
$last_country = NULL;
//fetch the data, until there are no rows left.
while($row = $result->fetch_assoc()) {
//if the last_country is not equal to the current country, then we start a division (<div>) and a table (<table>)
if($last_country != $row['country']) {
//but if last country does not explicitly equal null, then we first must close the division and the table.
if($last_country !== NULL) {
echo '</table></div>';
}
echo '<div style="float:left;">
<table style="border:1px solid black">
<tr>
<th>' . $row['country'] . '</th>
</tr>';
}
//every run of the while loop should output the city
echo '<tr><td>' . $row['city'] . '</td></tr>';
//after everything is done, we now set the current country, to the last_country (for the new loop).
$last_country = $row['country'];
}

[Yohanne]

 

Make sure to change your database credentials, and table names.  You will have to do the table styling also.

<?php
//create the sql query string, take note that the order by clause sorts the countries in alphabetical order
$sql = "SELECT a.country, b.city FROM country AS a JOIN city AS b ON a.country_id = b.country_id ORDER BY a.country";
 
//create a mysqli object
$db = new mysqli("localhost", "my_username", "my_password", "my_database");
//query the database
$result = $db->query($sql);
//create a variable to hold the last known country name.
$last_country = NULL;
//fetch the data, until there are no rows left.
while($row = $result->fetch_assoc()) {
//if the last_country is not equal to the current country, then we start a division (<div>) and a table (<table>)
if($last_country != $row['country']) {
//but if last country does not explicitly equal null, then we first must close the division and the table.
if($last_country !== NULL) {
echo '</table></div>';
}
echo '<div style="float:left;">
<table style="border:1px solid black">
<tr>
<th>' . $row['country'] . '</th>
</tr>';
}
//every run of the while loop should output the city
echo '<tr><td>' . $row['city'] . '</td></tr>';
//after everything is done, we now set the current country, to the last_country (for the new loop).
$last_country = $row['country'];
}

 

Thank you, jc and i work as your advice above and it works great. but when i trying to work on in my actual database. the header part going into redundant. why?

$x = "SELECT REQUEST.request_id, USER.address FROM pro_user_employee AS USER JOIN pro_request AS REQUEST ON USER.employee_id = REQUEST.employee_id";
$y= mysql_query($x); 

 

[jcbones]

You need to order by the indexed column.  This will ensure that all the column names are returned together.

 

 

$x = "SELECT REQUEST.request_id, USER.address FROM pro_user_employee AS USER JOIN pro_request AS REQUEST ON USER.employee_id = REQUEST.employee_id ORDER BY USER.address";

[Yohanne]

Wow.. it works thanks a lot..

[Yohanne]

i think, when i solve above. i get all the solution but not. so please help if ever knows that there is same scenario like below, please share it. and i need sample else tutorials since i have no really idea. 

 

[jcbones]

I am willing to help you, but I would need a database dump with sample data, 5 rows would be enough.

[Yohanne]

Okay, Thanks JC. but i think when you see my database, you might confuse since it is crowded, i might only me can understand it. but okay i give it to you since i know you are genius and you can understand it well. image below are might different to attachment but u can use it as a sample. 

 

pro_request.sql.zip

pro_user_employee.sql.zip

[jcbones]

You need to do some data normalization, as well as storing dates in the correct column types.  You were right about the data being confusing, but I think I got you on the right track.  I wasn't sure how or what the last column (balance) was suppose to be calculated by, so I didn't include that.  It would be easy to add, I would like to see what you come up with.

 

Edit: on second look, the last balance is in there, but you image had me adding another one.  Ahh 16hr days...

<?php
///////////////////////////
//Set your database details
define('DATABASE','test');
define('DATABASE_USER','root');
define('DATABASE_PASSWORD','');
define('DATABASE_HOST','localhost');
///////////////////////////
 
//database connection;
$db = new mysqli(DATABASE_HOST,DATABASE_USER,DATABASE_PASSWORD,DATABASE);
//query string:
$sql = "SELECT `item_code`,`item_name`,`address`,`quantity_receive`,`ret`,`sold` FROM `pro_user_employee` JOIN `pro_request` USING(`employee_id`) ORDER BY `address`";
//run query and check for results;
if(!$result = $db->query($sql)) {
//if query failed, find out why;
echo $db->error;
}
//make sure results are returned:
if($result->num_rows > 0) {
//if there are results, run them
while($row = $result->fetch_assoc()) {
//dump all data into an array for sorting.
$address_array[] = $row['address'];
$storage[$row['item_code']][$row['address']] = array('name' =>$row['item_name'], 
'received' => $row['quantity_receive'],
'retorn' => $row['ret'],
'sold' => $row['sold']
);
}
//process the storage array, sorting the output.
//clean up the address array:
$address_array = array_unique($address_array);
//clean up addresses array
$address_array = array_values($address_array);
//keys to adresses
$keys = array_keys($address_array);
//how many address do we have:
$count = count($address_array);
//build the table headers:
$table = <<<'EOF'
<table border=1>
<tr>
<th rowspan = "2">Item Code</th>
<th rowspan = "2">Description</th>
EOF;
foreach($address_array as $value) {
$table .=  '<th colspan="4">' . $value . '</th>';
}
//close first row, open second.
$table .= '</tr>
<tr>';
//build secondary headers, based on how many addresses.
for($i = 0; $i < $count; $i++) {
$table .= <<<'EOF'
<th>
Received
</th>
<th>
Retorn
</th>
<th>
Sold
</th>
<th>
Balance
</th> 
EOF;
}
//close second row.
$table .= '</tr>';
//loop through the stored data, and sort.
foreach($storage as $item_code => $addresses) {
//each item has it's own row, so start it, first column is the item code, so drop it here.
$table .= '<tr>
<td>' . $item_code . '</td>';
$row_starts = 0; //lets us know if the row has been started, This controls the data flow AFTER the item code.
$cols = NULL; //clear our column variable.
foreach($keys as $value) { //for each key from the addresses array.
if(array_key_exists($address_array[$value],$addresses)) { //see if that address exists in the storage array, under the current item code.
$value = $address_array[$value]; //if we got here, it did, so lets get the key that existed.
if($row_starts == 0) { //if our row hasn't started, we need to get the item description.
$name = '<td>' . $storage[$item_code][$value]['name'] . '</td>'; //storing it in an array named ($name).
$row_starts = 1; //now our row as started, so lets change the row starts variable.
}
$cols .= '<td>' . $storage[$item_code][$value]['received'] . '</td>'
.'<td>' . $storage[$item_code][$value]['retorn'] . '</td>'
.'<td>' . $storage[$item_code][$value]['sold'] . '</td>'
.'<td>' . ($storage[$item_code][$value]['received'] - $storage[$item_code][$value]['sold']) . '</td>'; //list out our columns, getting our balance by simply math.
} else { //if the address was not in the stored data, then assign the number 0 to the column.
$cols .= '<td>0</td>'
.'<td>0</td>'
.'<td>0</td>'
.'<td>0</td>';
}
}
$table .= $name . $cols; //before we open a new item, lets close this one by appending the name and cols variables back to the table variables.
$table .= '</tr>'; //then close the current row.
}
$table .= '</table>'; //all items have completed the run, now close the table.
 
echo $table; //print the table out to the page.
 
//echo '<pre>' . print_r($storage,true) . '</pre>';  //de-bugging purposes only.
} 
 

Edited September 6, 2013 by jcbones

[Yohanne]

Wow.. JC its really work.. a big thanks JC.. Thank you a lot.