Page 1 of 1
multiple queries help
Posted: Sun Aug 31, 2008 7:49 am
by Chinclub
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.
Re: multiple queries help
Posted: Sun Aug 31, 2008 9:38 am
by jaoudestudios
What query do you have so far? and what is your database structure?
Re: multiple queries help
Posted: Sun Aug 31, 2008 9:51 am
by Chinclub
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'");
Re: multiple queries help
Posted: Sun Aug 31, 2008 10:23 am
by jaoudestudios
Something like this...?
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);
That should do it, update all the user's pets in one query.
Re: multiple queries help
Posted: Sun Aug 31, 2008 10:43 am
by Chinclub
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.
Re: multiple queries help
Posted: Sun Aug 31, 2008 11:00 am
by jaoudestudios
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
Posted: Sun Aug 31, 2008 12:30 pm
by Chinclub
OK, Thanks. I did find that and I am now working to correct it.
