PHP Code:

$sql_text = "SELECT u.id, u.username, u.country, u.email, u.website, u.register_date, u.version, u.messenger, u.level_id, u.last_logged_in FROM ".$this->TABLES['users']." u, ".$this->TABLES['user_levels']." 1 WHERE 1.id = u.level_id ORDER BY u.level_id"; 


Ok, here I was supplied this code which gets information from a database. from the relivant table fields.

Lemme see if I understand this correctly.

IF table name has "u." infront of it, ".$this->TABLES['users']." u, This means that if u. is infront of the field name, it will get the field name out of the table "users" ??

Soo, ".$this->TABLES['user_levels']." 1 means that it will get the table names from the user levels table?

Which means I can do this, 1.order#, 1.username, 1.date FROM ".$this->TABLES['user_levels']."??????

So it acts asthough im getting information from two tables at the same time?

Resulting in,

PHP Code:

$sql_text = "SELECT u.id, u.username, u.country, u.email, u.website, u.register_date, u.version, u.messenger, u.level_id, u.last_logged_in FROM ".$this->TABLES['users']." u, 1.order#, 1.username, 1.date FROM ".$this->TABLES['user_levels']." 1 WHERE 1.id = u.level_id ORDER BY u.level_id"; 


This has me puzzled,
If anyone has any ideas, please post em
Thanks,
Lothop

In the most basic explanation.

When retrieving from more than 1 table "called a basic join" you can use the 'u' type thingymabobs as table identifiers,

So

SELECT u.id, u.username, u.country, u.email, u.website, u.register_date, u.version, u.messenger, u.level_id,
t.id, t.username (etc etc)
FROM
TABLE_ONE u, TABLE_TWO t, (etc etc)

Will get all from TABLE_ONE starting with u. and so on.

As for exchanging the u, t for a 1, 2 I have never tried it so I couldnt say if it works though it well could.

Nice to see someone else is taking advantages of MySql's advanced sql options.

Next week I expect you to be banging out left and right joins.

Ibbo

you can use the 'u' type thingymabobs

hehe... the technical term is Table Alias

the 'u' and 'l' mean, instead of using the full table name when referencing fields from the tables use this ('u') short name instead.

The basic syntax for this type of table join is this:

SELECT [field1], [field2]
FROM [table1], [table2]
WHERE [table1].[field1] = [table2].[field1]
    AND[where_condition]
ORDER BY [field]

Your particular query is actually not returning any data from the user_levels table because there is no "l.fieldname" in the select part of the query.
The user_levels table is being joined to the users table for sorting purposes only.

Thanks for your help guys, ibbo, thanks for explaining it a weebit more in english for me

Anacrusis, thanks for the structure,

SELECT [field1], [field2]
FROM [table1], [table2]
WHERE [table1].[field1] = [table2].[field1]
    AND[where_condition]
ORDER BY [field]

Time to get this puppy working
Lothop

Subsequently, how do I do two conditions, Eg.

PHP Code:

WHERE "u.username = hippy" AND "t.orderstatus = being shipped" 


Where the username is in the table "users" and the orderstatus being in the table "top_order"

Can it be done?

Yes

You can also match things like "where u.id=t.id and username="hippy" and t.orderstatus = "being shipped"
Just make sure you quote around what your regex-ing on and not the entire "t.orderstatus = being shipped"

Ibbo

Just make sure you quote around what your regex-ing on and not the entire "t.orderstatus = being shipped"

Great, so that what was wrong, thanks

Problem, this is my $sql_text.

PHP Code:

$sql_text = "SELECT u.username, u.email, u.street, u.country, u.region, u.city, u.firstname, u.lastname, u.phonenumber, u.id, u.cellnumber, t.date FROM ".$this->TABLES['users']." u, ".$this->TABLES['orderinfo']." t WHERE t.id = u.level_id ORDER BY u.id"; 


And this is my IF statement.
if ($data[$i]['date'] == '12') {
$str.='<img src="order2.gif">';
} else {
$str.='<img src="order.gif">';
}

For one entry. $data[$i]['date'] does = '12' but it shows order.gif.

So, im thinking its not getting the t.date out of the table.

Anyone know why?

I've found problem, it is only getting the information out of orderinfo table, for the first person...

How do i stop this?

I think its my where statement.


Also, how do I make it so it only shows the information that the username is say, henry?
LIMIT?

Because at the moment, It shows all members,

PHP Code:

WHERE t.username2 = '".$user_id."' 


I would think this would mean it would only show the information whois username is $user_id.

WHERE t.username2 = 'henry' and t.id = u.id and u.id = b.id is an example of linking all data together from seperate tables. As long as the chap is called Henry and all the id's in the tables match (HOPEFULLY CONTAINING DATA TO HENRY) then i see no problem.

"I would think this would mean it would only show the information whois username is $user_id."
Thats right, since your sql only selects the entry that has that user id then thats all it will return.

Ibbo
---
Practice makes perfect