[SOLVED] combine two db tables with queries.

[seany123]

i know this is like one of the first things i should have learnt but im still very fuzzy on this..

 

 

i want to basically join these two tables with these queries...

 

 

<?php
$query = $db->execute("select `id`, `username`, `prize_money`, `time`  from `lottery` order by `time` desc limit 100");
while($lotterywinner = $query->fetchrow())

$query = $db->execute("select `id`, `username`, `level`, `money`, `last_active`, `banned`, `staff`, `rm`, `ncolor`, `ncolorid`, `city_id` from `players`");
while($member = $query->fetchrow())
?>

 

i know the queries migght not be coded correctly.. but if someone could just give me an example of how its done then i can do the rest.

 

thanks

 

Seany123

[kickstart]

Hi

 

Are they 2 seperate tables? First query doesn't have a from clause.

 

All the best

 

Keith

[seany123]

Hi

 

Are they 2 seperate tables? First query doesn't have a from clause.

 

All the best

 

Keith

 

yeah they are two different tables from the same database...

 

i pasted the wrong query... so its been edited now.

 

 

to give you an idea of what im trying to do....

 

 

the first query is a lottery tables... everyday 1 player wins the lottery and is added to that table....

 

 

(im having a html table to show all the winners....)

 

 

the second query is needed to get the winners other infomation

[kickstart]

Hi

 

Something like this:-

 

$query = $db->execute("select a.id, a.username, a.level, a.money, a.last_active, a.banned, a.staff`, a.rm, a.ncolor, a.ncolorid, a.city_id, b.id, b.username, b.prize_money, b.time

FROM `players` a

LEFT OUTER JOIN `lottery` b

ON a.username = b.username order by `time` desc");

 

That would bring you back one or more rows player with matching lottery info from the other table. However depending on your data it could bring the same user back multiple rows for one player, one for each matching record on the lottery table.

 

All the best

 

Keith

[Masna]

Try...

 

$query = $db->execute("select p.id, p.username, p.level, p.money, p.last_active, p.banned, p.staff, p.rm, p.ncolor, p.ncolorid, p.city_id, l.id, l.username, l.prize_money, l.time from `players` p, `lottery` l where p.id=l.id");
while($member = $query->fetchrow())

[seany123]

Try...

 

$query = $db->execute("select p.id, p.username, p.level, p.money, p.last_active, p.banned, p.staff, p.rm, p.ncolor, p.ncolorid, p.city_id, l.id, l.username, l.prize_money, l.time from `players` p, `lottery` l where p.id=l.id");
while($member = $query->fetchrow())

 

just tried that and got this error...

 

Fatal error: Call to a member function on a non-object on line 29

 

line 29:

 

<?php

while($member = $query->fetchrow())

?>

[kickstart]

Hi

 

Not familiar with the database query methods you are using (looks similar to that used by phpBB). However possible the SQL has failed and so $query isn't valid. Try the SQL on its own in phpmyadmin.

 

All the best

 

Keith

[seany123]

This is what i got from phpmyadmin.

 

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

 

ERROR: Unknown Punctuation String @ 13

STR: -&

SQL: $query = $db->execute("select p.id, p.username, p.level, p.money, p.last_active, p.banned, p.staff, p.rm, p.ncolor, p.ncolorid, p.city_id, l.id, l.username, l.prize_money, l.time from `players` p, `lottery` l where p.id=l.id")

SQL query:

 

$query = $db->execute("select p.id, p.username, p.level, p.money, p.last_active, p.banned, p.staff, p.rm, p.ncolor, p.ncolorid, p.city_id, l.id, l.username, l.prize_money, l.time from `players` p, `lottery` l where p.id=l.id")

 

MySQL said:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$query = $db->execute("select p.id, p.username, p.level, p.money

[kickstart]

Hi

 

Just the SQL in phpmyadmin. Rest is php code, not SQL

 

select p.id, p.username, p.level, p.money, p.last_active, p.banned, p.staff, p.rm, p.ncolor, p.ncolorid, p.city_id, l.id, l.username, l.prize_money, l.time from `players` p, `lottery` l where p.id=l.id

 

All the best

 

Keith

[seany123]

Hi

 

Just the SQL in phpmyadmin. Rest is php code, not SQL

 

select p.id, p.username, p.level, p.money, p.last_active, p.banned, p.staff, p.rm, p.ncolor, p.ncolorid, p.city_id, l.id, l.username, l.prize_money, l.time from `players` p, `lottery` l where p.id=l.id

 

All the best

 

Keith

 

okay sorry about that i rarely use phpmyadmin to actually do any sql work.

 

here's the error:

 

#1054 - Unknown column 'l.id' in 'field list'

 

 

well basically that means it cant find id in my lottery table... reason being there isnt an id value in my lottery table.

 

 

so i tried changeing it to this...

 

select p.id, p.username, p.level, p.money, p.last_active, p.banned, p.staff, p.rm, p.ncolor, p.ncolorid, p.city_id,  l.username, l.prize_money, l.time from `players` p, `lottery` l where p.username=l.username

 

thinking it would join using the username...

 

and got this error:

 

MySQL said:

 

#1267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

 

[seany123]

oops i see.... i changed collations to the same and....

 

 

 

SQL query:

SELECT p.id, p.username, p.level, p.money, p.last_active, p.banned, p.staff, p.rm, p.ncolor, p.ncolorid, p.city_id, l.username, l.prize_money, l.time

FROM  `players` p,  `lottery` l

WHERE p.username = l.username

LIMIT 0 , 30

 

[seany123]

yay i have got everything working

 

 

thankyou to everyone for all your help on this problem.

 

 

Seany123