linking 3 databases

[geko21]

Hi guys Im having some problems understanding how I can get my 3 databases working I've used 3 tables to avoid table bloating.

 

The 3 tables I have are:

 

- storing the users username and password when they register and login

- storing the profile information on the user e.g name/age/location/picture etc

- storing the bike images and there details

 

I need help trying to get them working together somehow so when a visitor to the site clicks on a bike post and views the bike they can then click the users username on that bike post and be linked to that persons profile which shows the users details i.e name/age/location/picture etc.

 

Im a real noobie when it comes to php/mysql so if anyone could help me out it would be great  

[requinix]

Start learning SQL. The bit that applies to your situation is how to JOIN tables together.

SELECT fields you want FROM bike table
JOIN user table ON however you know which bike belongs to which user
JOIN profile table if you need it ON however you know which profile row belongs to which user row

[VTJ]

Sorry I misundertood your question before posting and my reply wasn't very helpful. I tried deleting it but you can't. You can only modify it. woops

[geko21]

Start learning SQL. The bit that applies to your situation is how to JOIN tables together.

SELECT fields you want FROM bike table
JOIN user table ON however you know which bike belongs to which user
JOIN profile table if you need it ON however you know which profile row belongs to which user row

 

thanks for the quick reply buddy

 

can you just explain to me aswell how the 2 databases that have the foriegn keys actually update? e.g if a new member signs upto the site and their infomation is posted into the username database do they also get a row in the two databases using foreign keys (profile and bikes)? because from what I've done the past I have had to manually put the id into the foreign key fields which obviously isn't the way of really doing it.

 

 

[kickstart]

Hi

 

Normally you would insert the other records manually. If there is a 1 to 1 relationship then maybe putting it onto a separate table isn't required, while with a 1 to many relationship (presume a person can have many bikes) then it doesn't seem to make sense to randomly insert one bike (when they may not actually have any).

 

All the best

 

Keith

[geko21]

Hi

 

Normally you would insert the other records manually. If there is a 1 to 1 relationship then maybe putting it onto a separate table isn't required, while with a 1 to many relationship (presume a person can have many bikes) then it doesn't seem to make sense to randomly insert one bike (when they may not actually have any).

 

All the best

 

Keith

 

Hi Keith, I was just thinking though is it not the user_id from the session in an insert string that gets taken from the page and posted into the other tables foreign key version of user_id? 

 

Thanks

[kickstart]

Hi

 

No. Mysql knows nothing about the php sessions. If you want that you will have to manually do it.

 

When you insert the user the user_id would likely be an integer autonumber field. You would use mysql_insert_id() to retrieve it and use that in child records you inserted afterwards.

 

All the best

 

Keith

[Muddy_Funster]

geko - I think you might have the wrong idea about what a foreign key is and does.  In its simplest form it is just an indexing tool, it lets one table know that if it wants to have a value in that field it has to first be able to reffrence that value in the table that it is looking up for the FK.  What type of index it is depends on the relationship between the tables on that field. 

 

While you can cascade updates and deletions from the key table down to the child tables, you can't cascade the creation of a new record as there is no way to enforce that a new parent record will always merit a new child record being created at the same time.

[geko21]

geko - I think you might have the wrong idea about what a foreign key is and does.  In its simplest form it is just an indexing tool, it lets one table know that if it wants to have a value in that field it has to first be able to reffrence that value in the table that it is looking up for the FK.  What type of index it is depends on the relationship between the tables on that field. 

 

While you can cascade updates and deletions from the key table down to the child tables, you can't cascade the creation of a new record as there is no way to enforce that a new parent record will always merit a new child record being created at the same time.

 

haha oook  ... what do I have to do then when I have a table that has a primary key but it needs another ID field so that isnt auto icramented which stores a number (Im assuming is the session ID number of the user) so that the tables link together and display all that users information

 

unless your answering the first question I asked about creating new records haha?

 

