[SOLVED] imposible query

[pietbez]

i need some help with a mysql query

i know this should be quite simple but its to much for my little brain.

 

one table holds song information like:

 

table name=songs:

song_id,  song_title,  artist,  year.

 

one table holds user favourites like:

 

table name=favourites:

user_name, song_id

 

so by making the user_name available to php, i want to be able to list that user's favourite songs plus song title artis and year.

 

so if i give the php a variable of the user name: jojo, the output should be

 

the wall / pink floyd / 1978

satisfaction / the stones / 1974

hey jude / the beatles / 1976

 

please can some one help me out here

[Prismatic]

<?php
  $query = mysql_query("SELECT * FROM favorites WHERE user_name = 'username'");
  while($row = mysql_fetch_row($query))
  {
      $songInfo = mysql_query("SELECT * FROM songs WHERE song_id = '{$row['song_id']}'");
      $songInfo = mysql_fetch_row($songInfo);
      
      echo $songInfo["song_title"] ." / ". $songInfo["artist"] ." / ". $songInfo["year"] ."\n";
  }
  
?>

[abdfahim]

SELECT favourites.user_name, songs.song_title, songs.artist, songs.year FROM songs,favourites WHERE songs.song_id=favourites.song_id AND favourites.user_name='jojo'

 

if you have multiple user to check, for example jojo,koko,lolo,momo,nono etc,

 

SELECT favourites.user_name, songs.song_title, songs.artist, songs.year FROM songs,favourites WHERE songs.song_id=favourites.song_id AND favourites.user_name IN ('jojo','koko','lolo','momo','nono')

 

[btherl]

And the join option..

 

SELECT *
FROM favorites
JOIN songs USING (song_id)
WHERE user_name = '$user_name'

[pietbez]

<?php
  $query = mysql_query("SELECT * FROM favorites WHERE user_name = 'username'");
  while($row = mysql_fetch_row($query))
  {
      $songInfo = mysql_query("SELECT * FROM songs WHERE song_id = '{$row['song_id']}'");
      $songInfo = mysql_fetch_row($songInfo);
      
      echo $songInfo["song_title"] ." / ". $songInfo["artist"] ." / ". $songInfo["year"] ."\n";
  }
  
?>

 

 

this is the output im getting

 

/ / / / / / / / / / / /

 

how can that be

[pietbez]

