Jump to content

Dynamically create a table's columns, and fill it correctly with data from MySQL

ngng

By ngng
in PHP Coding Help

 Share

Recommended Posts

ngng Newbie

ngng

Posted
Posted

I've been stuck on this for the past two days, maybe I'm trying to do too much!  What I'd like to do, is take the selected rows from my database, generate columns dynamically to reflect the "size" column, and then dump the data respectively into the table that is generated.

 

Here's what I've got so far:

camcoview.png

 

Using this database:

camcodb.png

 

What I've done is given each row/column a designation by assigning a X,Y value (tag_no, size).  I created an array that uses a key in the same X,Y format.  I loop through the rows to dump the array value that corresponds to the key. 

 

The data doens't display correctly if I there are multiple tag_no's that have the same sizes, with different quantities.

camcoview2.png

 

There's got to be a much easier way of doing this?  :-\ ??? >:(

 

Code:

<table>
<tr>
	<td width="100">Tag No.</td>
	<?  //Table header below:
		//generate TD for sizing

		$count = 0;
		while ($size_rows = mysql_fetch_array($sizes_result)) {						
			$size = $size_rows['size'];
			$header_array[$count] = $size;
			$count++;
		}


		//$unique_header = array_unique($header_array);

			foreach( $header_array as $size => $value ){
				echo "<td bgcolor=\"034FFF\" width=\"50\" align=\"center\">";
				echo $value . "'";
			echo "</td>";
			}

		// end SIZE columns
	?>
	<td align="right" width="100">Pieces</td>		
	<td align="right" width="100">Lineal</td>
	<td align="right" width="100">FBM</td>
</tr>
</table>



<table>

		<?
		//acutal data table

		//create array of table data
				$sizes_q = "select * from inventory_sizes WHERE `TID` = $UID ORDER by size";
				$sizes_result = mysql_query($sizes_q,$conn);

				$column_position = 0;

				while ($size_rows = mysql_fetch_array($sizes_result)) {							
					$size = $size_rows['size'];						
					$tag_no = $size_rows['tag_no'];
					$qty = $size_rows['qty'];


					$identifier = $tag_no . "." . $size;

					$table_data[$identifier] = $qty;
				}


		//table 
		$tag_rows_q = "select * from inventory_sizes WHERE `TID` = $UID ORDER by size";
		$tag_rows_result = mysql_query($tag_rows_q,$conn);			
		while ($tag = mysql_fetch_array($tag_rows_result)) {


			$lineal = $tag['lineal'];
			$TID = $tag['UID'];
			$tag_no = $tag['tag_no'];
			$row_position = 0;

			echo "<tr>";
			echo "	<td width=\"100\">";
			echo $tag_no;
			echo "	</td>";



				$sizes_q = "select * from inventory_sizes WHERE `TID` = $UID  ORDER by size";
				$sizes_result = mysql_query($sizes_q,$conn);

				$column_position = 0;

					while ($size_rows = mysql_fetch_array($sizes_result)) {						
						$qty = $size_rows['qty'];
						$size = $size_rows['size'];
						$tag_ID = $size_rows['TID'];

						echo "<td width=\"50\" align=\"right\">";
						$array_UID = "$tag_no.$size";

						echo $table_data[$array_UID];
						//echo $array_UID;

						echo "</td>";

						//new shit

						$column_position++;
					}





			echo "	<td align=\"right\" width=\"100\">";
			echo "		$pcs";
			echo "	</td>";

			echo "	<td align=\"right\" width=\"100\">";
			echo "		$lineal";
			echo "	</td>";

			echo "	<td align=\"right\" width=\"100\">";
			echo $tag['FBM'];
			echo "	</td>";								
			echo "</tr>";


		}
		?>

</table>

<?

		echo "<pre>";
			print_r(array($table_data));	
		echo "</pre>";
?>

Psycho Prolific Member

Psycho

Posted
Posted

Ok, there is definitely some inefficiency there. I had to make some assumptions on this. For instance I don't see hwere you are doing the size query, but I assume the values are the same as in the inventory table. Also, I am assuming the "Pieces" label is a description for the size columns, so I have left it out. Also, I did not know where the "Lineal" value is coming from for each item.

 

Here is some code that should work for you. There are probably some syntax errors, but the logic should be sound:

 

<?php

echo "<table>\n";
echo "    <tr>\n";
echo "        <td>\n";

//create array of sizes
while ($size_rows = mysql_fetch_array($sizes_result)) {						
    $header_array[] = $size_rows['size'];
}

//Create size TD headers
foreach( $header_array as $size){
    echo "        <td bgcolor=\"034FFF\" width=\"50\" align=\"center\">$size'</td>\n";
}

//Show other headers
echo "        <td align=\"right\" width=\"100\">Lineal</td>\n";
echo "        <td align=\"right\" width=\"100\">FBM</td>\n";
echo "    </tr>\n";

//Query for inventory results
$query = "select * from inventory_sizes WHERE `TID` = $UID ORDER by size";
$result = mysql_query($query,$conn);

