Another "apply to all entries" thing

[djfox]

Ok, I have the code from before to apply to all entries in the database:

mysql_query("update ownedpets set food = (food - " .$_REQUEST["food"].")");
mysql_query("update ownedpets set play = (play - " .$_REQUEST["play"].")");

 

But let`s say in the database table, I enter the new field "species". Well all the entries would have a blank and would have nothing for that.

 

I`d like to some way use a form to update all the entries with the proper information for the field "species". But each one will have a different species name. In this example, the table "ownedpets" already has a field of "spec" which has a number.

 

I have another table "p_species" with the fields "id, name". The "id" would be the same number as "spec" from "ownedpets".

 

So, when the submission for this form is made, I need applied to all entries that if ownedpets spec=1, then species needs to update with "name" from "p_species" where id=1, and that all entries if ownedpets spec=5, then species would update with "name" from "p_species" where id=5, etc etc etc.

 

How would I get that accomplished?

[Psycho]

Your question is not very clear, but if I am understanding it correctly you want to set the field "species" in the "ownededpets" table with the value from the "name" field of the "p_species" table where the id is the same.

 

If that is correct then you are doing this all wrong. The records in the two tables are already associated with each other. It makes no sense to duplicate a piece of data. It would be a very simple query to get the species for a particular ownedpet record by referencing the species table:

 

SELECT o.*, p.name as species

FROM ownsedpets o

LEFT JOIN p_species p ON o.spec = p.id

 

That will give you a result list of every owned pet record with it's associated species name

[djfox]

Well, the reason I want to do that is because I`m entering a search in which people can type in the name of the species they may want to look for. But if spec is a number, they can`t find the entires of, say, "cat".