show query result on table horizontally

gralfitox · December 16, 2014

I´ve reached to

    <?php require_once('Connections/conexxion.php'); ?>
    <?php
    mysql_select_db($database_conexxion, $conexxion);
    $query_consulta = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc";
    $consulta = mysql_query($query_consulta, $conexxion) or die(mysql_error());
    $row_consulta = mysql_fetch_assoc($consulta);
    $totalRows_consulta = mysql_num_rows($consulta);
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>test</title>
    </head>
     
    <body>
     
       <?php
    echo "<table align=center>";
     
    $columnes = 10; # Número de columnas (variable)
     
    if (($rows=mysql_num_rows($consulta))==0) {
      echo "<tr><td colspan=$columnes>No hay resultados en la BD.</td></tr> ";
    } else {
      echo "<tr><td colspan=$columnes>$rows Resultados </td></tr>";
    }
     
    for ($i=1; $row = mysql_fetch_row ($consulta); $i++) {
    $resto = ($i % $columnes); # Número de celda del <tr> en que nos encontramos
    if ($resto == 1) {echo "<tr>";} # Si es la primera celda, abrimos <tr>
       echo "<td>$row[1]</td>";
    if ($resto == 0) {echo "</tr>";} # Si es la última celda, cerramos </tr>
    }
    if ($resto <> 0) { # Si el resultado no es múltiple de $columnes acabamos de rellenar los huecos
    $ajust = $columnes - $resto; # Número de huecos necesarios
    for ($j = 0; $j < $ajust; $j++) {echo "<td> </td>";}
    echo "</tr>"; # Cerramos la última línea </tr>
    }
    mysql_close($connexion);
    echo "</table>";
    ?>
     
     
     
    </body>
    </html>
    <?php
    mysql_free_result($consulta);
    ?>

it "works", but results doesn´t start at number 1 but number 2

anyways, The intent of this table is to show the stock of a product, a lens, that has a range of Rx, we use to see this range this way, and show the stock this way would be very productive, but I can´t figure out how to achive it, any sugestion??

Edited December 16, 2014 by gralfitox

Barand · December 16, 2014

That's because you fetch a row at the top of your script then ignore it.

Remove

$row_consulta = mysql_fetch_assoc($consulta);

gralfitox · December 16, 2014

thank, you, worked!

any sugestion on achieving that kind of table? I´m not sure what loop fits best for that?

Barand · December 16, 2014

Which field (A) provides the values for the column headings?

Which field (B) provides the values for the row headings?

Which field © provides the values for the totals?

You would need something like

SELECT A, B, SUM(C) as C
FROM ...
GROUP BY A, B

then a while() loop to process the results.

Output a row of totals for B when A changes

gralfitox · December 16, 2014

row and column headings are not query results, they are always the same and I can use the table as template for all my products, I mean that the only thing I want to achieve is to fill the blank cells this way, take a look

I don´t know if I explain myself.

Psycho · December 16, 2014

Your images are broken. I assume you have some type of "property" to use as the column headers that specifies some type of description for the records and you want to show the quantity in stock for each item by that property. Something like this?

Name   | Blue | Green | Red | Yellow
---------------------------------------
Item 1 |   5  |   6   |  6  |  17
Item 2 |   2  |   6   |  0  |   9
Item 3 |  12  |  13   |  7  |   4
Item 4 |   4  |   6   |  0  |   3
Item 5 |   0  |   6   |  3  |  23

(with a possible "total" column at the end). If that is correct,then I assume it is possible for some records to have a 0 quantity for some columns. Are the columns "fixed" or would you be retrieving them from the database? Your current query is grouping by the item_id. To do the above we would also need to group by the column header value. What field would represent the column headers?

Edited December 16, 2014 by Psycho

gralfitox · December 16, 2014

