I have a virtual pet site that I have done with very basic PHP knowledge. I am trying to write a new add-on that will edit several things at once. I looked through the PHP Manual but couldn't find what I needed. Maybe what I want to do is not possible. I was hoping to run a query that will look in a certain table and find all "pets" owned by a certain user. Then for each of those pets get its status and add 1, get its points and add 11, and then update the database with the new info.
So far the queries I find can either only do one at a time or will only pull general info. Can anyone point me in the right direction or let me know if my idea is impossible.
multiple queries help
Moderator: General Moderators
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: multiple queries help
What query do you have so far? and what is your database structure?
Re: multiple queries help
This is the code when you feed each pet one at a time. I would like to be able to feed all pets of one person at one time.
Code: Select all
$petsql = mysql_query("SELECT * FROM `pets` WHERE `id`='$petID' AND `user`='$user'");
while($petrow = mysql_fetch_array($petsql)) {
$ppoints = $petrow["points"];
$stats = $petrow["status"];
}
$newpoints = $ppoints + $fpoints;
$stats = $stats + 1;
$date = date("Y-m-d");
$update2 = mysql_query("UPDATE `pets` SET `status`='$stats', `points`='$newpoints', `lastfed`='$date' WHERE `id`='$petID' AND `user`='$user'");
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: multiple queries help
Something like this...?
That should do it, update all the user's pets in one query.
Code: Select all
// btw you need to protect your queries from injection! but thats another story
$q = "UPDATE pets SET status = status + '1', points = points + '".$fpoints.', lastfed = '".$date."' WHERE user = '".$user."'";
$sql = mysql_query($q);
Re: multiple queries help
Thanks so much! That is exactly what I needed.
Thanks for the tip on injection. Guess my newbie ignorance is showing. I'm not sure what that is, but I certainly look it up.
Thanks for the tip on injection. Guess my newbie ignorance is showing. I'm not sure what that is, but I certainly look it up.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: multiple queries help
I would definitely look it up as someone could easily corrupt/delete your database if there is no protection. In php.net they give an example of minimum protection.
Re: multiple queries help
OK, Thanks. I did find that and I am now working to correct it. 