Help with a QUERY

[Canman2005]

Hi all

 

Really wondering if anyone can help me as im totally stumpted on this.

 

Basically I have a "members" table, which looks pretty much like

 

MEMBERS

ID    NAME

1      David

2      Bob

3      Sarah

 

this is a list of all my site members.

 

I then have another table called "scores", which basically stores an ID number from the members table, an example looks like

 

 

SCORES

ID    MEMBER_ID

1      1

2      2

3      3

4      1

5      2

 

What I want to do is run a QUERY which basically displays a name of a member on screen, such as "David", but displays the member after the last member that appears in the "scores" table, but then loop it.

 

If you look in my "scores" table, you will see row 5 has the member value of 2 which relates to "Bob" in the "members table", so what the QUERY would show would be "Sarah" as she appears after "Bob".

 

If the last row in the "score" table has contained "Sarah" who is ID 3 of the "members" table, then the next result to show would be "David" as he is the the next one listed in the "members" table (when you loop it), then "Bob", then "Sarah" and so on.

 

The scores table after time would look something like

 

SCORES

ID    MEMBER_ID

1      1

2      2

3      3

4      1

5      2

6      3

7      1

8      2

9      3

 

Does that make sense? Can anyone help or give any advice?

 

Been trying to crack this for the last 5 hours with no luck.

 

Thankd in advance

 

Dave

[budimir]

Try to search on google for "MySQL Join"!!!

 

You will get the answer to your question!

[Canman2005]

Hi Budimi

 

Thanks, I know about the JOIN, I have been trying with that, but not getting the result I need.

 

But its more about getting one result at a time and also getting it to loop.

 

Does my post below make much sense to you?

[Canman2005]

What I tried is

 

SELECT m.id, m.member_id FROM members m LEFT JOIN scores s ON s.member_id = m.id ORDER BY s.id, m.id ASC LIMIT 1

 

But it doesnt seem to loop, im sure i've done something wrong

[budimir]

Try this:

 

SELECT * FROM members LEFT JOIN scores ON s.member_id = m.id ORDER BY m.id ASC LIMIT 1

 

Try to echo it to see the result, or copy/paste to phpmyadmin to see the result.

[Canman2005]

Nope, that didnt seem to work budimir

 

Does my post make sense?

[budimir]

Well, the problem is that I don't know the names of the db fields, so it's hard to make the query correct.

 

The sintaks you wrote is OK, but you need to check the names of db fields and what are you joining where.

[Minase]

with a query you can return just 1 result he want to parse everything from table

 

i made an example for you

 

$select = mysql_query("SELECT * FROM `scores`") or die(mysql_error());
while($r = mysql_fetch_array($select)){
$name = mysql_query("SELECT NAME FROM `members` WHERE ID = '".$r['MEMBER_ID']."'");
echo "something"
// use $name where you want to display the name from current row
// use $r['column_name'] 

}

hope  it helps

 

//edited the post forget something ( i did put the variable directly ,give me a hammer in the head )

[budimir]

Yep, that's right, but as I understand his query didn't work at all!!

 

If you want all the data from the table then just remove LIMIT 1 from the edn oh the query!!!

[Canman2005]

Hi

 

Nope, sorry that didn't work.

 

Let me explain some more.

 

Firsly here is an export on my tables

 

CREATE TABLE `members` (
 `id` int(100) NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY  (`id`)
);

INSERT INTO `members` (`id`, `name`) VALUES 
(1, 'David'),
(2, 'Bob'),
(3, 'Sarah');

CREATE TABLE `scores` (
 `id` int(100) NOT NULL auto_increment,
 `member_id` int(10) NOT NULL,
 PRIMARY KEY  (`id`)
);

INSERT INTO `scores` (`id`, `member_id`) VALUES 
(1, '1'),
(2, '2'),
(3, '3'),
(4, '1'),
(5, '2');

 

So what my query should do is return a member at a time.

 

If you look in the "scores" table, you will see in the "member_id" field, it stores the ID number of the each members from the "members" table.

 

So if you look at the last row of "scores" table, you will see the value for the field "member_id" value is set to 2, this relates to member ID 2 of the "members" table, which is "Bob".

 

So what my query should do, is return "Sarah" as she is listed after "Bob", if "Bob" was the last entry in the "scores" table under "member_id", then it would then return "David" as he is next in the sequence, due to the LOOP.

 

Does that make more sense?

 

Thanks

[budimir]

OK, so the query for joining the names to it's ID in a dieffernt table is

 

"SELECT * FROM members LEFT JOIN scores ON members.id = scores.member_id ORDER BY name ASC";

 

 

I think that is what you are asking for!! Correct???

 

[Minase]

i hope i am not wrong (this situation made me confuse)

you need to change your actual query cause it will return just first value selected after that you can do a select from members with returning next row not the actual one.

 

