PHP - 执行其他查询

Question

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.

Answer 1

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;