使用公共列从两个表中选择all - 相同的列名

Question

table banners

| id | src       | roll |w  |...|
-------------------------
| 1  | blue.jpg  | 7    |120|...|
| 2  | white.jpg | 5    |250|...|
| 3  | green.jpg |      |140|...|

table brolls

| id | src        |...|
-------------------
| 4  | jones.jpg  |...|
| 5  | abba.jpg   |...|
| 7  | italia.jpg |...|

I need to select all from both tables

tables have different number of columns

not all rows from banners have a roll value

banners.roll is in fact brolls.id

while ($row = $st->fetch()){
    $items .=
    "<img src = '" . $row['banners.src'] . "'
    data-id = " . $row['banners.id'] . "
    data-roll = '" . $row['brolls.src'] . "'
    data-w = " . $row['banners.w'] . "
    alt='img'>

";
}

So final result should be:

<img src = 'blue.jpg' data-id=1 data-roll='italia.jpg' data-w=120 alt='img'>

I need all images from banners listed this way.

Any help?

Answer 1

You do not specify what type of database you have. I guess you are using mysql. You could be clearer on saying what you want. I guess you are looking for the SQL queries and the PHP code to sort the results the way you want.

Here is a suggestion.

The SQL must be:

$sql_a = 'SELECT id, src, roll, w FROM banners';
$sql_b = 'SELECT id, src FROM brolls';

You should get all of them in 2 different queries, and form 2 different arrays, one for each: $array_banners and $array_brolls; with the next structure:

$array_banners['id value'] = array('src' => 'src value', 'roll' => 'roll value', 'w' => 'w value')

$array_brolls['id value'] = 'src value';

Then you will do the image html coding:

foreach( $array_banners as $k => $v)
{
"<img src = '" . $v['src'] . "'
    data-id = " . $k . "
    data-roll = '" . $array_brolls[$v['roll']]['src'] . "'
    data-w = " . $v['w'] . "
    alt='img'>

"
};

And ready.