CODEWITHSUNDEEP
phpmysqlsql
John Harbert
John Harbert

Reputation: 197

Joining two mysql_fetch_arrays

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 
        )

Upvotes: 0

Views: 67

Answers (1)

Nikolaos Dimopoulos
Nikolaos Dimopoulos

Reputation: 11485

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";
}

Upvotes: 1

Related Questions