How to print from multiple queries if one query contain Null result?

Question

I am new in PHP. I have problem in printing my data on my browser. I have Five queries. My four queries is based on the result on First query

1st query:

 $opinion_id = "SELECT `client_id` FROM `pacra_client_opinion_relations` WHERE `opinion_id` = 379";
$result = mysql_query($opinion_id) or die;
$row = mysql_fetch_assoc($result);
$client_id = $row['client_id'];

This query fetch the client_id and on the basis of client_id my remaining queries will be work.

Query 2:

$q_opinion="SELECT r.client_id,c.id,t.id,a.id,o.id,c.name as opinion, r.notification_date, t.title as ttitle,a.title as atitle,o.title as otitle, l.title as ltitle, s.title as stitle, pr.opinion_id, pc.id, pr.client_id as pr_client, pc.address, pc.liaison_one, city.id, pc.head_office_id, city.city, pc.title as cname
FROM og_ratings r 
    inner join
(
  select max(notification_date) notification_date,
    client_id
  from og_ratings
  group by client_id
  ) r2
  on r.notification_date = r2.notification_date
  and r.client_id = r2.client_id
LEFT JOIN og_companies c
ON r.client_id = c.id
LEFT JOIN og_rating_types t
ON r.rating_type_id = t.id
LEFT JOIN og_actions a
ON r.pacra_action = a.id
LEFT JOIN og_outlooks o
ON r.pacra_outlook = o.id
LEFT JOIN og_lterms l
ON r.pacra_lterm = l.id
LEFT JOIN og_sterms s
ON r.pacra_sterm = s.id
LEFT JOIN pacra_client_opinion_relations pr
ON pr.opinion_id = c.id
LEFT JOIN pacra_clients pc
ON pc.id = pr.client_id
LEFT JOIN city
ON city.id = pc.head_office_id
WHERE r.client_id  IN (SELECT opinion_id FROM pacra_client_opinion_relations WHERE client_id = $client_id)
";

Query 3:

$q_opinion1 = "SELECT r.client_id,c.id,t.id,a.id,o.id,c.name as opinion, r.notification_date, t.title as ttitle,a.title as atitle,o.title as otitle, l.title as ltitle, s.title as stitle, pr.opinion_id, pc.id, pr.client_id as pr_client, pc.address, pc.liaison_one, city.id, pc.head_office_id, city.city, pc.title as cname
FROM og_ratings r 
    inner join
(
  select max(notification_date) notification_date,
    client_id
  from og_ratings
  group by client_id
  ) r2
  on r.notification_date = r2.notification_date
  and r.client_id = r2.client_id
LEFT JOIN og_companies c
ON r.client_id = c.id
LEFT JOIN og_rating_types t
ON r.rating_type_id = t.id
LEFT JOIN og_actions a
ON r.pacra_action = a.id
LEFT JOIN og_outlooks o
ON r.pacra_outlook = o.id
LEFT JOIN og_lterms l
ON r.pacra_lterm = l.id
LEFT JOIN og_sterms s
ON r.pacra_sterm = s.id
LEFT JOIN pacra_client_opinion_relations pr
ON pr.opinion_id = c.id
LEFT JOIN pacra_clients pc
ON pc.id = pr.client_id
LEFT JOIN city
ON city.id = pc.head_office_id
WHERE r.client_id  IN (SELECT client_id FROM og_ratings WHERE client_id = 379)";

Query 4:

$q_opinion2="SELECT
   r.client_id,c.id,t.id,a.id,o.id,c.name as opinion, r.notification_date, t.title as ttitle,a.title as atitle,o.title as otitle, l.title as ltitle, s.title as stitle, pr.opinion_id, pc.id, pr.client_id as pr_client, pc.address, pc.liaison_one, city.id, pc.head_office_id, city.city, pc.title as cname