//Loop through result set
while ($record = mysql_fetch_array($result)) {

    //Start record row
    echo "    <tr>\n";				
    echo "        <td>$record['tag_no']</td>\n";

    //Loop through header array. If current record matches the header
    //size, show the #pieces otherwise shouw empty TD cell
    for ($i=0; $i<count($header_array); $i++) {

        if ($record['size']==$header_array[$i]) {
            $pieces = $record['size'];
        } else {
            $pieces = " "; //Show empty cell
        }

        echo "        <td>$pieces</td>";
    }

    //Show remaining values for the record
    echo "        <td align=\"right\" width=\"100\">Lineal(?)</td>\n";
    echo "        <td align=\"right\" width=\"100\">$record['FBM']</td>\n";
    echo "    </tr>\n";
}

echo "</table>\n";

?>

ngng Newbie

ngng

Posted
  • Author
Posted

thanks for the reply.  I didn't want to include the rest of the code, but you assumed correctly.  let me play with the code, I appreciate your help and will post my results :)

 

Ok, there is definitely some inefficiency there. I had to make some assumptions on this. For instance I don't see hwere you are doing the size query, but I assume the values are the same as in the inventory table. Also, I am assuming the "Pieces" label is a description for the size columns, so I have left it out. Also, I did not know where the "Lineal" value is coming from for each item.

 

Here is some code that should work for you. There are probably some syntax errors, but the logic should be sound:

 

<?php

echo "<table>\n";
echo "    <tr>\n";
echo "        <td>\n";

//create array of sizes
while ($size_rows = mysql_fetch_array($sizes_result)) {						
    $header_array[] = $size_rows['size'];
}

//Create size TD headers
foreach( $header_array as $size){
    echo "        <td bgcolor=\"034FFF\" width=\"50\" align=\"center\">$size'</td>\n";
}

//Show other headers
echo "        <td align=\"right\" width=\"100\">Lineal</td>\n";
echo "        <td align=\"right\" width=\"100\">FBM</td>\n";
echo "    </tr>\n";

//Query for inventory results
$query = "select * from inventory_sizes WHERE `TID` = $UID ORDER by size";
$result = mysql_query($query,$conn);

//Loop through result set
while ($record = mysql_fetch_array($result)) {

    //Start record row
    echo "    <tr>\n";				
    echo "        <td>$record['tag_no']</td>\n";

    //Loop through header array. If current record matches the header
    //size, show the #pieces otherwise shouw empty TD cell
    for ($i=0; $i<count($header_array); $i++) {

        if ($record['size']==$header_array[$i]) {
            $pieces = $record['size'];
        } else {
            $pieces = " "; //Show empty cell
        }

        echo "        <td>$pieces</td>";
    }

    //Show remaining values for the record
    echo "        <td align=\"right\" width=\"100\">Lineal(?)</td>\n";
    echo "        <td align=\"right\" width=\"100\">$record['FBM']</td>\n";
    echo "    </tr>\n";
}

echo "</table>\n";

?>

sasa Newbie

sasa

Posted
Posted

try

<?php
mysql_connect('localhost') or die('blek');
mysql_select_db('test');
$sql ="Select distinct size from invertory_size where TID=2 order by size asc";
$r = mysql_query($sql) or die('upš');
while ($row = mysql_fetch_array($r)){
$pos[$row['size']] = $i++;
}
$sql ="Select * from invertory_size where TID=2 order by tag_no asc, size asc";
$r = mysql_query($sql) or die('upš');
echo '<table border="3">
<tr>
<td width="100">Tag No.</td>';
foreach ($pos as $key => $value) echo "<td bgcolor=\"034FFF\" width=\"50\" align=\"center\">",$key,'</td>';
echo'<td align="right" width="100">Pieces</td>
<td align="right" width="100">Lineal</td>
<td align="right" width="100">FBM</td></tr>';
$curent = '';
$start = true;
while ($row = mysql_fetch_array($r)){
if($start) {
	echo '<tr><td>', $row['tag_no'],'</td>';
	$i = 0;
	$start = false;
} elseif ($row['tag_no']!=$curent) {
	$curent = $row['tag_no'];
	while ($i++ < count($pos)) {
		echo '<td> </td>';
		//$i++;
	}
	echo '<td> </td><td> </td><td> </td></tr><tr><td>'.$row['tag_no'].'</td>';
	$i = 0;
}
while ($i++ < $pos[$row['size']]) {
	echo '<td> </td>';
	//$i++;
}
echo '<td>',$row['qty'],'</td>';
}
while ($i++ < count($pos)) echo '<td> </td>';
echo '<td> </td><td> </td><td> </td></tr></table>';
?>

ngng Newbie

ngng

Posted
  • Author
Posted

thanks for the reply.  I didn't want to include the rest of the code and forgot to include the size query which is located at the top of the page. 

 

		$UID = $_REQUEST['UID'];

		$sizes_q = "select * from inventory_sizes WHERE `TID` = $UID ORDER BY size";
		$sizes_result = mysql_query($sizes_q,$conn);
		$sizes_total = mysql_num_rows($sizes_result);

 

The pieces is acutally the quantity of given size (designated in feet, 6', 7', etc).  Lineal hasn't been coded yet, but it is the product of the QTY x SIZE.  FBM is a value pulled directly from the DB.

 

