How to populate array(s) with data retrieved from an SQL database and output to HTML Table

[Dezzy217]

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.

[Psycho]

You could just create the output when you retrieve the records from the database result - no need to create an array as that only adds overhead. But, it is simple enough

 

 

$query = "SELECT * FROM table";
$result = mysql_query($query);
 
$dataArray = array();
while($row = mysql_fetch_assoc($result))
{
    $dataArray[] = $row;
}

[Dezzy217]

The way I'm wanting this to output is in the following format

 

 

Column 1        Column 2                  Column 3

 

 - First Row    - First Row Description   - First Row Data

 

 - Second Row   - Second Row Description  - Second Row Date

 

 - Third Row    - Third Row Description   - Third Row Date

 

 

 

I'm wanting to retrieve the data based on the ROWID and then input into an Array (might require a loop) before starting the echoing this out into the table to the desired output.

 

 

The method you've suggested posts echo's out all results one after another. I'm wondering if theres a way to output in the format above?

 

Thanks

[adam_bray]

Taking the example posted above, surely all you need to do is this - 

$query = "SELECT * FROM table";
$result = mysql_query($query);
 
print '<table>';

while($row = mysql_fetch_assoc($result))
{
   print '<tr>';
   print '<td>'.$row['id'].'</td><td>'.$row['description'].'</td><td>'.$row['date'].'</td></tr>';
}
print '</table>';

[Psycho]

I'm wanting to retrieve the data based on the ROWID and then input into an Array (might require a loop) before starting the echoing this out into the table to the desired output.

 

 

The method you've suggested posts echo's out all results one after another. I'm wondering if theres a way to output in the format above?

 

What do you mean "The method you've suggested posts echo's out all results one after another". You asked for a way to put the results of the query into an array and that was what I provided. If you are talking about my comment

 

 

You could just create the output when you retrieve the records from the database result . . .

Then one of us is failing to understand (hint: it's not me). Whether you output the results while retrieving the records from the query result or if you output then from an array makes absolutely no difference. There are valid reasons to drop results from a query into an array, but the reason you are supposing is not one of them. See adam's example above.

Edited March 27, 2014 by Psycho