Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

double while loop not working with sub query

Posted on 2011-05-08
11
Medium Priority
?
519 Views
Last Modified: 2013-12-12
its not looping the second query while loop based on ids its putting them under the same record.

the record out put should be i.e. :  
             
                catagory date
                catagory notes
                              catagory comment 1
                              catagory comment 2
                catagory date
                catagory notes
                              catagory comment 3
                              catagory comment 4

here is the code:

$result = mysql_query("SELECT a.Id, a.Type, 
a.Dates, 
a.Uidfk as Uidfk, 
b.Id as Did, 
b.comment as Comment, 
b.dates as Day, 
b.sfk as Sfk ,
c.sfk as sfk1, 
d.Memo as Memo,
d.Date as Dates1,
e.ProfileName, 
substr(i.Path, 4) as Path
FROM 
asstatusupdate as a   
left join asstatusdata as b 
on a.id = b.sfk 
left join asmanystatusupdate as c
on b.sfk = c.sfk
left join ascomments as d
on d.id = c.cfk
right join ASWebInfo as e
on e.Uidfk = a.uidfk
right join ASManyAlbums as f
on f.UserId=a.uidfk
right join ASAlbums as g
on f.AlbumId=g.Id
right join ASTitle as h
on g.Id=h.AlbumId
right join ASData as i
on h.Id=i.TitleId
where a.uidfk in (select friendid from asfriends where uidfk0='1') and i.DefaultProfilePic='Y' order by dates desc;
");

$category_id = '';

while($row = mysql_fetch_array($result)) {
	if ($row['Id'] != $category_id) {
		$category_id = $row['Id'];
	
	// grab table variables	
	    $Type = $row['Type'];
		$Dates = $row['Dates'];
		$Comment = $row['Comment'];
		$Name = $row['Name'];
		$Path = $row['Path'];
		$FriendId = $row['UIdFk'];
		$SFK = $row['sfk'];
		
		echo '<table align="center" width="40%" border="3" cellpadding="0" cellspacing="0">'; 
		echo '<tr>';
		echo '<td align="center">'; echo "$Name"; echo "</br>"; echo  '<a href="source/source.php?FriendId=' ."$FriendId". '"><img src="' ."$Path". '" height="120" width="120" align="middle" border="3" /></a>'; echo '</td>';
		echo '<td align="center"> '; echo "$Dates" ; echo "</br>"; echo "</br>"; echo "$Type"; //echo "</td>";
	    echo "</br>"; 
		 echo "$Comment"; echo '</td>'; // echo "</br>";
		echo '<td>'; echo "$category_id" ; echo '</td>';
		echo '<td>'; echo "$SFK"; echo '</td>';
		echo '</tr>';
	    echo '</table>';

		
	
	
} // end of if
$comments= mysql_query("SELECT 
c.sfk as sfk1,
c.UIdFk0,
d.Memo as Memo,
d.Date as Dates1,
e.ProfileName,
substr(i.Path, 4) as Path
FROM 
asmanystatusupdate as c
left join ascomments as d
on d.id = c.cfk
right join ASWebInfo as e
on e.Uidfk = c.uidfk0
right join ASManyAlbums as f
on f.UserId=c.uidfk0
right join ASAlbums as g
on f.AlbumId=g.Id
right join ASTitle as h
on g.Id=h.AlbumId
right join ASData as i
on h.Id=i.TitleId
where c.uidfk0 in (select friendid from asfriends where uidfk0='1') and i.DefaultProfilePic='Y' and c.sfk='$SFK' order by dates desc;
");
while($user_row = mysql_fetch_array($comments)){
	       // test the query
			if($row['sfk'] == $user_row['sfk']) {
			
			$Dates1 = $user_row['Dates1'];
			$Memo = $user_row['Memo'];
			$Name1 = $user_row['Name'];
			$Path1 = $user_row['Path'];
			$FriendId1 = $user_row['UIdFk0'];
			$SFK1 = $user_row['sfk1'];
			
			// display table
			
			
			echo '<table align="center" width="40%" border="3" cellpadding="0" cellspacing="0">'; 
		echo '<tr>';
		echo '<td align="center">'; echo "$Name1"; echo "</br>"; echo  '<a href="source/source.php?FriendId=' ."$FriendId1". '"><img src="' ."$Path1". '" height="120" width="120" align="middle" border="3" /></a>'; echo '</td>';
		echo '<td align="center"> '; echo "$Dates1" ; echo "</br>"; echo "</br>"; //echo "</td>";
	    echo "</br>"; 
		 echo "$Memo"; echo '</td>'; // echo "</br>";
		echo '<td>'; echo "$catagory_id" ; echo '</td>'; 
		echo '<td>'; echo "$SFK1"; echo '</td>';
		echo '</tr>';
	    echo '</table>';

				
				
			 } // end of if
	
	} // end of while loop
}// end of main loop

Open in new window


0
Comment
Question by:only1wizard
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35715750
How do you even know the queries worked at all?  The mysql_query() command returns a value, and this script tries to use the value without knowing what kind of data type was returned!  See:
http://php.net/manual/en/function.mysql-query.php
0
 

Author Comment

by:only1wizard
ID: 35715815
i know that these querys work because i put in debug variables to ensure that each while loop is running. The problem is with the second query im trying to pull the results based on the first query forein key.

here is the code again i notice i did not remove a test variable from the second query.
my ultimate goal is to have the second query pull results based on first query forein key and have them displayed as shown i.e.

category date 1
        comment
category date 2
        comment

here is the code:
$result = mysql_query("SELECT a.Id, a.Type, 
a.Dates, 
a.Uidfk as Uidfk, 
b.Id as Did, 
b.comment as Comment, 
b.dates as Day, 
b.sfk as Sfk ,
c.sfk as sfk1, 
d.Memo as Memo,
d.Date as Dates1,
e.ProfileName, 
substr(i.Path, 4) as Path
FROM 
asstatusupdate as a   
left join asstatusdata as b 
on a.id = b.sfk 
left join asmanystatusupdate as c
on b.sfk = c.sfk
left join ascomments as d
on d.id = c.cfk
right join ASWebInfo as e
on e.Uidfk = a.uidfk
right join ASManyAlbums as f
on f.UserId=a.uidfk
right join ASAlbums as g
on f.AlbumId=g.Id
right join ASTitle as h
on g.Id=h.AlbumId
right join ASData as i
on h.Id=i.TitleId
where a.uidfk in (select friendid from asfriends where uidfk0='1') and i.DefaultProfilePic='Y' order by dates desc;
");

$category_id = '';

while($row = mysql_fetch_array($result)) {
	if ($row['Id'] != $category_id) {
		$category_id = $row['Id'];
	
	// grab table variables	
	    $Type = $row['Type'];
		$Dates = $row['Dates'];
		$Comment = $row['Comment'];
		$Name = $row['Name'];
		$Path = $row['Path'];
		$FriendId = $row['UIdFk'];
		$SFK = $row['sfk'];
		
		echo '<table align="center" width="40%" border="3" cellpadding="0" cellspacing="0">'; 
		echo '<tr>';
		echo '<td align="center">'; echo "$Name"; echo "</br>"; echo  '<a href="source/source.php?FriendId=' ."$FriendId". '"><img src="' ."$Path". '" height="120" width="120" align="middle" border="3" /></a>'; echo '</td>';
		echo '<td align="center"> '; echo "$Dates" ; echo "</br>"; echo "</br>"; echo "$Type"; //echo "</td>";
	    echo "</br>"; 
		 echo "$Comment"; echo '</td>'; // echo "</br>";
		echo '<td>'; echo "$category_id" ; echo '</td>';
		echo '<td>'; echo "$SFK"; echo '</td>';
		echo '</tr>';
	    echo '</table>';

		
	
	
} // end of if
$comments= mysql_query("SELECT 
c.sfk as sfk1,
c.UIdFk0,
d.Memo as Memo,
d.Date as Dates1,
e.ProfileName,
substr(i.Path, 4) as Path
FROM 
asmanystatusupdate as c
left join ascomments as d
on d.id = c.cfk
right join ASWebInfo as e
on e.Uidfk = c.uidfk0
right join ASManyAlbums as f
on f.UserId=c.uidfk0
right join ASAlbums as g
on f.AlbumId=g.Id
right join ASTitle as h
on g.Id=h.AlbumId
right join ASData as i
on h.Id=i.TitleId
where c.uidfk0 in (select friendid from asfriends where uidfk0='1') and i.DefaultProfilePic='Y'  order by dates desc;
");
while($user_row = mysql_fetch_array($comments)){
	       // test the query
			if($row['sfk'] == $user_row['sfk']) {
			
			$Dates1 = $user_row['Dates1'];
			$Memo = $user_row['Memo'];
			$Name1 = $user_row['Name'];
			$Path1 = $user_row['Path'];
			$FriendId1 = $user_row['UIdFk0'];
			$SFK1 = $user_row['sfk1'];
			
			// display table
			
			
			echo '<table align="center" width="40%" border="3" cellpadding="0" cellspacing="0">'; 
		echo '<tr>';
		echo '<td align="center">'; echo "$Name1"; echo "</br>"; echo  '<a href="source/source.php?FriendId=' ."$FriendId1". '"><img src="' ."$Path1". '" height="120" width="120" align="middle" border="3" /></a>'; echo '</td>';
		echo '<td align="center"> '; echo "$Dates1" ; echo "</br>"; echo "</br>"; //echo "</td>";
	    echo "</br>"; 
		 echo "$Memo"; echo '</td>'; // echo "</br>";
		echo '<td>'; echo "$catagory_id" ; echo '</td>'; 
		echo '<td>'; echo "$SFK1"; echo '</td>';
		echo '</tr>';
	    echo '</table>';

				
				
			 } // end of if
	
	} // end of while loop
}// end of main loop

Open in new window

0
 

Author Comment

by:only1wizard
ID: 35716896
ok - i've went through the code and made the changes per the web link that you have referenced for me in the previous post.

attached is my display of errors with the comments which is the second query which is looping to many times and not displaying on a per record = record. as i have in the code for the second query.

thanks in advance for your help,

Theo Werntz II
source-elements.pdf
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 82

Expert Comment

by:hielo
ID: 35716900
On line 46 I think you meant:
$FriendId = $row['Uidfk'];/*Only the "U" is uppercase in line 3 (your first query)*/

Also, the correct BREAK tag is "<BR/>", NOT "</BR>". Tags always start with a letter, so what you actually meant was "<BR/>" (the slash is after the "R", not before the "B".

Lastly, "...my ultimate goal is to have the second query pull results based on first query forein key...", I was expecting your INNER/second query to "LINK" some id of your first query to select records for your inner query - something like(referring to line 85 of your original post):
...where (c.UIdFk0={$row['Uidfk']}) AND c.uidfk0 in ...

but not sure if those are the fields that are "related". Hope you get the idea.
0
 
LVL 9

Expert Comment

by:Erdinç Güngör Çorbacı
ID: 35716949

try changing following line (in your first post)

where c.uidfk0 in (select friendid from asfriends where uidfk0='1') and i.DefaultProfilePic='Y' and c.sfk='$SFK' order by dates desc;

with 

where c.uidfk0 in (select friendid from asfriends where uidfk0='1') and i.DefaultProfilePic='Y' and c.sfk='".$SFK."' order by dates desc;


or better redesign your sql structure because this logic is not much effective and consumes much resources

Open in new window

0
 

Author Comment

by:only1wizard
ID: 35716955
thanks for your help, i've changed the uidfk to upper case where needed as it should be per table element.

fixed the breaks.

the row that is relevant is c.sfk={$row['sfk']} on line 85. can you show a relevant way im getting errors.
0
 
LVL 9

Expert Comment

by:Erdinç Güngör Çorbacı
ID: 35717012
i saw the relevancy have you changed
c.sfk='.$SFK.'
to
c.sfk='".$SFK."'
sometimes solutions are that easy :)

btw try assigning query strings to a variable and then use that in mysql_query and if there is an error dump that variable to see if query string has an error
0
 
LVL 82

Accepted Solution

by:
hielo earned 1500 total points
ID: 35717315
try:
//c.sfk={$row['sfk']}
$result = mysql_query("SELECT a.Id, a.Type, 
a.Dates, 
a.Uidfk as Uidfk, 
b.Id as Did, 
b.comment as Comment, 
b.dates as Day, 
b.sfk as Sfk ,
c.sfk as sfk1, 
d.Memo as Memo,
d.Date as Dates1,
e.ProfileName, 
substr(i.Path, 4) as Path
FROM 
asstatusupdate as a   
left join asstatusdata as b 
on a.id = b.sfk 
left join asmanystatusupdate as c
on b.sfk = c.sfk
left join ascomments as d
on d.id = c.cfk
right join ASWebInfo as e
on e.Uidfk = a.uidfk
right join ASManyAlbums as f
on f.UserId=a.uidfk
right join ASAlbums as g
on f.AlbumId=g.Id
right join ASTitle as h
on g.Id=h.AlbumId
right join ASData as i
on h.Id=i.TitleId
where a.uidfk in (select friendid from asfriends where uidfk0='1') and i.DefaultProfilePic='Y' order by dates desc;
");

$category_id = '';

echo '<table align="center" width="40%" border="3" cellpadding="0" cellspacing="0">'; 
while($row = mysql_fetch_array($result)) {
	if ($row['Id'] != $category_id) {
		$category_id = $row['Id'];
	
		// grab table variables	
	    	$Type = $row['Type'];
		$Dates = $row['Dates'];
		$Comment = $row['Comment'];
		$Name = $row['Name'];
		$Path = $row['Path'];
		$FriendId = $row['Uidfk'];
		$SFK = $row['sfk'];
		
		echo '<tr>';
		echo '	<td align="center">', $Name, '<br><a href="source/source.php?FriendId=',$FriendId,'"><img src="',$Path,'" height="120" width="120" align="middle" border="3" /></a></td>';
		echo '	<td align="center"> ',$Dates,'<br><br>',$Type,$Comment,'</td>'; 
		echo '	<td>',$category_id,'</td>';
		echo '	<td>',$SFK,'</td>';
		echo '</tr>';
	} // end of if

	$comments= mysql_query("SELECT 
          c.sfk as sfk1,
          c.UIdFk0,
          d.Memo as Memo,
          d.Date as Dates1,
          e.ProfileName,
          substr(i.Path, 4) as Path
     FROM 
     asmanystatusupdate as c
     left join ascomments as d     on d.id = c.cfk
     right join ASWebInfo as e     on e.Uidfk = c.uidfk0
     right join ASManyAlbums as f     on f.UserId=c.uidfk0
     right join ASAlbums as g     on f.AlbumId=g.Id
     right join ASTitle as h     on g.Id=h.AlbumId
     right join ASData as i     on h.Id=i.TitleId
     where c.sfk={$row['sfk']} AND c.uidfk0 in (select friendid from asfriends where uidfk0='1') and i.DefaultProfilePic='Y'  order by dates desc;
     ");

	while($user_row = mysql_fetch_array($comments)){
		// test the query
		//if($row['sfk'] == $user_row['sfk']) 
		{
			
			$Dates1 = $user_row['Dates1'];
			$Memo = $user_row['Memo'];
			$Name1 = $user_row['Name'];
			$Path1 = $user_row['Path'];
			$FriendId1 = $user_row['UIdFk0'];
			$SFK1 = $user_row['sfk1'];
			
			//row and cell for "outer" while		
			echo '<tr>';
			echo '<td colspan="4" style="padding-left:1em;">';
				// display table
				echo '<table align="center" width="40%" border="3" cellpadding="0" cellspacing="0">'; 
				echo '<tr>';
				echo '	<td align="center">',$Name1,'<br><a href="source/source.php?FriendId=',$FriendId1,'"><img src="',$Path1,'" height="120" width="120" align="middle" border="3" /></a></td>';
				echo '	<td align="center"> ',$Dates1,'<br><br><br>',$Memo,'</td>'
				echo '	<td>',$catagory_id,'</td>'; 
				echo '	<td>',$SFK1,'</td>';
				echo '</tr>';
				echo '</table>';
			//end row and cell for outer while
			echo '</td>';
			echo '</tr>';
		} // end of if
	} // end of while loop
}// end of main loop
echo '</table>';

Open in new window

0
 

Author Closing Comment

by:only1wizard
ID: 35721331
thanks for your help
0
 

Author Comment

by:only1wizard
ID: 35721372
one thing that i left out is that it is pulling twice the amount of records.
0
 
LVL 82

Expert Comment

by:hielo
ID: 35721622
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question