insert value from another table

[rskuse]

I have two tables:

clients:
id | clients

projprog:
id | clientid | clients | date | subject | post | post by

where clients.id = projprog.clientid

Table clients stores all my current clients and table projprog stores the progress of the projects I am working on. I want to be able to add data to projprog via a form so that clients can view the progress of their projects. This I can do fine but each time I update projprog I need clients.id to be entered into projprog.clientid and this is where I am having trouble.

I think I am on the right lines with the following code but at the moment it is entering multiple rows into my projprog table which I think is due to my select statement:



PHP:--------------------------------------------------------------------------------

<?php

$client = $_POST['client'];
$subject = $_POST['subject'];
$post = $_POST['post'];
$postby = $_POST['postby'];
$date = date(&quot;Ymd&quot;

//connect to the DB
include('../db.php');

//set up the first query
$query1 = &quot;SELECT clients.id FROM clients, projprog where clients.id = projprog.clientid&quot;;
//run the first query
$result1 = mysql_query($query1, $connection) or die(mysql_error());
while ( $query_row = mysql_fetch_assoc ( $result1 ) )
{
//set up the second query
$query = &quot;INSERT INTO projprog (clientid, clients, date, subject, post, postby) values ('$query_row[id]', '$client', '$date', '$subject', '$post', '$postby')&quot;;
//run the second query
$result = mysql_query($query, $connection) or die(mysql_error());
header( &quot;Location: admin.php&quot; );
}

?>

--------------------------------------------------------------------------------


Can anyone offer me any advice on where I am going wrong?

Thankyou in advance....

 

[sleipnir214]

I think that a bit of table reorganization is in order. The progress of a project is an attribute of a project, not a client, unless the client changes during the development of a project.

I would have three tables -- one for clients, one for projects, and a third for the progress of a project.

The third table would only need to store the project id and progress data.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[rskuse]

It's generally one client, one project, so i'm not sure that a separate projects table would serve any purpose. I just want the id of the client to be present in the projprog table so that when I do a select query:

&quot;SELECT * FROM clients, projprog WHERE clients.id = projprog.clientid AND clients.id = 1&quot;

I can echo the results of a specific client.

 

[sleipnir214]

In the long term, I think you will be better off with three tables.

The equivalent query would be:

SELECT * from clients c, projects p, projprog pp
WHERE pp.projectid = p.projectid and
p.clientid = c.id
WHERE c.id = 1

Anyway, that is not the kind of query you're going to be using anyway. If you already know the client's id, why are you querying the client table?


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[rskuse]

I'm querying the client table because in my html form I use a drop down that pulls all clients from the database and then I want to enter project progress according to the client I have selected from the drop down.

Am I making any sense, sorry if i'm not explaining it very well??!!

 

[sleipnir214]

In that case, you don't need to include the projprog table in your query.

Here's how I'm visualizing it:

A script produces a list of clients. You select one and submit.

Using the submitted client id, a script then produces a list of projects. The page includes a hidden field which includes the client id. You select a project and submit.

A script then takes the client id and project id and produces an entry page which includes the client and project ids in hidden fields. You fill in project progress information and submit.

A script then enters the data into the table.


I keep saying &quot;a script&quot;, but all the scripts mentioned could be a single script which does the appropriate thing based on input. You could, for example, include information on each page which tells the script at what phase of the entry process you are.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[rskuse]

fyi...

I kept the two tables I had originally but used the following query to populate my drop down box:

---------------------------------------------------
<?php
//connect to the DB
include('../db.php');
//set up the query
$query = &quot;select clients.clientid, clients.clients from clients&quot;;
//run the query
$result = mysql_query($query, $connection) or die(mysql_error());
//The following lines return the results from the query and assign them to variables
while($row = mysql_fetch_array($result)){
$name = $row['clients'];
$id = $row['clientid'];
//This line prints out the HTML Code with the returned data
echo &quot;<option value='$id'>$name</option>&quot;;
};
mysql_free_result($result);
?>
---------------------------------------------------

therefore the value of $id is being submitted to my database.

Thanks for your help....