Merging results from two mysql databases

[deansatch]

Using date = $date1 makes the db2 results disappear.

I assume this is because it is trying to display results from database 2 which are on the same dates as database 1 results. However, there should be no 2 gigs on the same date anyway.

The record_id works for displaying them all, it just seems impossible to get them sorted.

[deansatch]

problem solved using array_multisort

See the result here

http://www.pennysbackinblack.co.uk/gigscomb.php

[redarrow]

Well done post an example ok.

 

Always post examples so we all learn ok mate cheers.

[craygo]

As someone suggested in the first couple of posts, I would have just inserted all the data from both tables into a temporary table, Then just queried the temporary table, sort it and echo it out. Think it would be alot less code.

 

Ray

[deansatch]

Here is the finished code

$ct = 0;
$store = Array();

mysql_connect($server1, $username, $pwd);
mysql_select_db($db1);

$result = mysql_query("SELECT * FROM table_3 WHERE id > 0 ");
while($row=mysql_fetch_array($result)) {
    $date[6][$ct] = $row["date"];
   $store[0][$ct] = date("l jS F Y", strtotime($date[6][$ct]) );
    $store[1][$ct] = $row["venue"];
    $store[2][$ct] = $row["tickets"];
    $store[3][$ct] = $row["time"];
    $store[4][$ct] = $row["address"];
    $store[5][$ct] = "#0000ff";
    ++$ct;
}

mysql_connect($server2, $username2, $pwd);
mysql_select_db($db2);

$result = mysql_query("SELECT * FROM table_1 WHERE id > 0 ");
while($row=mysql_fetch_array($result)) {
    $date[6][$ct] = $row["date"];
    $store[0][$ct] = date("l jS F Y", strtotime($date[6][$ct]) );
    $store[1][$ct] = $row["venue"];
    $store[2][$ct] = $row["tickets"];
    $store[3][$ct] = $row["time"];
    $store[4][$ct] = $row["address"];
    $store[5][$ct] = "#ff0000";
    ++$ct;
}

$st = array_multisort($date[6],SORT_DESC,$store[0], $store[1], $store[2], $store[3], $store[4], $store[5]);

$ct_end = $ct - 1;

echo '<table border="1">';
echo '<tr>';
echo '<th align="center">Date</th>';
echo '<th align="center">Venue</th>';
echo '<th align="center">Tickets</th>';
echo '<th align="center">Time</th>';
echo '<th align="center">Address</th>';
echo '</tr>';

for ($i = 0; $i <= $ct_end; ++$i) {
echo <<<EOT
<tr>
   <td style="align:center;color:{$store[5][$i]};">{$store[0][$i]}</td>
   <td style="align:center;color:{$store[5][$i]};">{$store[1][$i]}</td>
   <td style="align:center;color:{$store[5][$i]};">{$store[2][$i]}</td>
   <td style="align:center;color:{$store[5][$i]};">{$store[3][$i]}</td>
   <td style="align:center;color:{$store[5][$i]};">{$store[4][$i]}</td>
</tr>
EOT;
}   

echo '</table>';