Displaying multi-tables in one HTML table, something like that..

BrianM · June 3, 2008

Alright, well here is the code for my first table --

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>MPS - View Client Data</title>
</head>

<?php

$mysql_hostname = "localhost";
$mysql_username = "brian";
$mysql_password = "";
$mysql_database = "mps";

$mysql_connect = mysql_connect("$mysql_hostname", "$mysql_username", "$mysql_password") or die(mysql_error());

mysql_select_db("$mysql_database") or die(mysql_error());

if (isset($_COOKIE['ID_my_site']))

{

$username = $_COOKIE['ID_my_site'];
$password = $_COOKIE['Key_my_site'];

$check = mysql_query("SELECT * FROM mps_login WHERE username = '$username'") or die(mysql_error());

while($info = mysql_fetch_array($check))

{

if ($password != $info['password'])

{

header("location: http://www.game-zero.org/mps/index.php");

}

{

?>

<body>

<p>Menu</p>
<br />
<a href="http://www.game-zero.org/mps/home.php">Home</a> |
<a href="http://www.game-zero.org/mps/index.php">Login</a> |
<a href="http://www.game-zero.org/mps/register.php">Register</a> |
<a href="http://www.game-zero.org/mps/logout.php">Logout</a>

<br /><br />

<p>View Client Data</p>

<br />

<a href="http://www.game-zero.org/mps/create_cdata.php">Create Client Data</a> |
<a href="http://www.game-zero.org/mps/view_cdata.php">View Client Data</a> |
<a href="http://www.game-zero.org/mps/view_rdata.php">View Report Data</a>

<br /><br />

<?php

mysql_select_db("$mysql_database") or die(mysql_error());

mysql_query("ALTER TABLE mps_cdata ORDER BY projectnumber");

$result = mysql_query("SELECT * FROM mps_cdata");

echo "<form action='http://www.game-zero.org/mps/search_projects.php' method='post'>
<table border='0'>
<tr><td>Search Projects:</td><td>
<input type='text' name='search' maxlength='60'>
<input type='submit' name='search_projects' value='Search'>
</td></tr>
</table>
</form>
<br />";

echo "<table border='1' cellpadding='0' cellspacing='0'>
<tr>
<th> Project Number </th>
<th> Project Name </th>
<th> Client Name </th>
<th> Client Address </th>
<th> Client Office Phone </th>
<th> Client Fax </th>
<th> Client Cell </th>
<th> Client Email </th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td><center><a href='view/index.php?table=" . $row['projectnumber'] . "'>" . $row['projectnumber'] . "</a></center></td>";
echo "<td><center>" . $row['projectname'] . "</center></td>";
echo "<td><center>" . $row['clientname'] . "</center></td>";
echo "<td><center>" . $row['clientaddress'] . "</center></td>";
echo "<td><center>" . $row['clientofficephone'] . "</center></td>";
echo "<td><center>" . $row['clientfax'] . "</center></td>";
echo "<td><center>" . $row['clientcell'] . "</center></td>";
echo "<td><center>" . $row['clientemail'] . "</center></td>";
echo "</tr>";
}
echo "</table>";

mysql_close($mysql_connect);

?>

</body>
</html>

<?php

}

}

}

else

{

header("location: http://www.game-zero.org/mps/index.php");

}

?>

That displays each row from a table inside a database in the one table of a page that I want just fine.

How am I suppose to display something similar to that, where it displays multiple rows from a database inside an HTML table on a page, but instead of each row displaying on the page coming from inside a new row from one table, it needs to select a new row from a new table that is created each time new information is submitted.

jonsjava · June 3, 2008

*head explodes* huh? Could you dumb it down? I just ate. (please explain exactly what you mean)

BrianM · June 3, 2008

*head explodes* huh? Could you dumb it down? I just ate. (please explain exactly what you mean)

Would you like an image/screen shot of the page to show as an example of what I'm trying to accomplish, would that help any?

jonsjava · June 3, 2008

yes. that would be great.

BrianM · June 3, 2008

Also, while I take screen shots and write up another more informative paragraph, can anyone help me out with this... if it makes any since at all.

How do I use a PHP/MySQL command to say: SELECT * FROM `all tables in a selected database here, instead of just one table` is there any way to select multiple tables from one database like that. That may be one solution to my problem, possibly.

jonsjava · June 3, 2008

I tend to cheat in this (not the best at mysql, at times) I have a table of tables, if you will. it contains id, table_name

then I do this:

<?php
$sql = "SELECT * FROM `table_table`;";
$result = mysql_query($sql);
$table_array = array();
while ($row = mysql_fetch_assoc($result)){
$table_array[] .= $row['table_name'];
}
$result_array = array();
$count = 0;
foreach ($table_array as $value){
$sql1 = "SELECT * FROM `$value`;";
$result1 = mysql_query($sql1);
while ($row1 = mysql_fetch_assoc($result1)){
$result_array[$count][] .= $row1['desired_table'];
$count++;
}
}
?>

It's ugly, but you get one heck of a multi-dimensional array

BrianM · June 3, 2008

This would and will be extremely useful. But, every time a new clients information is submitted to the database a new table is created, so how would I inform/update the page that displays the tables to show the new entry without having to edit the code? Your code would be a great help though if I could figure this out.

jonsjava · June 3, 2008

run a 2nd query, inserting the table name into the table_table

