Retreive data from two tables??

[munster]

Hi Guys,

 

Ive got this peice of code to pic out and display the number of the member that submitted

the picture:

 

<?php

$conn=@mysql_connect('*****', '******', '******');

$rs=mysql_select_db('*******', $conn);

 

$sql="select submitter, code from pictures where small_width=221 and submitter<>0";

$rs=mysql_query($sql, $conn);

while ( $row=mysql_fetch_array($rs) )

{

echo("<img src=pictures/tiny".$row["code"].".jpg><br>");

echo("submitter: ".$row["submitter"]."<br>");

 

}

?>

 

What i want though is to display the members nickname which is held in another table but have no idea

how to do this.

 

So the submitter value i retrieved above is the id number for the nickname i am trying to retrieve in the other table i just don`t now how to get it.

 

 

What i`m actually trying to do is display all pictures with who submitted them underneath.

 

Any help would be much apprciated.

 

Many thanks

 

Munster

[munster]

Hi guys,

 

Can anyone tell me if its at least possible or point me in the direction

of some help on the subject as i can`t find anything that makes sense.

 

Thanks

 

Munster

[fenway]

Sounds like you want a multi-table select; something like:

 

select p.submitter, s.nickname, p.code from pictures as p, submitters as s where p.submitter = s.submitter p.small_width=221 and p.submitter<>0

 

This will find all submitter records (which I assume hold a nickname column) that match the submitter UID (which you've called simply submitter, which is slightly unclear to me).

 

Hope that makes sense.

[munster]

Hey Fenway,

 

Thanks for the reply ive been playing around with it but just get errors.

The script was written by someone else for me some time ago and i have very limited (guess you no that!)

knowledge of php.

 

Basically as i said i`m trying to display all pictures uploaded by a certain member.

I have two tables one called pictures holding:

ID | SUBMITTER | CODE .... and a few more such as picture height and width etc.

submitter contains a number which relates to his nickname in the table below, code is the name of the

relavant picture.

 

Second table called members holding:

ID | NICKNAME ... and again various other imformation.

 

The submitter value taken from the first table relates directly to the ID number in this table (and nickname is the one i want) ... i have no idea why the nickname isn`t simply displayed as a string in the first table.

 

I hope this makes it a bit clearer maybe you could tell me where i`m going wrong.

So the end result is im just trying to match the submitters number up with his nickname and display the picture but i`m just so confused!!

 

Many thanks

 

Munster

 

 

<?php

$conn=@mysql_connect('localhost', '*****', '******');

$rs=mysql_select_db('*******', $conn);

 

$sql="select p.submitter, s.nickname, p.code from pictures as p, submitters as s where p.submitter=s.submitter and p.submitter>0";

$rs=mysql_query($sql, $conn);

while ( $row=mysql_fetch_array($rs) )

{

echo("<img src=pictures/tiny".$row["p.code"].".jpg><br>");

echo("submitter: ".$row["s.nickname"]."<br>");

 

}

?>

[fenway]

The reason you got errors was because I guessed the column names, and I was wrong: the FK "submitter" in pictures maps to "ID" in the members table (not submiters, as I erroneously guessed).

 

Try the following:

 

select p.submitter, s.nickname, p.code from pictures as p, members as m where p.submitter = m.id

 

This will return all of them -- you can obviously add to the WHERE clause as desired.

 

Hope that helps.

[munster]

Hey Fenway,

 

I`ve still got problems the original line gave an error so i took out the s.nickname which now

supplies the first 30 pictures fine but still displays the number of the submitter underneath instead

of the actual nickname of the submitter that i wanted.

 

I tried a number of differant arrangments but all either caused errors or no differance.

 

Many thanks for your continued help.

 

Munster

 

 

<?php

$conn=@mysql_connect('localhost', '*****', '******');

$rs=mysql_select_db('******', $conn);

 

$sql="select p.submitter, p.code from pictures as p, members as m where p.submitter = m.id limit 30";

$rs=mysql_query($sql, $conn);

while ( $row=mysql_fetch_array($rs) )

{

echo("<img src=pictures/tiny".$row["code"].".jpg><br>");

echo("submitter: ".$row["submitter"]."<br>");

 

}

?>

[LazyJones]

the s.nickname is just what you wanted, so don't take it out. What was the error then? (look for my signature for error hunting).

[munster]

Hey,

 

Lazyjones and Fenway got it now.

 

Many thanks

 

Both

 

 

[munster]

Hey guys,

 

Ive got another problem ive spent 3 days other and a book and still cant get

 

I`m now trying to get the top ten members nicknames who have one the most battles...

Obviously i know how to get the nickname thanks to your help earlier in this thread and the formula

below ive worked out will get the top 10 pictures that have one the most battles :

 

SELECT winner, COUNT(*) as ccount FROM challenges WHERE winner IS NOT NULL group by winner order by ccount desc limit 10

 

But this simply lists the top ten picture id`s where as i want the submitters nickname of these pictures.

The id from the above formula is the id in the "pictures" table which also holds the submitter id from earlier in this thread so i think it should be possible but ive just drawn blanks

 

I hope that makes sense if anyone can help out there it would be much appreciated.

 

Regards

 

Munster

[fenway]

I don't follow at all... you refer to members and pictures, and I don't see either in your query. But if you want to get the group-wise maximum (e.g. of a COUNT), then you're going to need a subselect or a temporary table -- you can't do this with JOINs.

[munster]

[!--quoteo(post=336617:date=Jan 14 2006, 09:44 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 14 2006, 09:44 PM) 336617[/snapback][/div][div class=\'quotemain\'][!--quotec--]

I don't follow at all... you refer to members and pictures, and I don't see either in your query. But if you want to get the group-wise maximum (e.g. of a COUNT), then you're going to need a subselect or a temporary table -- you can't do this with JOINs.

 

Hi Fenway,

 

What i have is a table named "pictures" with colums "id","submitter"(as a number)

 

another table called "members" with colums "id" (which relates exactly to the submitter colum above),

"nickname"

 

and another called "challenges" with colums "id", "champion_id","champion_votes","challenger_id","challenger_votes","winner"(holds the id number of the picture that one)

 

what i`m trying to do is display the top ten members nicknames that have one the most battles.

this select:

 

SELECT winner, COUNT(*) as ccount FROM challenges WHERE winner IS NOT NULL group by winner order by ccount desc limit 10

 

selects the top ten pictures that have one the most battles but i now need to find out who submitted them pictures and display there nicknames in order.

 

Now the "id" in the pictures table holds the related value to the result the select above produces and the "submitter" value in the pictures table then tells you who submitted that picture then its just a case of

incorporating the original line you helped me with to convert the "submitter" value to his nickname.

 

Is that at all possible with one select or do i have to do it some other way.

Excuse my ignorance i`m searching and reading books like crazy but getting no where.

 

All the best

 

munster