0
<option>location1</option>
<option>location2</option>
<option>location3</option>

<option>engineer for location1</option>
<option>engineer for location2</option>
<option>engineer for location3</option>

wen i choose a location the corresponding service engineer for that location should be added to d database

in d user form ther will be no option for engineer for that location, by selecting the location, it should automatically add the service engineer for that region

code:
sql:

insert into details(m_sno, pickmodule, 
pickmodulemanuf, cardreader, cardreadermanuf, 
printer, printermanuf, location, engname, date) values 
(1001, "BA540", "TRANTECH", "FC148", "TMDSECURITY", 
"PS345", "LEICESTER", "location1", "ELLANORE", CURDATE());

Dis code is for mysql insert operation

but in d forms ther will be no option for the name ellanore, by default selection of location the eng name should also be added in d database

Bcoz there will be 3 location with 3 engineers.

Edited by Ezzaral: Added code tags. Please use them to format any code that you post.

2
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by tryingtofindout
0

It's difficult to understand, letme make it clear!!

In the userform, for locations following are the options

<option>location1</option>
<option>location2</option>
<option>location3</option>

This one is for service engineers in different location

<option>engineer for location1</option>
<option>engineer for location2</option>
<option>engineer for location3</option>

If the location is dubai, then the service engineer for that location is elanore

If it's different location, different engineer


when i choose a location the corresponding service engineer for that location should be added to the database

In the user form ther will be no option for adding engineer name for that location, by selecting the location, it should automatically add the service engineer for that region

code:
sql:

insert into details(m_sno, pickmodule, 
pickmodulemanuf, cardreader, cardreadermanuf, 
printer, printermanuf, location, engname, date) values 
(1001, "BA540", "TRANTECH", "FC148", "TMDSECURITY", 
"PS345", "LEICESTER", "location1", "ELLANORE", CURDATE());

this code is for mysql insert operation

but in the forms ther will be no option for entering the name ellanore, by default selection of location should add the engineer for that location

i hope u understand!!!

Edited by Ezzaral: Added code tags. Please use them to format any code that you post.

1

i hope u understand!!!

Doubtful, but I'll give it a try...

If I am reading you correctly, you want the user to select a location from a HTML form and submit that location to a PHP page. On that PHP page you want an engineer name (presumably to be found in the database somewhere?) that corresponds to the given location to be inserted into a database table?

Ok, if that is so, this is how I would go about implementing this.
First of all, I would design a table to hold a list of my locations, and a second table to hold a list of my engineers. Then I would add a column to the locations table that would hold the ID of the engineer assigned to that location. - It would looks something like:

+------------------+     +-----------+
| locations        |     | engineers |
+------------------+     +-----------+
| id (PK)          |  |-<| id (PK)   |
| name             |  |  | name      |
| engineer_id (FK) |<-|  | etc...    |
| etc...           |     +-----------+
+------------------+

(This is a typical one-to-many (1:N) relationship)

With this, I can query the database to get a list of locations with their respective engineers like so:

SELECT  l.`name` AS 'location',
        e.`name` AS 'engineer'
FROM    `locations` AS l
LEFT JOIN `engineers` AS e
    ON  e.`id` = l.`engineer_id`

Now then. Not really knowing what the point of the INSERT query you were talking about is, I am going to assume it's purpose is to add an entry to a list of projects that need the engineers attention. - Your first impulse might be to want to add the name of the engineer to that list. That is not the way you should do this. - What you should do is just add the location ID to the list. Remember, we already have a list of locations. Putting the actual name (e.g. "Dubai") into the table that lists the projects is redundant. (Duplication of data should be avoided at (almost) all cost!)

So, to add that to the table structure above, you would have a database that looks something like:

+------------------+     +-----------+
    | locations        |     | engineers |
    +------------------+     +-----------+
 |-<| id (PK)          |  |-<| id (PK)   |
 |  | name             |  |  | name      |
 |  | engineer_id (FK) |<-|  | etc...    |
 |  | etc...           |     +-----------+
 |  +------------------+ 
 |
 |  +------------------+ 
 |  | projects         |  
 |  +------------------+ 
 |  | id (PK)          | 
 |  | client           | 
 |->| location_id (FK) |
    | start_date       |
    | end_date         |
    | price            |
    | etc...           |
    +------------------+

(Again, a typical one-to-many (1:N) relationship)

Then, to get a list of projects, including the name of the location and the name of the engineer, I would do this:

SELECT
        p.`id` AS 'project_id',
        p.`client`,
        p.`start_date`,
        p.`end_date`,
        p.`price`,
        l.`name` AS 'location_name`',
        e.`name` AS 'engineer_name'
FROM    `projects` AS p
LEFT JOIN `locations` AS l
    ON l.`id` = p.`location_id`
LEFT JOIN `engineers` AS e
    ON e.`id` = l.`engineer_id`;

And this would give me a list of all projects, including the name of the location and the name of the engineer.

Am I close?

P.S.
To avoid having to use the above query every time you want to query that data, you can define a view based on the query. It will act as a "virtual" table, made up of the result set of the query.

CREATE VIEW `project_list` AS
SELECT
        p.`id` AS 'project_id',
        p.`client`,
        p.`start_date`,
        p.`end_date`,
        p.`price`,
        l.`name` AS 'location_name`',
        e.`name` AS 'engineer_name'
FROM    `projects` AS p
LEFT JOIN `locations` AS l
    ON l.`id` = p.`location_id`
LEFT JOIN `engineers` AS e
    ON e.`id` = l.`engineer_id`;

With that, you can now just query a project_list table to get the info in the query. - You could, for example, do this:

/* Get all the data, just as if you had executed
 * the qyery itself, unaltered. */
SELECT * FROM `project_list`;

/* Only return a few specific columns from the query
 * limited by a specific location name. */
SELECT 
        `client`
        `start_date`,
        `end_date`,
        `engineer_name`
FROM    `project_list`
WHERE   `location_name` = 'Dubai';
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.