Update Multiple Rows At Once With Differing Values

[Usarjjaco]

Hello everyone,This conversation is carrying over from the HTML area where it started. I've been implementing an API pull system. The system I'm implementing is set up so that when players deposit money to a certain player/place in game, it credit's their account on the website. I know how to pull the XML data now, so here is my next endeavor:Let's say I run this every hour and a half... and I want it to add "credits" to all the necessary accounts- XML Comes in and gets parsed, then I use php to request all the ones that are transactions being receivedQ1: How to I get my PHP/SQL to update for instance:user a deposits 2,000user b deposits 5,000user c deposits 1,000There could be 1 user or 50 that make a deposit, so I need a way that it automatically adds credits to all the given accounts who put in credits.Hope that makes sense. Thanks in advance guys.

[niche]

your connect script here$result = your SELECT script herewhile ($row = mysql_fetch_array($result)) { define your variables here your connect script here your UPDATE script here}

[thescientist]

I think a loop would work. When you parse the XML document, you could make an array (of objects, perhaps) to store all the user info that you are going to need to update. Then, use that array as part of the loop to execute as many update statements as you need. Chances are you will probably have to check if the user exists in the table already so that way you know if it needs to be an UPDATE or an INSERT.

[niche]

thescientist makes a good point about INSERT.

[Usarjjaco]

I think a loop would work. When you parse the XML document, you could make an array (of objects, perhaps) to store all the user info that you are going to need to update. Then, use that array as part of the loop to execute as many update statements as you need. Chances are you will probably have to check if the user exists in the table already so that way you know if it needs to be an UPDATE or an INSERT.

Ok thanks for the advice guys. ... Now here's a question I have... Here's the way it worksThe API data that I'm pulling is the "Wallet Journal" for my character (This shows all inbound and outbound transactions, each transaction has a row.Is there any way to only parse rows which contain me as a recipient and place those into a table?) .. I know that using SQL I can select certain rows based on parameters.. but what about selectively taking XML based on parameters? (Because they all are in the "rowset" category in the XML.Then I was thinking to keep the table of transactions from getting overpopulated I would have the table delete rows with timestamps older than a week or something similar.. is this do-able?

[thescientist]

can you post an example of the XML being returned?

[Usarjjaco]

Certainly.This is what the journal return looks like:

<eveapi version="2">  <currentTime>2008-08-22 12:00:16</currentTime>  <result>	<rowset name="entries" key="refID"	 columns="date,refID,refTypeID,ownerName1,ownerID1,ownerName2,ownerID2,argName1,argID1,amount,balance,reason, taxReceiverID, taxAmount">	  <row date="2008-08-22 03:36:00" refID="1578932679" refTypeID="54" ownerName1="corpslave"		   ownerID1="150337897" ownerName2="Secure Commerce Commission" ownerID2="1000132"		   argName1="" argID1="0" amount="-8396.99" balance="576336941.61" reason="" taxReceiverID="" taxAmount="" />	  <row date="2008-08-22 03:36:00" refID="1578932678" refTypeID="2" ownerName1="anonymous"		   ownerID1="30497503" ownerName2="corpslave" ownerID2="150337897"		   argName1="725524391" argID1="0" amount="1399498.50" balance="576345338.60" reason="" taxReceiverID="" taxAmount="" />	  <row date="2008-08-21 22:18:00" refID="1578164958" refTypeID="42" ownerName1="corpslave"		   ownerID1="150337897" ownerName2="" ownerID2="0"		   argName1="" argID1="0" amount="-152939.40" balance="574647640.10" reason="" taxReceiverID="" taxAmount="" />	  <row date="2008-08-21 21:42:00" refID="1578064292" refTypeID="46" ownerName1="corpslave"		   ownerID1="150337897" ownerName2="Secure Commerce Commission" ownerID2="1000132"		   argName1="" argID1="1" amount="-100.00" balance="576279220.79" reason="" taxReceiverID="" taxAmount="" />	  <row date="2008-08-21 08:35:00" refID="1576226363" refTypeID="63" ownerName1="corpslave"		   ownerID1="150337897" ownerName2="Secure Commerce Commission" ownerID2="1000132"		   argName1="9956913" argID1="0" amount="-20200000.00" balance="573681668.81" reason="" taxReceiverID="" taxAmount="" />	  <row date="2008-08-21 00:12:00" refID="1575178039" refTypeID="64" ownerName1="Secure Commerce Commission"		   ownerID1="1000132" ownerName2="corpslave" ownerID2="150337897"		   argName1="9956913" argID1="0" amount="5300000.00" balance="612310142.18" reason="" taxReceiverID="" taxAmount="" />	  <row date="2008-08-20 13:10:00" refID="1575178032" refTypeID="85" ownerName1="CONCORD"		   ownerID1="1000125" ownerName2="anonymous" ownerID2="173993711" argName1="Jita"		   argID1="30001660" amount="135000.00" balance="609292267.52" reason="29200:15," taxReceiverID="1734917694" taxAmount="15000.00" /> 	  <row date="2008-08-20 05:19:00" refID="1572531631" refTypeID="34" ownerName1="anonymous"		   ownerID1="30497503" ownerName2="corpslave" ownerID2="150337897"		   argName1="" argID1="30497503" amount="911000.00" balance="611148267.52" reason="" taxReceiverID="" taxAmount="" />	  <row date="2008-08-20 05:19:00" refID="1572531630" refTypeID="33" ownerName1="anonymous"		   ownerID1="30497503" ownerName2="corpslave" ownerID2="150337897"		   argName1="" argID1="30497503" amount="945000.00" balance="610237267.52" reason="" taxReceiverID="" taxAmount="" />	  <row date="2008-08-20 04:15:00" refID="1572401485" refTypeID="15" ownerName1="corpslave"		   ownerID1="150337897" ownerName2="Federal Intelligence Office" ownerID2="1000121"		   argName1="" argID1="1" amount="-504.00" balance="597984259.02" reason="" taxReceiverID="" taxAmount="" />	</rowset>  </result>  <cachedUntil>2008-08-22 12:15:16</cachedUntil>

