Newbie:Link Database

B

Brad Robinson

Guest
Hi Guys

I'm pretty new to this and i am trying to create and application using a mysql database which i have created the structure for and now need to do some configuration of.

Here's where i need the advice, the way my layout works is that i have multiple tables with one primary key that is in every table in the database called "id".

I need this to auto increment starting from 1 and going upwards and also link all the tables together ao that if i call up the applicant by name all the items he has requested appears too, with this unique id input from the applicant insert page.


Any help would be much appreciated and i know that i could probably learn it from reading manuals but time is of the essence and i've done the majority of the ground work, and the deadline's monday.

Thanks Guys

 
D

Debbie S

Guest
Brad

I don't completely understand what you are trying to achieve. Could you provide a small sample of what one of the linked tables would look like and how you want the final data to appear when viewing in DaDaBIK?

For example:

Linked table:
ID | Value
1 | itemone
2 | itemtwo
3 | itemthree

Main table output:
as above - show how you want the results displayed.

If you can make this available somewhere that I can view, I'd be happy to look at it for you.

Debbie
(Latest version of DaDaBIK when this message was posted: 3.1 Beta)
 
B

Brad Robinson

Guest
I'ts not really that difficult i dont think but it's knowing the syntax and i'm not big on mysql. I'll show you what i am trying to achieve;

Table1 User

|..id..|..name..|..crypt..|.MembNum
...1......Mr A....iu3e93....1015485
...2......Mr B....234923....1023247
...3......Mr C....23kckc....1234103

Table2 Car

|..id..|..Make..|..Model..|..cc|....RegNo...|
....1.....fiat.....panda....1000....a346bhr
....2.....vaux.....astra....1200....mw54wlit
....3....renault...cleo.....1200....b458jut


If i searched for applicant by name i would then need to be able to show the car table in the same results window and show that Mr B had a Vauxhall Astra as well as showing all the other details in the field for the id from the car table.

As below


Search Mr B

Result

MrB
Vauxhall Astra
1200cc
mw54wlit
1023247


Thanks for the help

Brad
 
D

Debbie S

Guest
Brad

OK ... I see what you are trying to achieve. Based on your example above, here is how you would construct the link to the 'Car' table from the 'User' table:

In the admin.php, in the "Configure the DaDaBIK Interface for ....." section, ensure you are working with the 'User' table. Select the "interface configurator" link in the paragraph below the "for this table enable" line.

In the configurator, select the field in which you wish to display the details from the 'Car' table. If you do not have a field to hold this data, you will have to create one. For this field, fill in the values below:

Primary key field == id *
Primary key table == Car
Linked fields == Make~Model~cc~RegNo

* Your primary key in Car table should be named different from the primary key in User table. Duplicate primary key names has been discovered as a 3.1 Beta bug and will be fixed in the final release. Although, it is good practise to have your primary keys named differently anyways.

Hope this helps!
Debbie
(Latest version of DaDaBIK when this message was posted: 3.1 Beta)
 
B

Brad Robinson

Guest
Thats excellent cheers debbie

i get it now, so i need to create a seperate field which has all the information from the all the other fields compiled into one field at the end of the table which is then output.


One last thing if you could please debbie.

Could you please let me know how i now go about making the id on the applicant table increase as an autoincrement without any human interaction and then the other id fields in the rest of the tables are blanked out but still have the same value as the applicant id field.

Thanks
Brad
 
D

Debbie S

Guest
Brad

A field is made to auto-increment within MySQL itself -- that is not controlled by DaDaBIK. A program like phpMyAdmin (http://www.phpmyadmin.net/home_page/) makes managing databases at the MySQL level very easy. The primary key in any table should always be set to auto-increment.

I'm not sure how you would go about ensuring the value of the id field in 'User' always matches the value of the id field in 'Car' unless you add the data for both/all tables at the same time:

Insert data into 'Car' table so the new record is '4', then into next table so value is '4' and so on until you insert a new record into the 'User' table (as id '4') using the data from the other tables just inserted.

To make the id field not display, you set that in the admin.php interface configurator for each table. In my tables, I set my primary key field to:

Field present in the search form? N
Field present in the results page? N
Field present in the details page? N
Field present in the insert/update form? N

This is my standard except for one database where the primary key field is being used as a ticket number. In this case I have it set to display only in the results and search form. On the details and update form, the field is displayed in the title at the top so users can see the ticket number they are working on.

I don't really understnd why making sure the id fields match in all tables is a concern since the linking feature will link the data you select regardless of what id number it happens to be. In most cases, the id (primary key) is used as a means to "grab" a particular record and display it.

Good luck!
Debbie
(Latest version of DaDaBIK when this message was posted: 3.1 Beta)
 
Top