Multiple database crosstab MYSQL & PHP

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Matthew Clubb
    #1

    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('C onnections/newsdb.php');
    require_once('C onnections/basedb.php');
    mysql_select_db ($database_news db, $newsdb);
    $res_newsdb = mysql_query("SE LECT * FROM tblnews ORDER BY newsdate DESC");
    mysql_select_db ($database_base db, $basedb);
    $row_newsdb=mys ql_fetch_object ($res_newsdb);
    $res_basedb=mys ql_query("SELEC T * from tblemployees WHERE
    ID=$row_newsdb->postedby");
    ?>

    <html>
    <link href="body01.cs s" 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="_MainFr ame">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_arr ay($res_newsdb) ;
    $res_base = mysql_fetch_arr ay($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_resu lt($res_newsdb) ;
    echo('</TABLE>');

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

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


    Tags: None
    • Paul Barfoot
      #2
      Re: Multiple database crosstab MYSQL &amp; PHP

      Matthew

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

      $results = mysql_query("SE LECT newsdb.tblnews. *, basedb.tblemplo yees .*
      FROM newsdb.tblnews INNER JOIN basedb.tblemplo yees ON
      newsdb.tblnews. postedby = basedb.tblemplo yees.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" <matt@mwclubb.c o.uk> wrote in message
      news:cn0mht$n86 $1$830fa7b3@new s.demon.co.uk.. .[color=blue]
      > 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('C onnections/newsdb.php');
      > require_once('C onnections/basedb.php');
      > mysql_select_db ($database_news db, $newsdb);
      > $res_newsdb = mysql_query("SE LECT * FROM tblnews ORDER BY newsdate DESC");
      > mysql_select_db ($database_base db, $basedb);
      > $row_newsdb=mys ql_fetch_object ($res_newsdb);
      > $res_basedb=mys ql_query("SELEC T * from tblemployees WHERE
      > ID=$row_newsdb->postedby");
      > ?>
      >
      > <html>
      > <link href="body01.cs s" 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="_MainFr ame">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_arr ay($res_newsdb) ;
      > $res_base = mysql_fetch_arr ay($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_resu lt($res_newsdb) ;
      > echo('</TABLE>');
      >
      > echo("<BR /><P><A HREF='addnews.p hp'>Add news</A></P>");
      >
      > ?>
      > </p>
      > <p><span class="body01"> To email the website manager click </p>
      > </body>
      > </html>
      > ---------------------------------------------------------------------
      >
      >[/color]


        Comment

        • Matthew Clubb
          #3
          Re: Multiple database crosstab MYSQL &amp; PHP

          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" <Paul@theglobal family.fsworld. co.uk> wrote in message
          news:cn0rse$5rp $1@news6.svr.po l.co.uk...[color=blue]
          > Matthew
          >
          > I was shown recently how you can query more than one database in one SQL
          > query:
          >
          > $results = mysql_query("SE LECT newsdb.tblnews. *, basedb.tblemplo yees .*
          > FROM newsdb.tblnews INNER JOIN basedb.tblemplo yees ON
          > newsdb.tblnews. postedby = basedb.tblemplo yees.ID ORDER BY
          > newsdb.tblnews. newsdate DESC");
          >
          > You only need one of the connections to a database (either will do). Until[/color]
          I[color=blue]
          > was shown this I thought it was one connection, one database.
          >
          > --
          > Paul Barfoot
          >
          >
          > "Matthew Clubb" <matt@mwclubb.c o.uk> wrote in message
          > news:cn0mht$n86 $1$830fa7b3@new s.demon.co.uk.. .[color=green]
          > > 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('C onnections/newsdb.php');
          > > require_once('C onnections/basedb.php');
          > > mysql_select_db ($database_news db, $newsdb);
          > > $res_newsdb = mysql_query("SE LECT * FROM tblnews ORDER BY newsdate[/color][/color]
          DESC");[color=blue][color=green]
          > > mysql_select_db ($database_base db, $basedb);
          > > $row_newsdb=mys ql_fetch_object ($res_newsdb);
          > > $res_basedb=mys ql_query("SELEC T * from tblemployees WHERE
          > > ID=$row_newsdb->postedby");
          > > ?>
          > >
          > > <html>
          > > <link href="body01.cs s" 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"[/color][/color]
          target="_MainFr ame">Bulletin[color=blue][color=green]
          > > 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_arr ay($res_newsdb) ;
          > > $res_base = mysql_fetch_arr ay($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_resu lt($res_newsdb) ;
          > > echo('</TABLE>');
          > >
          > > echo("<BR /><P><A HREF='addnews.p hp'>Add news</A></P>");
          > >
          > > ?>
          > > </p>
          > > <p><span class="body01"> To email the website manager click </p>
          > > </body>
          > > </html>
          > > ---------------------------------------------------------------------
          > >
          > >[/color]
          >
          >[/color]


            Comment

            Previous template Next
            Working...