Jump to content

Table structure based on row count?


Round
Go to solution Solved by Barand,

Recommended Posts

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 by Round
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by QuickOldCar
Link to comment
Share on other sites

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 by Barand
  • Like 2
Link to comment
Share on other sites

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 by Round
Link to comment
Share on other sites

  • Solution

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>";
?>
  • Like 1
Link to comment
Share on other sites

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 by kicken
Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

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 by Round
Link to comment
Share on other sites

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.

×
×
  • 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.