Jump to content

How can I echo 2 queries from 2 sql databases?

brianwyn

By brianwyn
in PHP Coding Help

 Share

Recommended Posts

brianwyn Newbie

brianwyn

Posted
Posted

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();
?>

Link to comment
Share on other sites
xyph Advanced Member

xyph

Posted
Posted

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

Link to comment
Share on other sites
xyph Advanced Member

xyph

Posted
Posted

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...

Link to comment
Share on other sites
brianwyn Newbie

brianwyn

Posted
  • Author
Posted

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>';

 

 

Link to comment
Share on other sites
xyph Advanced Member

xyph

Posted
Posted

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');

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.