$query = mysql_query("SELECT favourite.user_name, songs.song_title, songs.artist, songs.year FROM songs,favourite WHERE songs.song_id=favourite.song_id AND favourite.user_name='jojo'

");
  while($row = mysql_fetch_row($query))
  {
  $Song_title.=$row["Song_title"].'|';
  $artist.=$row["artis"].'|';
  $year.=$row["year"].'|';
}
echo "&Song_title=".$Song_title."&artist=".$artist."&year=".$year;
?>

and this gives me

 

&Song_title=|||||&artist=|||||&year=|||||

[sniperscope]

here you go

 

	<table>
        	<tr>
            	<th>SONG ID</th>
                <th>Username</th>
            </tr>
<?php
$username = $_POST['username'];
mysql_select_db($yourdatabase, $yourconnectionstring);
  	$query = "SELECT * FROM favorites WHERE user_name = 'username'";
$r = mysql_query($query, $yourconnectionstring) or die(mysql_error());
$row_r = mysql_fetch_assoc($r);
$totalrow = mysql_num_rows($r);

do{
	$song_info = $row_r['song_id'];

	$query_for_song = "SELECT * FROM songs WHERE song_id = '$song_info'";
	$r_song = mysql_query($query_for_song, $yourconnectionstring) or die(mysql_error());
	$row_song = mysql_fetch_assoc($r_song);

	?>
            <tr>
            	<td><?php echo $r_song['song_id']; ?></td>
                <td><?php echo $r_song['username']; ?></td>
            </tr>
<?php
    } while ($row_r = mysql_fetch_array($r));
?>
    </table>
<?php
mysql_free_result($r);
mysql_free_result($r_song);
?>

 

[pietbez]

and here is what i get this time.

 

SONG ID Username

 

as if my fields are empty. but they are not.

 

i think im starting to go mental! i know it must be a small thing

[gevans]

what code did you use, and can you show your implementation?

[btherl]

mysql_fetch_row()  fetches a result row as an integer indexed array.  will give you the column names.

[pietbez]

OK, to sum up.

 

i will show various examples of code that people have suggested. but unfortunatly all with similar results.

 

No1

<?php

include("../inc/db.inc.php"); 


  $query = mysql_query("SELECT * FROM favourite WHERE user_name = 'jojo'");
  while($row = mysql_fetch_row($query))
  {
      $songInfo = mysql_query("SELECT * FROM songs WHERE song_id = '{$row['song_id']}'");
      $songInfo = mysql_fetch_row($songInfo);
      
      echo $songInfo["song_title"] ." / ". $songInfo["artist"] ." / ". $songInfo["year"] ."\n";
  }
  
?>

 

and this is the output i get:

 

/ / / / / / / / / /

 

 

 

No2.

<?php

include("../inc/db.inc.php"); 

$query = mysql_query("SELECT favourite.user_name, songs.song_title, songs.artist, songs.year FROM songs,favourite WHERE songs.song_id=favourite.song_id AND favourite.user_name='jojo'

");
  while($row = mysql_fetch_row($query))
  {
  $song_title.=$row["song_title"].'|';
  $artist.=$row["artis"].'|';
  $year.=$row["year"].'|';
}
echo "&song_title=".$song_title."&artist=".$artist."&year=".$year;
?>

 

and this is the output i get:

 

&song_title=|||||&artist=|||||&year=|||||

 

 

No3.

<?php

include("../inc/db.inc.php"); 

$query = mysql_query("SELECT *
FROM favourite
JOIN songs USING (song_id)
WHERE user_name = '$user_name'");
  while($row = mysql_fetch_row($query))
  {
  $song_title.=$row["song_title"].'|';
  $artist.=$row["artist"].'|';
  $year.=$row["year"].'|';
}
echo "&song_title=".$id."&artist=".$name."&year=".$votes;
?>

 

and this is the output i get:

 

&song_title=&artist=&year=

 

 

 

 

any ideas?

[sasa]

mysql_fetch_row()  fetches a result row as an integer indexed array.  will give you the column names.

[Psycho]

For the love of all that is good and wholesome in the world - DO NOT USE LOOPING QUERIES!!! It is horribly inefficient and uses tons of system resources. The whole point of a "relational" database is that the records are related between tables and you can simply do a JOIN.

 

btherl gave you the perfect , single query to get the data you want (no loops!).

 

As btherl tried to point out (but messed up the link) mysql_fetch_row() returns records as an enumerated array (i.e. $row[0], $row[1], etc.) You need to use mysql_fetch_assoc();

 

Try this (not tested)

$query = "SELECT *
          FROM favorites
          JOIN songs USING (song_id)
          WHERE user_name = '$user_name'";

$result = mysql_query($query) or die(mysql_error());

echo "<table border=\"1\">\n";
echo "<tr><th>Title</th><th>Artist</th><th>Year</th></tr>\n";

while($record = mysql_fetch_assoc($result))
{
    echo "</tr>\n";
    echo "<th>{$record['song_title']}</th>\n";
    echo "<th>{$record['artist']}</th>\n";
    echo "<th>{$record['year']}</th>\n";
    echo "</tr>\n";
}

echo "</table>";

[btherl]

mysql_fetch_row()  fetches a result row as an integer indexed array.  will give you the column names.

 

Oops, what I meant to say was:

 

mysql_fetch_array() will give you the column names.

 

That'll teach me to preview before posting

 

Anyway, just replace mysql_fetch_row() with mysql_fetch_array() and you'll be set.

 

[pietbez]

wow! finaly! success!

 

i have a populated table. thanks to everyone who helped.

 

but now i have one more problem.

 

i dont need the output in table form.

 

i am sending this data to a flash file and the flash needs the data in an aray. this format to be precise.

 

&song_title=song1|song2|song3|&artist=artist1|artist2|artist3|&year=2001|2002|2003|

 

i know i am a pain in thr arse!

 

 

[Psycho]

This forum is to "help" people with problems and advance their knowledge, not to do it all for you. The code provided would be simple to modify. Did you even try to modify it for your needs?

 

You state that you need the data in an array, but your example shows the data in a delimited string. Which is it?

 

Assuming the second:

<?php

$query = "SELECT *
          FROM favorites
          JOIN songs USING (song_id)
          WHERE user_name = '$user_name'";

$result = mysql_query($query) or die(mysql_error());

while($record = mysql_fetch_assoc($result))
{
    $titles[]  = $record['song_title'];
    $artists[] = $record['artist'];
    $years[]   = $record['year'];
}

$output  = '&song_title=' . implode('|', $titles) . '|';
$output .= '&artist=' . implode('|', $artists) . '|';
$output .= '&year=' . implode('|', $years) . '|';

?>

[gevans]

This forum is to "help" people with problems and advance their knowledge, not to do it all for you. The code provided would be simple to modify. Did you even try to modify it for your needs?

 

You state that you need the data in an array, but your example shows the data in a delimited string. Which is it?

 

Assuming the second:

<?php

$query = "SELECT *
          FROM favorites
          JOIN songs USING (song_id)
          WHERE user_name = '$user_name'";

$result = mysql_query($query) or die(mysql_error());

while($record = mysql_fetch_assoc($result))
{
    $titles[]  = $record['song_title'];
    $artists[] = $record['artist'];
    $years[]   = $record['year'];
}

$output  = '&song_title=' . implode('|', $titles) . '|';
$output .= '&artist=' . implode('|', $artists) . '|';
$output .= '&year=' . implode('|', $years) . '|';

?>

 

Personally I wouldn't use the wildcard in a JOIN query, usually with a join both tables are going to have something of some relevance to one another in it (otherwise why bother). I'd recommend pulling out the fields that are required.

[pietbez]

This forum is to "help" people with problems and advance their knowledge, not to do it all for you. The code provided would be simple to modify. Did you even try to modify it for your needs?

 

You state that you need the data in an array, but your example shows the data in a delimited string. Which is it?

 

Assuming the second:

 

 

im sorry if you feel my post does not belong here, but i am keen to learn php. all my background is flash actionscript.

and yes i did try to modify it for my needs. you have to remember, what looks obvious to an expert can be a mountain for a noob

[Psycho]

Personally I wouldn't use the wildcard in a JOIN query, usually with a join both tables are going to have something of some relevance to one another in it (otherwise why bother). I'd recommend pulling out the fields that are required.

 

Agreed. I was being lazy and just used the query provided by someone in a previous post.

 

im sorry if you feel my post does not belong here, but i am keen to learn php. all my background is flash actionscript.

and yes i did try to modify it for my needs. you have to remember, what looks obvious to an expert can be a mountain for a noob

 

It's not that it doesn't belong here. But it is much more appreciated when someone shows they made the effort. Especially when a solution is provided and then the OP changes the parameters to what they really wanted. If you had posted the attempt you made at modifying the code it would have shown that you were taking some initiative. In any event, I hope you have what you need now.

[pietbez]

yes, thanks for all the help.

 

my php and flash is now getting along like a house on fire (with a few modifications from my side .)