We help IT Professionals succeed at work.

Using PHP, how do I loop through and display db results in particular way?

justmelat
justmelat asked
on
332 Views
Last Modified: 2013-12-12
this is apparently a very difficult question, but i will try again today.

I need to spit out some data in a particular way, to see how: look at this link: http://mywebgenius.net/one.htm

here is the query I am using:

SELECT     TBL_ACCOUNT.UserUID, TBL_ACCOUNT.ID, TBL_ACCOUNT.UserLevel, TBL_ACCOUNT.PCCount, TBL_ACCOUNT.Enter_ShardNum,
                      TBL_ACCOUNT.LastPlaySlot, TBL_ACCOUNT.LastLoginTime, TBL_PC.PCUID, TBL_PC.FirstName, TBL_PC.LastName, TBL_PC.Gender
FROM         TBL_ACCOUNT INNER JOIN
                      TBL_PC ON TBL_ACCOUNT.UserUID = TBL_PC.UserUID
where TBL_ACCOUNT.ID like '%red%'

I am using a while loop like this>> while($row = mssql_fetch_array($qry)) to spit out the data and then $row['ID'], etc.  to get the exact field, but think i need a do..while loop or for loop to get my data to show the way I want it to.  I tried a nested while loop, but that didn't work, or perhaps I am not coding correctly.

Someone suggested a foreach loop and a while, but it's not working.  Can anyone help me by showing the coding for the please? - Thanks in advance.
Comment
Watch Question

Commented:
can you post your code?

Also I don't quite follow what you are trying to accomplish.

Author

Commented:
Hi NP

look at the linke again.  I have added a second pic.  The pic shows how the data is displayed with my current qry.
what I want is for the data in tbl_account which is the UsuerID, ID, PCCount to print one time, then spit out the info from tbl_PC which is the firstname and lastname

does that make sense.  

Author

Commented:
here is my current code:
<html>
<title></title>
<head> 
 
</head> 
<body onload="document.srcFrm.btnSubmit.focus();">
 
<?php
session_start();
session_register("sess_host");
session_register("sess_searchName");
 
 
 
$phpself = $_SERVER['PHP_SELF'];
 