edit

I mean run the 2nd query when you create the new table. So, create table query, update table_table query

BrianM · June 3, 2008

Is there a command to pull up all the tables listed in one database, maybe that would help me.

And I will write something similar to what you suggested and give it a try.

jonsjava · June 3, 2008

<?php
$host = "localhost";
$db = "your_db"; 
$db_user = "user";
$db_password = "pass";
$link = mysql_connect($host, $db_user, $db_password);
mysql_select_db($db);
$sql = "SHOW TABLES FROM $db";
$result = mysql_query($sql);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_row($result)) {
    echo "Table: {$row[0]}\n";
}

mysql_free_result($result);
?>

*edit*

A better approach.

BrianM · June 3, 2008

Heres what I have --

<?php
echo "<table border='1' cellpadding='0' cellspacing='0'>
<tr>
<th> Report Date </th>
<th> Report Preview </th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td><center><a href='view/index.php?table=" . $row['date'] . "'>" . $row['date'] . "</a></center></td>";
echo "<td><center>" . $row['report'] . "</center></td>";
echo "</tr>";
}
echo "</table>";
?>

And when I add in $result = mysql_query("SHOW TABLES FROM reports"); I get these errors:

Notice: Undefined index: date in C:\Program Files\Apache Group\Apache2\htdocs\mps\view_rdata.php on line 92

Notice: Undefined index: report in C:\Program Files\Apache Group\Apache2\htdocs\mps\view_rdata.php on line 93

jonsjava · June 3, 2008

make sure date is the proper name.

<?php
echo "<table border='1' cellpadding='0' cellspacing='0'>
<tr>
<th> Report Date </th>
<th> Report Preview </th>
</tr>";

while($row = mysql_fetch_assoc($result))
{
echo "<tr>";
echo "<td><center><a href='view/index.php?table=" . $row['date'] . "'>" . $row['date'] . "</a></center></td>";
echo "<td><center>" . $row['report'] . "</center></td>";
echo "</tr>";
}
echo "</table>";
?>

BrianM · June 3, 2008

It is, the two fields are date and report.

DarkWater · June 3, 2008

SHOW TABLES returns the table names, not their data.

BrianM · June 3, 2008

So my best bet is to store each table inside of a table?

jonsjava · June 3, 2008

SHOW TABLES returns the table names, not their data.

I know

Is there a command to pull up all the tables listed in one database, maybe that would help me.

And I will write something similar to what you suggested and give it a try.

BrianM · June 3, 2008

I didn't understand that last post lol

jonsjava · June 3, 2008

I was letting him know that show tables shows the table names, not the data in the tables. I also let him know that you had requested information on how to get the table names.

DarkWater · June 3, 2008

I was letting him know that show tables shows the table names, not the data in the tables. I also let him know that you had requested information on how to get the table names.

I'm still not sure as to why you quoted ME, telling ME that you knew, when obviously you knew. I was talking to BrianM (who was trying to get the data out of that command as if it had selected actual table data).

jonsjava · June 3, 2008

brain fart. sorry. Misunderstood your comment.

BrianM · June 3, 2008

Well I got all of that working just fine, now for one last problem, and my website is done!

I'm trying to insert to values into a row under two fields, ProjectNumber and Date. The first field, ProjectNumber, is inserting just fine, as well as Date, but instead of inserting the current date on the same row, which is what I want it to do, it creates and inserts the date on a new row... here is my code, I really would appreciate any help with this, like I said, after I finish this piece here, I'm complete done with my first web site!

<?php

mysql_select_db("reports") or die(mysql_error());

$insert_three = "CREATE TABLE `".$_POST['projectnumber']."`
(
ID mediumint(9) not null auto_increment,
PRIMARY KEY(ID),
ProjectNumber mediumtext not null,
Date mediumtext not null,
Report mediumtext not null
)";
mysql_query($insert_three, $mysql_connect) or die(mysql_error());

mysql_query("INSERT INTO `".$_POST['projectnumber']."` (ProjectNumber)
VALUES ('".$_POST['projectnumber']."')");

mysql_query("INSERT INTO `".$_POST['projectnumber']."` (Date)
VALUES ('".date("m-d-y")."')");

?>

<?php

mysql_query("INSERT INTO `".$_POST['projectnumber']."` (ProjectNumber)
VALUES ('".$_POST['projectnumber']."')");

mysql_query("INSERT INTO `".$_POST['projectnumber']."` (Date)
VALUES ('".date("m-d-y")."')");

?>

Like I said, the second query, the date, wont insert on the same row, it creates a second row and inserts the date there. :\ Anyone know how to fix this, I even tried closing the connection to the database and reopening, but that didn't work.

DarkWater · June 3, 2008

You need to do it in one query. Not two.

BrianM · June 3, 2008

So use a variable to store both queries and then do mysql_query($var); ??

DarkWater · June 3, 2008

No. O_O Look:

mysql_query("INSERT INTO `".$_POST['projectnumber']."` (ProjectNumber, Date)

VALUES ('".$_POST['projectnumber']."', '".date("m-d-y")."')");

Didn't think that was so hard. O_O

BrianM · June 3, 2008

OOOOOO I did that when I wrote my user registration script to insert the values into the database.

Makes a whole lot more sense now, thanks!

Sign In

Displaying multi-tables in one HTML table, something like that..

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information