koolsamule wrote:Hi Chaps,
I have a Query that pulls data from 3 different job tables:
Code: Select all
tbl_jobs
- jobid (auto) primary key
- jobinvsent (y,n)
tbl_jobxml
- jobid (auto) primary key
- jobinvsent (y,n)
tbl_jobtrans
- jobid (auto) primary key
- jobinvsent (y,n)
I'm able to present the data in an HTML table, but what I need is the ability to update the values of jobinvsent to 'y' of all the jobid's presented.
I'm guessing I'll need to wrap the table in a form and then post it to a script.php file, but I don't know how to gather all the jobid's in such a way that the scrip file will know which table it came from.
Would really appreciate any help!
When you do you're SELECT query to retrieve the jobs (I assuming using a UNION?), create a new column in the result which tells the table name, e.g.
Code: Select all
SELECT jobid, jobname, jobdesc, etc, etc2, etc3, 'tbl_jobs' as table FROM tbl_jobs
UNION
SELECT jobid, jobname, jobdesc, etc, etc2, etc3, 'tbl_jobxml' as table FROM tbl_jobxml
UNION
SELECT jobid, jobname, jobdesc, etc, etc2, etc3, 'tbl_jobtrans' as table FROM tbl_jobtrans
That way you have a column in the result that tells you where that particular result came from.
When you say you need to update the jobinvsent column to Y, do you mean when its displayed, or does the user have to do some additional action (e.g. select a checkbox and click a button)? If as soon as its displayed, there's no need to send the info to the HTML page and submit it back (waste of time), simply run another query afterwards to update the column of any items returned by the first query.
If you want the user to have to do something before it gets marked as sent, then simply send the table name along with the job id to the HTML form, then when the form is submitted back to your PHP script it gives both the table name and job id.
Be careful though, escaping the table name won't do anything to prevent SQL injection so you must compare it against a pre-defined list of tables before running any queries on it!
hth