if ($_POST['btnSubmit'] == 'Submit' )
{
	if (!$_POST['host'])
		{
			echo "<font color=#FF0000 size=5><b>You must choose an environment to complete this search</b></font>";
			echo "<script>setTimeout(\"document.location.href = 'search_proc_h.php'\",2000)</script>";
		}
		
	else 
	{
	
	if ($_POST['host']== 'V1')
			{
				$sess_host = $host = 'TBS-5E689BFE3D6.turner.com';
			}
			else{
				$sess_host = $host='cmogp1devg8.turner.com';
			}}
	
	$searchName = trim($_POST['txt_search']);
	$connection=mssql_connect($sess_host, $user, $password) or Die("<br><br>DB Env Selection Not Complete. ");
	mssql_select_db($dbname, $connection);
 
			if (!$_POST['R1'] || !$_POST['txt_search'])
			{
			echo "<b>Sorry, You did not enter all the necessary search criteria, Please Try Again.</b>";
			echo "<script>setTimeout(\"document.location.href = 'search_proc_h.php'\",2000)</script>";
 
			exit;
			}
else{
	$sess_searchName = $searchName;
	
		if ($_POST['R1']== 'username')
			{
			echo "<br><br><b><u><font color=#000080>Search Results Below by User ID.</font></u></b><br><br>";
			$qry = mssql_query("select UserUID, ID, Password, GameStartFlag, PCCount from TBL_ACCOUNT where ID LIKE   '%".$sess_searchName."%'",$connection);
			//$qry = mssql_query("SELECT TBL_ACCOUNT.UserUID, TBL_ACCOUNT.ID, TBL_ACCOUNT.PCCount, TBL_ACCOUNT.ID, TBL_ACCOUNT.Password, TBL_PC.FirstName, TBL_PC.LastName FROM TBL_ACCOUNT INNER JOIN TBL_PC ON TBL_ACCOUNT.UserUID = TBL_PC.UserUID where TBL_ACCOUNT.ID LIKE   '%".$searchName."%'",$connection);
			$num_results = mssql_num_rows($qry);
			
			if ($num_results == 0)
				{
				echo "<b><font color=#800000 size=4>No records found.  Please try again.</font></b>";
				}
				else
				{
					echo "<b>".$num_results." record(s) found.";
				}}
			elseif ($_POST['R1']== 'charname')
				{
				echo "<br><br><b><u><font color=#000080>Search Results Below by Character Name.</font></u></b><br><br>";
				$qry = mssql_query("SELECT TBL_ACCOUNT.UserUID, TBL_ACCOUNT.PCCount, TBL_ACCOUNT.ID, TBL_ACCOUNT.Password, TBL_PC.FirstName, TBL_PC.LastName FROM TBL_ACCOUNT INNER JOIN TBL_PC ON TBL_ACCOUNT.UserUID = TBL_PC.UserUID where TBL_PC.FirstName LIKE '%".$searchName."%'"." or TBL_PC.LastName LIKE '%".$searchName."%'",$connection) or die("char qry died");
				$num_results = mssql_num_rows($qry);
				if ($num_results == 0)
				{
				echo "<b><font color=#800000 size=4>No records found.  Please try again.</font></b>";
				}
				else
				{
					echo "<b>".$num_results." record(s) found.";
				}
				}
			echo "<table border=1 id=table1 width=50% style='border-collapse: collapse'><tr>";
			echo "<td align=center  bgcolor=#000080><b><font color=#ffffff>User ID</td><td align=center  bgcolor=#000080><b><font color=#ffffff>User Name</td><td align=center  bgcolor=#000080><b><font color=#ffffff>PCCount</td><td align=center  bgcolor=#000080><b><font color=#ffffff>GameStartFlag</td></tr>";
					$grey = "#c0c0c0";
					$white = "#FFFFFF";
					$i=0;
 
			while($row = mssql_fetch_array($qry))
				{
					$color = ($i%2 ? $grey:$white);
					if ($row['GameStartFlag'] == 1)
					$msg = "<a href=userStuck_proc.php?txt_userStuck=".$row['UserUID'].">Unstick</a>";
					else $msg=" ";
					
					echo "<tr><td bgcolor=$color>".$row["UserUID"]."</td><td bgcolor=$color><a href=userDetail_proc.php?txt_viewDetail=$row[UserUID]".">".$row["ID"]."</a></td><td align=center bgcolor=$color>".$row["PCCount"]."</td><td align=center bgcolor=$color>".$row["GameStartFlag"]."  ".$msg."</td></tr>";
					//<td align=center bgcolor=$color><a href=userModify_proc.php?txt_modUser=$row[UserUID]>Modify</a>  |  <a href=userDetail_proc.php?txt_viewDetail=$row[UserUID]>View</a></td>
					 $i++;
 
				}
				echo "</table>";
				exit;
}}
 
 
	echo "<form method=POST action=$phpself name=srcFrm >";
	echo "<table border=1 id=table1 width=55% style='border-collapse: collapse'><tr>";
echo "<input type=radio value=V1 name=host><font color=#000080><b>Local    <input type=radio checked value=V2 name=host>Unstable Env<br><br>";
  echo "<td align=center width=49%><b>Search by User ID:<br><input type=radio checked value=username name=R1></td>";
  echo "<td align=center width=50%><b>Search by Character Name:<br><input type=radio value=charname name=R1></td>";
  echo "<tr><td colspan=3><p align=center><b>Enter Search Criteria:<b><br> <input type=text name=txt_search size=35 onKeyDown=(event.which==13||event.keyCode==13)this.form.submit();></td></tr></p>";
  echo "<tr><td colspan=3><p align=center><input type='submit' value=Submit name=btnSubmit></td></tr></p>";
  
echo "</form>";
 
?>
</body></html>

Open in new window

Commented:
that requires two querys, one to get the information that you want to only show once, then a nested one in the loop that shows the first data, that gets the subsequent data and show it afterwards.

Author

Commented:
can you show me how? and here is the catch now if there are 5 users who have various characters from the tbl_PC table I need to display like this:

userID 1
    char1
    char2
userID 2
    char1
    char2
    char3
userID 3
     char1

see what i mean, it won't always be the return of one record.  Look at that first pic here: http://justmelat1.brinkster.net/one.htm 

Author

Commented:
Take a look at this snippet of code:   It works, but two problems, i don't understand arrays yet, 2) because of that, I can't get it to display anything except the UserUID
while($row = mssql_fetch_object($qresult))
  {
  $array[$row->UserUID][] = array($row->FirstName,$row->LastName);
  }
 
