need mysql help

[thenewprogrammer]

trying to make profile page which displays information from 3 differnt table using full join for the current user thats logged in which is in the variable $user.

need to select table 'users' to get their info and than connect users to the table 'pets' for pet information and the petid number, than connect that to table called 'pettable' which has oringal pet image and other things. tryed with full join but dont really get how the syntac goes

currently have

SELECT * FROM users FULL JOIN pets ON user_name='$user'
FULL JOIN pettable ON pet_id=pet_num

which fails when i test it.

 

[jtwhite]

Can you explain what you're wanting to do in a different way?

 

[thenewprogrammer]

Can you explain what you're wanting to do in a different way?

Making a profile for the user whos logged in for a virtual pet site. i have the log in already and the user set to $user.

i need all information from 3 differnt tables for the profile of the current user. One table has the user information called "users"(contains gender,age,location,email..etc). Second table has the pet information with the pet id and the user who owns that pet and this table is called "pets"(contains information about petid,pet level,attack,defense,hitpoints). The third tableis called "pettable" and has the oringal pet information + images of the pet(contains the oringal pet stats and image and color of the pets which i want called by pet_num).

to sum it all up i need these 3 tables all joined together so i can grab all information out of it using variable $user set as the restriction so it knows which row to grab which i have set already.

 

[misson]

Could you post the table structures (CREATE TABLE statements)? That's fairly necessary info.

MySQL doesn't support the "FULL" keyword in the JOIN clause. You can use a UNION of left & right joins, but it might not be necessary.

Using a single query to fetch user and pet info seems less efficient and more resource intensive than separate queries for user and pet info, as the server will have to join the `users` table to the others and repeat the user info when transmitting the rows.

 

[thenewprogrammer]

how do i get the create table structure if i did it in phpmyadmin?

 

[misson]

You can run arbitrary SQL queries in phpMyAdmin by selecting the "SQL" tab, which is between the "Structure" and "Search" tabs in most sections. Select the database and run "SHOW CREATE TABLE tablename;" for each table.

 

[thenewprogrammer]

these are the 3 tables i want to join and need to be able to echo out all the information at differnt times.

 $sql = 'SELECT * FROM `users` WHERE 1 LIMIT 0, 30 '; 
 
$sql = 'SELECT * FROM `pets` WHERE 1 LIMIT 0, 30 ';
 
$sql = 'SELECT * FROM `pettable` WHERE 1 LIMIT 0, 30 ';

 

[misson]

That's not the table structure. Run "SHOW CREATE TABLE tablename;" for each table.

 

[thenewprogrammer]

k here we go. i know some of the data types are wrong. but these are the tables i need joined.

users table

