syntax problem, help with the correct way to display

[CodeMama]

I have this query and snippet which works and I can echo the correct records:

 $query =  "SELECT admin.UserName, admin.AdminID, workorders.WorkOrderID,
workorders.CreatedDate, workorders.Location, workorders.WorkOrderName,
workorders.FormName, workorders.STATUS, workorders.Notes, workorders.pod
FROM admin LEFT JOIN workorders ON (admin.AdminID = workorders.AdminID)
WHERE admin.Retail1 = 'yes'
";
  
    $result = mysql_query ($query) ;    
    //$row = mysql_fetch_array($result);
    while ($row = mysql_fetch_row($result)){
    $admin_id = $row['AdminID'];
    for ($i=0; $i<mysql_num_fields($result); $i++);
        echo $row[$i] . " ";
        
        }

 

My problem lies in now I want to take the hodge podge display of the echo$row[$i] and neatly display it and I am trying to use something like this:

<?php echo $row['WorkOrderID']; ?>

but get nothing I also tried with just the $i ... what is the correct way to use the results from the query to display individual field results?

[rhodesa]

mysql_fetch_row - Get a result row as an enumerated array

mysql_fetch_array - Fetch a result row as an associative array, a numeric array, or both

mysql_fetch_assoc - Fetch a result row as an associative array

 

...so you need to use one of the last two

 

 

[CodeMama]

I tried that originally using assoc and array but then I was only getting the first record in the table and not all of them, the way it is now it returns all of them so I am wondering how to take

$row['$i'] and turn that back into an array?

 

$display = mysql_fetch_assoc($row['$i'])  ??? something along these lines. but I'm still having the syntax issue i'm guessing because I get the blank page of death...

[CodeMama]

Well no blank page but my $display variable returns nothing so I'm losing my results somewhere:

    $query =  "SELECT admin.UserName, admin.AdminID, workorders.WorkOrderID,
workorders.CreatedDate, workorders.Location, workorders.WorkOrderName,
workorders.FormName, workorders.STATUS, workorders.Notes, workorders.pod
FROM admin LEFT JOIN workorders ON (admin.AdminID = workorders.AdminID)
WHERE admin.Retail1 = 'yes'
";
  
    $result = mysql_query ($query) ;    
    //$row = mysql_fetch_array($result);
    while ($row = mysql_fetch_row($result)){
    $admin_id = $row['AdminID'];
    for ($i=0; $i<mysql_num_fields($result); $i++);
        //echo $row[$i] . " ";
        $display = mysql_fetch_assoc($row['$i']) . " "; not sure about this ...         echo $display['$i'] . " ";   
        }
        

[rhodesa]

no...like this:

 

 $query =  "SELECT admin.UserName, admin.AdminID, workorders.WorkOrderID,
workorders.CreatedDate, workorders.Location, workorders.WorkOrderName,
workorders.FormName, workorders.STATUS, workorders.Notes, workorders.pod
FROM admin LEFT JOIN workorders ON (admin.AdminID = workorders.AdminID)
WHERE admin.Retail1 = 'yes'
";

    $result = mysql_query ($query) ;   
    while ($row = mysql_fetch_array($result)){
      $admin_id = $row['AdminID'];
      print_r($row);
    }

[CodeMama]

Well that def. works and pulls the correct records but then when I try to display them neatly like this:

 

<TR bgcolor="<?php echo $row_color ?>">
                <TD><FONT SIZE="-2">K<?php echo $row['WorkOrderID']; ?></FONT></TD>
                <TD><FONT SIZE="-2"><?php echo $row['Location']; ?></FONT></TD>
                <TD><FONT SIZE="-2">

 

I get nothing for WorkOrderID or Location....

[kenrbnsn]

When you do this:

<?php
    while ($row = mysql_fetch_array($result)){
      $admin_id = $row['AdminID'];
      echo '<pre>' . print_r($row,true) . '</pre>';
}
?>

what is shown on the screen?

 

Ken

[CodeMama]

The records I need are printed , but then why further down the page will they not display and if I echo $row I get nothing (further down)

somewhere it seems the $row is losing its data...how

 

[CodeMama]

Here is the code below what's working and I have highlighted where it stops echoing the results:

 

//Query works to return correct records 
    $query =  "SELECT admin.UserName, admin.AdminID, workorders.WorkOrderID,
workorders.CreatedDate, workorders.Location, workorders.WorkOrderName,
workorders.FormName, workorders.STATUS, workorders.Notes, workorders.pod
FROM admin LEFT JOIN workorders ON (admin.AdminID = workorders.AdminID)
WHERE admin.Retail1 = 'yes'
";

    $result = mysql_query ($query) ;   
    while ($row = mysql_fetch_array($result)){
      $admin_id = $row['AdminID'];
      //print_r($row);
      echo '<pre>' . print_r($row,true) . '</pre>';
    }

   
        
    if ($row['Retail1'] == "NO") {
        header ("Location: Welcome.php?AdminID=$AdminID&msg=Sorry, you do not have access to that page.");
    }
    
if (isset($_GET['SortBy'])) {$SortBy = $_GET['SortBy'];} else {$SortBy = 'WorkOrderID DESC';}
if (isset($_GET['Page'])) {$Page = $_GET['Page'];} else {$Page = 1;}

$PerPage = 30;
$StartPage = ($Page - 1) * $PerPage;

        
     
     
      $Total = ceil(mysql_num_rows($result)/$PerPage);
    
     //Reveal Variables for Debugging
   // include("VariableReveal2.php");
If ($Page > 0) {$PagePrev = ($Page - 1);} else {$PagePrev = '';}
If ($Page < $Total) {$PageNext = ($Page + 1);} else {$PageNext = '';}
?>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<TITLE>Work Order System - Administrative Section</TITLE>
<LINK REL="STYLESHEET" HREF="inc/style.css">
</head>
<body>

<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0">
<tr>
<td colspan="9">
this will not display here... even though it does above in the code... 
<?php    while ($row = mysql_fetch_array($result)){      $admin_id = $row['AdminID'];      echo '<pre>' . print_r($row,true) . '</pre>';}?>
</td>
</tr>

------------------------------------------------------------

[kenrbnsn]

You need to use the values in the while loop.

 

You can't do another while like that.

 

Ken

[CodeMama]

So there is no way to just echo the records like echo $row['fieldname'] (the regular way) because of the WHILE?  Is it possible to move the closing loop bracket to encompass the html on the page for the neatly table layout of the results?

 

[CodeMama]

Wow so in answer to my own question it did work to move the while end bracket...yay...I'll be a php coder in no time!! Thanks everyone!

[kenrbnsn]

You can try using mysql_data_seek

<?php
mysql_data_seek($result,0);
?>

before the second while statement.

 

Ken