While in a while statement

[Ell20]

Hey,

 

Im trying to get a result such as this:

 

Group Name 1: Link 1, Link 2....(for how many links there are)

Group Name 2: Link 1, Link 2.... (for how many links there are)

etc....

 

So far I have this:

	  <?php
	  $result3 = mysql_query("SELECT * FROM pictures WHERE user_id = '$userid'") or die(mysql_error());
	  $found3 = false;
	   
	  while ($i3 = mysql_fetch_array($result3)) {;
	  $group = $i3['group_name'];
	  
	  echo "<b>".$i3['group_name']."</b>: ";

	  $result2 = mysql_query("SELECT * FROM pictures WHERE user_id = '$userid' AND group_name = '$group'") or die(mysql_error());
      $found2 = false;
	  
	  $r = 1;
      while ($i2 = mysql_fetch_array($result2)) {
      $found2 = true;
	  ?>
	  <a href="../uploaded_files/<?=$i2['picture_name']?>" target="_blank">Link <?=$r?></a>
	  <?php 
	  $r = $r + 1;
	  }} ?>

 

Which is close however its printing out the group name and links too many times.

 

Can anyone help me finsihed this? Appreciate the help

 

Thanks

 

 

[trq]

This would be much better (more efficient) achived in one query.

 

<?php

$sql = "SELECT group_name, picture_name FROM pictures WHERE user_id = '$userid' GROUP BY group_name";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    $group = '';
    while ($row = mysql_fetch_array($result)) {
      if ($group != $row['group_name']) {
        $group = $row['group_name'];
        echo "<b>$group</b>: ";
      }
      echo "<a href=\"../uploaded_files/{$row['picture_name']}\" target=\"_blank\">{$row['picture_name']}</a>";
    }
  }
}

?>

[Ell20]

Thanks for the help.

 

However at the moment its only printing the first picture of each group out whereas there can be upto 10 pictures per group?

 

Also is it possible to adapt the code to display Link 1, Link 2 incrementing rather than the actual picture name?

 

Thanks alot

[trq]

Are you sure there are more than one result per user? Should work, and this will fix it to numbered links.

 

<?php

$sql = "SELECT group_name, picture_name FROM pictures WHERE user_id = '$userid' GROUP BY group_name";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    $group = '';
    while ($row = mysql_fetch_array($result)) {
      if ($group != $row['group_name']) {
        $group = $row['group_name'];
        $i = 1;
        echo "<b>$group</b>: ";
      }
      echo "<a href=\"../uploaded_files/{$row['picture_name']}\" target=\"_blank\">Link $i</a>";
      $i++;
    }
  }
}

?>

[Ell20]

Excellent for the links, thanks.

Its not per user its per group.

 

So each user can have many groups, which we have working, then within each group there can be many pictures?

 

Thanks

[trq]

Hard to tell but it sounds to me linke you don't need the WHERE clause at all.

 

<?php $sql = "SELECT group_name, picture_name FROM pictures GROUP BY group_name"; ?>

[Ell20]

Yeah thats required.

 

Basically the system is as follows;

While statement to displays all users profiles

Within that while is this statement which displays the groups following by all the pictures in that group for each user

 

Cheers

[trq]

maybe you should look into the GROUP BY clause and try and come up with a statement yourself. for whatever reason, I'm having a hard time following what your after.

[Ell20]

I have had a go but still learning really so struggling.

 

Basically what you have done is right but it also needs to loop through the pictures and display the link for each of the pictures in the group.

 

So say Group 1 has 5 pictures it should print out; Group 1: Link 1, Link 2, Link 3, Link 4, Link 5

 

Really appreciate all the help.

[trq]

So say Group 1 has 5 pictures it should print out; Group 1: Link 1, Link 2, Link 3, Link 4, Link 5

 

Which is exactly what my first (and second) piece of code should do.

 

Can we see your table stucture? Maybe its not how I picture it.

[Ell20]

O right how annoying for some reason its only displaying Link 1 and not carrying on.

 

Pictures Table:

picture_id

user_id

group_name

picture_name

 

Thanks

[Ell20]

Just noticed something else as well, not sure if its related, but the link dosent actually work.

 

Its only pointing to /uploaded_files, not /uploaded_files/picturename

 

Thanks

[trq]

Can I see some sample output from the following code?

 

<?php

 $sql = "SELECT * FROM pictures ORDER BY user_id, group_name";
 if ($result = mysql_query($sql)) {
   if (mysql_num_rows($result)) {
     while ($row = mysql_fetch_assoc($result)) {
       foreach ($row as $k => $v) {
         echo "$k = $v<br />";
       }
       echo "<br /><br />";
     }
   }
 }

?>

 

And can you post the code your using?

[Ell20]

Output:

 

picture_id = 13

user_id = 15

group_name = Sexy

picture_name = 1207168305-4.jpg

 

