Table structure based on row count?

[Round]

Hello All,
 
I really hope I explain this properly.
 
What I am attempting to achieve and I'm failing miserably at is to create the table layout based on the number of rows returned from a query.
 
Basically if 1 row is returned I want a table to be structured like:

<table>
<tr>
<td></td>
</tr>
</table>

 If 2 rows are returned structure like:

<table>
<tr>
<td></td>
<td></td>
</tr>
</table>

if 3 rows are returned structure like:

<table>
<tr>
<td></td>
<td></td>
</tr>
<tr>
<td colspan="2"></td>
</tr>
</table>

The number of rows returned by the query is infinite.

 

The last row of the table needs to be spanned across the 2 columns if the row count returned is odd.

If the row count returned is equal I want each row of the table to have two columns.

 

I know that if only one row is returned I could use:

<table>
<tr>
<td colspan="2"></td>
</tr>
</table>

to satisfy any if's/loops.

 

I am really struggling with the fact the number of record rows returned is unknown.

 

Any help much appreciated

Edited May 10, 2016 by Round

[benanamen]

What your wanting to do with the colspan doesn't make any sense. To handle the other part is simple.

<table>
<?php foreach ($result as $row) : ?>
<tr><td><?= $row['some_column'] ?></td></tr>
<?php endforeach; ?>
</table>

Edited May 10, 2016 by benanamen

[gizmola]

Ok, so what have you tried so far?

 

Are you getting the row count before you start to output the table?

 

What determines when a new table row should be output?  Your description doesn't provide that information, and it's necessary.  I am guessing from the colspan = 2 that you intend to emit one every 2 columns.

 

Speaking candidly, this is a rather trivial problem.   You should be able to figure out what you need using the modulus operator:  '%'.  See:  http://php.net/manual/en/language.operators.arithmetic.php

[ginerjm]

Your sample seems to boil down to this:

if only one row simply show the table with that one column.

If more than one row, always show two records per row until the end.

That's not hard

 

echo "<table>";
$eof = false;
while (!$eof)
{
if (false=$row=$qrslts->fetch()))
$eof = true;
else
{
echo "<tr>";
echo "<td>".$row['fld1']...."</td>";
if (false = $row=$qrslts->fetch())
$eof = true;
else
{
echo "<td>".$row['fld1']..."</td>";
}
echo "</tr>";
}
}
echo "</table";

I'll let you figure out that other problem.

Edited May 10, 2016 by QuickOldCar

[Barand]

Am I right in my assumption that you are selecting as single field EG

SELECT name FROM tablename;

If, say, there are five rows then, instead of this

+--------+
| name 1 |
+--------+
| name 2 |
+--------+
| name 3 |
+--------+
| name 4 |
+--------+
| name 5 |
+--------+

you want

+--------+--------+
| name 1 | name 2 |
+--------+--------+
| name 3 | name 4 | 
+--------+--------+
|      name 5     |
+-----------------+

If so, I'd use array_chunk to put the data in pairs
 

$sql = "SELECT CONCAT(fname,' ',lname) as name
        FROM pupil";
$res = $pdo->query($sql) ;
$data = array_chunk($res->fetchAll(), 2);

echo "<table border='1'>";

foreach ($data as $row) {
    echo "<tr>";
    if (count($row)==2) {
        foreach ($row as $n) echo "<td>{$n['name']}</td>";
    }
    else echo "<td colspan='2'>{$row[0]['name']}</td>";
    echo "</tr>\n";
}

echo "</table>";

Edited May 11, 2016 by Barand

[Round]

Exactly barand.

 

Thanks

 

I will have a look and try your example

[Round]

Ok so I have tried the example and as I'm using mssql and not mysql I have had to change it a bit.

 

I now have:

$sqlsn = "SELECT system_name FROM tbl_sub_systems WHERE section=\"21a\"";

$rssn = mssql_query( $sqlsn, $conn)
	or die ("Cannot execute");
	
$data = array_chunk(mssql_fetch_array($rssn), 2);

echo "<table border=\"1\">";

foreach ($data as $row)
{
    echo "<tr>";
    if (count($row)==2)
	{
        foreach ($row as $n) echo "<td>".$n['system_name']."</td>";
    }
    else 
	{
		echo "<td colspan=\"2\">".$row[0]['system_name']."</td>";
	}
    echo "</tr>";
}

echo "</table>";

But it only ever return a count of 2.

 

So it only creates 1 row of the table which oddly displays the first letter of the first result in each column.

 

So for instance I have 3 records:

 

AB

CD

EF

 

The desired result being

+--------+--------+
|   AB   |   CD   |
+--------+--------+
|        EF       |
+-----------------+

 but I am getting

+--------+--------+
|    A   |    A   |
+--------+--------+

If the records are:

 

Z1

Y1

X1

 

I get:

+--------+--------+
|    Z   |    Z   |
+--------+--------+

For starters I don't even understand why it's only displaying the first letter??

 

I am truly lost.

 

Many thanks

Edited May 11, 2016 by Round

[benanamen]

I am interested to know what you are doing that requires that kind of formatting.

[Barand]

You will need to process the query results differently if you are not using PDO. Try

<?php
$sqlsn = "SELECT system_name FROM tbl_sub_systems WHERE section=\"21a\"";

$rssn = mssql_query( $sqlsn, $conn)
    or die ("Cannot execute");

//
// read data into an array first 
//   
$rows = [];
while ($row = mssql_fetch_row($rssn)) {
    $rows[] = $row[0];
}

//
// now chunk this array
//
$data = array_chunk($rows,2);

echo "<table border=\"1\">";

foreach ($data as $row)
{
    echo "<tr>";
    if (count($row)==2)
    {
        foreach ($row as $n) echo "<td>".$n."</td>";
    }
    else 
    {
        echo "<td colspan=\"2\">".$row[0]."</td>";
    }
    echo "</tr>";
}

echo "</table>";
?>

[kicken]

You should not be using the mssql_* functions. They have been deprecated for a while, and removed in PHP 7.

 

MSSQL Extension

Warning This feature was REMOVED in PHP 7.0.0.

 

Alternatives to this feature include:

PDO_SQLSRV (Windows only)

PDO_ODBC

SQLSRV (Windows only)

Unified ODBC API

Edited May 12, 2016 by kicken

[Round]

 

You will need to process the query results differently if you are not using PDO. Try

<?php
$sqlsn = "SELECT system_name FROM tbl_sub_systems WHERE section=\"21a\"";

$rssn = mssql_query( $sqlsn, $conn)
    or die ("Cannot execute");

//
// read data into an array first 
//   
$rows = [];
while ($row = mssql_fetch_row($rssn)) {
    $rows[] = $row[0];
}

//
// now chunk this array
//
$data = array_chunk($rows,2);

echo "<table border=\"1\">";

foreach ($data as $row)
{
    echo "<tr>";
    if (count($row)==2)
    {
        foreach ($row as $n) echo "<td>".$n."</td>";
    }
    else 
    {
        echo "<td colspan=\"2\">".$row[0]."</td>";
    }
    echo "</tr>";
}

echo "</table>";
?>

 

 

Works perfectly.

 

Many thanks

[Round]

You should not be using the mssql_* functions. They have been deprecated for a while, and removed in PHP 7.

 

MSSQL Extension

 

The server is the version it has always been. I don't control the server.

 

PHP was upgraded when it needed to be and has stayed the same since.

 

Haven't had any issues still using mssql_

 

It seems sqlsrv is an easier transition for the code to be changed.

 

I will be looking into upgrading requests and code change.

 

Many thanks

Edited May 12, 2016 by Round