Fw: Building html tables for sets of MySQL results

RE: amount of elements in an array ? - building dynamic html tables for sets of MySQL results ----- Original Message ----- From: Nick To: php general mailin list ; Nold, Mark Sent: Friday, July 14, 2000 11:59 AM Subject: Re: Building html tables for sets of MySQL results hi mark, thanks a lot for the time you took out to reply to my message. After a while i did get the code to work - my problem was caused by the fact that mysql_fetch_array does not return a 2D array for some reason. That can be fixed by using while ($data[]=mysql_fetch_row($result)){} (it works also with mysql_fetch-array, but for some reason this has the effect of duplicating the columns- one with the column name, the next containing the same result set but with the column number....) I guess what this function does is parses the information from each row into $data, with the row number as a first key and the field number as the second key. mysql_fetch_row returns true as long as there are rows with results in them hence the while.... I have actually found another way to build a table from a mysql query using my own brains (well whatever's left), which i developed coz i couldn't get your version to work. This is the result: function maketable2 ($query) { $db = new DBconnect; //connects to the db using a mysql db class called thru an include $db->dbquery($query); file://queries using ibid; #first build table echo "<table><table border=1 bgcolor='#eeeeee'>"; // whatever #build table headlines; echo "<tr>"; while ($column3= $db->fetch_fields()): // quite simply a mysql_fetch_fields in the db class. this nifty mysql function fetches the name of the columns pertaining to our query echo "<th>$column3[name]</th>"; // "name" is the key to the field name in the array returned by mysql_fetch_fields endwhile; #enter results into database while($db->next_record()): // another db class function. this returns true as long as there are results to be read from the table $i = 0; $j = sizeof($db->Record); echo "<tr>"; while ($i < $j) { echo "<td>", $db->Record[$i], "</td>"; // the array containg the mysql_fetch_array results - a part of the next_record function $i++;} endwhile; #close table echo "</table>";} # now this is how to use this function : $query= "select * from dbtable"; echo maketable2($query); ?> I'm posting this in case anyone else is interested. To use your class with mysql (again, in case anyone is interested), add the lines : mysql_connect ('host', 'username', pass'); $query= "select * from table"; $result= mysql_db_query("database", $query); while ($data[]=mysql_fetch_row($result)){} To the top of the code.... Anyways, thanks again for your detailed answer, which i really appreciate and thanks to which i now kind of understand multidimensional arrays (and how to use and read into them) - (all references to keys etc... result from your email ! let me know if a misunderstanding is apparent....) thanks again, nick ----- Original Message ----- From: Nold, Mark To: 'Nick' ; php general mailin list Sent: Wednesday, July 12, 2000 2:06 AM Subject: RE: amount of elements in an array ? - building dynamic html tables for sets of MySQL results --------------------------------------------------------------------------------------------- Disclaimer: The information contained in this email is intended only for the use of the person(s) to whom it is addressed and may be confidential or contain legally privileged information. If you are not the intended recipient you are hereby notified that any perusal, use, distribution, copying or disclosure is strictly prohibited. If you have received this email in error please immediately advise us by return email at postmaster@normandy.com.au and delete the email document without making a copy. --------------------------------------------------------------------------------------------- What i would suggest is that you first throw your entire results into a 2 Dimensional array, then write a function for taking a 2D array into a table. Try the following. <? function maketable($ary){ $table = "<table border=1 cellspacing=0>\n"; reset($ary); $firstrow = current($ary); $table .= "<tr>"; while(list($col,$val) = each($firstrow)){ $columns[] = $col; $name = ucwords($col); // You could do lots of nice transformations here.. $table .= "<th>$name</th>"; } $table .= "</tr>\n"; reset($ary); while(list($key,$row) = each($ary)){ $table .= "<tr>"; reset($columns); while(list($key,$col) = each($columns)){ $val = $row["$col"]; $table .= "<td>$val</td>"; } $table .= "</tr>\n"; } $table .= "</table>\n"; return $table; } // Now this comes from your DB. $data[1]["field1"] = 1; $data[1]["field2"] = 2; $data[1]["field3"] = 3; $data[2]["field1"] = 4; $data[2]["field2"] = 5; $data[2]["field3"] = 6; echo maketable($data); ?> $data should be from your DB though. This does one odd thing in that it looks at the first row to set $columns, this is so the right data from the right field goes into the correct column. I would suggest that you put something like this function into an include file that you can then call from anywhere. Once you develop it more you could make it into a class where you could set anything from background colours to css classes. NB: This method is slower (slightly.. depending on the size of your tables) than building the table as you return the data, but it is generic and reusable. (Imaging never coding a table just call maketable() and its done :) In my opinion this is more important than slight speed differences. (Our extremely complex table classes take about 5 seconds to build a 3000x20 table so hey, most are _much_ smaller) Good luck, mn