Displaying MYSQL database table into PHP table |
Displaying MYSQL database table into PHP table |
joyful |
Mar 30 2011, 11:43 PM
Post
#1
|
Advanced Member Group: Members Posts: 239 Joined: 15-November 10 Member No.: 13,147 |
Hey,
I have set up a MYSQL database and added a table with some data. I want to display this in a table in PHP that is 3 wide, like this: CODE <table width="100" border="0" cellspacing="0" cellpadding="0"> <tr> <td>item1</td> <td>item2</td> <td>item3</td> </tr> <tr> <td>item4</td> <td>item5</td> <td>item6</td> </tr> <tr> <td>item7</td> <td>item8</td> <td>item9</td> </tr> </table> and so on... Now, I have been using this PHP: CODE <?php //connect to the server $link = mysql_connect('localhost', 'root', ''); if (!$link) { die('Could not connect: ' . mysql_error()); } //connect to the database mysql_select_db(product_index); //query the database $query = mysql_query("SELECT * FROM products WHERE type = 'bracelets'"); //fetch the results / convert results into an array WHILE($rows = mysql_fetch_array($query)): $product_name = $rows['product_name']; $id = $rows['id']; $description = $rows['description']; $price = $rows['price']; $image_large = $rows['image_large']; $image_thumb = $rows['image_thumb']; $page_link = $rows['page_link']; $purchase_link = $rows['purchase_link']; echo "$product_name<br>$description<br>$price<br>$image_large<br>$image_thumb<br>$page_link<br>$purchase_link<br><br><br>"; endwhile; ?> This works great, but I what to display this on a table that is 3 wide and repeats displaying on to a another line after 3 and so on. I found (what I want) this forum post but could not figure out how to apply it to mine: http://php.bigresource.com/Track/php-C1yROxlq/ Please note, obviously I do not want the checkbox in the forum, I just the table they use. Thanks in advance. -- This post has been edited by joyful: Mar 30 2011, 11:46 PM |
Frederiek |
Mar 31 2011, 01:47 AM
Post
#2
|
Programming Fanatic Group: Members Posts: 5,146 Joined: 23-August 06 From: Europe Member No.: 9 |
The following article may be of help: http://devzone.zend.com/node/view/id/641 .
|
joyful |
Mar 31 2011, 02:06 PM
Post
#3
|
Advanced Member Group: Members Posts: 239 Joined: 15-November 10 Member No.: 13,147 |
Hey,
Thanks Frederiek that page was very helpful... I was able to put the MYSQL data into a table in php with this code: CODE <?php // set database server access variables: $host = "localhost"; $user = "root"; $pass = ""; $db = "product_index"; // open connection $connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!"); // select database mysql_select_db($db) or die ("Unable to select database!"); // create query $query = "SELECT * FROM products"; // execute query $result = mysql_query($query) or die ("Error in query: $query. ".mysql_error()); // see if any rows were returned if (mysql_num_rows($result) > 0) { // yes // print them one after another echo "<table cellpadding=10 border=1>"; while($row = mysql_fetch_row($result)) { echo "<tr>"; echo "<td>"."<center>".$row[7]."<br>".$row[2]."<br>".$row[0]."<br>".$row[4]."<br>".$row[8]."</center>"."</td>"; echo "<td>" . $row."</td>"; echo "<td>".$row."</td>"; echo "</tr>"; } echo "</table>"; } else { // no // print status message echo "No rows found!"; } // free result set memory mysql_free_result($result); // close connection mysql_close($connection); ?> It looks like this: The only issue now is: I want to display this (my data) from left to right up to 3 columns wide and then have it go to a new line. How can I have it automatically organize it in this way? It seems to my that the guy on this forum managed to do this (I just do not understand it). Thanks a lot -- |
Brian Chandler |
Mar 31 2011, 10:49 PM
Post
#4
|
Jocular coder Group: Members Posts: 2,460 Joined: 31-August 06 Member No.: 43 |
I really think you need to go and read some tutorials on elementary programming, and/or elementary html.
It's not clear quite what html output you want, and it's not clear what you don't understand about php either. Well, OK, I'll guess... You have something like (pseudocode: you have to fill in the details) CODE while ( new-item-from-database) echo '<tr><td>', details-of-item, '</td></tr>'; But you want to start a new row of the table only every three items?? So you need to keep track of the column. CODE define('COLS', 3); // number of columns $col = 0; // number of the last column filled echo '<tr>'; // start first row while ( new-item-from-database) { $col++; if ($col == COLS) // have filled the last row { $col = 0; echo '</tr><tr>'; // start a new one } echo '<td>', details-of-item, '</td>'; } echo '</tr>'; // end last row This post has been edited by Brian Chandler: Mar 31 2011, 10:51 PM |
joyful |
Apr 1 2011, 01:19 AM
Post
#5
|
Advanced Member Group: Members Posts: 239 Joined: 15-November 10 Member No.: 13,147 |
Hey, Thanks a lot for the help/code Brian.
Though I do know HTML well, I am new to php and am striving to learn more (thru projects and tutorials). I highly appreciate the patience and generosity of all I encounter in the forum. As for the code... I tried it out (thanks by the way, its exactly what I want), the only issue was: it did not display the 3rd item on the 1st line: This is how I did it (with your great code!): CODE <?php // set database server access variables: $host = "localhost"; $user = "root"; $pass = ""; $db = "product_index"; // open connection $connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!"); // select database mysql_select_db($db) or die ("Unable to select database!"); // create query $query = "SELECT * FROM products"; // execute query $result = mysql_query($query) or die ("Error in query: $query. ".mysql_error()); define('COLS', 3); // number of columns $col = 0; // number of the last column filled echo '<table border="5px">'; echo '<tr>'; // start first row while ($rows = mysql_fetch_array($result)) { $col++; if ($col == COLS) // have filled the last row { $col = 0; echo '</tr><tr>'; // start a new one } echo '<td>', '<center>', '<img src="', $rows[7], '" width="150px"/>', '<br>', $rows[2],'<br>', $rows[0],'<br>', $rows[4],'<br>', $rows[8], '</center>', '</td>'; } echo '</tr>'; // end last row echo "</table>"; // free result set memory mysql_free_result($result); // close connection mysql_close($connection); ?> Is this issue something I did? What is causing this (skipping from the 2nd item on the 1st line to the 1st item on the 2nd line)? Thanks a lot for your patience and help. -- |
Brian Chandler |
Apr 1 2011, 11:17 AM
Post
#6
|
Jocular coder Group: Members Posts: 2,460 Joined: 31-August 06 Member No.: 43 |
Probably I made a mistake. So the thing to do is debug the program -- print out diagnostic info, (such as the value of $col ) so you can see what's happening.
|
joyful |
Apr 1 2011, 12:07 PM
Post
#7
|
Advanced Member Group: Members Posts: 239 Joined: 15-November 10 Member No.: 13,147 |
I know this a very lame question... How do you do this (print diagnostics)? Is it like this?:
CODE define('COLS', 3); // number of columns $col = 0; // number of the last column filled echo '<table border="5px">'; echo '<tr>'; // start first row while ($rows = mysql_fetch_array($result)) { $col++; if ($col == COLS) // have filled the last row { $col = 0; echo '</tr><tr>'; // start a new one } echo '<td>', '<center>', '<img src="', $rows[7], '" width="150px"/>', '<br>', $rows[2],'<br>', $rows[0],'<br>', $rows[4],'<br>', $rows[8], '</center>', '</td>'; print ($col); } echo '</tr>'; // end last row echo "</table>"; Is that the right place? If so is the right thing to print? When I added this it just said: 120120 Is this referring to the way the items in each row (col)? If this is the right thing, what does this mean the issue is? Thanks a lot, Sorry about the dumb questions. -- |
Darin McGrew |
Apr 1 2011, 12:56 PM
Post
#8
|
WDG Member Group: Root Admin Posts: 8,365 Joined: 4-August 06 From: Mountain View, CA Member No.: 3 |
Here's what I see, based on the what the print command produced.
The first time through the loop: - $col is 0 - $col is incremented to 1 - $col is not 3 so you don't start a new row The second time through the loop: - $col is 1 - $col is incremented to 2 - $col is not 3 so you don't start a new row The third time through the loop: - $col is 2 - $col is incremented to 3 - $col is 3 so you do start a new row Do you see the problem? |
joyful |
Apr 1 2011, 01:01 PM
Post
#9
|
Advanced Member Group: Members Posts: 239 Joined: 15-November 10 Member No.: 13,147 |
Here's what I see, based on the what the print command produced. The first time through the loop: - $col is 0 - $col is incremented to 1 - $col is not 3 so you don't start a new row The second time through the loop: - $col is 1 - $col is incremented to 2 - $col is not 3 so you don't start a new row The third time through the loop: - $col is 2 - $col is incremented to 3 - $col is 3 so you do start a new row Do you see the problem? Yes, Darin thanks this seems right. The only difference from what you said is: after line one it is fine (I tried going further) so I think it might be like this: The first time through the loop: - $col is 0 - $col is 1 - $col is 3 The second time through the loop: - $col is 1 - $col is 2 - $col is 3 The third time through the loop: - $col is 1 - $col is 2 - $col is 3 How can we fix this issue? -- This post has been edited by joyful: Apr 1 2011, 01:10 PM |
Brian Chandler |
Apr 1 2011, 02:19 PM
Post
#10
|
Jocular coder Group: Members Posts: 2,460 Joined: 31-August 06 Member No.: 43 |
(Thanks Darin!)
Yes, this is a very common sort of error -- "off-by-one". Well, the problem really in all of these formatting things is that you have a "telegraph-pole arrangement": you only need to output the separator bit at the internal points, not the beginning and end (or equivalently, the beginning and end bits are just different). Say you count columns from 1, modulo 3 (1-2-3-1-2-3) then you want to start a new row when the current column is 1 modulo 3, but not when this 1 is the first one in the list. Grr. I sort of wrote an indication of the error myself: $col is the last column filled. So start it at 0 as above. Then on each output operation test if the last column filled is *already* 3. Right? Thus: CODE if ($col == COLS) // last time filled the last column { $col = 1; // so now we're filling first column echo '</tr><tr>'; // start new row } else $col++; // only increment otherwise I think this is right. Of course there is no warranty. I have not tested it, just argued eloquently that it *must* be right. Or as Charlie Brown would put it, "How can we lose if we're so sincere?" |
Darin McGrew |
Apr 1 2011, 04:04 PM
Post
#11
|
WDG Member Group: Root Admin Posts: 8,365 Joined: 4-August 06 From: Mountain View, CA Member No.: 3 |
As Brian pointed out, this is an off-by-one error, and it's a very common type error. (If it weren't a common type of error, we probably wouldn't have a name for it.) Anyway, let me see if I can explain it more clearly:
The 1st time through the loop: - $col starts as 0 - $col is incremented to 1 - $col is not 3 so you don't start a new row - you echo markup for a td element (the 1st td of the 1st tr) The 2nd time through the loop: - $col starts as 1 - $col is incremented to 2 - $col is not 3 so you don't start a new row - you echo markup for a td element (the 2nd td of the 1st tr) The 3rd time through the loop: - $col starts as 2 - $col is incremented to 3 - $col is 3 so you set $col to 0 and start a new row - you echo markup for a td element (the 1st td of the 2nd tr) The 4th time through the loop: - $col starts as 0 - $col is incremented to 1 - $col is not 3 so you don't start a new row - you echo markup for a td element (the 2nd td of the 2nd tr) The 5th time through the loop: - $col starts as 1 - $col is incremented to 2 - $col is not 3 so you don't start a new row - you echo markup for a td element (the 3rd td of the 2nd tr) The 6th time through the loop: - $col starts as 2 - $col is incremented to 3 - $col is 3 so you set $col to 0 and start a new row - you echo markup for a td element (the 1st td of the 3rd tr) Do you see the problem? The easiest fix would be to increment $col only after you test to see if it is equal to 3. That would make the 3rd time through the loop look like this: The 3rd time through the loop: - $col starts as 2 - $col is not 3 so you don't start a new row - $col is incremented to 3 - you echo markup for a td element (the 3rd td of the 1st tr) Does this make sense? |
joyful |
Apr 1 2011, 04:53 PM
Post
#12
|
Advanced Member Group: Members Posts: 239 Joined: 15-November 10 Member No.: 13,147 |
Hey,
Thanks a lot. I now see the problem and have fixed it. This is the code that worked: CODE define('COLS', 4); // number of columns $col = 0; // number of the last column filled echo '<table border="5px">'; echo '<tr>'; // start first row while ($rows = mysql_fetch_array($result)) { $col++; if ($col == COLS) // have filled the last row { $col = 1; echo '</tr><tr>'; // start a new one } echo '<td>', '<center>', '<img src="', $rows[7], '" width="150px"/>', '<br>', $rows[2],'<br>', $rows[0],'<br>', $rows[3],'<br>', $rows[4],'<br>', $rows[8], '</center>', '</td>'; print ($col); } echo '</tr>'; // end last row echo "</table>"; I had to change it to "define('COLS', 4);" as well. Thank you both for help, I really appreciate it. -- |
Brian Chandler |
Apr 1 2011, 11:57 PM
Post
#13
|
Jocular coder Group: Members Posts: 2,460 Joined: 31-August 06 Member No.: 43 |
QUOTE I had to change it to "define('COLS', 4);" as well. That is Not Good! Obviously "COLS" is supposed to mean "the number of columns", so you shouldn't make it be the number of columns plus 1. The whole point of defining a constant is that you can reuse the code/page with a different number of columns, and if you want 5 you will write 5, not 6. You asked about debug printing: simply use echo, with an easily spotted format, and don't indent to line up with the real program. So e.g. insert: echo "<br>**** col: $col ***"; |
joyful |
Apr 2 2011, 10:50 AM
Post
#14
|
Advanced Member Group: Members Posts: 239 Joined: 15-November 10 Member No.: 13,147 |
Hey, I do not quite under stand were I should put this:
QUOTE echo "<br>**** col: $col ***"; I tried this: CODE define('COLS', 3); // number of columns $col = 0; // number of the last column filled echo '<table class="inner_table">'; echo '<tr>'; // start first row while ($rows = mysql_fetch_array($result)) { $col++; if ($col == COLS) // have filled the last row { $col = 1; echo '</tr><tr>'; // start a new one } echo '<td class="sub_table">', '<center>', '<img src="', $rows[7], '" class="index_image" />', '<br>', '<span class="index_list_bold">', $rows[2], '<span><br><span class="index_list">','<br>', $rows[0],'<br>', $rows[4],'<br>', '<a href="', $rows[8], '" class="index_list">More info</a>', '</span>', '<br>', $col, '</center>', '</td>'; } echo '</tr>'; // end last row echo "</table>"; With this, the $col echo looked like this 12 12 12 There is just 2 per line when I do this. Were else should I try "debug printing" to find the issue? Also, Is mend to be like this?: 123 123 123 Or this?: 012 012 012 What can I do to fix this? Thanks a lot. -- This post has been edited by joyful: Apr 2 2011, 10:56 AM |
Brian Chandler |
Apr 2 2011, 12:10 PM
Post
#15
|
Jocular coder Group: Members Posts: 2,460 Joined: 31-August 06 Member No.: 43 |
You seem to have copied the "wrong" version again. It says
CODE $col++; if ($col == COLS) // have filled the last row As I pointed out above, this is wrong: if $col means "the last column filled", which it does, then incrementing, *then* testing for equality to the number of columns, is wrong. Isn't it? If the last column done was 2, and I increment to get 3, this is now the number of the *current* column, and now is not the time to start a new row. About debugging: in html it's much messier than normal programming, because for example if you are inside a table, simple output will not appear in the right place, unless you are careful to put it inside a <td>. One answer to this is to debug your programs by always looking at the source output, but it's partly personal choice. Incidentally, your echo format is rather odd (not "wrong"). You have things like: echo '<td class="sub_table">', '<center>', '<img src="', $rows[7], '" class="index_image" />', '<br>', '<span class="index_list_bold">', $rows[2], '<span><br><span class="index_list">','<br>', $rows[0],'<br>', $rows[4],'<br>', '<a href="', $rows[8], '" class="index_list">More info</a>', '</span>', '<br>', $col, '</center>', '</td>'; where each html element is a separate string. But there's no reason to do this: just echo '<td class="sub_table"><center><img src="', $rows[7], ... for example. If you split it into several distinct echos, you may be able to insert a separate debugging echo in between: thus echo '<td class="sub_table"><center>'; echo "<br>DEBUG: col = $col "; echo '<img src="', $rows[7], ... For example. Hope this helps. Don't worry too much about the *html* syntax when you are producing debugging output; just so long as you can see what's happening. |
joyful |
Apr 3 2011, 02:47 PM
Post
#16
|
Advanced Member Group: Members Posts: 239 Joined: 15-November 10 Member No.: 13,147 |
Hey,
Tell me if I am completely misunderstanding how to do the debugging thing: CODE while ($rows = mysql_fetch_array($result)) { $col++; if ($col == COLS) // have filled the last row { $col = 0; echo '</tr><tr>'; // start a new one } echo '<td class="sub_table"><center>'; echo "<br>DEBUG: col = $col "; echo '<img src="', $rows[7], '" class="index_image" /><br><span class="index_list_bold">', $rows[2], '<span><br><span class="index_list"><br>', $rows[0],'<br>', $rows[4],'<br><a href="', $rows[8], '" class="index_list">More info</a></span><br>', $col, '</center></td>'; } I know that I put $col to 0 put I am not sure (at this point) what to put there. This now what it says after "debug" for each item: line 1: 12 line 2: 012 line 3: 012 line 4: 012 Also, I don't understand what is wrong here: CODE $col++; if ($col == COLS) // have filled the last row This is how it was to begin with. Thanks. -- |
Darin McGrew |
Apr 3 2011, 07:12 PM
Post
#17
|
WDG Member Group: Root Admin Posts: 8,365 Joined: 4-August 06 From: Mountain View, CA Member No.: 3 |
The first time through the loop, $col starts as 0. The second time through the loop, $col starts as 1. The third time through the loop, $col starts as 2.
The question is, when do you increment $col, and when do you test to see if $col is 3? Here is what happens the third time through the loop now: QUOTE - $col starts as 2 - $col is incremented to 3 - test $col: $col is 3 so you set $col to 0 and start a new row - you echo markup for a td element (the 1st td of the 2nd tr) Here is what should happen the third time through the loop: QUOTE - $col starts as 2 - test $col: $col is not 3 so you don't start a new row - $col is incremented to 3 - you echo markup for a td element (the 3rd td of the 1st tr) Do you see the difference? |
joyful |
Apr 3 2011, 09:16 PM
Post
#18
|
Advanced Member Group: Members Posts: 239 Joined: 15-November 10 Member No.: 13,147 |
Yes, I (now) understand, But what can I do to fix this? What do I have to change? You have explained the problem very well (thank you). How can we fix this?
Thanks -- |
Darin McGrew |
Apr 3 2011, 10:36 PM
Post
#19
|
WDG Member Group: Root Admin Posts: 8,365 Joined: 4-August 06 From: Mountain View, CA Member No.: 3 |
Move the "$col is incremented" step after the "test $col" step.
|
joyful |
Apr 4 2011, 04:14 PM
Post
#20
|
Advanced Member Group: Members Posts: 239 Joined: 15-November 10 Member No.: 13,147 |
Hey, Thanks so much, this worked.
Sorry it took me a little to understand. Here is the code" CODE define('COLS', 3); // number of columns $col = 0; // number of the last column filled echo '<table class="inner_table">'; echo '<tr>'; // start first row while ($rows = mysql_fetch_array($result)) { $col++; echo '<td class="sub_table"><center>'; echo '<img src="', $rows[7], '" class="index_image" /><br><span class="index_list_bold">', $rows[2], '<span><br><span class="index_list"><br>', $rows[0],'<br>', $rows[4],'<br><a href="', $rows[8], '" class="index_list">More info</a></span><br>', $col, '</center></td>'; if ($col == COLS) // have filled the last row { $col = 0; echo '</tr><tr>'; // start a new one } } echo '</tr>'; // end last row echo "</table>"; Thanks again. -- |
Lo-Fi Version | Time is now: 26th April 2024 - 09:00 AM |