CREATE TABLE `users` (
 `user_id` bigint(100) NOT NULL auto_increment,
 `user_name` varchar(65) NOT NULL,
`user_password` varchar(65) NOT NULL,
 `user_email` varchar(65) NOT NULL,
 `user_gender` varchar(65) NOT NULL,
 `user_birthday` bigint(100) NOT NULL,
 `user_gold` bigint(255) NOT NULL,
 `user_lumber` bigint(255) NOT NULL,
`user_join` varchar(65) NOT NULL,
 `user_lastseen` varchar(65) NOT NULL,
 `user_access` varchar(65) NOT NULL,
PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

pets table

CREATE TABLE `pets` (
 `id` bigint(100) NOT NULL auto_increment,
 `user_id` varchar(65) NOT NULL,
`pet_id` varchar(65) NOT NULL,
 `species_id` varchar(65) NOT NULL,
 `pet_gender` varchar(65) NOT NULL,
`pet_color` varchar(65) NOT NULL,
 `pet_atk` varchar(65) NOT NULL,
 `pet_def` varchar(65) NOT NULL,
 `pet_armor` varchar(65) NOT NULL,
 `pet_hp` varchar(65) NOT NULL,
 `pet_int` varchar(65) NOT NULL,
 `pet_pet_id` varchar(65) NOT NULL,
 `pet_level` varchar(65) NOT NULL,
 `pet_energy` varchar(65) NOT NULL,
`pet_born_date` varchar(65) NOT NULL,
 `pet_name` varchar(65) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=latin1

pettable

CREATE TABLE `pettable` (
 `pet_num` bigint(100) NOT NULL auto_increment,
 `petname` varchar(65) NOT NULL,
 `petcolor` varchar(65) NOT NULL,
 `petspecies` varchar(65) NOT NULL,
`petatk` varchar(65) NOT NULL,
 `petdef` varchar(65) NOT NULL,
 `pethitpoints` varchar(65) NOT NULL,
 `petspeed` varchar(65) NOT NULL,
`petlvl` varchar(65) NOT NULL,
 `image` varchar(65) NOT NULL,
 PRIMARY KEY  (`pet_num`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

 

[misson]

Is a user allowed to have more than one pet? What are the purposes for the "pets" table, the "pettable" table and the "pets.pet_pet_id" column?

 

[thenewprogrammer]

users can have more than 1 pet.
user table contains all information about real life user information.
table pets contain information of user pets and their stats.
petstable contains the basic stats and the image to the pet such as attack when you first create it

id 1|Dragon|atk 4 |def 3|hitpoints 2|color blue|bluedragon img
id2 |Dragon|atk 4|def3|hitpoints 2 |color red|reddragon img

etc. Pettable contains basic stats for the creation of the pet and their color img.

the table pets has pet_id which is linked to petstable for the pets they have. and so on.

 

[misson]

So "pettable" is all the kinds of pets? Would "kinds", "species" or "subspecies" be a better name for "pettable"?

As pettable contains the base stats, why does it have a "petlvl" column?

the table pets has pet_id which is linked to petstable for the pets they have. and so on.

Right, but what is "pets.pet_pet_id" for?

 

[thenewprogrammer]

So "pettable" is all the kinds of pets? Would "kinds" or "species" be a better name for "pettable"?


Right, but what is "pets.pet_pet_id" for?

pet id is number in what the species is in pettable but pettalbe has it petnum instead. so petid 1 is = to petnum 1. that way when i have multiple pets and want to echo it in their profile i can do the color of image easier. like pet num 1 is red, pet num 2 is blue version etc.

 

[misson]

In table "pets", you've got 3 pet ID columns: "id", "pet_id" and "pet_pet_id". "id" is the id for a user's pet, "pet_id" is the ID for a kind of pet (i.e. pets.pet_id references pettable.petnum), and then there's "pet_pet_id"... What's "pet_pet_id"? It's really hard to understand you when you call something by different names in your post, such as "pet id" and "petid" or "petnum" and "pet num". Please stick to the names (column, table, &c. ) you use in the source. Prefix column names with table names when appropriate (e.g. when different columns in different tables have the same name).

The purpose of "pettable" is still unclear. Are the pets in "pettable" variants of each user's pets (that is, each entry in "pets" will have one or more entries in "pettable; a user can have a dragon, an color it blue or red)? Or does "pettable" specify the basic types of pets a user can have (e.g. there is one and only one blue dragon in "pettable")? Again, would "kinds", "species" or "subspecies" be a better name for "pettable"?

 

[thenewprogrammer]

pettable is for when the pets are registered. when pet are registered it takes the oringal stats(atk,def) from this table and updates it into pets table which is the table the users pets are in. these two tables have to be different becuase the users can upgrade their stats but when new people come we dont want them starting off with 1000 atk points. each pet_num in pettable stands for a species and a color and a img.

also can we talk on aim? might make this easier.

 

[descalzo]

users table

CREATE TABLE `users` (
 `user_id` bigint(100) NOT NULL auto_increment,
  ........
PRIMARY KEY  (`user_id`)
)

pets table

CREATE TABLE `pets` (
 `id` bigint(100) NOT NULL auto_increment,
 `user_id` varchar(65) NOT NULL,
`pet_id` varchar(65) NOT NULL,
..........
 PRIMARY KEY  (`id`)
)

pettable

CREATE TABLE `pettable` (
 `pet_num` bigint(100) NOT NULL auto_increment,
...............
 PRIMARY KEY  (`pet_num`)
)

Ok, I took the liberty to remove the "extra" fields.
My assumptions:

pets.user_id links to users.user_id
.... users can have more than one pet in pets
pets.pet_id links to pettable.pet_num
.... each pet occurs exactly once in pets and once in pettable
..... each user_name is unique

Given a user_name, you want to get all his pets and all their information

1. This will return 0 rows if either the user does not exist or does not have any pets
2. If user exists and has pets, it will return as many rows as he has pets. His personal information will be repeated each time.

SELECT * FROM users JOIN pets       ON pets.user_id = users.user_id

First you JOIN the first two tables ON the fields that link pets and users

SELECT * FROM users JOIN pets       ON pets.user_id = users.user_id 
                                   JOIN pettable ON pets.pet_id = pettable.pet_num

Then you JOIN that with the third table ON the fields that link it with pets

SELECT * FROM users JOIN pets       ON pets.user_id = users.user_id 
                                   JOIN pettable ON pets.pet_id = pettable.pet_num
                WHERE users.user_name='$user'

Finally, use WHERE to select which row(s) you want.

You should test your result for
1) failure - bad SQL
2) 0 rows - no such user or he has no pets
3) number of rows, do not just take the first row of the result.

 

[misson]

pettable is for when the pets are registered. when pet are registered it takes the oringal stats(atk,def) from this table and updates it into pets table which is the table the users pets are in.

The second scenario I described in post 14, then. Still need to know about pets.pet_pet_id (not pets.pet_id or pets.id).

also can we talk on aim? might make this easier.

I'll PM you about this.

 

[thenewprogrammer]

got my probem solved thnx for the help.