Best way to update a string with mysql in a foreach loop

Question

i have to update two different tables when submitting a form.

first one is a string containing all hotels information for each posted date

$index = 0;
$insert ="";

foreach($_POST['day'] as $index => $day) {

    $day = $day; 
    $name = $_POST['name'][$index];
    $sgl = $_POST['sgl'][$index];
    $dbl = $_POST['dbl'][$index];
    $nights = $_POST['nights'][$index];
    $status = $_POST['status'][$index];
    $ref = $_POST['ref'][$index];
    $breakfast = $_POST['breakfast'][$index];
    $meal = $_POST['meal'][$index];

    $insert .= "$day|$name|$sgl|$dbl|$nights|$status|$ref|$breakfast|$meal;";       

}

$data['details_accommodation'] = $insert;

This code works ok to update the first table.

i need then to isolate each $day (mysql date) and update a second year table with the same string where $day matches the corresponding date. and i'm stuck. hope i'm clear enough with mu problem !

[Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574) — eggyal, Dec 26 '12 at 14:49
What would you do with 200 hotels reference on multiple years tables ? having these strings reduces the size of the database and because there is no open field it's normalised. i then use an explode to print out the datas. — shad0wproxy, Dec 26 '12 at 14:58
You seem to have fallen for [the XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). It's impossible to give a good answer to this question as it currently stands, because it's not at all clear what is the *actual business problem* you're trying to solve; all I can say with reasonable certainty is that your current approach is fraught with difficulties. — eggyal, Dec 26 '12 at 15:04
i need to update an allotment table with name of hotel and number of SGL and DBL room for each given date. my allotment table has one field for each day of a year and one text column to receive the string. as i can't create 200 columns or 200 tables, one for each hotels, i don't see any solution than that. unless you have a better idea ! — shad0wproxy, Dec 26 '12 at 15:11

score 0 · Answer 1 · edited Dec 26 '12 at 16:18

0

Instead of Inserting DAY, try to insert UNIX timestamp, in both tables. Hope this resolves your problem

edited Dec 26 '12 at 16:18

HemChe

2,249
1
21
33

answered Dec 26 '12 at 15:52

Allahbakash.G

1,805
1
15
17

score -1 · Answer 2 · answered Dec 26 '12 at 15:01

-1

I'd much rather use arrays, and the implode function to join the values, rather than string concatenation. It gives far more flexibility with the values then, and no trailing join value set with the final loop.

My advice: Gather your your data into multiple arrays indexed by "$index", e.g: $refs[$index] = $value

Then use the implode function to do your concatenation values: $string = implode(',', $refs);

This will then simplify data arrays for you to manipulate and save. Hope this helps!

answered Dec 26 '12 at 15:01

Dan Belden

1,199
1
9
20

yes thanks. its easier to manipulate but considering im working on a shared database i'm concerned with the resources taken by array against concatenation. anyway i figured a simpler way: i will consider updating allotments separately each hotel by each hotel for a given month. – shad0wproxy Dec 26 '12 at 18:18

Best way to update a string with mysql in a foreach loop

2 Answers2