Issue using two php arrays in a html link

[AdriftUniform]

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.

[blocblue]

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.

[AdriftUniform]

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?

[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.

[blocblue]

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.

[AdriftUniform]

That worked great thanks guys!