Your images are broken. I assume you have some type of "property" to use as the column headers that specifies some type of description for the records and you want to show the quantity in stock for each item by that property. Something like this?
Name   | Blue | Green | Red | Yellow---------------------------------------Item 1 |   5  |   6   |  6  |  17Item 2 |   2  |   6   |  0  |   9Item 3 |  12  |  13   |  7  |   4Item 4 |   4  |   6   |  0  |   3Item 5 |   0  |   6   |  3  |  23
(with a possible "total" column at the end). If that is correct,then I assume it is possible for some records to have a 0 quantity for some columns. Are the columns "fixed" or would you be retrieving them from the database? Your current query is grouping by the item_id. To do the above we would also need to group by the column header value. What field would represent the column headers?

Ok, so column heading is the cilindro.cilindro value and row heading is esfera.esfera value. I'm at my mobile and have no access to my db. I'll post my schema model to see what's possible to do

Let me try to explain myself again, I'm not english speaker so sorry about it.

I have filled the movimiento table with all possible rx combinations for testing pourposes. So i have 1 item id 1 in stock with rx 4.00 esf 0.00 cil

2 item id 1 in stock with rx 3.75 esf 0.00 cil

3 item id 1 in stock with rx 3.75 esf -0.25 cil

4 item id 1 in stock with rx 3.50 esf 0.00 cil

An so on, is clear this way?

Edited December 16, 2014 by gralfitox

Psycho · December 16, 2014

Are the columns "fixed" or would you be retrieving them from the database?

This is important. Do you have a table with all the possible values of "cilindro"? If not, and there is any value where the quantity for all products is 0 - there would be no records in the result set.

gralfitox · December 16, 2014

I have a table cilindro with all possible values, and a esfera table too.

What do you mean fixd columns?

Sorry i was editing while you were answering

Edited December 16, 2014 by gralfitox

gralfitox · December 20, 2014

