Jump to content

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/141181-solved-imposible-query/
Share on other sites

<?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";
  }
  
?>

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')

 

<?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

$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=|||||

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);
?>

 

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?

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>";

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! ;D

 

 

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) . '|';

?>

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.

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 :(

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.