Get 2 database tables assign to one output

[Big-Gaz]

Hi,

I have 2 database tables:
XXXX_user
XXXX_userpoints

From XXXX_user I need:
id, username, gender, gender, floor(period_diff(extract(year_month from NOW()),extract(year_month from birth_date))/12) as age, allow_viewonline, city, country, state_province

From XXXX_userpoints I need
id, points

I am using PHP for coding.

Now I need to get data from both of them and assign them the same output
For example

$TopList = SELECT GET BOTH database tables
Then:
foreach ($TopList as $row) {

It is possible to get both at the same time under $TopList ?
If not can I get them as 2 separate ones then assign them both as $row if so how?

[Big-Gaz]

Would this do it
ut. = user table
upt. = user points table



$TopList = $osDB->getAll ('SELECT ut.id as id, ut.username as username, ut.gender as gender, ut.allow_viewonline as allow_viewonline, ut.city as city, ut.country as country, ut.state_province as state_province, ut.floor(period_diff(extract(year_month from NOW()),extract(year_month from birth_date))/12) as age, upt.points as points FROM ! as ut ! as upt WHERE ut.status = ? ORDER BY points DESC LIMIT 10', array(USER_TABLE, USER_POINTS_TABLE, Active));

[Old Pedant]

No, you are not *JOINING* the tables.

Might I ask why you mess with the ! and substitution if you *know* the table names going in?

Also, I would *strongly* suggest creating the SQL as a simple string, first, so that you can echo it as a debug tool.

Also, there is no need to do the "ut.id as id" as that is what will happen, by default, anyway. Only if you need to *change* the field name do you do that.

Also, functions such a FLOOR do not "belong" to any table. So you clearly can't do things such as ut.floor( ).

So:

$sql = "'SELECT ut.id, ut.username, ut.gender, ut.allow_viewonline, ut.city, ut.country, ut.state_province, "
. " FLOOR(period_diff(extract(year_month from NOW()),extract(year_month from ut.birth_date))/12) as age, upt.points"
. " FROM `user table` as ut `user points table` as upt "
. " WHERE ut.id = utp.id "
. " AND ut.status = $Active "
. " ORDER BY utp.points DESC LIMIT 10;";

echo "DEBUG: " . $sql . "<hr>";
...

The code in red there is the JOIN that you were missing.

Now, I do *not* think that's going to get you what you want. But try it out, first. You might want to try it in a DB tool, not in PHP, first.

I *suspect* that what you are really after is this:

$sql = "'SELECT ut.id, ut.username, ut.gender, ut.allow_viewonline, ut.city, ut.country, ut.state_province, "
. " FLOOR(period_diff(extract(year_month from NOW()),extract(year_month from ut.birth_date))/12) as age, "
. " SUM(upt.points) AS totalPoints "
. " FROM `user table` as ut `user points table` as upt "
. " WHERE ut.id = utp.id "
. " AND ut.status = $Active "
. " GROUP BY ut.id, ut.username, ut.gender, ut.allow_viewonline, ut.city, ut.country, ut.state_province, "
. " FLOOR(period_diff(extract(year_month from NOW()),extract(year_month from ut.birth_date))/12) "
. " ORDER BY SUM(utp.points) DESC LIMIT 10;";

echo "DEBUG: " . $sql . "<hr>";
...

[Big-Gaz]

Hi,

Thanks for the reply :D

Might I ask why you mess with the ! and substitution if you *know* the table names going in?
I do it this way as the script I am using, does it that way.

Below code got it working there was a missing , between as ut and XXXX_userpoints
It get the results I want in phpmyadmin

Code
SELECT ut.id, ut.username, ut.gender, ut.allow_viewonline, ut.city, ut.country, ut.state_province, FLOOR(period_diff(extract(year_month from NOW()),extract(year_month from ut.birth_date))/12) as age, upt.points FROM XXXX_user as ut, XXXX_userpoints as upt WHERE ut.id = upt.id AND ut.status='active' ORDER BY upt.points DESC LIMIT 10;