I'm a couple months into grasping PHP and MySQL basics.
I am currently querying table A and displaying it live as an end user (ie, mysite.com/includes/querying.php). I'm echoing out all the different fields in table A to display beside two decision buttons; accept and deny. The data is being produced from form requests each as a new row. I'd like to have the control to determine if I want to accept or deny each request (row) independently.
My goal is to take this data querying from table A and select accept or deny and have the row written to another table; table B for querying additional data.
For example, the row in each table itself in it's simplest can have two fields, name and status. By default I have the status on all new rows set as PENDING. So I query Jon Doe and PENDING. Jane Doe and PENDING. Joe Doe and PENDING, and so on.
Once a new row of data is queried from table A and I select accept, the accept button forces writing this information into table B, switching the status from PENDING to Y or N and removing it from table A. Most of this is easy to complete in a couple steps.
Also, I started to make some progress by having the form submission write the data to BOTH table A and table B on submit. Then I'd only need to update the status from PENDING to Y or N in table B. However when I tried using WHERE I could only write a blanket condition that updates the status column in every row in table B which is below. I am clueless on how to make this statement specific to only the row I am selecting.
$query = "UPDATE table B SET confirmed='Y' WHERE confirmed='PENDING';
So I was wondering if there is a simple way to base the field update from PENDING to Y / N by checking an auto-increment ID, an email address, etc, basically something unique against itself?
As for resources I've utilized Learning PHP, MySQL & JavaScript from O'Reilly and completed many Google searches with failed attempts and phrasing.
You might want to rethink your database design, and google 3rd normal form. You want to avoid duplicating data. I recommend using 1 table and adding a column, or have an additional table that you join to.
But to answer your question you can use the AND keyword in your query.
UPDATE table B SET confirmed='Y' WHERE confirmed='PENDING' AND firstName = 'Jon' AND lastName = 'Doe';
Doing an update by the primary key would be best, because you could have multiple people with the same name. Ex:
UPDATE table B SET confirmed='Y' WHERE id = 1234;