asked on

How to display JSON data properly within an auto fill text box with PHP and AJAX

Hello everybody, hope you had a wonderful start for 2019.

I'm having problems with a jQuery/AJAX + PHP autofiller. I'm trying to fetch all the posibble results for the users in my DB, no matter what the user search for (could be username, name or last name). So far I've managed to fetch everything from my DB using PHP with the following script:

$sql
=
"
SELECT user_id, apellido, primer_nombre, nombre_usuario
FROM dbo.v3_usuarios
WHERE (nombre_usuario LIKE :parte_usuario)
OR (primer_nombre LIKE :parte_usuario2)
OR (apellido LIKE :parte_usuario3)
"
;

try
{
	
	$parte_usuario = '%' . strval($parte_usuario) . '%';
	
	$core = Core::getInstance();
	$stmt = $core->dbh->prepare($sql);
	$stmt->bindParam(':parte_usuario', $parte_usuario, PDO::PARAM_STR);
	$stmt->bindParam(':parte_usuario2', $parte_usuario, PDO::PARAM_STR);
	$stmt->bindParam(':parte_usuario3', $parte_usuario, PDO::PARAM_STR);
	
	if ($stmt->execute())
	{
		
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		
		$usuarios = array();
		
		foreach ($result as $key => $value) {
			
			array_push($usuarios, $value['user_id'], $value['apellido'], $value['primer_nombre'], $value['nombre_usuario']);
			
		}
		
		echo json_encode($usuarios);
		
	} else { echo 'Couldn\'t connect to DB'; }

	$stmt = null;
	$core = null;

} catch(PDOException $e) { echo $sql . "<br>" . $e->getMessage(); }

Open in new window

And I've managed to fetch it using AJAX with:

$('#usuario-buscar').typeahead({
	source: function (query, result) {
		$.ajax({
			url: "somepage.php",
			data: 'query=' + query,            
			dataType: "json",
			type: "POST",
			success: function (data) {
				// console.log(data);
				result($.map(data, function (item) {
					return item;
					//console.log(item);
				}));
			}
		});
	}
});

Open in new window

This is taken from an example in phppot.com and I can make it work with only one field, but I would like to show the results like "USERNAME - Lastname Fisrtname".

Any chance to get help with this? All the tries I've made repeat tens of times each result in the "dropdown" it creates for the textbox.

BTW the user_id, while I don't want it displayed in the auto fill dropdown, will be used to populate some hidden text box or something for the form to grab.

Thanks in advance!

David Favor

How you do this varies based on your data.

What you're looking for sounds like the equivalent of the JSON Formatter site, which unpacks JSON + formats it into pretty, human, readable form.

There are a variety of GitHub projects + other code libraries which do this.

https://github.com/marianoguerra/json.human.js turned up quickly for the search - github javascript json human readable - along with 300K other results.

ASKER CERTIFIED SOLUTION

Zakaria Acharki

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cesar Aracena

ASKER

Thanks Zakaria! Your answer led me to the following "success" function:

success: function (data) {
	result($.map(data, function (item) {
		return(item['apellido'] + ' ' + item['primer_nombre'] + ' - ' + item['nombre_usuario']);
		// $('#usuario-buscar-id').val(item['user_id']);
	}));
}

Open in new window

Now, as you can see in the commented line, I need to write the selected "user_id" to another text box. How can I do that? For the sake of easiness I included it not only as the key for the array, but also inside the array as an object.

Zakaria Acharki

You're welcome,

Your line must work if you put it before the return statement, but the field usuario-buscar-id will always hold the last item['user_id'] from the returned data, is that what you want?