[SOLVED] Displaying Database information in table

[gump47371]

First post, very basic, but I was unable to find another post similar.

 

I am wanting to take the information in my database and display it in a table. I have been able to bring the information to my page, display the fields I want, but am unable to determine how to display it in a table. Right now, I have each line as its own table.

 

Here is what I have so far: http://www.fortwayneneeds.com/product_list.php

 

Here is my code:

 

<?

//connect to mysql

//change user and password to your mySQL name and password

mysql_connect("localhost","USERNAME","PASSWORD");

 

//select which database you want to edit

mysql_select_db("DBNAME");

 

//select the table

$result = mysql_query("select * from TABLENAME");

 

//grab all the content

while($r=mysql_fetch_array($result))

 

 

{

  //the format is $variable = $r["nameofmysqlcolumn"];

  //modify these to match your mysql table columns

 

  $id=$r["prod_id"];

  $upc=$r["prod_code"];

  $name=$r["prod_name"];

  $price=$r["prod_price"];

  $size=$r["prod_weight"];

 

echo "<table border='1'>";

echo "<tr>

<td>$id</td>

<td>$upc</td>

<td>$name</td>

<td>$price</td>

<td>$size</td>";

 

  //display the row

  //echo "$id \t $upc \t $name \t $price \t $size <br>";

}

 

?>

 

Thanks in advance for the help, as I am trying to learn things on the fly, but have hit a snag with this.

 

Love the forum, have learned quite a bit lurking thus far.

[rhodesa]

just move your <table> tags outside of the loop:

<?
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","USERNAME","PASSWORD");
   
//select which database you want to edit
mysql_select_db("DBNAME");

//select the table
$result = mysql_query("select * from TABLENAME");

echo "<table border='1'>";

//grab all the content
while($r=mysql_fetch_array($result))


{   
   //the format is $variable = $r["nameofmysqlcolumn"];
   //modify these to match your mysql table columns

   $id=$r["prod_id"];
   $upc=$r["prod_code"];
   $name=$r["prod_name"];
   $price=$r["prod_price"];
   $size=$r["prod_weight"];

echo "<tr>
<td>$id</td>
<td>$upc</td>
<td>$name</td>
<td>$price</td>
<td>$size</td>
</tr>";

   //display the row
   //echo "$id \t $upc \t $name \t $price \t $size <br>";
}
echo "</table>";
?> 

[mikesta707]

well first off, that doesn't even close the table tag. but if you wanted to create a table with all the information do something like this

 

//create the table head
echo "<table border='1'>"
echo "<tr><td>prod_id</td>";
echo "<td>prod_code</td>";
#etc etc
echo "</tr>";

//get sql and loop through it

//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","USERNAME","PASSWORD");
   
//select which database you want to edit
mysql_select_db("DBNAME");

//select the table
$result = mysql_query("select * from TABLENAME");

//grab all the content
while($r=mysql_fetch_array($result))


{   
   //the format is $variable = $r["nameofmysqlcolumn"];
   //modify these to match your mysql table columns

   $id=$r["prod_id"];
   $upc=$r["prod_code"];
   $name=$r["prod_name"];
   $price=$r["prod_price"];
   $size=$r["prod_weight"];

echo "<tr>
<td>$id</td>
<td>$upc</td>
<td>$name</td>
<td>$price</td>
<td>$size</td>";
echo "</tr>";

}

 

that will create a table, but of course this script can only be used for that specific query, and it won't work with any other queries

[gump47371]

Thanks for your replies, this worked great!

 

mike, based on your reply, is there a way I could have done it so that it could be more flexible and used in other cases in the future? 

 

You have been great to solve this problem, but wondering if I can solve future problems with this also.

 

Thanks again!

[mikesta707]

of course! that is the beauty of programming. Ill give an example

 

for sake of ease, im going to leave out the connect and db select statements, and just show the function

 