FROM
  og_ratings r 


  INNER JOIN (
    SELECT client_id, max(notification_date) notification_2nd_date
    FROM og_ratings
    WHERE client_id IN (SELECT `opinion_id` FROM `pacra_client_opinion_relations` WHERE `client_id` = $client_id) AND
      (client_id, notification_date) NOT IN (
        SELECT client_id, max(notification_date)
        FROM og_ratings GROUP BY client_id
          ORDER BY  client_id DESC)
    GROUP BY client_id
      ORDER BY  client_id DESC
   ) r2
  ON r.notification_date = r2.notification_2nd_date
     AND r.client_id = r2.client_id
  LEFT JOIN og_companies c ON r.client_id = c.id
  LEFT JOIN og_rating_types t ON r.rating_type_id = t.id
  LEFT JOIN og_actions a ON r.pacra_action = a.id
  LEFT JOIN og_outlooks o ON r.pacra_outlook = o.id
  LEFT JOIN og_lterms l ON r.pacra_lterm = l.id
  LEFT JOIN og_sterms s ON r.pacra_sterm = s.id
  LEFT JOIN pacra_client_opinion_relations pr ON pr.opinion_id = c.id
  LEFT JOIN pacra_clients pc ON pc.id = pr.client_id
  LEFT JOIN city ON city.id = pc.head_office_id
WHERE
  r.client_id IN (
    SELECT opinion_id FROM pacra_client_opinion_relations
    WHERE client_id = $client_id
  )";

Query 5:

$q_opinion3="SELECT
   r.client_id,c.id,t.id,a.id,o.id,c.name as opinion, r.notification_date, t.title as ttitle,a.title as atitle,o.title as otitle, l.title as ltitle, s.title as stitle, pr.opinion_id, pc.id, pr.client_id as pr_client, pc.address, pc.liaison_one, city.id, pc.head_office_id, city.city, pc.title as cname
FROM
  og_ratings r 


  INNER JOIN (
    SELECT client_id, max(notification_date) notification_2nd_date
    FROM og_ratings
    WHERE client_id IN (SELECT client_id FROM og_ratings WHERE client_id = 379) AND
      (client_id, notification_date) NOT IN (
        SELECT client_id, max(notification_date)
        FROM og_ratings GROUP BY client_id
          ORDER BY  client_id DESC)
    GROUP BY client_id
      ORDER BY  client_id DESC
   ) r2
  ON r.notification_date = r2.notification_2nd_date
     AND r.client_id = r2.client_id
  LEFT JOIN og_companies c ON r.client_id = c.id
  LEFT JOIN og_rating_types t ON r.rating_type_id = t.id
  LEFT JOIN og_actions a ON r.pacra_action = a.id
  LEFT JOIN og_outlooks o ON r.pacra_outlook = o.id
  LEFT JOIN og_lterms l ON r.pacra_lterm = l.id
  LEFT JOIN og_sterms s ON r.pacra_sterm = s.id
  LEFT JOIN pacra_client_opinion_relations pr ON pr.opinion_id = c.id
  LEFT JOIN pacra_clients pc ON pc.id = pr.client_id
  LEFT JOIN city ON city.id = pc.head_office_id
WHERE
  r.client_id IN (
    SELECT client_id FROM og_ratings WHERE client_id = 379)
  )";

If query 1 query Bring client_id then query 2 and query 4 will be executed but if there is no client_id then query 3 and query 5 will be executed.

if ($client_id == NULL)
{
    $query = $q_opinion1;
    $query1 = $q_opinion3;
    }
    else{
$query = $q_opinion;
$query1 = $q_opinion2;
    }
  $result1 = mysql_query($query) or die;
  $result2 = mysql_query($query1) or die;

Remaining PHP code is

$opinion = array();

while($row1 = mysql_fetch_assoc($result1))
{        
    $opinion[]= $row1['opinion'];
    $action[]= $row1['atitle'];
    $long_term[]= $row1['ltitle'];
    $outlook[]= $row1['otitle'];
    $rating_type[]= $row1['ttitle'];
    $short_term[]= $row1['stitle'];


}
while($row2 = mysql_fetch_assoc($result2))
{
    $p_long_term[]= $row2['ltitle'];
    $p_short_term[]= $row2['stitle'];
}
?>

And My HTML code is