these are my tables that im needing to work together

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL,
  `password` varchar(40) NOT NULL,
  `email` varchar(32) NOT NULL,
  `first_name` varchar(40) NOT NULL,
  `last_name` varchar(40) NOT NULL,
  `gender` varchar(40) NOT NULL,
  `about_user` varchar(150) NOT NULL,
  `birthday` date NOT NULL,
  `city` varchar(40) NOT NULL,
  `country` varchar(40) NOT NULL,
  `picture` varchar(40) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;


CREATE TABLE IF NOT EXISTS `cycles` (
  `bike_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `bike_type` varchar(20) NOT NULL,
  `frame` varchar(20) NOT NULL,
  `description` text NOT NULL,
  `large_1` varchar(60) NOT NULL,
  `large_2` varchar(60) NOT NULL,
  `large_3` varchar(60) NOT NULL,
  `thumbnail` varchar(60) NOT NULL,
  PRIMARY KEY (`bike_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

[kickstart]

Hi

 

Using an example.

 

Say you had a users details on one table. Each use can have 0 or more bikes, and another table lists those bikes.

 

The bike table would probably have an autoincremented primary key, but would also have a column which contains the id of the owning user (which would probably be the autoincremented primary key of the user from the users table).

 

When adding a user you would get the key of that user and then add the bikes with the key value from the users table being known.

 

If you had bikes that could be associated with multiple users then you would use an intermediate table to link the users and bikes together. This would have its own primary key (which we probably wouldn't care in the slightest about its value) and then a column for the user and a column for the bike. Any user would have as many rows as required on this table for all the bikes they were associated with.

 

Using your tables you would insert something like this:-

 

$sql = "INSERT INTO `users` (`user_id`,`username`,`password`,`email`,`first_name`,`last_name`, `gender`,`about_user`,`birthday`,`city`,`country`,`picture`) VALUES (NULL,'Joe90','1234','Joe@test.com','Joe','Bloggs', 'm','Some random user','1990:12:25','London','UK','someimageurl');
$rs = mysql_query($sql) or die(mysql_error()." $sql");
$UserId = mysql_insert_id();
$sql = "INSERT INTO `cycles` (`bike_id`,`user_id`,`bike_type`,`frame`,`description`,`large_1`,`large_2`,`large_3`,`thumbnail`) VALUES(NULL,$UserId,'Big Bike','Big Frame','Some description of a bike','Some Text','Some Text','Some Text','imageurl');
$rs = mysql_query($sql) or die(mysql_error()." $sql");

 

 

All the best

 

Keith

[geko21]

Hi

 

Using an example.

 

Say you had a users details on one table. Each use can have 0 or more bikes, and another table lists those bikes.

 

The bike table would probably have an autoincremented primary key, but would also have a column which contains the id of the owning user (which would probably be the autoincremented primary key of the user from the users table).

 

When adding a user you would get the key of that user and then add the bikes with the key value from the users table being known.

 

If you had bikes that could be associated with multiple users then you would use an intermediate table to link the users and bikes together. This would have its own primary key (which we probably wouldn't care in the slightest about its value) and then a column for the user and a column for the bike. Any user would have as many rows as required on this table for all the bikes they were associated with.

 

 

Aaaah ok I think I get you. So I would have the 2 tables and for example I would have the image uploader script linked to the 'cycles' table which would update the cycle contents and pictures and then on the edit profile page I would link to the 'user' database which would update their profiles but the cycles page with the foriegn key implamented would take that user_id from the 'users' table and put it in its version of 'user_id' field?

 

Would I just echo out the related database fields to show the information for each bike and its member info as I would do for a single database result or would I have to use JOINS?

 

example of what I mean ::

I want to show the username, city, country, frame, bike_type and the thumbnail

 

[kickstart]

Hi

 

Yep, 2 tables.

 

You would be best to use a JOIN. Something like:-

 

SELECT a.user_id, a.username, a.password, a.email, a.first_name, a.last_name, a.gender, a.about_user, a.birthday, a.city, a.country, a.picture, b.bike_id, b.bike_type, b.frame, b.description, b.large_1, b.large_2, b.large_3, b.thumbnail
FROM users a
INNER JOIN cycles b
ON a.user_id = b.user_id

 

All the best

 

Keith