Mutiple Tables within one query

[mcfmullen]

Hello,

 

I have a php file calling several MySQL tables under given conditions. The code below does function, HOWEVER I want to combine the output of the two queries into one table:

 

EX: Query 1 outputs 3 animals, Query 2 outputs 2 buildings. Under given code the output is as follows:

 

animal1, animal2, animal3

building1, building2

 

I want to make it so that the output is:

 

animal1, animal2, animal3, building1, building2

 

Anyone have any ideas?

 

<?php
$columns = 5;

$result2 = mysql_query("SELECT Animals.photoAnimal, Animals.nameAnimal FROM Animals LEFT JOIN animalMethods USING (nameAnimal) WHERE animalMethods.nameMethod  = '{$_GET['decoration']}'");

$numofrows = mysql_num_rows($result2);
echo "<br><table width='99%'><tr><th colspan='5'><h2>Found Inside</h2></th></tr>";
for($i = 0; $i < $numofrows; $i++) {
    $row = mysql_fetch_array($result2);
    if($i % $columns == 0) {
        echo "<tr>";
    }
    echo "<td><img src='".$animalimg.$row['photoAnimal']."' height='100px' width='100px'>";
    echo "<br>";
    echo "<a href='http://www.cool.com/animalspec.php?animal={$row['nameAnimal']}'>{$row['nameAnimal']}</a></td>";
    if(($i % $columns) == ($columns - 1) || ($i + 1) == $numofrows) {
        echo "</tr>";
    }
}

$result3 = mysql_query("SELECT Buildings.photoBuilding, Buildings.nameBuilding FROM Buildings LEFT JOIN buildingMethods USING (nameBuilding) WHERE buildingMethods.nameMethod  = '{$_GET['decoration']}'");

$numofrows = mysql_num_rows($result3);
for($i = 0; $i < $numofrows; $i++) {
    $row = mysql_fetch_array($result3);
    if($i % $columns == 0) {
        echo "<tr>";
    }
    echo "<td><img src='".$buildingimg.$row['photoBuilding']."' height='100px' width='100px'>";
    echo "<br>";
    echo "<a href='http://www.cool.com/buildingspec.php?building={$row['nameBuilding']}'>{$row['nameBuilding']}</a></td>";
    if(($i % $columns) == ($columns - 1) || ($i + 1) == $numofrows) {
        echo "</tr>";
    }
}
echo "</table>";
?>

[Mchl]

http://dev.mysql.com/doc/refman/5.0/en/union.html

[mcfmullen]

Thank you for pointing that out!

 

[mcfmullen]

There's a problem though. If I use the UNION, how can I store the output into a useable array for php/html formatting?

 

As it is, animal and building images are stored in separate folders. There is no question that they be stored in the same folder. Also, animals and buildings lead to different urls (different php layouts). I want the output to display the image and name of the animal or building inside one cell of a table to a maximum of 5 columns and unlimited rows (until there are none).

 

As it is, the output I have is:

________________________________________

| Animal Photo 1  | Animal Photo 2  | Animal Photo 3 |

| Animal Name 1  | Animal Name 2  | Animal Name 3 |

_________________________________________

| Building Photo 1 | Building Photo 2 | Building Photo 3 |

| Building Name 1 | Building Name 2 | Building Name 3 |

 

The output I want is:

____________________________________________________________________

| Animal Photo 1  | Animal Photo 2  | Animal Photo 3 | Building Photo 1 | Building Photo 2 |

| Animal Name 1  | Animal Name 2  | Animal Name 3 | Building Name 1 | Building Name 2 |

_____________

| Building Photo 3 |

| Building Name 3 |

 

 

Using UNION without PHP:

animal photo 1 animal name 1

animal photo 2 animal name 2

animal photo 3 animal name 3

building photo 1 building name 1

building photo 2 building name 2

building photo 3 building name 3

 

[mcfmullen]

An update:

 

Here's my current code:

<?php
$columns = 5;

$result2 = mysql_query("SELECT Animals.photoAnimal, Animals.nameAnimal FROM Animals LEFT JOIN animalMethods USING (nameAnimal) WHERE animalMethods.nameMethod  = '{$_GET['decoration']}' UNION SELECT Buildings.photoBuilding, Buildings.nameBuilding FROM Buildings LEFT JOIN buildingnMethods USING (nameBuilding) WHERE buildingMethods.nameMethod  = '{$_GET['decoration']}'");

$numofrows = mysql_num_rows($result2);
echo "<br><table width='99%'><tr><th colspan='5'><h2>Found Inside</h2></th></tr>";
for($i = 0; $i < $numofrows; $i++) {
    $row = mysql_fetch_array($result2);
    if($i % $columns == 0) {
        echo "<tr>";
    }
    echo "<td><img src='".$animalimg.$row['photoAnimal']."' height='100px' width='100px'>";
    echo "<br>";
    echo "<a href='http://www.cool.com/animalspec.php?animal={$row['nameAnimal']}'>{$row['nameAnimal']}</a></td>";
    if(($i % $columns) == ($columns - 1) || ($i + 1) == $numofrows) {
        echo "</tr>";
    }
}
echo "</table>";
?>

 

I get the output I want with this code. HOWEVER:

 

1) the urls for the buildings don't work (they try to look in animalspec.php rather than buildingspec.php)

2) the images for the buildings don't work (they look in the animals folder rather than the buildings folder)

 

So the question is, how do I get the urls to look in the right place?