well, studying a little I´ve written this

	<?php require_once('Connections/conexxion.php'); ?>
		<?php
	if (!function_exists("GetSQLValueString")) {
	function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
	{
	  if (PHP_VERSION < 6) {
		$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
	  }
	
	  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
	
	  switch ($theType) {
		case "text":
		  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
		  break;    
		case "long":
		case "int":
		  $theValue = ($theValue != "") ? intval($theValue) : "NULL";
		  break;
		case "double":
		  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
		  break;
		case "date":
		  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
		  break;
		case "defined":
		  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
		  break;
	  }
	  return $theValue;
	}
	}
	
		if (!function_exists("GetSQLValueString")) {
		function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
		{
		  if (PHP_VERSION < 6) {
			$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
		  }
		
		  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
		
		  switch ($theType) {
			case "text":
			  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
			  break;    
			case "long":
			case "int":
			  $theValue = ($theValue != "") ? intval($theValue) : "NULL";
			  break;
			case "double":
			  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
			  break;
			case "date":
			  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
			  break;
			case "defined":
			  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
			  break;
		  }
		  return $theValue;
		}
		}
		
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_Recordset1 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 1";
		$Recordset1 = mysql_query($query_Recordset1, $conexxion) or die(mysql_error());
		$row_Recordset1 = mysql_fetch_assoc($Recordset1);
		$totalRows_Recordset1 = mysql_num_rows($Recordset1);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila2 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 1,2";
		$fila2 = mysql_query($query_fila2, $conexxion) or die(mysql_error());
		$row_fila2 = mysql_fetch_assoc($fila2);
		$totalRows_fila2 = mysql_num_rows($fila2);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila3 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 3,3";
		$fila3 = mysql_query($query_fila3, $conexxion) or die(mysql_error());
		$row_fila3 = mysql_fetch_assoc($fila3);
		$totalRows_fila3 = mysql_num_rows($fila3);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila4 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 6,4";
		$fila4 = mysql_query($query_fila4, $conexxion) or die(mysql_error());
		$row_fila4 = mysql_fetch_assoc($fila4);
		$totalRows_fila4 = mysql_num_rows($fila4);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila5 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 10,5";
		$fila5 = mysql_query($query_fila5, $conexxion) or die(mysql_error());
		$row_fila5 = mysql_fetch_assoc($fila5);
		$totalRows_fila5 = mysql_num_rows($fila5);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila6 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 15,6";
		$fila6 = mysql_query($query_fila6, $conexxion) or die(mysql_error());
		$row_fila6 = mysql_fetch_assoc($fila6);
		$totalRows_fila6 = mysql_num_rows($fila6);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila7 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 21,7";
		$fila7 = mysql_query($query_fila7, $conexxion) or die(mysql_error());
		$row_fila7 = mysql_fetch_assoc($fila7);
		$totalRows_fila7 = mysql_num_rows($fila7);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila8 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 28,8";
		$fila8 = mysql_query($query_fila8, $conexxion) or die(mysql_error());
		$row_fila8 = mysql_fetch_assoc($fila8);
		$totalRows_fila8 = mysql_num_rows($fila8);
		
		mysql_select_db($database_conexxion, $conexxion);
	$query_fila9 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 36,25";
	$fila9 = mysql_query($query_fila9, $conexxion) or die(mysql_error());
	$row_fila9 = mysql_fetch_assoc($fila9);
	$totalRows_fila9 = mysql_num_rows($fila9);
		
		?>
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>test</title>
	</head>
	
	<body>
	<table align=center>
	<tr>
	  <td width="40px;" align="center"> </td>
		<td width="40px;" align="center">0.00</td>
		<td width="40px;" align="center">-0.25</td>
		<td width="40px;" align="center">-0.50</td>
		<td width="40px;" align="center">-0.75</td>
		<td width="40px;" align="center">-1.00</td>
		<td width="40px;" align="center">-1.25</td>
		<td width="40px;" align="center">-1.50</td>
		<td width="40px;" align="center">-1.75</td>
		<td width="40px;" align="center">-2.00</td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_Recordset1['esfera']; ?></td>
	  <td align="center"><?php echo $row_Recordset1['stock']; ?></td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila2['esfera']; ?></td>
	  <?php do { ?><td align="center">
		  <?php echo $row_fila2['stock']; ?>
		  </td><?php } while ($row_fila2 = mysql_fetch_assoc($fila2)); ?>
		<td align="center" bgcolor="#0000FF"> </td>
		
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila3['esfera']; ?></td>
	  <?php do { ?><td align="center">
		  <?php echo $row_fila3['stock']; ?>
		  </td><?php } while ($row_fila3 = mysql_fetch_assoc($fila3)); ?>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila4['esfera']; ?></td>
	  <?php do { ?>
		<td align="center"><?php echo $row_fila4['stock']; ?></td>
		<?php } while ($row_fila4 = mysql_fetch_assoc($fila4)); ?>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila5['esfera']; ?></td>
	  <?php do { ?>
		<td align="center"><?php echo $row_fila5['stock']; ?></td>
		<?php } while ($row_fila5 = mysql_fetch_assoc($fila5)); ?>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila6['esfera']; ?></td>
	  <?php do { ?>
		<td align="center"><?php echo $row_fila6['stock']; ?></td>
		<?php } while ($row_fila6 = mysql_fetch_assoc($fila6)); ?>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila7['esfera']; ?></td>
	  <?php do { ?>
		<td align="center"><?php echo $row_fila7['stock']; ?></td>
		<?php } while ($row_fila7 = mysql_fetch_assoc($fila7)); ?>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila8['esfera']; ?></td>
	  <?php do { ?>
		<td align="center"><?php echo $row_fila8['stock']; ?></td>
		<?php } while ($row_fila8 = mysql_fetch_assoc($fila8)); ?>
	<td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<?php do { ?>
	  <tr>
		<td align="center"><?php echo $row_fila9['esfera']; ?></td>
		<td align="center"><?php echo $row_fila9['stock']; ?></td>
		<td align="center"> </td>
		<td align="center"> </td>
		<td align="center"> </td>
		<td align="center"> </td>
		<td align="center"> </td>
		<td align="center"> </td>
		<td align="center"> </td>
		<td align="center"> </td>
	  </tr>
	  <?php } while ($row_fila9 = mysql_fetch_assoc($fila9)); ?>
	<tr>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	</tr>
	</table>
	   <?php
	
	
	$columnas = 9; # Número de columnas (variable)
	
	
	for ($i=1; $row = mysql_fetch_row ($Recordset1); $i++) {
	$resto = ($i % $columnas); # Número de celda del <tr> en que nos encontramos
	if ($resto == 1) {echo "<tr>";} # Si es la primera celda, abrimos <tr>
		echo "<td width=\"40px;\" align=\"center\">$row[1]</td>"; 
	if ($resto == 0) {echo "</tr>";} # Si es la última celda, cerramos </tr>
	}
	if ($resto <> 0) { # Si el resultado no es múltiple de $columnes acabamos de rellenar los huecos
	$ajust = $columnas - $resto; # Número de huecos necesarios
	for ($j = 0; $j < $ajust; $j++) {echo "<td> </td>";}
	echo "</tr>"; # Cerramos la última línea </tr>
	}
	mysql_close($connexion);
	echo "</table>";
	?> 
	  
	
	
	</body>
	</html>
	<?php
	mysql_free_result($fila1);
	
	mysql_free_result($Recordset1);
	
	mysql_free_result($fila2);
	
	mysql_free_result($fila3);
	
	mysql_free_result($fila4);
	
	mysql_free_result($fila5);
	
	mysql_free_result($fila6);
	
	mysql_free_result($fila7);
	
	mysql_free_result($fila8);
	
	mysql_free_result($fila9);
	
	mysql_free_result($consulta);
	?>

