Array update

[vjava]

I have imported a flat-file database into mysql.  Some of the tables have arrays with values such as "a||b||c" or "a||g||s" where a, b, c, g and s are keys for the real values, i.e. apple, boy, cat, girl and snake.  I want to update my database to replace the coded array "a||b||c" to "apple||boy||cat".  I am not sure how to go about doing this.  I believe I may have to use the explode, implode and case functions, but don't know where to start.  Any help would be greatly appreciated.

TIA

[Psycho]

A little more information is necessary here. How are these arrays stored in the database? Where do youwant to put these values? If you have an array of values in a single field and you want to break them up you would either need to create multiple records int he same table (one for each value) or create a separate table for these values and link them to the original record. Which you do would depend on several factors.

Could you give the scheme for one of the tables in question with some sample data and explain what you want to end up with?

[vjava]

I have a table called features with the following columns
id        field_name        field_value
1        indoor              b||tt||sk
2        outdoor            s||tn||p

I have an excel spreadsheet that gives the real values for the field_value codes, i.e., b = badminton, tt = table tennis, tn = tennis, etc.  When I display the array values in html, it shows the codes and I would rather have the arrays updated with the real values rather than codes.  Some of the codes such as p (pool) are common for both the indoor and outdoor field_names columns.

Thanks for your prompt response.

[zq29]

I think you could probably do this with the str_replace() function...
[code]<?php
$r = mysql_query("SELECT `id`,`the_field` FROM `the_table`") or die(mysql_error());
$search = array("a","b","c","g","s");
$replace = array("apple","boy","cat","girl","snake");
while($rr = mysql_fetch_assoc($r)) {
    $new = str_replace($search,$replace,$rr['the_field']);
    mysql_query("UPDATE `the_table` SET `the_field`='$new' WHERE `id`='$rr[id]'") or die(mysql_error());
}
?>[/code]
I'm not sure if subsequent search/replacements will overwrite each other though, I wouldn't have thought so if they were used in the same function call... If they do overwrite each other, you will have to go with the loop/explode/switch/implode method.

[vjava]

Thanks for your response.  I tried the str_replace function and it works well on the first go.  But as I update the database and add more fields, the str_replace function replaces values again. For example existing fields with "Apple||Boy||Cat| now are "Applepple||Boyoy||Catat"

Is there a way to make the str_replace function only find unique A, B and C values.

TIA

[sasa]

try[code]<?php
$search = array("a","b","c","g","s");
$replace = array("apple","boy","cat","girl","snake");
$a = array_combine($search,$replace);
$r = mysql_query("SELECT `id`,`the_field` FROM `the_table`") or die(mysql_error());
while($rr = mysql_fetch_assoc($r)) {
$r = explode('||',$rr['the_field']);
$out = array();
foreach ($r as $d) if (array_key_exists($d,$a)) $out[]=$a[$d]; else $out[]=$d;
$out = implode('||',$out);
    mysql_query("UPDATE `the_table` SET `the_field`='$out' WHERE `id`='$rr[id]'") or die(mysql_error());
}
?>[/code]

[vjava]

sasa,
Thanks for your reply.  I tried the implode explode approach, but am getting the following error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/tp/html/update.php on line 8

TIA

[zq29]

[quote author=vjava link=topic=120709.msg495617#msg495617 date=1167774272]
sasa,
Thanks for your reply.  I tried the implode explode approach, but am getting the following error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/tp/html/update.php on line 8

TIA
[/quote]Most likely that your query is failing, double check that it is correct.

[vjava]

I wanted to thank all of you for your help.  The array_combine functions works great!