Phpmysql report data changed after updating one mysql table

Databases
1

I am generating report from following mysql query which consist of two tables. 1)Consultant and 2) iap4. Consultant table consist of consultant_name and share1. where as iap4 table consist of docname, qty, price1, discunt, tot4 and tech1.

SELECT share1, docname, qty, price1, discunt, tot4, tech 
FROM consultant INNER JOIN iap4 ON
consultant.consultant_name = iap4.docname

1

all work fine in reporting. But when I update consultant share in table consultant, then it also update all the previous data with new updating share. I want to learn is there any way by which we can stop affect of updated share1 on previous entered records.

2

Records for what?
If you’re trying to store the information as part of an iap4 “record” (whatever iap4 is)… you would need to put that field as part of the iap4 table. Because that’s how tables work.

3

i knew currently i am doing in same way using java script, i insert value from table to iap4 table. But the issue is i have different consultant which need to be entered and all have different share.

4

Is the share related to the consultant, or the iap4?
If it’s related to the consultant, when you update a consultant, all of the matching entries for the consultant will change along with them.
If it’s related to the iap4, you would store that information in the iap4 table, and then changes to the consultant would not affect the iap4 table.

If it’s some other oddball combination (a consultant and iap4 could have multiple shares), then you’d need a table between them.

1 Like
5

yes its related to consultant
and yes when i update consultant, then all entries related to customer will update.

tabl between them how ???

6

I dont understand the desired state of affairs.

Let’s try a basic example.

I’ve got a consultant, Dave.
Dave has an ID of 1, and a share1 of “wark” (i dunno what goes there, but roll with me.
I’ve got another consultant, Sarah.
Sarah has an ID of 2, and a share1 of “test”.
I have an iap4, which has a docname of “Dave”, qty of 3, and price of 2.
I have an iap4, which also has a docname of “Dave”, qty of 6, and price of 7.

If I run your query right now, I get 2 rows, which both have share1 “wark”, both have docname of “Dave”. One of them has a qty of 3, the other a qty of 6.

What do you want to update, and what do you want to have come out of the report at the end?

1 Like
7

BTW it is never a good idea to use a name as a unique key…

2 Likes
8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.