Multiple array insert into database

Question

I'm using the following arrays:

$name = array("Veldspar","Scordite","Pyroxeres","Plagioclase","Omber","Kernite","Jaspet","Hemorphite","Hedbergite","Gneiss","Dark Ochre","Crokite","Spodumain","Bistot","Arkanor","Mercoxit");
$typeids= array(1230,1228,1224,18,1227,20,1226,1231,21,1229,1232,1225,19,1223,22,11396);
$url="http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=".join('&typeid=',$typeids);
$pricexml=file_get_contents($url);
$xml=new SimpleXMLElement($pricexml);

foreach($typeids as $typeid)
{
    $item=$xml->xpath('/evec_api/marketstat/type[@id='.$typeid.']');
    $price= (float) $item[0]->buy->max;
    $price=round($price,2);

   $query1 = "INSERT INTO data (Price) VALUES ('$price');";

   $q1 = mysqli_query($conn,$query1) or die ('Error posting data');
    echo $typeids[$index].$name[$index].$price[$index]."\n";
}
foreach($typeids as $index => $value)
{
   $query = "INSERT INTO data (typeID, Name) VALUES ('$typeids[$index]','$name[$index]');";

   $q = mysqli_query($conn,$query) or die ('Error posting data');
    echo $typeids[$index].$name[$index]."\n";

}

Its for a game I play, Eve online. What I'm trying to accomplish is get the price/itemID/Name of the item and inject that into my database. I can get all 3 items between my 2 foreach loops. However when I go to put it in the database its creating 2 sets of rows.

+--------+-------------+-------+
| typeID | Name        | Price |
+--------+-------------+-------+
|      0 |             |    15 |
|      0 |             |    27 |
|      0 |             |    55 |
|      0 |             |    58 |
|      0 |             |    91 |
|      0 |             |   227 |
|      0 |             |   434 |
|      0 |             |   740 |
|      0 |             |   708 |
|      0 |             |   914 |
|      0 |             |  1505 |
|      0 |             |  3202 |
|      0 |             |  1600 |
|      0 |             |  2900 |
|      0 |             |  3180 |
|      0 |             | 11800 |
|   1230 | Veldspar    |     0 |
|   1228 | Scordite    |     0 |
|   1224 | Pyroxeres   |     0 |
|     18 | Plagioclase |     0 |
|   1227 | Omber       |     0 |
|     20 | Kernite     |     0 |
|   1226 | Jaspet      |     0 |
|   1231 | Hemorphite  |     0 |
|     21 | Hedbergite  |     0 |
|   1229 | Gneiss      |     0 |
|   1232 | Dark Ochre  |     0 |
|   1225 | Crokite     |     0 |
|     19 | Spodumain   |     0 |
|   1223 | Bistot      |     0 |
|     22 | Arkanor     |     0 |
|  11396 | Mercoxit    |     0

I've tried combing all 3 things into 1 foreach loop but it wasn't honoring the API call to get the price, which is why I resulted to 2 different foreach loops.

My current solution in mind is to make it so the rows are combined, is there some sort of SQL command I can run for the price foreach loop that it will just add it to the top row and go down?

INSERT always creates a new row. If you use two loops, the second one needs to use UPDATE to modify the rows created in the first loop. — Barmar
– Barmar, Commented Mar 20, 2014 at 4:13
thanks for that info, I ended up doing a newb method and truncating the table before each update, I'll try the update run instead — Skiedude
– Skiedude, Commented Mar 20, 2014 at 6:31

Barmar · Accepted Answer · 2014-03-20 04:16:17Z

1

I think this should do it in one loop:

foreach($typeids as $index => $typeid)
{
    $item=$xml->xpath('/evec_api/marketstat/type[@id='.$typeid.']');
    $price= (float) $item[0]->buy->max;
    $price=round($price,2);

    $query1 = "INSERT INTO data (typeID, Name, Price) VALUES ('$typeid', '$name[$index]', '$price');";

    $q1 = mysqli_query($conn,$query1) or die ('Error posting data');
    echo $typeids[$index].$name[$index].$price[$index]."\n";
}

answered Mar 20, 2014 at 4:16

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Skiedude Over a year ago

Wow! I swear I tried that and it didn't work, however I was using [index] on all of them so thats where I went wrong. Thanks for speedy quick reply

Collectives™ on Stack Overflow

Multiple array insert into database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related