And I'm trying to get it to pull just the rows that are where the they are inbound transactions (Inbound would have my character name/id as owner2)

[thescientist]

excellent, good start. Have you started reading through the PHP tutorials regarding parsing XML? That would be your first place to start. It should be down and on the left.http://www.w3schools.com/php/default.asp

[Usarjjaco]

excellent, good start. Have you started reading through the PHP tutorials regarding parsing XML? That would be your first place to start. It should be down and on the left.http://www.w3schools.com/php/default.asp

I have read thru them but still feeling a little daft to be honest. I'm attempting to use SimpleXML. I can get it to pull all of the data, but then translating that into a format that I can store into a DB is proving harder than expected.

<?php$number = 602236694;$idnum = 1904604;$apiKey = ZYU0bWwW7zM3uC0zfy8Ct2umWMSwKHr078Xvzr5fqVKXgO0MVBzpGGHiv5b4OCme;$xml_feed_url = 'http://api.eve-online.com/char/WalletJournal.xml.aspx?characterID=' . $number . '&userID=' . $idnum . '&apiKey=' . $apiKey;$ch = curl_init();curl_setopt($ch, CURLOPT_URL, $xml_feed_url);curl_setopt($ch, CURLOPT_HEADER, false);curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);$xml = curl_exec($ch);curl_close($ch);$xmlr = new SimpleXMLElement($xml);foreach($xmlr->children() as $child)  {  echo $child->getName() . ": " . $child . "<br />";  }?>

Above is the code I'm using. That seems to successfully pull the data, however once I get to this point is where I feel daft. I cna get it to print out on the page, but I need to save to a data table

[thescientist]

so, the echo not print anything on the screen? Or can you get the values you need on the screen but aren't sure what to do after that?

[Usarjjaco]

The latter of the two. I seem to be able to get the results to print on the screen, but how do I convert those results ... I'm guessing something with making a variable for them and then storing the variables... but the print out using the name above prints both the Category (like ownerID:) as well as result (020847149) ... So what I need to do is get it to store in a table column of ownerID, but only store the ID # .. if that makes sense

[Usarjjaco]

Quick bump for my above post. Anyone have an idea?

[thescientist]

what if you have it just print out child?

foreach($xmlr->children() as $child)  {  echo $child . "<br />";  }

I'm not that sharp with sight reading XML code, so you might need to look up some XML DOM references for this. If that works, than storing the values in an array should get you all the values you need saved to one variable.