problem for sub page looping

bbmak

Hi:
I am building a lyric page, but i encounter some problems
I am able to make the loop for all the artist name.
http://www.bt-zone.biz/bbmak/allartist.php

but I want to make a page that lists all artist names, and then when I click the artist name, a list of the song titles come up. I want to know how to I make that?

Here is my table structure

CREATE TABLE artists (
artist_id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
PRIMARY KEY(artist_id)
);

CREATE TABLE lyrics (
lyric_id INT NOT NULL AUTO_INCREMENT,
artist_id INT NOT NULL,
song_title VARCHAR(30) NOT NULL,
lyric TEXT NOT NULL,
PRIMARY KEY(lyric_id),
INDEX(artist_id)
);

here is the show.php code, i koow something wrong here, but i don't know where

I can not display the song_title for each artists in show.php

<?
$artist_id = $_GET['artist_id'];
$query = "select * From lyric where id = '$artist_id'";

$result = mysql_query("select song_title from lyric where id ='artist_id'");
while($r=mysql_fetch_array($result))
{

$song_title=$row["song_title"];

//display the row
echo "$song_title";
}
?>

Kudose

<?php
  $artist = $_GET['artist_id'];
  $sql = mysql_query("SELECT * FROM artist WHERE artist_id='".$artist."'");
  $fetch = mysql_fetch_array($sql);

  //display the artist name
  echo 'Artist: '.$fetch['first_name'].' '.$fetch['last_name'].'<br><br>';

  $sql = mysql_query("SELECT song_title FROM lyric WHERE artist_id='".$artist."'");
  //show all of the artist's songs
  while($fetch = mysql_fetch_array($sql))
    echo $fetch['song_title'].'<br>';
?>

Kudose

As a sidenote...

In your script, you were accessing the row via $row, but your while loop set it to $r.

bbmak

Actually do you have tutorial of this part because i am not really familiar with it. I just start to learn program a subpage yesterday

//display the artist name
echo 'Artist: '.$fetch['first_name'].' '.$fetch['last_name'].'<br><br>';

$sql = mysql_query("SELECT song_title FROM lyric WHERE artist_id='".$artist."'");
//show all of the artist's songs
while($fetch = mysql_fetch_array($sql))
echo $fetch['song_title'].'<br>';
?>

Kudose

Nope, sorry. But I can explain it a little.

In your code, you are assigning $row['song_title'] to $song_title before you output it.

I just skipped that step and displayed the data from the array.

$song_title = $fetch['song_title'];
echo $song_title;

//does the same thing, but requires less typing
echo $fetch['song_title'];

I hope I explained the right part...the part that you were new to. If not, I can try to explain the other parts as well.

bbmak

and also I am not really understand the last part here?
WHERE artist_id='".$artist."'");

and what is the . infront and behind artist mean because i never see it

Kudose

Also, I used concatenation. Basically a shortcut to allow you to mix variables into a string, or attach a string to the same string.

$string1 = "Hello";
$string2 = "World";

//slow way;
echo $string1;
echo " ";
echo $string2;

//outputs Hello World

//concat way

echo $string1." ".$string2;

//outputs Hello World

bbmak

WHERE artist_id='".$artist."'");

do you also have the tutorial of this part? because i never see the . . around $artist before.

because if i am doing that i would just put WHERE artist_id='$artist'");

Kudose

It works either way.

Sometimes, I think within doube quotes, the variable will either show up blank or show a literal of the variable instead of the variable's data. That sounds kinda confusing...even to me and I wrote it. 😉

That's why I just use concat almost all the time, so I don't haev to battle with remembering where it will flaw and where it wont.

http://www.google.com/search?hl=en&lr=&q=concatenation+PHP&btnG=Search

bbmak

yes you are right. I just did that it shows up all the variables
but when i use {}. it will be okay in the variable