Displaying multiple MySQL tables in one HTML table with PHP, experiencing loop trouble

Question

This is the code I am trying to use to display information from multiple MySQL tables in one table. I'm also trying to create an else clause if the table information doesn't exist. This is my failed attempt at it.

I keep editing this as I peck away at it but at this point I still can't get it to display the final else when the row doesn't exist.

  <?
  include"db.inc.php";//database connection
  $item = "SELECT * FROM Item";
  $result = mysql_query($item);
  while ($row=mysql_fetch_array($result)) // Display information from Item Table.
  {
    echo ("<tr><td><a href=\"edit_item.php?id=$row[ItemID]\" target=\"_blank\">Edit</a></td>");
    echo ("<td>$row[ItemID]</td>");
    echo ("<td>$row[Category]</td>");
    echo ("<td>$row[Cost]</td>");
    echo ("<td>$row[Condition]</td>");
    echo ("<td>$row[PurchaseLot_PurchaseLotID]</td>");
    echo ("<td>$row[Location]</td>");
    echo ("<td>$row[Date]</td>");
    echo ("<td>$row[Desc]</td>");
    echo ("<td>$row[Notes]</td>");
    echo ("<td><a href=\"photo_upload.php?id=$row[ItemID]\" target=\"_blank\">Pics</a></td>");
    echo ("<td><a href=\"item_info.php?id=$row[ItemID]\" target=\"_blank\">Info</a></td></tr>");
    $info = "SELECT * FROM Info WHERE Item_ItemID = $row[ItemID] ";
    $info_results = mysql_query($info);
    while ($info_row = mysql_fetch_array($info_results))  
        {
        if ($info_row['Item_ItemID'] = $row['ItemID']) // Display Information from Info table If column exists with same ItemID.
            {
            echo ("<tr><td colspan=\"12\">");
            echo ("Manufacturer:$info_row[Manufacturer]<br>");
            echo ("Model:$info_row[Model]<br>");
            echo ("Model Number:$info_row[ModelNumber]<br>");
            echo ("Serial Number:$info_row[SerialNumber]<br>");
            echo ("Service Number:$info_row[ServiceNumber]<br>");
            echo ("</td></tr>");
            } else //else dispaly Add information to info link.
                    { 
                    echo ("<tr><td colspan=\"12\"><a href=\"item_info.php?id=$row[ItemID]\" target=\"_blank\">Add Extra Information</a></td></tr>");
                    }
        }
  }
  ?>

Rasmus P · Accepted Answer · 2013-12-19 02:44:28Z

1

you could re-write the SQL statement in your $info "SELECT * FROM Info" to "SELECT * FROM Info wHERE Item_ItemID=$row['ItemID'] ... and then delete the

if ($info_row['Item_ItemID'] = $row['ItemID'])

Plus you new a while ind your code after $info_results = mysql_query($info);

* EDITED *

then you do like this, instead of

while ($info_row = mysql_fetch_array($info_results))  
        {
        if ($info_row['Item_ItemID'] = $row['ItemID']) // Display Information from Info table If column exists with same ItemID.
            {
            echo ("<tr><td colspan=\"12\">");
            echo ("Manufacturer:$info_row[Manufacturer]<br>");
            echo ("Model:$info_row[Model]<br>");
            echo ("Model Number:$info_row[ModelNumber]<br>");
            echo ("Serial Number:$info_row[SerialNumber]<br>");
            echo ("Service Number:$info_row[ServiceNumber]<br>");
            echo ("</td></tr>");
            } else //else dispaly Add information to info link.
                    { 
                    echo ("<tr><td colspan=\"12\"><a href=\"item_info.php?id=$row[ItemID]\" target=\"_blank\">Add Extra Information</a></td></tr>");
                    }
        }

try this

if(mysql_num_rows($info)>0){ 
while($info_row = mysql_fetch_array($info){ 
echo ("<tr><td colspan=\"12\">");
            echo ("Manufacturer:$info_row[Manufacturer]<br>");
            echo ("Model:$info_row[Model]<br>");
            echo ("Model Number:$info_row[ModelNumber]<br>");
            echo ("Serial Number:$info_row[SerialNumber]<br>");
            echo ("Service Number:$info_row[ServiceNumber]<br>");
            echo ("</td></tr>");
} 
}else { 
echo ("<tr><td colspan=\"12\"><a href=\"item_info.php?id=$row[ItemID]\" target=\"_blank\">Add Extra Information</a></td></tr>"); 
}

edited Dec 19, 2013 at 2:44

answered Dec 19, 2013 at 2:28

Rasmus P

1661 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DcHerrera Over a year ago

i just figured this out I edited the code above but I still can't get it to display my else clause.

Rasmus P Over a year ago

then you do like this instead if(mysql_num_rows($info)>0){ while($info_row = mysql_fetch_array($info){ echo ("<tr><td colspan=\"12\">"); echo ("Manufacturer:$info_row[Manufacturer]<br>"); .... echo ("</td></tr>"); } }else { echo ("<tr><td colspan=\"12\"><a href=\"item_info.php?id=$row[ItemID]\" target=\"_blank\">Add Extra Information</a></td></tr>"); }

yhAm · Accepted Answer · 2013-12-19 02:41:35Z

i try to edit your code but im not able to try it.

<?
  include"db.inc.php";//database connection
  $item = "SELECT * FROM Item";
  $result = mysql_query($item);
  while ($row=mysql_fetch_array($result)) // Display information from Item Table.
  {
    echo ("<tr><td><a href=\"edit_item.php?id=$row[ItemID]\" target=\"_blank\">Edit</a></td>");
    echo ("<td>$row[ItemID]</td>");
    echo ("<td>$row[Category]</td>");
    echo ("<td>$row[Cost]</td>");
    echo ("<td>$row[Condition]</td>");
    echo ("<td>$row[PurchaseLot_PurchaseLotID]</td>");
    echo ("<td>$row[Location]</td>");
    echo ("<td>$row[Date]</td>");
    echo ("<td>$row[Desc]</td>");
    echo ("<td>$row[Notes]</td>");
    echo ("<td><a href=\"photo_upload.php?id=$row[ItemID]\" target=\"_blank\">Pics</a></td>");
    echo ("<td><a href=\"item_info.php?id=$row[ItemID]\" target=\"_blank\">Info</a></td></tr>");
    $info = "SELECT * FROM Info where Item_ItemID = ".$row['ItemID'];
    $info_results = mysql_query($info);
    if ($info_row = mysql_fetch_array($info_results))  
        {
            echo ("<tr><td colspan=\"12\">");
            echo ("Manufacturer:$info_row[Manufacturer]<br>");
            echo ("Model:$info_row[Model]<br>");
            echo ("Model Number:$info_row[ModelNumber]<br>");
            echo ("Serial Number:$info_row[SerialNumber]<br>");
            echo ("Service Number:$info_row[ServiceNumber]<br>");
            echo ("</td></tr>");
        } 
        else { //else dispaly Add information to info link.
            echo ("<tr><td colspan=\"12\"><a href=\"item_info.php?id=$row[ItemID]\" target=\"_blank\">Add Extra Information</a></td></tr>");
         }        
  }
  ?>

i hope this is the answer.

Collectives™ on Stack Overflow

Displaying multiple MySQL tables in one HTML table with PHP, experiencing loop trouble

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related