How can I echo 2 queries from 2 sql databases?

[brianwyn]

I have searched everywhere for an answer but I haven't had any luck and then I found this site.

 

Okay so what I'm doing is trying to call 1 announcement from 1 server and another announcement from another server and put them in order by date. but everything I do isn't working. Here is the index page, think you can help?

 

<?php
include './u&ainclude/mainlayout.php';
include("billing/dbconnect.php");
include("billing/includes/functions.php");
function displayNews(){
   global $database;
   $q = "SELECT name,subname,date,type,content FROM ".TBL_NEWS." ORDER BY date DESC,date";
   $result = $database->query($q);
   /* Error occurred, return given name by default */
   $num_rows = mysql_numrows($result);
   if(!$result || ($num_rows < 0)){
      echo "Error displaying info";
      return;
   }
   /* Display table contents */
   for($i=0; $i<$num_rows; $i++){
      $name  = mysql_result($result,$i,"name");
      $subname = mysql_result($result,$i,"subname");
  $date = $data["date"];
      $date = fromMySQLDate($date);
      $type = mysql_result($result,$i,"type");
      $content = mysql_result($result,$i,"content");
  $announcement = $data["announcement"];
if($type == 'Server'){
	$type = "<img src=\"Images/updates.gif\">";
} else {
	$type = "<img src=\"Images/technical.gif\">";
}
      echo "<dt><span class=\"newsdate\">$date</span>$name</dt><dd><table width=\"100%\"><tbody><tr><td style=\"text-align: justify; vertical-align: top;\">$content</td><td style=\"padding-left: 1em; text-align: right; vertical-align: top;\">$type</td></tr></tbody></table>";
   }
$query = "SELECT * FROM tblannouncements WHERE published='on' ORDER BY date DESC LIMIT 0,3"; 
$result = mysql_query($query);
while ($data = mysql_fetch_array($result)) {
    $id = $data["id"];
    $date = $data["date"];
    $title = $data["title"];
    $announcement = $data["announcement"];
    $date = fromMySQLDate($date);
    echo '<p><font color="#cccccc">'.$date.'</font> - <b>'.$title.'</b><br />'.$announcement.'</p>';
}
}
?>

<?php
displayNews();
?>

[xyph]

Isn't working is pretty vague.

 

Are you sure you've set up PHP to display all errors?

 

If you're using some sort of database class, it would be smart to include it.

 

Finally, there's so much wrong with that code. I'd suggest abandoning whatever or examples you've been following up until now

[brianwyn]

I cant abandon this. Sorry when I say it isn't working I mean, well.. http://hostingtb.com there is the example.

[xyph]

What example?

 

The way you've set up your code is archaic and hard to debug. You jump between procedural and OOP, you use globals when you should be passing via arguments, you have a function for an action you seem to only need to perform once, and you haven't isolated the issue to a point where we can actually help you. There's no way we can recreate your issue with the code you've posted. You also still haven't described what kind of error you're getting...

[brianwyn]

I didnt write everything your looking at, I do know its hideous and Im trying to fix it. I don't get an error message of any sort its just displaying incorrectly. There is NEWS and there is ANNOUNCEMENT, I am trying to get them both display in order by date but how I have this written the ANNOUNCEMENT is showing up after the NEWS no matter what the date is.

 

   for($i=0; $i<$num_rows; $i++){
      $name  = mysql_result($result,$i,"name");
      $subname = mysql_result($result,$i,"subname");
  $date = $data["date"];
      $date = fromMySQLDate($date);
      $type = mysql_result($result,$i,"type");
      $content = mysql_result($result,$i,"content");
  $announcement = $data["announcement"];
if($type == 'Server'){
	$type = "<img src=\"Images/updates.gif\">";
} else {
	$type = "<img src=\"Images/technical.gif\">";
}
      echo "<dt><span class=\"newsdate\">$date</span>$name</dt><dd><table width=\"100%\"><tbody><tr><td style=\"text-align: justify; vertical-align: top;\">$content</td><td style=\"padding-left: 1em; text-align: right; vertical-align: top;\">$type</td></tr></tbody></table>";
   }