//edit

 

he want to return last MemberID in scores and to make it ID + 1 (after that select the name from members table)

[Canman2005]

Nope, sorry.

 

The last entry in my "scores" table is

 

(5, '2');

 

which means that is member number 2, who is

 

(2, 'Bob'),

 

so what the query should do, is display

 

(3, 'Sarah');

 

because she is next in the members database.

 

If the last entry in my "scores" table was

 

(6, '3');

 

then that would be display

 

(1, 'David'),

 

because of a LOOP happening to the members database (ie: Sarah is the last member in the "members" table, so it should loop back to the first row, being David)

 

Does that make more sense?

[Minase]

"he want to return last MemberID in scores and to make it ID + 1 (after that select the name from members table)"

 

 

that is exactly what i mean

1)you want to return last MemberID from scores

2) if returned ID is 2 (Bob) the query to return 3 (Sarah)

that is exactly what i did mean

[Canman2005]

So how can I do a ID + 1?

 

Also, when it gets to the last row of the members table, how can I ask it to looop around to first row of the members table?

 

Thanks very much

[Canman2005]

So how can I do a ID + 1?

 

Also, when it gets to the last row of the members table, how can I ask it to looop around to first row of the members table?

 

Thanks very much

[Minase]

$select = mysql_query("SELECT * FROM scores ORDER BY ID DESC LIMIT 1");
$r = mysql_fetch_array($select);
$id = $r['member_id'] + 1;
if (! mysql_query("SELECT name FROM members WHERE ID = '" . $id . "' ") ) {
$return = mysql_query("SELECT name FROM members ORDER BY ID ASC LIMIT 1");
} else {
$return = mysql_query("SELECT name FROM members WHERE ID = '" . $id . "' ");
}

 

a little messy and experimental code (theoretically it should work,i did not test it)

[Canman2005]

Thank Minase, that's almost it

 

The only thing I can't figure out now, is how to get it to loop the members rows once the last once has been inserted into "scores".

 

So if the members data was

 

ID     NAME

1      David

2      Bob

3      Sarah

 

then when ID 3 has been added to the "scores" table, it should then loop back round to "David"

 

Does that make sense?

 

Thanks superstars

[Minase]

the above code is doing that

[Canman2005]

It doesn't seem to be for me, I have the following

 

$select = mysql_query("SELECT * FROM scores ORDER BY ID DESC LIMIT 1");
$r = mysql_fetch_array($select);
$id = $r['member_id'] + 1;
if(!mysql_query("SELECT id, name FROM members WHERE id = '".$id."' "))
{
$return = "SELECT id, name FROM members ORDER BY id ASC LIMIT 1";
}
else
{
$return = "SELECT id, name FROM members WHERE id = '".$id."' ";
}

$show = @mysql_query($return,$connection) or die(mysql_error());
while($r = mysql_fetch_array($show))
{
print $r['name']
}

 

But when it gets to "member" ID number 3 (which is the last one) it seems to not display anything, which makes me think it isn't looping.

 

Any ideas?

[Minase]

it is not working cause you did it wrong you did refine $r

 

this code is experimental also,and it may not work 100%

$select = mysql_query("SELECT * FROM scores ORDER BY ID DESC LIMIT 1");
$r = mysql_fetch_array($select);
$id = $r['member_id'] + 1;
if(!mysql_query("SELECT id, name FROM members WHERE id = '".$id."' "))
{
$return = "SELECT id, name FROM members ORDER BY id ASC LIMIT 1";
$r = mysql_fetch_array(mysql_query($return));
}
else
{
$return = "SELECT id, name FROM members WHERE id = '".$id."' ";
$r = mysql_fetch_array(mysql_query($return));
}

print $r['name']

[Canman2005]

Thanks very much

 

It just seems when you reach the last row of the members table, it returns an empty ID

 

I think that might be because we get the last ID of the members table and add 1 and that ID wouldnt exist

[Minase]

post your current code

[Canman2005]

It's exactly like your code.

 

I added

 

if(isset($_GET['start']))
{
$id = 1;
}

 

just under

 

$id = $r['member_id'] + 1;

 

which seems to work

[Minase]

that code wont work good

cause everytime the id will be 1

$select = mysql_query("SELECT * FROM scores ORDER BY ID DESC LIMIT 1");
$r = mysql_fetch_array($select);
$id = $r['member_id'] + 1;
if(!mysql_query("SELECT id, name FROM members WHERE id = '".$id."' "))
{
$id = 1;
$return = "SELECT id, name FROM members WHERE id = '".$id."' ";
$r = mysql_fetch_array(mysql_query($return));
}
else
{
$return = "SELECT id, name FROM members WHERE id = '".$id."' ";
$r = mysql_fetch_array(mysql_query($return));
}

print $r['name']

 

try that code should work just fine