0

I'm wondering how to populate array(s) with data retrieved from an SQL database and outputting this into a HTML table.

I'm using the following table structure

CREATE TABLE PROJECT_RECORDS (
RECORDS_ROWID number(3) not null,
RECORDS_LISTCOLUMNID number (3) not null,
RECORDS_RECORDVALUE varchar2 (25),
constraint PROJECT_RECORDS primary key(RECORDS_ROWID, RECORDS_LISTCOLUMNID));

Sample Data

RECORDS_ROWID RECORDS_LISTCOLUMNID RECORDS_RECORDVALUE     
------------- -------------------- --------------------
        1                    1     Sample                    
        1                    2     Sample description          
        2                    1     Data                      
        2                    2     Data Description

From this I want to populate a html table e.g.

|Sample | Sample description |
|Data   | Data Description   |

I have currently only been able to select the data and place into a single array with all of the RECORDS_RECORDVALUE into a single table column which isn't what I want.

I believe you would populate an array using a loop for each RECORDS_ROWID and then echo this into a table. Any help would be greatly appreciated.

Improve this question
2
  • You may get more helpful answers by tagging with the specific DBMS you are using and removing the tags for speculative implementation details (foreach, while-loop). Since you are asking for implementation advice, it's better not to bias that advice with implementation-related tags. Commented Mar 26, 2014 at 18:36
  • Although your question after this is a duplicate of this one, I propose to treat this one as the duplicate, as the later one contains your attempt. Please don't ask questions more than once, as it creates unnecessary work. Commented Mar 27, 2014 at 19:09

3 Answers 3

Reset to default
1

First, you need a connection to your database, so:

<?php
$con=mysqli_connect("localhost","your username","your password","your database");

if (mysqli_connect_errno())
  {
 echo "Failed to connect to MySQL: " . mysqli_connect_error();
 }

Then, make a variable with your MySQL Query for the information you want to pull up, so:

$result = mysqli_query($con,"SELECT * FROM project_records");

That will query your database. Now, echo out your query in table form:

 echo "<table border='1' cellpadding='10'>";
    echo "<tr> <th>Sample</th> <th>Sample Description</th></tr>";

 while($row = mysqli_fetch_array($result))
 {
  echo '<td>' . $row['name of your desired outputted column'] . '</td>';
  echo '<td>' . $row['name of your  other desired outputted column'] . '</td>';
  echo "</tr>"; 
  }
 echo "</table>";
  ?>

That should work. Now I just made a two column table, but I assume you know enough html to make the exact table you want.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

It's probably worth wrapping string output in htmlentities, otherwise an XSS vulnerability may be introduced. Also, it is better to allow static HTML to be stored as HTML, rather than wrapping it in echo tags, so that your IDE can understand the structure of the document.
1

Replace the code in the echo tag the field name.. 

<table border="1" width="100%" cellspacing="0" cellpadding="0">
    <tr>
        <td>Sample</td>
        <td>Sample Description</td>
        <td>Data</td>
        <td>Data Description</td>
    </tr>
<?php
$stmt = mysql_query("SELECT * from PROJECT_RECORDS");
while ($data = mysql_fetch_array($stmt))
{
    <tr>
        <td><?php echo $data['Sample'];?></td>
        <td><?php echo $data['Sample_Description'];?></td>
        <td><?php echo $data['Data'];?></td>
        <td><?php echo $data['Data_Description'];?></td>
    </tr>
    <?php } ?>
</table>
Improve this answer

1 Comment

It's probably worth wrapping string output in htmlentities, otherwise an XSS vulnerability may be introduced. +1 for not wrapping all the HTML in echo statements, though!
0

The following code could solve your problem.

<?php
$link=mysqli_connect("host","username","password","database") or die("Error " . mysqli_error($link));

$result = mysqli_query($link,"SELECT * FROM PROJECT_RECORDS");

$counter = 1;

echo "<table border='1'>";
    echo "<tr> <th>Serial No.</th><th>Row ID</th> <th>Column ID</th> <th>Sample Description</th></tr>";

while($row = mysqli_fetch_assoc($result)) {
  echo '<td>' . $counter .'</td>'.
  echo '<td>' . $row['RECORDS_ROWID'] . '</td>';
  echo '<td>' . $row['RECORDS_LISTCOLUMNID'] . '</td>';
  echo '<td>' . $row['RECORDS_RECORDVALUE'] . '</td>';

  echo "</tr>"; 
}
 echo "</table>";
  ?>
Improve this answer

1 Comment

It's probably worth wrapping string output in htmlentities, otherwise an XSS vulnerability may be introduced. Also, it is better to allow static HTML to be stored as HTML, rather than wrapping it in echo tags, so that your IDE can understand the structure of the document.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.