Jump to content

Retreive data from two tables??


munster

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

}

?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

}

?>

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

[!--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

 

Link to comment
Share on other sites

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.