justmelat
asked on
Using PHP, how do I loop through and display db results in particular way?
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,
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.
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,
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.
ASKER
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.
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.
ASKER
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>
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.
ASKER
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
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
ASKER
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>";
}
}
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:
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));
?>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the code you posted doesn't match the image you showed me.
however,
do something like this.
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 />";
}
}
}
}
ASKER
ok, let me try.
ASKER
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).
ASKER
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?
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.
ASKER
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.
Also I don't quite follow what you are trying to accomplish.