$query = "SELECT * FROM tblannouncements WHERE published='on' ORDER BY date DESC LIMIT 0,3"; 
$result = mysql_query($query);
while ($data = mysql_fetch_array($result)) {
    $id = $data["id"];
    $date = $data["date"];
    $title = $data["title"];
    $announcement = $data["announcement"];
    $date = fromMySQLDate($date);
    echo '<p><font color="#cccccc">'.$date.'</font> - <b>'.$title.'</b><br />'.$announcement.'</p>';

 

 

[xyph]

It's no so much if you made it, it's if you understand it. Here's how I'd handle it, assuming I couldn't use SQL to sort.

 

<?php

// First server connection
$db1 = new MySQLi('localhost','root','','db');
// Second server connection
$db2 = new MySQLi('localhost','root','','db');
// This will hold our data
$data = array();

// get data from first source
$q = 'SELECT
UNIX_TIMESTAMP(date) as timestamp,
DATE_FORMAT(date, "%b %d %Y %h:%i %p") as date,
content
FROM table1';
$r = $db1->query($q) or die($db1->error);
while( $row = $r->fetch_assoc() )
$data[] = array_merge($row,array('type'=>'table1'));
$r->free();

// get data from second source, add to same $data array
$q = 'SELECT
UNIX_TIMESTAMP(date) as timestamp,
DATE_FORMAT(date, "%b %d %Y %h:%i %p") as date,
content
FROM table2';
$r = $db2->query($q) or die($db2->error);
while( $row = $r->fetch_assoc() )
$data[] = array_merge($row,array('type'=>'table2'));
$r->free();

// view our data array to verify contents
var_dump($data);

// sort based on the 'stamp' key, descending (newest to oldest)
function cmp_stamp($a,$b) {
return $b['timestamp'] > $a['timestamp'];
}
usort($data,'cmp_stamp');

// view our sorted array
var_dump($data);

// loop thtough our data
foreach($data as $entry) {
echo "{$entry['content']} was posted on {$entry['date']} and was from the source: {$entry['type']}<br>";
}

?>

 

Outputs

array
  0 => 
    array
      'timestamp' => string '1342306800' (length=10)
      'date' => string 'Jul 14 2012 04:00 PM' (length=20)
      'content' => string 'foo' (length=3)
      'type' => string 'table1' (length=6)
  1 => 
    array
      'timestamp' => string '1342465200' (length=10)
      'date' => string 'Jul 16 2012 12:00 PM' (length=20)
      'content' => string 'bar' (length=3)
      'type' => string 'table1' (length=6)
  2 => 
    array
      'timestamp' => string '1342364400' (length=10)
      'date' => string 'Jul 15 2012 08:00 AM' (length=20)
      'content' => string 'cas' (length=3)
      'type' => string 'table2' (length=6)
  3 => 
    array
      'timestamp' => string '1342584000' (length=10)
      'date' => string 'Jul 17 2012 09:00 PM' (length=20)
      'content' => string 'bah' (length=3)
      'type' => string 'table2' (length=6)
array
  0 => 
    array
      'timestamp' => string '1342584000' (length=10)
      'date' => string 'Jul 17 2012 09:00 PM' (length=20)
      'content' => string 'bah' (length=3)
      'type' => string 'table2' (length=6)
  1 => 
    array
      'timestamp' => string '1342465200' (length=10)
      'date' => string 'Jul 16 2012 12:00 PM' (length=20)
      'content' => string 'bar' (length=3)
      'type' => string 'table1' (length=6)
  2 => 
    array
      'timestamp' => string '1342364400' (length=10)
      'date' => string 'Jul 15 2012 08:00 AM' (length=20)
      'content' => string 'cas' (length=3)
      'type' => string 'table2' (length=6)
  3 => 
    array
      'timestamp' => string '1342306800' (length=10)
      'date' => string 'Jul 14 2012 04:00 PM' (length=20)
      'content' => string 'foo' (length=3)
      'type' => string 'table1' (length=6)
bah was posted on Jul 17 2012 09:00 PM and was from the source: table2
bar was posted on Jul 16 2012 12:00 PM and was from the source: table1
cas was posted on Jul 15 2012 08:00 AM and was from the source: table2
foo was posted on Jul 14 2012 04:00 PM and was from the source: table1

 

Using the following SQL tables and data

--
-- Table structure for table `table1`
--

CREATE TABLE IF NOT EXISTS `table1` (
  `date` datetime NOT NULL,
  `content` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `table1`
--

INSERT INTO `table1` (`date`, `content`) VALUES
('2012-07-14 16:00:00', 'foo'),
('2012-07-16 12:00:00', 'bar');

-- --------------------------------------------------------

--
-- Table structure for table `table2`
--

CREATE TABLE IF NOT EXISTS `table2` (
  `date` datetime NOT NULL,
  `content` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `table2`
--

INSERT INTO `table2` (`date`, `content`) VALUES
('2012-07-15 08:00:00', 'cas'),
('2012-07-17 21:00:00', 'bah');

[brianwyn]

thanks a lot, that helped a bunch