Member Avatar for Bennyton

Hey all, new to this site but i'm trying to call multiple values from multiple mysql databnases to show up in one table, but its throwing up an error at line 55, or

while($row = mysql_fetch_array($result));

so i was wondering if I'm causing havoc with this statement here, im new to php, and i'm not really enjoying the experience at the moment!
Any help at all would be amazing... thanks!

<!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>
 <title><?php echo $pagetitle; ?></title>
 <meta http-equiv="Content-Type"
  Content="text/html; charset=utf-8" />
 
<link rel=stylesheet type="text/css" href="styles1.css"> 
</head>
<body>
<div id="header">
<div id="branding">
<h1><img src= "logo.gif" width="250" height="100" alt=logo" /></h1>
</div> <!—end of branding div -->

</div> <!--end of header div -->

<div id="navigation">
<ul>
<li><a href= "staff.php">Exit</a></li>
</ul>
</div> <! -- end of navigation div -- >  


<div id="bodycontent">

<?php

mysql_connect("x.x.x.x", "xxx", "xxx")
or die (mysql_error("Could not connect to database, please try login again"));

$db = mysql_select_db("team7testdb") or die (mysql_error("Connection Error"));

$query = "(SELECT * FROM businesscustomer) union all(SELECT * FROM businesscontact) union all(SELECT * FROM telephone) union all(SELECT * FROM email)";
$result = mysql_query($query);
echo "<table border='2'>
<tr>
<th>Business Name</th>
<th>Vat Number</th>
<th>Company Type</th>
<th>Customer ID</th>
<th>Contact Number</th>
<th>First Name</th>
<th>Last Name</th>
<th>Job Title</th>
<th>Department</th>
<th>Home Number</th>
<th>Work Number</th>
<th>Mobile Number</th>
<th>Personal Email</th>
<th>Work Email</th>
</tr>";

while($row = mysql_fetch_array($result));
{
echo "<tr>";
echo "<td>" . $row['businessName'] . "</td>";
echo "<td>" . $row['vatNo'] . "</td>";
echo "<td>" . $row['companyType'] . "</td>";
echo "<td>" . $row['customerID'] . "</td>";
echo "<td>" . $row['contactNo'] . "</td>";
echo "<td>" . $row['fName'] . "</td>";
echo "<td>" . $row['lName'] . "</td>";
echo "<td>" . $row['department'] . "</td>";
echo "<td>" . $row['jobTitle'] . "</td>";
echo "<td>" . $row['number'] . "</td>";
echo "<td>" . $row['number2'] . "</td>";
echo "<td>" . $row['number3'] . "</td>";
echo "<td>" . $row['emailAdd'] . "</td>";
echo "<td>" . $row['emailAdd2'] . "</td>";
echo "</tr>";
}
  
echo "</table>";


?>
Edited by Ezzaral because: Removed db login details.
  • 2 Contributors
  • 3 Replies
  • 234 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by diafol
Member Avatar for diafol

Are you sure about this??

"(SELECT * FROM businesscustomer) union all(SELECT * FROM businesscontact) union all(SELECT * FROM telephone) union all(SELECT * FROM email)";

I think you may be looking for some sort of JOIN not UNION.

Edited by diafol because: n/a
Member Avatar for Bennyton

thanks for your reply ardev, it might be easier to see whati'm doing if i show you the code before i added the union all statements...

<!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>
 <title><?php echo $pagetitle; ?></title>
 <meta http-equiv="Content-Type"
  Content="text/html; charset=utf-8" />
 
<link rel=stylesheet type="text/css" href="styles1.css"> 
</head>
<body>
<div id="header">
<div id="branding">
<h1><img src= "logo.gif" width="250" height="100" alt=logo" /></h1>
</div> <!—end of branding div -->

</div> <!--end of header div -->

<div id="navigation">
<ul>
<li><a href= "staff.php">Exit</a></li>
</ul>
</div> <! -- end of navigation div -- >  


<div id="bodycontent">

<?php

mysql_connect("157.190.186.37", "burkedj", "R00046463")
or die (mysql_error("Could not connect to database, please try login again"));

$db = mysql_select_db("team7testdb") or die (mysql_error("Connection Error"));

$query = "SELECT * FROM businesscustomer";
$result = mysql_query($query);
echo "<table border='2'>
<tr>
<th>Business Name</th>
<th>Vat Number</th>
<th>Company Type</th>
<th>Customer ID</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['businessName'] . "</td>";
echo "<td>" . $row['vatNo'] . "</td>";
echo "<td>" . $row['companyType'] . "</td>";
echo "<td>" . $row['customerID'] . "</td>";
echo "</tr>";
  }

$query = "SELECT * FROM businesscontact";
$result = mysql_query($query);

echo "<table border='2'>
<tr>
<th>Contact Number</th>
<th>First Name</th>
<th>Last Name</th>
<th>Job Title</th>
<th>Department</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['contactNo'] . "</td>";
echo "<td>" . $row['fName'] . "</td>";
echo "<td>" . $row['lName'] . "</td>";
echo "<td>" . $row['department'] . "</td>";
echo "<td>" . $row['jobTitle'] . "</td>";
echo "</tr>";
  }


$query = "SELECT * FROM telephone";
$result = mysql_query($query);

echo "<table border='2'>
<tr>
<th>Home Number</th>
<th>Work Number</th>
<th>Mobile Number</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['number'] . "</td>";
echo "<td>" . $row['number2'] . "</td>";
echo "<td>" . $row['number3'] . "</td>";
echo "</tr>";
  }

$query = "SELECT * FROM email";
$result = mysql_query($query);


echo "<table border='2'>
<tr>


<th>Persobnal Email</th>
<th>Work Email</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['emailAdd'] . "</td>";
echo "<td>" . $row['emailAdd2'] . "</td>";
echo "</tr>";
  }
  
echo "</table>";


?>

This means i get 4 tables though,instead of one larger one, and it makes it incredibly hard to read the values properly if i have over 100, so i wanted them all to go into one table as a list instead...

heres pics of before, and how i'd like it to work...

[IMG]http://img146.imageshack.us/img146/7873/sampledisplay.th.png[/IMG]
[IMG]http://img850.imageshack.us/img850/1293/displayproblem.th.png[/IMG]

Member Avatar for diafol

I see. However, none of these tables are linked. It doesn't make sense to join them. A union as I understand it pretty much adds rows to a result set, not columns. Also, unioned tables need to have identical fields (or at least the fields named in the SQL need to be identical - or you can make them so via ALIAS (AS)).

I can't see how you'd make a BIG html table out of this disparate data. As I mentioned, the data simply doesn't fit together.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.