function makeTable($sql){
$query = mysql_query($sql) or die("ERROR" . mysql_error());
//lets make the header, and the first row

$row = mysql_fetch_assoc($query);

echo "<table border='1'>";
echo "<tr>";
//this will create the headers by looping through the array
//and echoing the array's indices
foreach($row as $key=>$value){
echo "<td>$key</td>";//you could also use <th> or table header tags
}
echo "</tr>";
//end the header row

//now make the first row
echo "<tr>";
foreach($row as $value){
echo "<td>$value</td>";
}
echo "</tr>";//end first row

//now lets go through the rest of the rows

while ($row = mysql_fetch_assoc($query)){
echo "<tr>";//start row
foreach($row as $value){
echo "<td>$value</td>";
}
echo "</tr>";//end row
}//end while

}//end function

 

if you were to call the function like so

 

makeTable("select * from TABLENAME");

 

the output should be the same as the code posted by me or rhode. There may be a syntax error or two, but the basic logic is still there

 

hope that helps!

 

[gump47371]

WHOA! Just when I think I am starting to learn something, I become confused again, LOL. Will have to study up on what you posted and implement it.

Thank you.

 

I do have one more question about this same code, though. I have a field in my DB that determines whether or not the product is displayed on my site. How would I edit my code to only display if the status is "1"?

 

I know very little about while and do while loops, and that I am able to put an IF statement in them, but am unable to determine what I need in the if statement or the else statement.

 

Sorry if this is not meant for this thread and I should start another, PLMK.

 

[mikesta707]

if ($row['status'] == 1){//assuming that its a numeric 1, not a string with the value of '1'
//output whatever
}

 

something like that? You could stick that in your while loop like:

 

while($r=mysql_fetch_array($result))


{   
   //the format is $variable = $r["nameofmysqlcolumn"];
   //modify these to match your mysql table columns

   $id=$r["prod_id"];
   $upc=$r["prod_code"];
   $name=$r["prod_name"];
   $price=$r["prod_price"];
   $size=$r["prod_weight"];
if ($r['status'] == 1){
echo "<tr>
<td>$id</td>
<td>$upc</td>
<td>$name</td>
<td>$price</td>
<td>$size</td>";
echo "</tr>";
}

}

 

 

 

[gump47371]

This is what I came up with, but don't know if it is right:

while($r=mysql_fetch_array($result))

do{
if {

	$status=="1";

	continue;

else {
	next product code;
}

Do I need an else, or will the while take care of all that?

[gump47371]

NM, was way off and too complicated again, I just tried what you posted, and works great.

 

Thanks again, really love this forum!

[mikesta707]

well, firstly thats not how a do while loops goes. A do while loop does the action, than checks if the while boolean statement is true. If it is, it does it again, until the statement isn't true. but you can;t really put a do inside a while like you did there. it should look something like

 

$cookies = 0;
do {
echo "Mmmmm...I love cookies! *munch munch munch*";
} while ($cookies > 1);

Output: Mmmmm...I love cookies! *munch munch munch

 

if you noticed, even though the while boolean statement was false, the "do" code block executed. Thats because the do block was executed, then the while statement was checked. it turned false, so the do code block wasn't executed again.

 

check out this tutorial for more information on that : http://www.tizag.com/phpT/dowhile.php

 

 

 

as far as the rest of your code, i'm afraid its mostly wrong too. When you write an if statement, you need a boolean statement next to it, like

 

if (some boolean statement){
//do this
}
else {
//do something else
}

 

So with your code, you want to make it look like

 

while(while($r=mysql_fetch_array($result)){
if ($status == '1'){
continue;
}
else {
//next product code
}
}

 

im assuming you already know how continue works so besides the syntax errors, your code's logic is about right

 

 

EDIT: Oh ok. make sure to mark to topic as solved!

[gump47371]

Appreciate the help and tutorials. Will check them out, and thank you again!