with this result

now I have 25 rows in the table that can be filled up to 9 results, you can see that there are 9 results for 2.00, 9 for 1.75 and so on but I am unable to achive this.

I can see that creating a new query for every row is a solution, but 25 + 9 more querys.... maybe too many queries?

Edited December 20, 2014 by gralfitox

Barand · December 20, 2014

try something like this with a single query

<?php
//
// GET CILINDRO VALUES
//
$sql = "SELECT cilindro FROM cilindro
        ORDER BY cilindro";
$cilindro = array();
$res = $mysqli->query($sql);
while ($row = $res->fetch_row()) {
    $cilindro[] = $row[0];
}
$thead = "<tr><th></th><th>" . join('</th><th>', $cilindro) . "</th></tr>\n";
$initial = array_fill_keys($cilindro, ''); // empty initial array for each row

//
// GET STOCK VALUES
//                
$sql = "SELECT 
   sum(compra+regula_mas-venta-taller-regula_menos)  as stock
 , cilindro
 , esfera 
FROM movimiento  
  JOIN item ON item.id_item=movimiento.id_item 
  JOIN rx ON rx.id_rx=item.id_rx 
  JOIN cilindro ON cilindro.id_cil=rx.id_cil  
  JOIN esfera ON esfera.id_esf=rx.id_esf 
GROUP BY movimiento.id_item 
ORDER BY esfera DESC";

$tdata = '';
$currEsfera = '';
$esfdata = $initial;
$res = $mysqli->query($sql);
while (list($stock,$cil, $esf) = $res->fetch_row()) {
    if ($esf != $currEsfera) {  // change of esfera?
        if ($currEsfera) {  // don't want initial balank values
            $tdata .= "<tr><td>$currEsfera</td><td>".join('</td><td>', $esfdata)."</td></tr>\n";
        }
        $currEsfera = $esf;
        $esfdata = $initial;
    }
    $esfdata[$cil] = $stock;
}
// output row for final esfera value
$tdata .= "<tr><td>$currEsfera</td><td>".join('</td><td>', $esfdata)."</td></tr>\n";
?>
<table border='1'>
<?php echo $thead, $tdata?>
</table>

