[SOLVED] SELECT from multiple table help

[herghost]

Hi all, I have this:

 

<?php 

$sql = "SELECT * FROM user ORDER BY RAND() LIMIT 10";
$result = mysql_query($sql ,$con);
while($myrow = mysql_fetch_array($result))
{

echo "<table width ='90%'>";
echo "<tr>";
echo "<td>";
echo  $myrow['bandname'];
echo "</td>";
echo "<td>";
echo  "<a href='newprofile.php?userid=";
echo $myrow['userid'];
echo "'>View</a>";
echo "</td>";


echo "</table>";
}
?>

 

Which simply displays some band names and links to a profile by gathering data from a table called user in a mysql database.

 

What I want to do is change this to only show the bands that are the same genre, however this data is stored in a table called 'banddata'

 

I can get the genre its self from a session, the tables themselves have a field called userid which is the same in both tables, so the userid=1 data is for the same band.

 

Thanks for any help or advice

 

 

[redarrow]

 

$sql = "SELECT * FROM books_table,accounts_table where accounts_table.id='books_table.id' ORDER BY RAND() LIMIT 10";

[GingerRobot]

 

$sql = "SELECT * FROM books_table,accounts_table where accounts_table.id='books_table.id' ORDER BY RAND() LIMIT 10";

 

Im sorry redarrow, but after 7.5k posts, are you really under the illusion that the above was at all helpful? It would seem you posted a query you found god-knows where which relates to a completely different database. While i might guess you're attempting to illustrate a join, do you really think the op is going to understand that, especially given the lack of explanation. It does nothing but confuse.

 

To the op: I'm not quite sure i follow what you're trying to do. Can you post a bit more of your table structure? What's the desired output? Also, you may find this tutorial quite helpful.

[redarrow]

Sorry look at this then.

 

example, say i had a database called people.

 

In people, i got two tables.

 

user and homes

 

user

----

user_id

name

surname

tel

notes

 

 

homes

-------

homes_id

user_id

home_type

 

 

Now let say, i wanted to get random homes, with the users name, together from the both database tables.

 

<?php session_start();

$sql="SELECT * FROM user,homes WHERE user.user_id='{$_SESSION['user_id']}' AND homes.user_id='{$_SESSION['user_id']}' ORDER BY RAND() LIMIT 10
";
?>

This will work if the session is the current users id.

[radi8]

Or, relating to the bands table defined initially using the inner join syntax:

 

$sql="
   SELECT a.*
   FROM user as a inner join banddata b on(a.userid = b.userid)
   WHERE b.genre = '".$_SESSION['genre']."'";
   ORDER BY RAND() LIMIT 10"; 

[herghost]

Hi all,

 

Thanks to both of you, and just this once I did get what red arrow was aiming it! Guess I must be learning after all!

 

This works for me:

 

<?php 

$sql = "SELECT * FROM user,banddata WHERE genre = '$genre' ORDER BY RAND() LIMIT 10";
$result = mysql_query($sql ,$con);
while($myrow = mysql_fetch_array($result))
{

echo "<table width ='90%'>";
echo "<tr>";
echo "<td>";
echo  $myrow['bandname'];
echo "</td>";
echo "<td>";
echo  "<a href='newprofile.php?userid=";
echo $myrow['userid'];
echo "'>View</a>";
echo "</td>";


echo "</table>";
}
?>