Joining two mysql_fetch_arrays

Question

I am trying to piece two queries together. Below is the code I'm using. However the table is splitting up the data. How can I remedy this? Or what better solutions are there?

while($row = mysql_fetch_array($result))
{
    echo "<tr id='centered' >";  
    echo "<td class='leftalign'>" . $row['Quarter_Name'] . "</td>";
    echo "<td>" . $row['Quarterly_yield'] . "</td>";
    echo "<td>" . $row['Quarterly_yield'] . "</td>";
    echo "<td>" . $row['Quarterly_yield'] . "</td>";
}

while($row = mysql_fetch_array($result8))
{
    echo "<td>" . $row['Quarterly_yield'] . "</td>";
}

The 2 Queries are as follows: They are nearly identical

SELECT  LEFT(A.F_ANOTRIMESTRE, 4) Year,
        RIGHT(A.F_ANOTRIMESTRE, 2) Quarter,
        IF(RIGHT(A.F_ANOTRIMESTRE, 2)=03,'Enero a Marzo',
            IF(RIGHT(A.F_ANOTRIMESTRE, 2)=06,'Abril a Junio',
                IF(RIGHT(A.F_ANOTRIMESTRE, 2)=09,'Julio a Septiembre',
                    IF(RIGHT(A.F_ANOTRIMESTRE, 2)=12,'Octubre a Diciembre', '')
                )
            )
        ) Quarter_Name,
        ROUND(A.POR_RENTABILIDAD, 2) Quarterly_yield
FROM    dr_rent_carteras_trimestres A
WHERE   A.ID_CARTERA = $ID_CARTERA
AND     A.IND_RENTABILIDAD = 1
AND     LEFT(A.F_ANOTRIMESTRE, 4) = (
            SELECT MAX(left(F_ANOTRIMESTRE, 4)) - 0
            FROM   dr_rent_carteras_trimestres
            WHERE  ID_CARTERA = $ID_CARTERA 
        )

Here is the 2nd one:

SELECT  LEFT(A.F_ANOTRIMESTRE, 4) Year,
        RIGHT(A.F_ANOTRIMESTRE, 2) Quarter,
        IF(RIGHT(A.F_ANOTRIMESTRE, 2)=03,'Enero a Marzo',
            IF(RIGHT(A.F_ANOTRIMESTRE, 2)=06,'Abril a Junio',
                IF(RIGHT(A.F_ANOTRIMESTRE, 2)=09,'Julio a Septiembre',
                    IF(RIGHT(A.F_ANOTRIMESTRE, 2)=12,'Octubre a Diciembre', '')
                )
            )
        ) Quarter_Name,
        ROUND(A.POR_RENTABILIDAD, 2) Quarterly_yield
FROM    dr_rent_carteras_trimestres A
WHERE   A.ID_CARTERA = $ID_CARTERA
AND     A.IND_RENTABILIDAD = 1
AND     LEFT(A.F_ANOTRIMESTRE, 4) = ( 
            SELECT MAX(left(F_ANOTRIMESTRE, 4)) - 1
            FROM   dr_rent_carteras_trimestres
            WHERE  ID_CARTERA = $ID_CARTERA 
        )

how about using UNION between them? However if you wrote that you know a lot more SQL than me :-) — user557846
– user557846, Commented Dec 5, 2012 at 23:14
Fetch the full arrays, then merge them. or loop and check the row is right and insert from the second array. then loop the full merged array to display. — shapeshifter
– shapeshifter, Commented Dec 5, 2012 at 23:32

Nikolaos Dimopoulos · Accepted Answer · 2012-12-06 00:17:44Z

In your code above you open the tr tag in the first loop but never close it. Also in the first loop you print out the Quarterly_yield three times yet there is one column calculated for that result. Effectively you are printing out the same thing.

A couple of options - Print each result from each query in its own row:

// Loop through the results and print 
while($row = mysql_fetch_array($result))
{
    echo "<tr id='centered' >\m";  
    echo "<td class='leftalign'>{$row['Quarter_Name']}</td>\n";
    echo "<td>{$row['Quarterly_yield']}</td>\n";
    echo "</tr>\n";
}

while($row = mysql_fetch_array($result8))
{
    echo "<tr id='centered' >\m";  
    echo "<td class='leftalign'>{$row['Quarter_Name']}</td>\n";
    echo "<td>{$row['Quarterly_yield']}</td>\n";
    echo "</tr>\n";
}

Or you can create an array with the results you have and then print that array. This effectively will join the results together. So let's assume that the array is structured as such:

$results[Year-Quarter] = array(Year, Quarter, QuarterName, Result1, Result2)

then you can construct the array as follows:

$results = array();

while($row = mysql_fetch_array($result))
{
    $key = $row['Year'] . '-' . $row['Quarter'];
    $results[$key] = array(
        'Year'            => $row['Year'],
        'Quarter'         => $row['Quarter'],
        'Quarter_Name'    => $row['Quarter_Name'],
        'Quarter_yield_1' => $row['Quarter_yield'],
        'Quarter_yield_2' => 0,
    );
}

while($row = mysql_fetch_array($result8))
{
    $key = $row['Year'] . '-' . $row['Quarter'];

    // Check if we have this key
    if (isset($results[$key]))
    {
        $results[$key]['Quarter_yield_2'] = $row['Quarter_yield'];
    }
    else
    {
        $results[$key] = array(
            'Year'            => $row['Year'],
            'Quarter'         => $row['Quarter'],
            'Quarter_Name'    => $row['Quarter_Name'],
            'Quarter_yield_1' => 0,
            'Quarter_yield_2' => $row['Quarter_yield'],
        );
}

and then print the results from the $results array

foreach ($results as $item)
{
    echo "<tr id='centered' >\m";  
    echo "<td class='leftalign'>{$item['Quarter_Name']}</td>\n";
    echo "<td>{$item['Quarter_yield_1']}</td>\n";
    echo "<td>{$item['Quarter_yield_2']}</td>\n";
    echo "</tr>\n";
}

Collectives™ on Stack Overflow

Joining two mysql_fetch_arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related