Page 1 of 1

[SOLVED] Newbie question about HTML tables and MySQL

Posted: Mon Jul 26, 2004 9:18 am
by xt07t
feyd | Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color] 


I am trying to build a simple table to display users and the number of posts to my blog. The problem is that they are on top of each other, rather than side by side. It is creating a new <tr> instead of a new <td>.

Code: Select all

<?php
if (! $dbh = mysql_connect('localhost','loginanme','loginpassword')) {
        die("Can't connect: ".mysql_error());
}
mysql_select_db('forums');
if (! $result = mysql_query("SELECT username, posts FROM user WHERE posts >= '30' ORDER by posts DESC")) {
        die("Can't execute query: ".mysql_error());
}
$rowsFound = @ mysql_num_rows($result);
while ($row=mysql_fetch_array($result))
  {
     for ($i=0; $i<mysql_num_fields($result); $i++)
         echo "\n<table border=1>\n<tr>" .
              "\n<td>$row[$i]" . "</td>" .
              "\n</tr>";
  }
echo "\n</table>\n";
mysql_close($dbh);
?>
The response comes out

Code: Select all

User1
2
User2
34
User3
64
Instead of

Code: Select all

User1  2
User2  34
User3  64
Ok I'm sure it is something really simple, but I'm missing it. I'm new, so give me some slack. :)


feyd | Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Mon Jul 26, 2004 10:26 am
by feyd
you're asking php to create a table for every field.. although only closing the last table. Move the table and table row set up echos outside the for loop.

Posted: Mon Jul 26, 2004 10:33 am
by xt07t
First off, sorry about not using the tags. I changed the code to

Code: Select all

<?php
if (! $dbh = mysql_connect('localhost','xxxx','xxxx')) {
        die("Can't connect: ".mysql_error());
}
mysql_select_db('forums');
if (! $result = mysql_query("SELECT username, posts FROM user WHERE posts >= '30' ORDER by posts DESC")) {
        die("Can't execute query: ".mysql_error());
}
$rowsFound = @ mysql_num_rows($result);
         echo "\n<table border=1>\n<tr>";
while ($row=mysql_fetch_array($result))
  {
     for ($i=0; $i<mysql_num_fields($result); $i++)
         echo "\n<td>$row[$i]" . "</td>";
  }
			  echo "\n</tr></table>\n";
mysql_close($dbh);
?>
This changes the result to bieng displayed on one line. What I want, though, is for the data pairs to be on thier own rows, but listed one after the other.

Code: Select all

&lt;table&gt;
&lt;tr&gt;&lt;td&gt;User1&lt;/td&gt;&lt;td&gt;34&lt;/td&gt;&lt;/tr&gt;
&lt;tr&gt;&lt;td&gt;User2&lt;/td&gt;&lt;td&gt;57&lt;/td&gt;&lt;/tr&gt;
&lt;tr&gt;&lt;td&gt;User3&lt;/td&gt;&lt;td&gt;89&lt;/td&gt;&lt;/tr&gt;
&lt;/table&gt;
I'm just not sure how to get the results of my query into this sort of format.

Posted: Mon Jul 26, 2004 11:08 am
by feyd
if they are in their own rows, they will appear on seperate lines of the output..

Posted: Mon Jul 26, 2004 11:19 am
by xt07t
I know, I WANT them to appear on separate rows. The problem is that they are not appearing that way. How can I get the data sets on their own rows?

The output should look like

Username_Here Number_of_posts_here
Username_Here Number_of_posts_here
Username_Here Number_of_posts_here
Username_Here Number_of_posts_here

Posted: Mon Jul 26, 2004 11:32 am
by feyd
the output you have above would do that:

Code: Select all

<table>
<tr><td>User1</td><td>34</td></tr>
<tr><td>User2</td><td>57</td></tr>
<tr><td>User3</td><td>89</td></tr>
</table>

Posted: Mon Jul 26, 2004 12:16 pm
by xt07t
I was using that only to show you what I want to happen. When I use the $row[$i] in my code to loop over the results of the query it doesn't put them into different TD's. I'm wondering how to get the data out of the query and into separate TD's?

Posted: Mon Jul 26, 2004 12:29 pm
by feyd
move the table row settings into the while loop, but outside the for loop.

Posted: Mon Jul 26, 2004 12:52 pm
by xt07t
I figured it out. Actually what I was doing wrong was the way I was disaplying the data. I was originally doing it this way

Code: Select all

while ($row=mysql_fetch_array($result))
  {
     for ($i=0; $i<mysql_num_fields($result); $i++)
         echo "\n<table border=1>\n<tr>" .
              "\n<td>$row[$i]" . "</td>" .
			  "\n</tr>";
  }
But this is not the way to put individual entries into <td>

I changed the code so that it no longer looped in the same maner and instaed of puttin the variable $i I used the column name WITHOUT double quotes in this case...

Code: Select all

while ($row=mysql_fetch_array($result))
  {
     for ($i=0; $i<mysql_num_fields($result) - 1; $i++)
         echo "\n<tr><td width = 150 bgcolor=#CCCCCC>$row[username]" . "</td>" .
			  "\n<td width = 25 bgcolor=#FFCC99>$row[posts]" . "</td>" .
			  "\n</tr>";
  }
I also added a "-1" to the loop because for some reason it was giving me two of everything. THis solved that issue.