[SOLVED] How do you do this?

[heldenbrau]

I have a table in a database called users, that is constantly changing.

 

I have another table called mythreads in another database which I need to update when the users table updates.

 

Is there a way of inserting into the mythreads table a reference to a feild in the other database.  So that when the field in the second database is selected it shows data from a field in the first database.

 

If there is a way to do this, how do you insert the field with PHP this is where I want to insert the field, it needs to reference the feild in users.users numposts where casenum=$casenum

 

$sql = "INSERT INTO mythreads$username (heading, casenum, lastpost, replies, type) VALUES ('$heading', '$casenum', '$date', 'HERE', '4')";

 

 

[ignace]

Yes but you need InnoDB check first your table uses InnoDB and not MyISAM. Syntax is like:

 

field TYPE REFERENCES table (field) ON UPDATE CASCADE ON DELETE CASCADE

 

More information can be found here: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

 

If you provide me your db schema I'll re-write the code to match InnoDB Don't forget to export the data first (you'll need the insert into lines afterwards)

[heldenbrau]

I've just realised that depending on the type 1-4 the field needs to reference a different table on a different database for each type. 

 

I have read and tried to understand the info that you gave me, but it looks as if the tables need to be in the same database to use the foreign key.  I wish I had put all the tables in the same database now.

[ignace]

I've just realised that depending on the type 1-4 the field needs to reference a different table on a different database for each type. 

 

I have read and tried to understand the info that you gave me, but it looks as if the tables need to be in the same database to use the foreign key.  I wish I had put all the tables in the same database now.

 

Not entirely sure but this could work to:

 

field TYPE REFERENCES database.table (field) ON UPDATE CASCADE ON DELETE CASCADE

[heldenbrau]

I have worked out that I can put the data from the 4 databases into one table in the same database.  So I only need to reference a different table.  But I don't know what to type to insert it.  Do I type:

 

$sql = "INSERT INTO mythreads$username (heading, casenum, lastpost, replies, type) VALUES ('$heading', '$casenum', '$date', 'field TYPE REFERENCES users postnum ON UPDATE CASCADE ON DELETE CASCADE WHERE casenum="$casenum"', '4')";

 

Sorry I am new to this, but I can't find the info anywhere

 

 

[ignace]

No this comes in your table defintion:

 

Assume this is one of your tables in your database:

 

CREATE TABLE users (
  users_id INTEGER NOT NULL AUTO_INCREMENT,
  # table defintion ..
  PRIMARY KEY (users_id)
);

CREATE TABLE threads (
  threads_id INTEGER NOT NULL AUTO_INCREMENT,
  threads_starter INTEGER REFERENCES users (users_id) ON UPDATE CASCADE ON DELETE CASCADE,
  KEY fk_threads_starter (threads_starter),
  PRIMARY KEY (threads_id)
);

[heldenbrau]

The problem is, I want to reference a field on the other table that is not the primary key.  The primary key on the table is casenum, which is a unique number, the number of a thread on a forum.  But the field I want to reference is the number of posts on that thread, a field called postnum.

 

So what I want to see in the field is the data in postnum where casenum=$casenum

[ignace]

If you have phpMyAdmin then do an export of the tables of your database(s)

[heldenbrau]

table postnum

"8";"8";"1251929065"
"9";"11";"1251930016"
"10";"13";"1251929877"
"1251832641";"6";"1251929258"

 

table mythreads

"Hello everybody";"1251832641";"1251836225";"0";"0"

 

Column 2 in mythreads is the thread number. 

 

In numposts column 2 is the number of posts on the thread and column 3 is the time of the last post.

 

Mythreads is a list of the members watched threads.  I want to display the table mythreads as a list to display.

 

The title, the time of the last post, and the number of posts on the thread.  And I need it to be sorted by most recent post first.  So columns 4 and 5 of threadnum, need to display the info from columns 2 and 3 of numposts (which is a table of every thread)

 

 

 

[ignace]

In SQL please not CSV

[heldenbrau]

-- Table structure for table `postnum`
--

CREATE TABLE IF NOT EXISTS `postnum` (
  `casenum` int(20) NOT NULL,
  `numposts` int(10) NOT NULL,
  `lastpost` int(20) NOT NULL,
  PRIMARY KEY  (`casenum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `postnum`
--

INSERT INTO `postnum` (`casenum`, `numposts`, `lastpost`) VALUES
(8, 8, 1251929065),
(9, 11, 1251930016),
(10, 13, 1251929877),
(1251832641, 6, 1251929258);

 

Table structure for table `mythreadskev2009`
--

CREATE TABLE IF NOT EXISTS `mythreadskev2009` (
  `heading` varchar(100) NOT NULL,
  `casenum` int(20) NOT NULL,
  `lastpost` int(20) NOT NULL,
  `replies` int(10) NOT NULL,
  `type` int(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `mythreadskev2009`
--

INSERT INTO `mythreadskev2009` (`heading`, `casenum`, `lastpost`, `replies`, `type`) VALUES
('Hello everybody', 1251832641, 1251836225, 4, 4);

[ignace]

SELECT * FROM postnum JOIN mythreads$username USING casenum

 

I am still unsure of what you actually want to achieve.

[heldenbrau]

I have a forum which displays a list of all the threads.  There are 4 tables, 1 for each category of the forum.  There is another table, numposts, which lists every single thread on the whole site including private threads.

 

Users can choose if they want to watch a thread, if they watch the thread it is shown on their settings page as their own custom list of threads.  I want this list to be sorted by the thread with the most recent post on it and also display the number of posts on each thread.  So the list needs to show.  Date of last post, Title, number of posts, and then be sorted by the most recent thread.

 

The list of watched threads is stored in a table called mythreads$username.  I can only update the number of posts and time of last post by updating postnum and then referencing the fields in mythreads.  Otherwise I would have to save the info to every single mythread$username table.

[heldenbrau]

JOIN looks like what I need.  But if I join the 2 tables, there will be 2 fields showing numposts and lastpost.  Should I delete those feilds from mythreads and then use JOIN?

[ignace]

SELECT heading, replies, type, mythreads$username.casenum mythreads_casenum, postnum.casenum postnum_casenum, mythreads$username.lastpost mythreads_lastpost, postnum.lastpost postnum_lastpost FROM postnum JOIN mythreads$username USING casenum

[heldenbrau]

I get an error from using that.

 

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 'casenum'

 

 

[heldenbrau]

I have sorted it out now and have joined the two tables, thanks a lot for your help.