picture_id = 12

user_id = 15

group_name = Sexy

picture_name = 1207168305-3.jpg

 

picture_id = 11

user_id = 15

group_name = Sexy

picture_name = 1207168305-2.jpg

 

picture_id = 14

user_id = 15

group_name = Sexy

picture_name = 1207168305-1.jpg

 

picture_id = 15

user_id = 15

group_name = Test

picture_name = 1207222691-2.jpg

 

picture_id = 16

user_id = 15

group_name = Test

picture_name = 1207222691-1.jpg

 

Code:

$result = mysql_query("SELECT * FROM models INNER JOIN Users ON Users.user_id = models.user_id LIMIT $from, $max_results") or die(mysql_error());
$found = false;
while ($i = mysql_fetch_array( $result )) {
$found = true;
$userid = $i['user_id']
?>
	<table width="80%" border="0" align="center" cellpadding="0" cellspacing="2" class="tiny">
        <tr>
          <td width="28%" rowspan="9" align="center">
	  <?php if($i['pic'] == "") { echo "No Profile Picture Yet"; } elseif ($i['pic'] != "" && $i['validate'] == 1) { ?>
	  <a href="../profileimages/main/<?=$i['pic']?>" target="_blank"><img border="0" src="../profileimages/thumbs/<?=$i['pic']?>"></a><?php } elseif ($i['pic'] != "" && $i['validate'] == 0) { echo "Profile Picture Pending"; } elseif ($i['pic'] != "" && $i['validate'] == 2) { echo "Profile Picture Declined"; } ?></td>
          <td width="25%"><div align="left"><strong>First Name/Nickname:</strong></div></td>
          <td width="47%"><?=$i['username'];?></td>
        </tr>
        <tr>
          <td><div align="left"><strong>Sex:</strong></div></td>
          <td><?=$i['sex'];?></td>
        </tr>
        <tr>
          <td><div align="left"><strong>Sexual Preference:</strong></div></td>
          <td><?=$i['sex_pref'];?></td>
        </tr>
        <tr>
          <td><div align="left"><strong>Member Type:</strong></div></td>
          <td><?=$i['account_type'];?></td>
        </tr>
        <tr>
          <td><div align="left"><strong>DOB:</strong></div></td>
          <td><?=$i['dob'];?></td>
        </tr>
        <tr>
          <td><div align="left"><strong>Social Status:</strong></div></td>
          <td><?=$i['sex_status'];?></td>
        </tr>
        <tr>
          <td><div align="left"><strong>Referrals:</strong></div></td>
          <td><?=$i['referals'];?></td>
        </tr>
        <tr>
          <td><div align="left"><strong>Friends:</strong></div></td>
          <td><?=$i['friends'];?></td>
        </tr>
        <tr>
          <td><div align="left"><strong>Rating:</strong></div></td>
          <td><?php if ($i['rating'] > 0) { echo $i['sex_status']; } else { echo "0"; }?></td>
        </tr>

        <tr>
          <td><strong>Submitted Pictures:</strong></td>
          <td colspan="2">
          CODE FOR PICTURES GOES HERE
</td>
        </tr>

[trq]

What is that code? Post the part your having trouble with or are you using exactly what I posted?

[Ell20]

This is the code which I am using, the code you posted:

 

$sql = "SELECT group_name, picture_name FROM pictures WHERE user_id = '$userid' GROUP BY group_name";
if ($result2 = mysql_query($sql)) {
  if (mysql_num_rows($result2)) {
    $group = '';
    while ($row = mysql_fetch_array($result2)) {
      if ($group != $row['group_name']) {
        $group = $row['group_name'];
        $i = 1;
	echo '<br>';
        echo "<b>$group</b>: ";
      }
      echo "<a href=\"../uploaded_files/{$row['picture_name']}\" target=\"_blank\">Link $i</a>";
      $i++;
    }
  }
}

[trq]

Well I'm sorry, without being at a server and testing, I can't see why thats not working for you.

[Ell20]

I took the GROUP BY off the original SQL statement and it has now printed: Link 1, Link 2 etc just the links are all the same at the moment!

 

Thanks for your help!

[Ell20]

DONE IT!

 

Thank you so much for your help!!

 

The code in case you were wondering:

 

<?php
$sql = "SELECT group_name, picture_name FROM pictures WHERE user_id = '$userid'";
if ($result2 = mysql_query($sql)) {
  if (mysql_num_rows($result2)) {
    $group = '';
    while ($row = mysql_fetch_array($result2)) {
      if ($group != $row['group_name']) {
        $group = $row['group_name'];
        $i = 1;
	echo '<br>';
        echo "<b>$group</b>: ";
      }
  $picture = $row['picture_name'];
      echo "<a href=\"../uploaded_files/{$picture}\" target=\"_blank\">Link $i</a>"." ";
      $i++;
    }
  }
}
?>