The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

2 Pages V  1 2 >  
Reply to this topicStart new topic
> Displaying MYSQL database table into PHP table
joyful
post 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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Frederiek
post 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 .
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
joyful
post 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:
IPB Image

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

--
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post 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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
joyful
post 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:
IPB Image
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.

--
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post 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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
joyful
post 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.

--
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Darin McGrew
post 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?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
joyful
post Apr 1 2011, 01:01 PM
Post #9


Advanced Member
****

Group: Members
Posts: 239
Joined: 15-November 10
Member No.: 13,147



QUOTE(Darin McGrew @ Apr 1 2011, 01:56 PM) *

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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post 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?"
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Darin McGrew
post 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?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
joyful
post 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.

--
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post 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 ***";

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
joyful
post 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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post 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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
joyful
post 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.

--
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Darin McGrew
post 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?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
joyful
post 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

--
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Darin McGrew
post 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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
joyful
post 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.

--
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

2 Pages V  1 2 >
Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 19th March 2024 - 12:21 AM