HTML Table from array

[gvp16]

I have a table with x ammount of rows and x ammount of columns, in each TD there is an input box which the user fills in, when they press save the information is input into the database in this form at

 

6,1,2,3,4,5,6,A,1,2,3,4,5,6,B,1,2,3,4,5,6  - the first number is the number of columns, 1- 6 is the top line of TDs (column headers) and then A 1-6 is row 1, B 1-6 is row 2 etc..

 

so i then out put the information into a table via a for loop :

 

while($row = mysql_fetch_array($r))
{
	$data = explode(',',$row["content_bottom"]);
	$datacount = count($data);

	echo"<table border='1'><tr><td>Tool Type</td>";
	$k = 1;
	for ($i = 1; $i <= $datacount; $i++) {

		if($data[$i] !=""){
		echo "<td>".$data[$i]."</td>";
		if($k == $data[0]-1)
		{
			echo"</tr><tr>";
			$k = -1;
		}
		$k++;
		}
	}
	echo"</table>";

 

I can see how i would delete a row of information, eg delete B 1-6, but any ideas how I could delete a column? or is there a better way of storing this information?

[scootstah]

Why not store it as a serialized mufti-dimensional array? Something like...

 

array(
     array(1,2,3,4,5,6),
     array(1,2,3,4,5,6)
)

 

Then you can traverse to the row and column that you want pretty easily.

[gvp16]

hmm...thats not something ive done before, the table is to contain a string in each cell, would it still work for that? also how would I for example remove column 2 from your example (2,2)?

 

Thanks

[scootstah]

Yes, it will still work.

$array = array(
array('col1', 'col2', 'col3', 'col4', 'col5', 'col6'),
array('col1', 'col2', 'col3', 'col4', 'col5', 'col6')
);

// remove column 2 from row 2
unset($array[1][1]);

 

 

[gvp16]

ok that seems to be exactly what I want, how would i remove a whole row (array 2 for example), also what format would I store this in a database?

 

Thanks for you replies.

[scootstah]

To remove row 2 just do unset($array[1]);

 

Remember that array indexes start at 0 so row 2 is actually index 1.

 

To store it in the database just serialize it. $array = serialize($array). Store it in a TEXT column. To get it back to array format, use unserialize().

[gvp16]

ok great thanks, ill give it ago.

 

Thanks very much

[Psycho]

If you are going to store this information in the database why are you going through all this work with arrays? If you are going to manage the data by rows, then you should store it by rows. Normally I advise against storing comma separated/serialized data in the database, but since the number of columns will be dynamic it may make sense to store each row in such a fashion.

 

So, take each row of data, serialize it and store it in the database with an index to identify which position in the table it is. Something like

table_id | row_index | row_data

 

You could then get the records for a table using

SELECT row_index, row_data FROM table_name WHERE table_id = 3 ORDER BY row_index

 

Although, if you need to manage the data on a record by record basis you really should store each cell as a unique piece of data. You could then get each row of data as a singe record (regardless of how many columns there are) using a GROUP BY clause and GROUP_CONCAT()

[gvp16]

i didnt want to set up columns for each table cell because the number of rows/ columns will be determined by the user when inputting the information, so I decided it would be better to store all the information in 1 field and then split it up for formatting, but i also need to consider the user wanting to add/ remove a column/ row after the table has been created.

 

Thanks

[scootstah]

You could do something like this:

table_columns

id | data

 

table_rows

id | column_id | data

 

Then it would look like:

 

table_columns

id | data
1  | column 1
2  | column 2
3  | column 3


id | column_id | data
1  | 1         | row 1 in column 1
2  | 1         | row 2 in column 1
3  | 1         | row 3 in column 1
4  | 1         | row 4 in column 1
5  | 1         | row 5 in column 1
6  | 1         | row 6 in column 1
7  | 2         | row 1 in column 2
8  | 2         | row 2 in column 2
9  | 2         | row 3 in column 2
10 | 2         | row 4 in column 2
11 | 2         | row 5 in column 2
12 | 2         | row 6 in column 2

 

Then use a query like this to get it:

SELECT c.id AS col_id, c.data AS col_data, r.id AS row_id, r.column_id AS row_column_id, r.data AS row_data
FROM table_columns AS c
LEFT JOIN table_rows AS r ON r.column_id = c.id

 

 

There. Easier to work with, and still dynamic.

[Psycho]

i didnt want to set up columns for each table cell because the number of rows/ columns will be determined by the user when inputting the information, so I decided it would be better to store all the information in 1 field and then split it up for formatting, but i also need to consider the user wanting to add/ remove a column/ row after the table has been created.

 

The fact that the number of columns/rows is dynamic has no bearing on the ability to properly store the data in a database. Don't revert to being lazy and using arrays because you don't know how to do it. Use this as a learning exercise. I already provided a database schema for storing each field in a logically formatted database. From that point, adding/removing rows or columns is a very easy operation.

[gvp16]

Thanks for all the replies guys, mjdamato i took your advise it gave it ago and it works exactly as i need it to, good learning exercise.

 

Thanks very much.