foreach($array as $UserUID => $names)
    {
    echo "<p><b>$UserUID</b><a href=createChar.php?txt_acctID=".$UserUID."><img src= ikon.jpg border=0></a></p>";
    foreach($order as $data)
        {
        echo "<p>{$data[0]} :: {$data[1]} :: {$data[2]}</p>";
        }
    }

Open in new window

Commented:
This is a devilishly simple solution:

modify your query so that the result is ordered by TBL_ACCOUNT.ID (the category column)

modify your loop like so:


<?php
 
$sql = "SELECT TBL_ACCOUNT.UserUID, TBL_ACCOUNT.ID, TBL_ACCOUNT.UserLevel, 
		TBL_ACCOUNT.PCCount, TBL_ACCOUNT.Enter_ShardNum, TBL_ACCOUNT.LastPlaySlot, 
		TBL_ACCOUNT.LastLoginTime, TBL_PC.PCUID, TBL_PC.FirstName, TBL_PC.LastName, 
		TBL_PC.Gender
		FROM TBL_ACCOUNT INNER JOIN TBL_PC ON TBL_ACCOUNT.UserUID = TBL_PC.UserUID
		WHERE TBL_ACCOUNT.ID like '%red%' ORDER BY TBL_ACCOUNT.ID";
 
$qry = mysql_query($sql,$db_conn);
$row = mysql_fetch_assoc($qry);
 
$next_ID = '';
$i = 0;
 
do{
 
$this_ID = $row['ID'];
 
//output the category row if required
if($this_ID != $next_ID) {
$i = 0;
 
?>
	<table border="1" id="table1" width="75%" style="border-collapse: collapse">
		<tr>
			<td><font color="#000080"><b>TBL_ACCOUNT.UserUID</b></font></td>
 
			<td><font color="#000080"><b>TBL_ACCOUNT.ID</b></font></td>
			<td><font color="#000080"><b>TBL_ACCOUNT.UserLevel</b></font></td>
			<td><font color="#000080"><b>TBL_ACCOUNT.PCCount</b></font></td>
		</tr>
		<tr>
			<td><?php echo $row['UserUID'] ?></td>
			<td><?php echo $row['ID'] ?></td>
 
			<td><?php echo $row['UserLevel'] ?></td>
			<td><?php echo $row['PCCount'] ?></td>
		</tr>
	</table>
<?php
} else {
 
$i++;
 
echo $i.'. '.$row['FirstName'].' '.$row['FirstName'].'<br />';
?>
 
 
<?php
}
 
$next_ID = $this_ID;
 
} while($row = mysql_fetch_assoc($qry));
 
?>

Open in new window

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
the code you posted doesn't match the image you showed me.
however,
do something like this.
$qry = mysql_query("SELECT DISTINCT UserUID, ID, UserLevel, PCCount FROM TBL_ACCOUNT");
$rc = mysql_num_rows($qry);
if ($rc >= 1) {
  while ($row = mysql_fetch_array($qry)) {
     echo $row['UserUID']." ".$row['ID']." ".$row['UserLevel']." ".$row['PCCount']."<br />";
     $sub_qry = mysql_query("SELECT FirstName, LastName FROM TBL_ACCOUNT Where UserUID = ".$row['UserUID'].";");
      $sub_rc = mysql_num_rows($sub_qry);
      if ($sub_rc >= 1) {
         while ($sub_row = mysql_fetch_array($sub_qry)) {
            echo $sub_row['FirstName']." ".$sub_row['LastName']."<br />";
         }
      }
  }
 
}

Open in new window

Author

Commented:
ok, let me try.

Author

Commented:
wow, keithslater, that's it, thanks so much!!!
No problem, and whatever you do, never do nested queries unless it's the only option (it usually isn't).

Author

Commented:
keithslater:

hopefully u r still monitoring this.  code works great, just one thing, if i have 5 results from the qry, only four of them spit out, how do i make that last record print?
There's no reason why it shouldn't print them all. That while loop, loops through all the records in the recordset.

Author

Commented:
I see what's up.  the row count is counting all rows returned, so there maybe be 9 rows returned, but once grouped/ordered there are only 8 or 7 displayed.  To that is fine.  Sorry.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.