Barand · December 20, 2014

Correction to above, main query should be GROUP BY esfera, cilindro

SELECT
   sum(compra+regula_mas-venta-taller-regula_menos)  as stock
 , cilindro
 , esfera
FROM movimiento  
  JOIN item ON item.id_item=movimiento.id_item
  JOIN rx ON rx.id_rx=item.id_rx
  JOIN cilindro ON cilindro.id_cil=rx.id_cil  
  JOIN esfera ON esfera.id_esf=rx.id_esf
GROUP BY esfera, cilindro
ORDER BY esfera DESC

gralfitox · December 22, 2014

sorry but no result.

maybe a problem with cilindro values, you´re getting all the cilindro values and in this id_item cilindro values take values from 0.00 to -2.00 in 0.25 steps??

Barand · December 22, 2014

Without data, that's all I can do.

gralfitox · December 22, 2014

sorry...

take a look

Barand · December 22, 2014

Strange you got no results. I ran the code against your data and got this (I reversed the order of the column heads)

gralfitox · December 22, 2014

I'll check again. Thank you anyways

gralfitox · December 23, 2014

hi again, so with this code

<?php require_once('Connections/conexxion.php'); 

mysql_select_db($database_conexxion, $conexxion);

?>
<html>
<body>
<?php
//
// GET CILINDRO VALUES
//
$sql = "SELECT cilindro FROM cilindro
        ORDER BY cilindro";
$cilindro = array();
$res = $mysqli->query($sql);
while ($row = $res->fetch_row()) {
    $cilindro[] = $row[0];
}
$thead = "<tr><th></th><th>" . join('</th><th>', $cilindro) . "</th></tr>\n";
$initial = array_fill_keys($cilindro, ''); // empty initial array for each row

//
// GET STOCK VALUES
//                
$sql = "SELECT
   sum(compra+regula_mas-venta-taller-regula_menos)  as stock
 , cilindro
 , esfera
FROM movimiento  
  JOIN item ON item.id_item=movimiento.id_item
  JOIN rx ON rx.id_rx=item.id_rx
  JOIN cilindro ON cilindro.id_cil=rx.id_cil  
  JOIN esfera ON esfera.id_esf=rx.id_esf
GROUP BY esfera, cilindro
ORDER BY esfera DESC";

$tdata = '';
$currEsfera = '';
$esfdata = $initial;
$res = $mysqli->query($sql);
while (list($stock,$cil, $esf) = $res->fetch_row()) {
    if ($esf != $currEsfera) {  // change of esfera?
        if ($currEsfera) {  // don't want initial balank values
            $tdata .= "<tr><td>$currEsfera</td><td>".join('</td><td>', $esfdata)."</td></tr>\n";
        }
        $currEsfera = $esf;
        $esfdata = $initial;
    }
    $esfdata[$cil] = $stock;
}
// output row for final esfera value
$tdata .= "<tr><td>$currEsfera</td><td>".join('</td><td>', $esfdata)."</td></tr>\n";
?>
<table border='1'>
<?php echo $thead, $tdata?>
</table>

    
</body>
</html>

I got this

Barand · December 23, 2014

You should notice that my code uses mysqli (as you should, or PDO)

Instead of :

mysql_select_db($database_conexxion, $conexxion);

use this to create a mysqli connection:

$mysqli = new mysqli (host, username, password, databasename);

using your credentials.

Edited December 23, 2014 by Barand

gralfitox · December 23, 2014

ok, now works , didn´t realize the mysqli point.

1 more question

1- how do you reversed the order of the column heads?

gralfitox · December 23, 2014

sorry, it´s fixed :happy-04:

thank you very very much

Sign In

show query result on table horizontally

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information