<table width="657">
        <tr>
            <td width="225"> <strong>Opinion</strong></td>
            <td width="62"> <strong>Action</strong></td>
            <td colspan="4"><strong>Ratings</strong></td>
            <td width="54"><strong>Outlook</strong></td>
            <td width="67"><strong>Rating Type</strong></td>
        </tr>
        <tr>
          <td width="225">&nbsp;</td>
          <td width="62">&nbsp;</td>
          <td colspan="2"><b>Long Term</b></td>
          <td colspan="2"><b>Short Term</b></td>
          <td width="54">&nbsp;</td>
          <td width="67">&nbsp;</td>
        </tr>
        <tr>
          <td width="225">&nbsp;</td>
          <td width="62">&nbsp;</td>
          <td width="52"><b>Current</b></td>
          <td width="45"><b>Previous</b></td>
          <td width="49"><b>Current</b></td>
          <td width="51"><b>Previous</b></td>
          <td width="54">&nbsp;</td>
          <td width="67">&nbsp;</td>
        </tr>
        <?php
        for ($i=0; $i<count($opinion); $i++) {
    //if ($opinion[$i] == "")continue;
        ?>


    <tr>
           <td><?php echo $opinion[$i]?></td>
          <td><?php echo $action[$i] ?></td>
          <td><?php echo $long_term[$i] ?></td>
          <td><?php echo $p_long_term[$i]?></td>
          <td><?php echo $short_term[$i] ?></td>
          <td><?php echo $p_short_term[$i] ?></td>
          <td><?php echo $outlook[$i] ?></td>
          <td><?php echo $rating_type[$i] ?></td>
        </tr>

        <?php
        }
?>
 </table>

Now Problem Is that

Sometime my query 5 contain null result. And due to this problem my data of query 3 is not printed. I want that if my any query contain Null result my rest of data will be print on my page.

Answer 1

it seems you are looping over the opinion array and using the index to select the corresponding value in $p_long_term[] and $p_short_term[] arrays. These arrays will be empty if query 5 fails.

<tr>
       <td><?php echo $opinion[$i]?></td>
      <td><?php echo $action[$i] ?></td>
      <td><?php echo $long_term[$i] ?></td>
      <td><?php echo $p_long_term[$i]?></td>**
      <td><?php echo $short_term[$i] ?></td>
      <td><?php echo $p_short_term[$i] ?></td>
      <td><?php echo $outlook[$i] ?></td>
      <td><?php echo $rating_type[$i] ?></td>
    </tr>

check if the key exists before echo.

<td><?php if(array_key_exists ($i, $p_long_term))echo $p_long_term[$i]?></td>
<td><?php if(array_key_exists ($i, $p_short_term))echo $p_short_term[$i] ?></td>

Answer 2

The problem lies in the logic during loop. You are echoing contents which are not present in the array of query5. You may try to check for element existence first and then echoing back to out stream.

for ($i=0; $i<count($opinion); $i++) {
    echo '<tr>';
    echo isset($opinion[$i])?  '<td>'. $opinion[$i] .'</td>' : '';
    echo isset($action[$i])?  '<td>'. $action[$i] .'</td>' : '';
    echo isset($long_term[$i])?  '<td>'. $long_term[$i] .'</td>' : '';
    echo isset($p_long_term[$i])?  '<td>'. $p_long_term[$i] .'</td>' : '';
    echo isset($short_term[$i])?  '<td>'. $short_term[$i] .'</td>' : '';
    echo isset($p_short_term[$i])?  '<td>'. $p_short_term[$i] .'</td>' : '';
    echo isset($outlook[$i])?  '<td>'. $outlook[$i] .'</td>' : '';
    echo isset($rating_type[$i])?  '<td>'. $rating_type[$i] .'</td>' : '';
    echo '</tr>';
}

Answer 3

Use this

$max = max(count($opinion),count($p_long_term));
for ($i=0; $i<$max; $i++) {
    echo '<tr>';
    echo isset($opinion[$i])?  '<td>'. $opinion[$i] .'</td>' : '';
    echo isset($action[$i])?  '<td>'. $action[$i] .'</td>' : '';
    echo isset($long_term[$i])?  '<td>'. $long_term[$i] .'</td>' : '';
    echo isset($p_long_term[$i])?  '<td>'. $p_long_term[$i] .'</td>' : '';
    echo isset($short_term[$i])?  '<td>'. $short_term[$i] .'</td>' : '';
    echo isset($p_short_term[$i])?  '<td>'. $p_short_term[$i] .'</td>' : '';
    echo isset($outlook[$i])?  '<td>'. $outlook[$i] .'</td>' : '';
    echo isset($rating_type[$i])?  '<td>'. $rating_type[$i] .'</td>' : '';
    echo '</tr>';
}