List data from custom database table in html table?

[elijahpaul]

I've created a custom table in the WHMCS DB, and would now like to display the contained table data on a custom page I've also created, in the form of an html table.

 

In mycustompage.php, I've passed the data to an array like so:

 

$result = mysql_query("SELECT * FROM tblclientscustom WHERE userid=".$ca->getUserID());
  $data = mysql_fetch_array($result);
  $customdata = $data;     

  $ca->assign('customdata', $customdata);

 

Then in mycustompage.tpl (using the clientareainvoices.tpl as a 'template') I've done the following:

 

{foreach from=$customdata item=customdata}
       <tr>
           <td>{$customdata.id}</td>
           <td>{$customdata.domain}</td>           
           <td>{$customdata.email}</td>
           <td>{$customdata.organization}</td>
    <td >{$customdata.timestamp}</td>
           <td><a href="viewcustdata.php?id={$customdata.id}">{$LANG.customdataview}</a></td>
       </tr>
{foreachelse}
       <tr>
           <td colspan="5">{$LANG.norecordsfound}</td>
       </tr>
{/foreach}

 

However the above just populates every row with the results from the first row of my custom DB table (tblclientscustom).

 

Have I made a mistake in setting the variable/array $customdata?

 

Also what is the 'item=customdata' setting? in the clientareainvoices.tpl template it's set to 'item=invoice'. Have I set this wrong too?

 

Any help appreciated.

Edited November 16, 2013 by elijahpaul

[pablobaldovi]

You are using the same variable name.

Try with something like:

 

{foreach from=$fields item=field}

<tr>

<td>{$field.id}</td>

<td>{$field.domain}</td>

<td>{$field.email}</td>

<td>{$field.organization}</td>

<td >{$field.timestamp}</td>

<td><a href="viewcustdata.php?id={$field.id}">{$LANG.customdataview}</a></td>

</tr>

{foreachelse}

<tr>

<td colspan="5">{$LANG.norecordsfound}</td>

</tr>

{/foreach}

 

- - - Updated - - -

 

using the same plural variable name from code,

$ca->assign('fields', $customdata);

[elijahpaul]

Thanks for your reply.

 

Ok. with the above I receive a very strange result.

 

For just the first DB row, the template table prints just the first character of each entry in every column for two rows!

 

I think a couple of illustrations may explain it better.

 

e.g. if my custom db table contains the following:

 

 Custom WHMCS database table

+----+------------------+------------------------+--------------------------+------------+
| id |      domain      |         email          |       organization       | timestamp  |
+----+------------------+------------------------+--------------------------+------------+
| 45 | ahab-fishing.com | [email]quint@ahab-fishing.com[/email] | Orca Inc                 | 15/11/2013 |
| 77 | oi.com           | [email]hooper@oi.com[/email]          | Oceanographic Institute  | 13/11/2013 |
+----+------------------+------------------------+--------------------------+------------+

 

The template code (you suggested) produces the following HTML table result:

 

 HTML Table Output

+----+--------+-------+--------------+-----------+
| id | domain | email | organization | timestamp |
+----+--------+-------+--------------+-----------+
| 4  | 4      | 4     | 4            | 4         |
| 4  | 4      | 4     | 4            | 4         |
| a  | a      | a     | a            | a         |
| a  | a      | a     | a            | a         |
| q  | q      | q     | q            | q         |
| q  | q      | q     | q            | q         |
| O  | O      | O     | O            | O         |
| O  | O      | O     | O            | O         |
| 1  | 1      | 1     | 1            | 1         |
| 1  | 1      | 1     | 1            | 1         |
+----+--------+-------+--------------+-----------+

 

As you can see, every column for two (twice) rows in the generated html table contains just the first character of each field from the custom DB table.

 

Also the second database row is ignored by the {foreach} (it seems).

 

Could this be due to the way the array is formatted via the 'mysql_fetch_array($result);' query?

Edited November 16, 2013 by elijahpaul

[Kian]

Because you are trying to iterate {$customdata} in a foreach while {$customdata} is a string and not an array. The error is here.

 

$result = mysql_query("SELECT * FROM tblclientscustom WHERE userid=".$ca->getUserID());
  $data = mysql_fetch_array($result);
  $customdata = $data;     

  $ca->assign('customdata', $customdata);  

 

Change to.

 

$result = mysql_query("SELECT * FROM tblclientscustom WHERE userid=".$ca->getUserID());
while($row = mysql_fetch_assoc($result))
{
$customdata[] = $row;	
}
$ca->assign('customdata', $customdata);

[elijahpaul]

 

Change to.

 

$result = mysql_query("SELECT * FROM tblclientscustom WHERE userid=".$ca->getUserID());
while($row = mysql_fetch_assoc($result))
{
$customdata[] = $row;	
}
$ca->assign('customdata', $customdata);

 

Thank you so much for this Kian. I spent the night brushing up on my MySQL (and 'while') and came up with the below:

 

$result = mysql_query("SELECT id, domain, email, organization, timestamp FROM tblclientscustom WHERE userid=".$ca->getUserID());

  $i = 0;

  while ($data = mysql_fetch_array($result))
     {		

	$id = $data['id'];
	$domain = $data['domain'];
	$email = $data['email'];
	$org = $data['organization'];
	$timestamp = $data['timestamp'];		

	$customdata[$i]['id'] = $id;
	$customdata[$i]['domain'] = $domain;
	$customdata[$i]['email'] = $email;
	$customdata[$i]['organization'] = $org;
	$customdata[$i]['timestamp'] = $timestamp;

	++$i;

}

  $ca->assign('customdata', $customdata);

 

Which works, but obviously your solution is far better.

 

Thanks again.