Building 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. ---------------------------------------------------------------------------- ----------------- First things first. Try the script as is and you should get a table. Next all you have to do is somehow set $data then call echo maketable($data); $data is a multidimensional array, specifically 2D array which is an Array of Arrays. An Array is a list of values $data[1] = "foo"; $data[2] = "bar"; $data[3] = "me"; So if you did: $i = 2; echo "List item $i is $data[$i]"; The value $i represents the KEY, and the $data[$i] the VALUE. You should get "List item 2 is bar". This is the basis for while loops of arrays. You can simply change $i and echo out all possible values. PHP doesn't differentiate between different types of arrays. (Like other scripting languages such as Perl) I would call the following an "Associative Array". An Assoc. Array has a meaningful key so; $count["sheep"] = 234; $count["goats"] = 987; $count["cows"] = 123; So how may cows do i have? echo "You have " . $count["cows"] . " number of cows"; NB: You could also; echo "You have $count[cows] number of cows"; But that is bad. But will work. Im not sure on the direction of using unquoted string keys but i was told that it wouldnt be fully supported in PHP4.. Another way of course is; $animal = "cows"; echo "You have $count[$animal] number of cows"; Now a 2D array is an Array of arrays... what would happen if you assigned $count to $data[1] ? You could have farms and their headcount stored in one variable (ie: your 2D array). Most data from databases can be represented by 2D arrays, think of them as Spreadsheets or Tables in that the first key is the row number and the second the field name. $data[1]["field1"] = 1; $data[1]["field2"] = 2; $data[1]["field3"] = 3; $data[2]["field1"] = 4; $data[2]["field2"] = 5; $data[2]["field3"] = 6; 2 rows and 3 fields. (NB: Just because row 1 has 3 fields doesnt mean row 2 must have 3 fields). This example could also be written; $row["field1"] = 1; $row["field2"] = 2; $row["field3"] = 3; $data[1] = $row; $row["field1"] = 4; $row["field2"] = 5; $row["field3"] = 6; $data[2] = $row; For MySQL i think (never used it) mysql_fetch_array will return an array of the row like $row above; $row = mysql_fetch_array(WHATEVER GOES HERE); $data[$i] = $row; or you could; $data[$i] = mysql_fetch_array(WHATEVER GOES HERE); as long as you keep track of $i. (WHich is your key and must be unique for each row). If you are smart however you can do $data[] = mysql_fetch_array(WHATEVER GOES HERE); Since the key isnt set, $data simply sets the key value to the next available value. This should give you enough to use the example i provided and answer your first 3 questions. Question 4 you should look up reset() (The first isnt required but i like to be thorough). Q5 is yes, reset is very different to unset() .. again RTFM. Q6 answer is no, they are just names (Column Name, Array Key, and an array representing one row). Q7. Answer is NO, you should try running the script as it will work with little effort, also since $col etc are inside a function and not marked as global they can not be effected by changes outside the function. The last hint is to look at include() this means you can put the maketable() function in a separate file and call it from every script you have. Nice huh? Imagine having 100 different pages with include("/path/to/includefile.inc"); // Do you mysql stuff to create $data echo maketable($data); This is an important programming concept as you dont have to do the same thing over and over. Lazyness is a virtue. Damn, thats 15 minutes of my life i'll never get back :) But hey i hope it helps. mn -----Original Message----- From: Nick [mailto:nick@carrierworld.com] Sent: Thursday, July 13, 2000 5:53 PM To: php general mailin list; Nold, Mark Subject: Re: amount of elements in an array ? - building dynamic html tables for sets of MySQL results hi, just a short note to say thanks for your email..... however, i have only been programming for 4 weeks :( and there are a couple of elements i havent understood in your script... *what is a 2D array and how to I read my results into one ? What is the difference between a 2D array and the array returned by mysql_fetch_array ? * I don't understand what some of the arrays represent: $data (how do i use/make this ?); why do you "reset ($ary)" so many times ? Isn't that the results array ? Are $col, $key, $row predefined mysql result variables ? As in, do i need to set any of these variables elsewhere or will the script know what i mean when i use these variables? i think that's about it..... thanks a lot for taking the time to answer my original query, i appreciate it and have already learnt quite a lot from your script (like using $table / return $table / echo to print the table out...). thanks for bearing with my beginners' questions :p cheers, nick ----- Original Message ----- From: Nold, Mark <mailto:Mark.Nold@normandy.com.au> To: 'Nick' <mailto:nick@carrierworld.com> ; php general mailin list <mailto:php-general@lists.php.net> 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 -----Original Message----- From: Nick [ mailto:nick@carrierworld.com <mailto:nick@carrierworld.com> ] Sent: Tuesday, July 11, 2000 9:13 PM To: php general mailin list Subject: amount of elements in an array ? - building dynamic html tables for sets of MySQL results i'm trying to write a script that dynamically generates HTML tables depending on the results from an SQL query. So far, I have managed to set the HTML table headers to be the same as those in the MySQL table. [thanks to rod k -cheers !] Now i'm trying to put the results in the table. Basically, I need a MySQL function to retrieve data and put it into the table until there is no data left.... my problem basically is that I do not have a way of putting the data into the table unless i know how many elements to put in before creating a new <tr>[html table row]. Is there a way of finding out the amount of elements in an array ? Then I could do something like: while($array=mysql_fetch_row ($this->Query_ID)): $i = 0; $j = [amount of elements in $array]; echo "<tr>"; while ($i < $j): echo "<tr><td>$db->Rowdata[$i]</td> <td>"; $i++; endwhile; echo "</tr>"; any ideas ? -nick