Array

[Macko888]

Hey Guys...

Brain is kind of dead on a late thursday afternoon in Cape Town,

Can anybody please assist...

what i am trying to do is:

1) in a table i have a column that is an array, its all in plain text
2) i have a table that has those names with id's
3) i want to get all values of the text to be id's, then repopulate the array and insert back into the table,

so instead of have and array like (word1,word2,word3) i want to take out $word1 replace it with an ID eg.3 and so on for the rest of the array for each record.
so then i would have (1,568,771) obviously.

The //*** part i have error handled and it shows that the array is populating with correct info according to the ID, the 2nd part my brain seems to be bummed!

dbconn() is my mysql_connect function

Please help fellow Developers!

<?php
$sql = "SELECT CandidateID, UserName, Categories FROM Candidates WHERE Categories != ''  ORDER BY CandidateID";
$rs = dbconn($sql);
$row = mysql_fetch_array($rs);
$rows = mysql_num_rows($rs);

       for ($f=0;$f<$rows;$f++){
    $array_cat = array(mysql_result($rs,$f,"Categories"));
    foreach ($array_cat as $ar){
    //*** echo mysql_result($rs,$f,"CandidateID") . $ar; ***
    $sql2 = "SELECT * FROM Can_Categories_List WHERE CategoryName =  '".$ar."' ";
    $rs2 = dbconn($sql2);
    $row2 = mysql_fetch_array($rs2);
    $rows2 = mysql_num_rows($rs2);
    for ($j=0;$j<$rows2;$j++){
    echo mysql_result($rs2,$j,"CategoryID") . "-" .mysql_result($rs,$f,"CandidateID") . "<br />";
                    }
                    }}

?>

[qazplm114477]

If you have data in the database that's saved as a string PHP will still read it as a string even if the data is written as an array. If you have data in the database written like this 'word1, word2, word3' then you can always use the explode function to turn it into an array ex:

$array = explode(',', 'word1, word2, word3');
echo $array[0]; //output word1
//you can always assign it to a variable right away with the list function
list($word1, $word2, word3) = explode(',', 'word1, word2, word3');
echo $word2; //output word2

So if i get you right: You want to search for an array item in a field and replace the word with an id value that corresponds to that word in another table? THis may not be as simple as it seems as you may have "words within words", e.g.

bat and batman. If you decide to replace 'bat' with corresponding id number 5, you'd get 5 and 5man. Perhaps it would be an idea to do an desc. order by word length on the id/word table. Get the id/word pairs from sql and loop over an sql query (an update query - replace the word if found with the id).

[Macko888]

ardav

thats exactly what is happening, I have a "B: A" and a "B: Accountant" and it does replace the B:A with 5 and 5ccountant.

Thanks for the suggestions guys, will try them as soon as i am at my desk

[arunmagar]

Hi ,
As you provide your problem on that basis
I have created code snippet, Please change wherever necessary.
This wil hep you and hope this is you want.....

<?php

$con = mysql_connect("localhost","username","pwd");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("databaseName", $con);

$sql = "SELECT CandidateID, UserName, Categories FROM Candidates WHERE Categories != '' ORDER BY CandidateID";

$result = mysql_query($sql);

while($row = mysql_fetch_array($result))
  {

 $CandidateID=$row['CandidateID'];
 $UserName=$row['UserName'];
$Categories=$row['Categories'];

$Categories_arr=explode(',', $Categories);   // Please use seprator whatever is in you database Categories column
$cnt=0;
$catstr='';
foreach ($Categories_arr as $value)
  {
  	$sql1 = "SELECT * FROM Can_Categories_List WHERE CategoryName = '$value'";

	$result1 = mysql_query($sql1);
	$cat_res=mysql_fetch_assoc($result1);
	$cat_id=$cat_res['CategoryID'];
	if($cnt==0)
	{
	$catstr=$cat_id;
	$cnt=1;
	}
	else
	{
		$catstr=$catstr.",".$cat_id;
	}
  } 
// this is your final category id string	$catstr
// now use update query for   $CandidateID

//  update Candidates set   Categories='$catstr' where CandidateID='$CandidateID'

  }


?>

[Macko888]

@arunmagar

your snippet works like a charm... thanks man! much appreciated!

I know this thread is solved, but I thought I'd offer another solution:

$result = mysql_query("SELECT CategoryID, CategoryName FROM Can_Categories_List ORDER BY length(CategoryName) DESC, CategoryName");
while($rows = mysql_fetch_array($result)){
  $update = mysql_query("UPDATE Candidates SET Categories = REPLACE(Categories, '{$rows['CategoryName']}', '{$rows['CategoryId']}'");
}

I haven't tested this, but that's how I'd approach it. I don't think you have to explode and implode/concatenate.