Hi, I am trying to create a group of html links that are dependant on two arrays. The data of the arrays is populated from a mysql database, here is my code:

<?php
include_once 'title.php';
include_once 'checkuser.php';

if (isset($_GET['view'])) $view = sanitizeString($_GET['view']);
else $view = $user;

$sql = "SELECT id FROM user WHERE user='$view'";
$result = mysql_query($sql) or die("Invalid query: " . mysql_error());
$id = mysql_result($result, 0);

$sql = "SELECT name from album WHERE user='$id'";
$query = mysql_query($sql) or die("Invalid query: " . mysql_error());
//$name = mysql_result($query, 0);

$sql = "SELECT id from album WHERE user='$id'";
$result = mysql_query($sql) or die("Invalid query: " . mysql_error());
while($id=mysql_fetch_assoc($result))
{
    while($name=mysql_fetch_assoc($query))
    {
    print "<br />";
    print "<br />";
    print '<a href="displayalbum.php?id=' . $id['id'] . '">';
    print $name['name'];
    print '</a>';
    print "<br />";
    }
}

?>

The current result is all names are printed as a link, but they all have the same id, I have tried other arrangements of the while statements but only get similar results such as:

while($id=mysql_fetch_assoc($result) && $name=mysql_fetch_assoc($query))
{
    print "<br />";
    print "<br />";
    print '<a href="displayalbum.php?id=' . $id['id'] . '">';
    print $name['name'];
    print '</a>';
    print "<br />";
}

This method totally ignored the first part of the while statement and only uses $name

I have also tried:

while($id=mysql_fetch_assoc($result))
{
    print "<br />";
    print "<br />";
    print '<a href="displayalbum.php?id=' . $id['id'] . '">';
    while($name=mysql_fetch_assoc($query))
    {
    print $name['name'];
    }
    print '</a>';
    print "<br />";
}

?>

This provides the opposite result to my original code, it prints all links with different id's but all have the same name.

Hope someone can help me,
Thanks.

Based on the queries you have posted, why not try getting the album id and name in the same query result?

$sql = sprintf("SELECT `album`.`id`, `album`.`name` FROM `album` INNER JOIN `user` ON `user`.`id` = `album`.`user` WHERE `user`.`user` = '%s', $view);

Then you need to retrieve only one result set and can iterate over it like so:

$result = mysql_query($sql) or die("Invalid query: " . mysql_error());

while($album = mysql_fetch_assoc($result)) {
    echo "<a href='displayalbum.php?id={$album['id']}' title='View album {$album['name']}'>{$album['name']}</a>";
}

R.

sorry you have lost me a bit there, I understand your explanation but am a little confused on the code, I will try and write it as I understand it so you can give feedback.

query:

$sql = "SELECT id, name FROM album WHERE user = '$id');

loop:

$result = mysql_query($sql) or die("Invalid query: " . mysql_error());
 
while($album = mysql_fetch_assoc($result)) 
{
    echo "<a href="displayalbum.php?id="{$album['id']}>{$album['name']}</a>";
}

would that also work?

Member Avatar
diafol
while($album = mysql_fetch_assoc($result)) 
{
    echo "<a href=\"displayalbum.php?id={$album['id']}\">{$album['name']}</a>";
}

Should work. You can't have double quotes within double quotes unless you escape them with a backslash.

Yes, that would work, however you would have to adapt your code to find your user by id, rather than username as you have been doing so before.

The query I posted simply joined the album and user tables on the user id field. In my example, I missed the closing double quote:

$sql = sprintf("SELECT `album`.`id`, `album`.`name` FROM `album` INNER JOIN `user` ON `user`.`id` = `album`.`user` WHERE `user`.`user` = '%s'", $view);

The function sprintf basically allows for binding parameters into a string. In this case the %s is replaced with the value of $view.

R.