Your code works very well with some minor changes.  One problem I still encounter, is a TAG_NO (unique) that has multiple sizes and quantity of wood, the header column generates duplicate header columns AND lists the data twice.  I used unique_array to eliminate duplicates in the header, but it messes up the rest of the tables.

 

camco_db_dupe.png

DB screenshot, NOTE: TID is acutally the UID being requested in the URL.

 

camco_dupe.png

This works.

 

camco_dupe.png

This is the duplicate error (you can also see this here: http://camcocedar.com/inventory_detail.php?UID=3)

ngng Newbie

ngng

Posted
  • Author
Posted

It works!  After reading over what you wrote, and the above coders responses I see how inefficent my code it  :P  Thanks to all.  What would be the best way to get the product of the size * qty?  Would it be better to do the calculations and store them in the row?

 

lineal.jpg

 

This code works! 

 

<?php
mysql_connect("localhost","USER","PASSWORD") or die (mysql_error());
mysql_select_db(camco_catalog) or die(mysql_error());

$UID = $_REQUEST['UID'];	

$sql ="SELECT DISTINCT size FROM inventory_sizes WHERE TID=$UID ORDER by size ASC";
$r = mysql_query($sql) or die('ughs');

while ($row = mysql_fetch_array($r)){
	$pos[$row['size']] = $i++;
}

$sql ="SELECT * FROM inventory_sizes WHERE TID=$UID ORDER by tag_no ASC, size ASC";
$r = mysql_query($sql) or die('ugh');

echo '<table border="3">
<tr>
<td width="100">Tag No.</td>';
foreach ($pos as $key => $value) echo "<td bgcolor=\"034FFF\" width=\"50\" align=\"center\">",$key,'</td>';
echo'<td align="right" width="100">Pieces</td>
<td align="right" width="100">Lineal</td>
<td align="right" width="100">FBM</td></tr>';
$curent = '';
$start = true;
while ($row = mysql_fetch_array($r)){
	if($start) {
		echo '<tr><td>', $row['tag_no'],'</td>';
		$i = 0;
		$start = false;
	} elseif ($row['tag_no']!=$curent) {
		$curent = $row['tag_no'];
		while ($i++ < count($pos)) {
			echo '<td> </td>';
			//$i++;
		}
		echo '<td> </td><td> </td><td> </td></tr><tr><td>'.$row['tag_no'].'</td>';
		$i = 0;
	}
	while ($i++ < $pos[$row['size']]) {
		echo '<td> </td>';
		//$i++;
	}
	echo '<td>',$row['qty'],'</td>';
}
while ($i++ < count($pos)) echo '<td> </td>';
echo '<td> </td><td> </td><td> </td></tr></table>';
?>

try

  • 2 weeks later...
ngng Newbie

ngng

Posted
  • Author
Posted

what numbe must be in lineal in 2nd row (tag no = 20000)

 

BTW in header of your table say thet  have 3 sizes but in table is just 2

 

fixed the header.

 

the FBM is stored in the row of the tag_no.  I would just pulll the FBM from the tag_no and display it

ngng Newbie

ngng

Posted
  • Author
Posted

There seems to be a little bug in the code I am having problems with...

 

The very first row of the table, regardless if it has multiple sizes, only displays the first size.  This is different than the other rows, where if there are multiple sizes, all get displayed in the same row:

 

camco_error.png

 

<?php
mysql_connect("localhost","xx","xxx") or die (mysql_error());
mysql_select_db(xxx) or die(mysql_error());

$sql ="Select distinct size from inventory_sizes where TID=1 order by size asc";
$r = mysql_query($sql) or die('ughs');

while ($row = mysql_fetch_array($r)){
$pos[$row['size']] = $i++;
}

$sql ="Select * from inventory_sizes where TID=1 order by tag_no asc, size asc";
$r = mysql_query($sql) or die('ugh');
echo '<table border="3">
<tr>
<td width="100">Tag No.</td>';
foreach ($pos as $key => $value) echo "<td bgcolor=\"034FFF\" width=\"50\" align=\"center\">",$key,'</td>';
echo'<td align="right" width="100">Pieces</td>
<td align="right" width="100">Lineal</td>
<td align="right" width="100">FBM</td></tr>';
$curent = '';
$start = true;
while ($row = mysql_fetch_array($r)){
if($start) {
	echo '<tr><td>', $row['tag_no'],'</td>';
	$i = 0;
	$start = false;
} elseif ($row['tag_no']!=$curent) {
	$curent = $row['tag_no'];
	while ($i++ < count($pos)) {
		echo '<td> </td>';
		//$i++;
	}
	echo '<td> </td><td> </td><td> </td></tr><tr><td>'.$row['tag_no'].'</td>';
	$i = 0;
}
while ($i++ < $pos[$row['size']]) {
	echo '<td> </td>';
	//$i++;
}
echo '<td>',$row['qty'],'</td>';
}
while ($i++ < count($pos)) echo '<td> </td>';
echo '<td> </td><td> </td><td> </td></tr></table>';
?>

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.