Re: [PHP] Re: [PHP-DB] Re: Problems with displaying results

On Tue, Mar 3, 2009 at 2:16 PM, Ashley Sheridan <[email protected]>wrote:

> On Tue, 2009-03-03 at 11:08 -0600, Terion Miller wrote:
> > On Tue, Mar 3, 2009 at 10:32 AM, Shawn McKenzie <[email protected]
> >wrote:
> >
> > > Terion Miller wrote:
> > > > I have two queries one pulls out which users to use and the second
> pulls
> > > > those users orders....
> > > > Looks something like this but is only pulling the first record:
> > > >
> > > >
> > > >     $query =  "SELECT `UserName`, `AdminID` FROM admin
> > > >       WHERE   Key1 =  'YES' ";
> > > >
> > > >     $result = mysql_query ($query) ;
> > > >     $row = mysql_fetch_assoc($result);
> > > >
> > > >     //Reveal Variables for Debugging
> > > >     // include("VariableReveal2.php");
> > > >      echo ("Hello <br>");
> > > >     //echo $row['AdminID'];
> > > >     echo ($row['UserName']);
> > > >
> > > >
> > > >
> > > >
> > > >     if ($row['Key1'] == "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;
> > > >
> > > >         second query here is using the $row from the first (and yes I
> > > know
> > > > not to use *, just did so here to keep post shorter)
> > > >
> > > >          $sql= "SELECT * FROM workorders WHERE AdminID =
> > > > '".$row['AdminID']."' ";
> > > >       // $sql .= "ORDER BY $SortBy LIMIT $StartPage, $PerPage";
> > > >       $result = mysql_query ($sql);
> > > >       $row2 = mysql_fetch_assoc($result);
> > > >       $Total = ceil(mysql_num_rows($result)/$PerPage);
> > > >
> > > >
> > > >     So this works but only half way as it only displays the first
> record
> > > in
> > > > the table.
> > > >
> > > >
> > > >
> > > >
> > > > Thanks
> > > > Terion
> > > >
> > > > Happy Freecycling
> > > > Free the List !!
> > > > www.freecycle.org
> > > > Over Moderation of Freecycle List Prevents Post Timeliness.
> > > > ------------------------------------------------
> > > > Twitter?
> > > > http://twitter.com/terionmiller
> > > > ------------------------------------------------
> > > > Facebook:
> > > > <a href="http://www.facebook.com/people/Terion-Miller/1542024891"
> > > > title="Terion Miller's Facebook profile" target=_TOP><img src="
> > > > http://badge.facebook.com/badge/1542024891.237.919247960.png"
> border=0
> > > > alt="Terion Miller's Facebook profile"></a>
> > > > Groucho Marx  - "I have had a perfectly wonderful evening, but this
> > > wasn't
> > > > it."
> > > >
> > >
> > > You need to lookup the mysql_fetch_assoc() function.  It only returns
> > > one row from a result set that may contain multiple rows.  You need
> > > something like:
> > >
> > > while($row = mysql_fetch_assoc($result)) {
> > >  // do something with $row
> > > }
> > >
> > > ---------------------------------------------------
> >
> > well I looked it up in my book and tried this example but still get the
> same
> > thing, the first record
> >
> >     $result = mysql_query ($query) ;
> >     //$row = mysql_fetch_array($result);
> >     while ($row = mysql_fetch_row($result)){
> >     for ($i=0; $i<mysql_num_fields($result); $i++)
> >         echo $row[$i] . " ";
> >         //print a return for neatness sake
> >         echo "\n";
> What happens if you just change your while loop to this:
>
> while($row = mysql_fetch_array($result))
> {
>    print_r($row);
> }
>
>
> Ash
> www.ashleysheridan.co.uk
>
> Still having problems with getting this script to work, the first part of
the query does now work since I used the suggested JOIN, so my results are
there and I can echo them but now I can't seem to get them to display neatly
somehow:

--------------------CODE THUS
FAR--------------------------------------------
   $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] . " ";

        }

    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;


         $sql= "SELECT WorkOrderID, CreatedDate, Location, WorkOrderName,
AdminID, FormName, Status, Notes, pod FROM `workorders` WHERE AdminID =
'".$row['AdminID']."' ";

       $query .= "ORDER BY $SortBy LIMIT $StartPage, $PerPage";
       $result=mysql_query($query) or die('Queryproblem: ' . mysql_error() .
'<br />Executed                   query: ' . $query);
      $row2 = mysql_fetch_assoc($result);



      while ($row2 = mysql_fetch_row($result)) {
     // for ($i=0; $i<mysql_num_fields($result); $i++);



      $Total = ceil(mysql_num_rows($result)/$PerPage);
      }

     //Reveal Variables for Debugging
    include("VariableReveal2.php"); this is interesting too because this
variable reveal script shows that row2 has nothing in it, and really I don't
need the second query but I don't know how to use the seperate fields in the
$row['$i'] I tried doing things like echo $i['AdminID'] and got nothing...
-----------------------------------------------------------------------------------------------------------------------
here is what the variable view returns:
$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' ORDER BY WorkOrderID DESC LIMIT 0,
30"$result"Resource
id #5"$row""$admin_id""$i"10"this does print out all 10 records if echo
$row['$i'] is used$SortBy"WorkOrderID DESC"$Page"1"$PerPage"30"$StartPage"0"
$sql"SELECT WorkOrderID, CreatedDate, Location, WorkOrderName, AdminID,
FormName, Status, Notes, pod FROM `workorders` WHERE AdminID = '' "$row2""
$Total"1"