PHP to output a SQL table

semsuks

I am working on a project for school, designing a website. I am trying to start small and make a basic message board.

I simply have users post a "name" "subject" and "message" to the mySQL database. This works fine.

I have done an echo statment to list the "messages" and that will just list them straight down. Now i want to be able to show all the columns of the sql table in my html page. I am having a lot of trouble trying to echo multiple columns at a time.

I want to echo a HTML table that has cells, one for the name,subject, and message. The end result would be a webpage that has a list of several HTML tables that have the information from the mySQL database

Here is my code:

$dbcnx = mysql_connect("localhost", "**", "****");
mysql_select_db("music", $dbcnx);
$groups = mysql_query("select from mboard", $dbcnx);

while ($row = mysql_fetch_array($groups)){
$name = $row["name"];
$subject = $row["subject"];
$message = $row["message"];
echo("$name . $subject . $message" . "<br>");
}

I am also trying to echo the HTML table with that information:

echo("<table border="1" cellspacing="1" width="500"><tr><td width="100" rowspan="2" >" . $name . "</td><td width="400">" . $subject . "</td></tr><tr><td width="400">" . $message . "</td></tr></table>");

thank you for you help

PHPdev

One thing that I see is that you need to escape the " in your HTML... like this..

echo "<table border = \"2\">";

Otherwise php thinks the " in your HTML is an end to a string... Im surprised that you did not get an error on this... or this could just be a typo....

Just an FYI... the Board has a tag

that can be used to nicly format and color your PHP code...

 echo "<table border=\"2\">

Let me know if this does not solve your problems...

PHPdev

semsuks

Thank you for fixing my /" problem. This will help me actually generate the HTML table, BUT...

My problem still exists with echoeing multiple items.

It seems to like re-echo for each variable in the echo statement.

So.. echo("Name: " . $name . " Subject: " . $subject . "<br>");

would output

Name: Bob
Name: Subject: Senior Project

It prints both those lines, but i want:
"Name: Bob Subject: SeniorProject"

I have the same problem when putting in the table, because it prints a new table for each element, instead of having all 3 elements in 1 table.

(here is a link if you want to see what it looks like with tables. The top table is what it should look like, and the following tables should be combined into tables that look like the top one
http://hawk.csc.vill.edu/~f02g04/mboard/s.php)

PHPdev

while ($row = mysql_fetch_array($groups)){ 
$name = $row["name"]; 
$subject = $row["subject"]; 
$message = $row["message"]; 
echo("$name . $subject . $message" . "<br>"); 
}

Well the output of this echo line... should look like this...

$name = Homer
$subject = DOH
$message = whatever...

homer . DOH . whatever... <br>

Try removing the " from around the variables...

I dont see any reason why it is outputing the name twice... I do see in the soucre of the page you have a table for every message.. which is fine...

Oh.... Try putting in a Name:, Subject:, Message: in your table cells where the message and Subject... are to print out... this may help you see what is going on in the source....

If that does not do it...

Maybe past the entire file here... blocking out the User and Pass ad you did before.. and I can take a closer look...

PHPdev

semsuks

The file is attached... but i am assuming that this is the only important code you need.

<?php
$dbcnx = mysql_connect("localhost", "***", "***");
mysql_select_db("music", $dbcnx);
$groups = mysql_query("select from mboard", $dbcnx);

while ($row = mysql_fetch_array($groups)){
$name = $row["name"];
$subject = $row["subject"];
$message = $row["message"];

echo("<table border=\"1\" cellspacing=\"1\" width=\"500\">
<tr>
<td width=\"100\" rowspan=\"2\" >" . $name . "</td>
<td width=\"400\" >" . $subject . "</td>
</tr>
<tr>
<td width=\"400\" >" . $message . "</td>
</tr>
</table>
<br>");
}
?>

By the way, I really appreciate you helpling me, especially since you are very responsive and quick.

PHPdev

Ok there is something fairly funky going on...

I took your file, and setup a db table called mboard with 3 fields... name, subject, and message.

Ran your file against my DB and everything came out like you want it to.

So I would say that there is not an issue with your code. I would suggest looking elsewhere...

Make sure your editing the correct file?

Not sure...

PHPdev

semsuks

well it was good to know that I was doing the correct thing, but what could it possibly be? Would the way my database was created, would that have an effect. Each of the three attributes of the table are set as TEXT. You said you tested it on your own database, how was that setup?

I am baffeled, it seems that the identical PHP is being parsed differently by your machine. So the only thing i can think of is that it is either a different PHP that is interpreting the code, or it is something different about the mySQL table.

PHPdev

I created the table useing 3 varchar fields

name varchar(25),
subject varchar(50),
message varchar(255)

It could be the way the data is being stored in the db. what is the code behind your insert form? Are you just running one insert statement. Do you still have the create statement for your mysql table...

You could always go to a mysql command line.. and run your select... see how the results come up?

I am running PHP 4.2.3 and MySQL 3.23

PHPdev

semsuks

Here is the PHP code for what gets inserted into the Database

<?php

$dbcnx = mysql_connect("localhost", "*", "*");
mysql_select_db("music", $dbcnx);

$mname=$POST["Name"];
$msubject=$POST["Subject"];
$message=$_POST["Body"];

$sql1 = "INSERT INTO mboard SET name='$mname'";
$sql2 = "INSERT INTO mboard SET subject='$msubject'";
$sql = "INSERT INTO mboard SET message='$message'";
if (@($sql1)){echo("<p>Name has been added</p>");}
if (@($sql2)){echo("<p>Subject has been added</p>");}
if (@($sql)){echo("<p>Message has been added</p>");}

For the creation, I believe we did:
create table mboard
(message text);

then we later altered the table to add a name and subject column, both being Text.

PHPdev

Yea your inserts are your issues... You are essentially creating 3 seperate records into the database. If you do a select you will probably see something like...

user | subject | message |

homer | | |

| testing | |

| | This is a test message|

what you want is something like

user | subject | message |

homer | test | This is a test message|

So what you need to to is change your inserts in to one insert... like this

insert into mboard (user, subject, message) values ('$name','$subject','$message')

you may have to break this string apart using ','" . $varualbe . "','"
for the php strings... jsut makes it a bit more readable...

This should solve your problem....

PHPdev

semsuks

You are right, our inserts are wrong and we are inserting 3 seperate indexes instead of having all 3 in one.

We are now having difficulty inserting multiple values into the mboard. We are recieving a syntax error at the line of our insert.

$mname=$POST["Name"];
$msubject=$POST["Subject"];
$message=$_POST["Body"];

$sql= INSERT INTO mboard(message, name, subject) values('$message','$mname','$msubject');

if (@($sql)){
echo("<p>Message has been added</p>");}

PHPdev

Again not sure if this is a typo, but $sql has to store the Insert STRING...

$sql= "INSERT INTO mboard (message, name, subject) values('$message','$mname','$msubject')";

or my prefered method

$sql = "INSERT INTO mboard (message, name, subject) values('" . $message . "','" . $mname . "','" . $msubject . "')";

Also needs a space between table name and ()

Try that and let me know...

PHPdev