468,247 Members | 1,296 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,247 developers. It's quick & easy.

Multiple database crosstab MYSQL & PHP

Please help, a bit new to this!

I've got two databases and I'm trying to look with values from one table in
one database and then look up a name from the other database, the two tables
are linked where 'postedby' in my newsdb database refers to 'ID' in my
tblemployees table in my basedb database.
All I get is one name shown and 6 records where there are actually 7.

Any help would be greatly appreciated.

Kind Regards,
Matthew

Code Below:
--------------------------------------------------------------
<?php
require_once('Connections/newsdb.php');
require_once('Connections/basedb.php');
mysql_select_db($database_newsdb, $newsdb);
$res_newsdb = mysql_query("SELECT * FROM tblnews ORDER BY newsdate DESC");
mysql_select_db($database_basedb, $basedb);
$row_newsdb=mysql_fetch_object($res_newsdb);
$res_basedb=mysql_query("SELECT * from tblemployees WHERE
ID=$row_newsdb->postedby");
?>

<html>
<link href="body01.css" rel="stylesheet" type="text/css">

<body>
<table width="500" border="0" class="body01" style='BORDER-COLLAPSE:
collapse'>
<tr>
<td>Latest News:</td>
<td><div align="right"><a href="../phpbb" target="_MainFrame">Bulletin
Board</a></div></td>
</tr>
</table>
<p class="body01">
<?php
echo ("<TABLE BORDER='1' WIDTH=450 style='BORDER-COLLAPSE: collapse'>");
$rows=1;
while($rows <= 10)
{
$res_news = mysql_fetch_array($res_newsdb);
$res_base = mysql_fetch_array($res_basedb);
$ndate = $res_news['newsdate'];
echo ('<TR><TD><FONT SIZE=-2><div align="center">' . $ndate .
'</div></FONT></TD><TD>' .
$res_news['news']) . '</TD><TD>' .
$res_base['IDname'] . '</TD></TR>';
$rows++;
}
mysql_free_result($res_newsdb);
echo('</TABLE>');

echo("<BR /><P><A HREF='addnews.php'>Add news</A></P>");

?>
</p>
<p><span class="body01">To email the website manager click </p>
</body>
</html>
---------------------------------------------------------------------
Jul 17 '05 #1
2 2636
Matthew

I was shown recently how you can query more than one database in one SQL
query:

$results = mysql_query("SELECT newsdb.tblnews.*, basedb.tblemployees .*
FROM newsdb.tblnews INNER JOIN basedb.tblemployees ON
newsdb.tblnews.postedby = basedb.tblemployees.ID ORDER BY
newsdb.tblnews.newsdate DESC");

You only need one of the connections to a database (either will do). Until I
was shown this I thought it was one connection, one database.

--
Paul Barfoot
"Matthew Clubb" <ma**@mwclubb.co.uk> wrote in message
news:cn*******************@news.demon.co.uk...
Please help, a bit new to this!

I've got two databases and I'm trying to look with values from one table
in
one database and then look up a name from the other database, the two
tables
are linked where 'postedby' in my newsdb database refers to 'ID' in my
tblemployees table in my basedb database.
All I get is one name shown and 6 records where there are actually 7.

Any help would be greatly appreciated.

Kind Regards,
Matthew

Code Below:
--------------------------------------------------------------
<?php
require_once('Connections/newsdb.php');
require_once('Connections/basedb.php');
mysql_select_db($database_newsdb, $newsdb);
$res_newsdb = mysql_query("SELECT * FROM tblnews ORDER BY newsdate DESC");
mysql_select_db($database_basedb, $basedb);
$row_newsdb=mysql_fetch_object($res_newsdb);
$res_basedb=mysql_query("SELECT * from tblemployees WHERE
ID=$row_newsdb->postedby");
?>

<html>
<link href="body01.css" rel="stylesheet" type="text/css">

<body>
<table width="500" border="0" class="body01" style='BORDER-COLLAPSE:
collapse'>
<tr>
<td>Latest News:</td>
<td><div align="right"><a href="../phpbb" target="_MainFrame">Bulletin
Board</a></div></td>
</tr>
</table>
<p class="body01">
<?php
echo ("<TABLE BORDER='1' WIDTH=450 style='BORDER-COLLAPSE: collapse'>");
$rows=1;
while($rows <= 10)
{
$res_news = mysql_fetch_array($res_newsdb);
$res_base = mysql_fetch_array($res_basedb);
$ndate = $res_news['newsdate'];
echo ('<TR><TD><FONT SIZE=-2><div align="center">' . $ndate .
'</div></FONT></TD><TD>' .
$res_news['news']) . '</TD><TD>' .
$res_base['IDname'] . '</TD></TR>';
$rows++;
}
mysql_free_result($res_newsdb);
echo('</TABLE>');

echo("<BR /><P><A HREF='addnews.php'>Add news</A></P>");

?>
</p>
<p><span class="body01">To email the website manager click </p>
</body>
</html>
---------------------------------------------------------------------

Jul 17 '05 #2
Oh my god, I was sure I'd tried that! but anyway,

Thanks very much for solving the frustration of my last week!!!

Its much simpler when you know how

Thanks again

Matt

"Paul Barfoot" <Pa**@theglobalfamily.fsworld.co.uk> wrote in message
news:cn**********@news6.svr.pol.co.uk...
Matthew

I was shown recently how you can query more than one database in one SQL
query:

$results = mysql_query("SELECT newsdb.tblnews.*, basedb.tblemployees .*
FROM newsdb.tblnews INNER JOIN basedb.tblemployees ON
newsdb.tblnews.postedby = basedb.tblemployees.ID ORDER BY
newsdb.tblnews.newsdate DESC");

You only need one of the connections to a database (either will do). Until I was shown this I thought it was one connection, one database.

--
Paul Barfoot
"Matthew Clubb" <ma**@mwclubb.co.uk> wrote in message
news:cn*******************@news.demon.co.uk...
Please help, a bit new to this!

I've got two databases and I'm trying to look with values from one table
in
one database and then look up a name from the other database, the two
tables
are linked where 'postedby' in my newsdb database refers to 'ID' in my
tblemployees table in my basedb database.
All I get is one name shown and 6 records where there are actually 7.

Any help would be greatly appreciated.

Kind Regards,
Matthew

Code Below:
--------------------------------------------------------------
<?php
require_once('Connections/newsdb.php');
require_once('Connections/basedb.php');
mysql_select_db($database_newsdb, $newsdb);
$res_newsdb = mysql_query("SELECT * FROM tblnews ORDER BY newsdate DESC"); mysql_select_db($database_basedb, $basedb);
$row_newsdb=mysql_fetch_object($res_newsdb);
$res_basedb=mysql_query("SELECT * from tblemployees WHERE
ID=$row_newsdb->postedby");
?>

<html>
<link href="body01.css" rel="stylesheet" type="text/css">

<body>
<table width="500" border="0" class="body01" style='BORDER-COLLAPSE:
collapse'>
<tr>
<td>Latest News:</td>
<td><div align="right"><a href="../phpbb" target="_MainFrame">Bulletin Board</a></div></td>
</tr>
</table>
<p class="body01">
<?php
echo ("<TABLE BORDER='1' WIDTH=450 style='BORDER-COLLAPSE: collapse'>");
$rows=1;
while($rows <= 10)
{
$res_news = mysql_fetch_array($res_newsdb);
$res_base = mysql_fetch_array($res_basedb);
$ndate = $res_news['newsdate'];
echo ('<TR><TD><FONT SIZE=-2><div align="center">' . $ndate .
'</div></FONT></TD><TD>' .
$res_news['news']) . '</TD><TD>' .
$res_base['IDname'] . '</TD></TR>';
$rows++;
}
mysql_free_result($res_newsdb);
echo('</TABLE>');

echo("<BR /><P><A HREF='addnews.php'>Add news</A></P>");

?>
</p>
<p><span class="body01">To email the website manager click </p>
</body>
</html>
---------------------------------------------------------------------


Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Steve Farber | last post: by
reply views Thread by CountDraculla | last post: by
1 post views Thread by contact.amarender | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.