Is there a way to split the field values pulled from a db table?

[sfraise]

I'm trying to add ui autocomplete functionality to some fields in a form and am running into a few issues.

The first is I want to be able to both add and pull multiple values from the one field. I had originally thought I could comma delimit the values and handle it that way, but it seems it's a bit more complicated than that. I've tried to explode the value but it doesn't go far enough, I think I need to find a way to refine the array down by trying to replace the comma in the code somehow.

 

Here's my current php code:

<?php

//connection information
$host = "localhost";
$user = "myuser";
$password = "mypass";
$database = "mydb";
$param = $_GET["term"];

//make connection
$server = mysql_connect($host, $user, $password);
$connection = mysql_select_db($database, $server);

//query the database
$query = mysql_query("SELECT cb_activities FROM jos_comprofiler WHERE cb_activities REGEXP '^$param'");

//build array of results
	for ($x = 0, $numrows = mysql_num_rows($query); $x < $numrows; $x++) {
	$row = mysql_fetch_assoc($query);
	$activities[$x] = array(cb_activitiesterm => $row[cb_activities]);	
}

//echo JSON to page
$response = $_GET["callback"] . "(" . json_encode($activities) . ")";
echo $response;

mysql_close($server);

?>

 

Someone else had mentioned maybe using something like this:

$string="([{\"cb_activitiesterm\":\"Kicking Cats, Working\"},{\"cb_activitiesterm\":\"Web Development\"}])";

$one=str_replace("([{\"cb_activitiesterm\":\"","",$string);
$two=str_replace("\"},{\"cb_activitiesterm\":\"",",",$one);
$three=str_replace("\"}])","",$two);

echo $three;

// now you can explode it ... by commas
$items=explode(",",$three); 

But of course the problem here is he's creating the string in the php file and adding the /'s when in reality this needs to be pulled from the database and then refined.

 

The values are currently formatted like this:

([{"cb_activitiesterm":"Kicking Cats, Working"},{"cb_activitiesterm":"Web Development"}])

Exploding simply adds a "" around the comma so it won't work.

I need it to format like this:

([{"cb_activitiesterm":"Kicking Cats"},{"cb_activitiesterm":"Working"},{"cb_activitiesterm":"Web Development"}])

 

My second issue is I need to be able to pass multiple values into the hidden input in the script and adding a comma/space between each value. Currently if you click another value it simply replaces the prior value with the next one.

Here's my script:

	<div id="editcbprofile">
	<div id="formWrap">
<div id="activities" class="ui-helper-clearfix">
<input id="cb_activitiesterm" type="text">
<input id="cb_activities" name="cb_activities" type="hidden">
				</div>
	</div></div>
	<script type="text/javascript" src="autocomplete/js/jquery-1.4.2.min.js"></script>
	<script type="text/javascript" src="autocomplete/js/jquery-ui-1.8.custom.min.js"></script>
	<script type="text/javascript">
		$(function(){

			//attach autocomplete
			$("#cb_activitiesterm").autocomplete({

				//define callback to format results
				source: function(req, add){

					//pass request to server
					$.getJSON("autocomplete/autocom.php?callback=?", req, function(data) {

						//create array for response objects
						var suggestions = [];

						//process response
						$.each(data, function(i, val){								
							suggestions.push(val.cb_activitiesterm);
						});

						//pass array to callback
						add(suggestions);
					});
				},

				//define select handler
				select: function(event, ui) {

					//create formatted activity
					var activity = ui.item.value,
						span = $("<span>").text(activity),
						a = $("<a>").addClass("remove").attr({
							href: "javascript:",
							title: "Remove " + activity
						}).text("x").appendTo(span);

		//add interest to activities div		          
                        span.insertBefore("#cb_activitiesterm");	
                                
                  //add activity to hidden form field
             $('input[name=cb_activities]').val(activity);
				},

				//define select handler
				change: function() {

					//prevent 'to' field being updated and correct position
					$("#cb_activitiesterm").val("").css("top", 2);
				}
			});

			//add click handler to activities div
			$("#activities").click(function(){

				//focus 'to' field
				$("#cb_activitiesterm").focus();
			});

			//add live handler for clicks on remove links
			$(".remove", document.getElementById("activity")).live("click", function(){

				//remove current activity
				$(this).parent().remove();

//add activity to hidden form field
             $('[name=cb_activities]').val().remove();

				//correct 'to' field position
				if($("#activities span").length === 0) {
					$("#cb_activitiesterm").css("top", 0);
				}				
			});				
		});
	</script>

 

And then my 3rd issue is if you remove a value it needs to also remove it from the hidden input. I've tried several attempts of the remove() in the onclick function but I can't get it to remove it.

 

Lol other than that it's cherry.

Any advice on any of these would be great.