I need to extract saved information from a database table with the autocomplete function on "N" number of entries. That is, in each entry of a new row, insert a new data and continue repeating said function (autocomplete).
Example: The information contained in the variable availableTags and availableTags2 is the information I want to extract from the database table.
What would the command for such a function look like?
index.html
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>jQuery UI Autocomplete - Default functionality</title>
<link rel="stylesheet" href="//code.jquery.com/ui/1.13.2/themes/base/jquery-ui.css">
<link rel="stylesheet" href="/resources/demos/style.css">
<script src="https://code.jquery.com/jquery-3.6.0.js"></script>
<script src="https://code.jquery.com/ui/1.13.2/jquery-ui.js"></script>
<script>
$( function() {
var availableTags2 = ["1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24"];
var availableTags = ["ActionScript","AppleScript","Asp","BASIC","C","C ","Clojure","COBOL","ColdFusion","Erlang","Fortran","Groovy","Haskel","Java","JavaScript","Lisp","Perl","PHP","Python","Ruby","Scala","Scheme", "MySQL", "PostgreSQL"];
$( ".tags" ).autocomplete({
source: 'pl.php',
select: function (event, ui) {
var index = availableTags.indexOf(ui.item.value);
$("#" $(event.target).attr("data-target")).val(availableTags2[index]);
}
});
} );
</script>
</head>
<body>
<div >
<label for="tags">Tags:</label>
<input data-target="code1" name="name">
<label for="tags">Number:</label>
<input name="code" id="code1"><br><br>
<label for="tags">Tags:</label>
<input data-target="code2" name="name">
<label for="tags">Number:</label>
<input name="code" id="code2"><br><br>
<label for="tags">Tags:</label>
<input data-target="code3" name="name">
<label for="tags">Number:</label>
<input name="code" id="code3"><br><br>
</div>
</body>
</html>
pl.php
<?php
if (isset($_GET['term'])){
# conectare la base de datos
include('./dbcontroller.php');
$db_handle = new DBController();
$return_arr = array();
$sqlc = "SELECT * FROM pl WHERE availableTags like '%".$_GET['term']."%' LIMIT 5";
$faq = $db_handle->runQuery($sqlc);
foreach($faq as $k=>$v) {
/* Recuperar y almacenar en conjunto los resultados de la consulta.*/
$row_array['value'] = $faq[$k]['availableTags'];
$row_array['availableTags']=$faq[$k]['availableTags'];
$row_array['availableTags']=$faq[$k]['availableTags'];
$row_array['availableTags2']=$faq[$k]['availableTags2'];
array_push($return_arr,$row_array);
}
/* Codifica el resultado del array en JSON. */
echo json_encode($return_arr);
}
?>
dbcontroller.php
<?php
class DBController {
private $host = "localhost";
private $user = "root";
private $password = "";
private $database = "programming languages";
function __construct() {
$this->conn = $this->connectDB();
}
function connectDB() {
$conn = mysqli_connect($this->host,$this->user,$this->password,$this->database);
return $conn;
}
function runQuery($query) {
$result = mysqli_query($this->conn,$query);
while($row=mysqli_fetch_array($result)) {
$resultset[] = $row;
}
if(!empty($resultset))
return $resultset;
}
function insertQuery($query) {
mysqli_query($this->conn, $query);
$insert_id = mysqli_insert_id($this->conn);
return $insert_id;
}
function getIds($query) {
$result = mysqli_query($this->conn,$query);
while($row=mysqli_fetch_array($result)) {
$resultset[] = $row[0];
}
if(!empty($resultset))
return $resultset;
}
function numRows($query) {
$result = mysqli_query($this->conn, $query);
$rowcount = mysqli_num_rows($result);
return $rowcount;
}
}
?>
programming languages.sql
-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Servidor: 127.0.0.1
-- Tiempo de generación: 29-01-2023 a las 17:54:32
-- Versión del servidor: 10.4.25-MariaDB
-- Versión de PHP: 8.1.10
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = " 00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Base de datos: `programming languages`
--
-- --------------------------------------------------------
--
-- Estructura de tabla para la tabla `pl`
--
CREATE TABLE `pl` (
`availableTags` varchar(250) NOT NULL,
`availableTags2` int(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Volcado de datos para la tabla `pl`
--
INSERT INTO `pl` (`availableTags`, `availableTags2`) VALUES
('ActionScript', 1),
('AppleScript', 2),
('Asp', 3),
('BASIC', 4),
('C', 5),
('C ', 6),
('Clojure', 7),
('Cobol', 8),
('ColdFusion', 9),
('Erlang', 10),
('Fortran', 11),
('Groovy', 12),
('Haskel', 13),
('Java', 14),
('JavaScript', 15),
('Lisp', 16),
('Pearl', 17),
('PHP', 18),
('Python', 19),
('Ruby', 20),
('Scala', 21),
('Scheme', 22),
('MySQL', 23),
('PostgreSQL', 24);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Thanks.
HERE MY PROBLEM When I extract the data from the variables availableTags and availableTags2 in input, the corresponding number does not appear, only the name of the programming language.
CodePudding user response：
Here is an example of how to run an autocomplete on an array in PHP and MySQL: Create a table in the database to store the values you want to autocomplete on. Use PHP to connect to the database and query the table to retrieve the values. Use JavaScript to create an input field and bind an autocomplete function to it, passing the array of values from PHP to the JavaScript function. When the user starts typing in the input field, the autocomplete function will display a list of suggestions based on the values in the array that match what has been typed so far. Here's an example of the code:
<?php
$conn = mysqli_connect("localhost", "username", "password", "database");
$query = "SELECT field FROM table WHERE field LIKE '%".$_GET['term']."%'";
$result = mysqli_query($conn, $query);
$array = array();
while ($row = mysqli_fetch_array($result)) {
$array[] = $row['field'];
}
echo json_encode($array);
?>
JavaScript:
$(document).ready(function() {
$("input").autocomplete({
source: "autocomplete.php",
minLength: 2
});
});
CodePudding user response：
Here is an example of how to implement an autocomplete on an array using PHP, MySQL and jQuery:
PHP code to fetch data from the database:
<?php
// Connect to the database
$conn = mysqli_connect("hostname","username","password","database_name");
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// SQL query to fetch data
$sql = "SELECT * FROM table_name WHERE field_name LIKE '%".$_GET['term']."%'";
$result = mysqli_query($conn, $sql);
// Store the data in an array
$data = array();
while ($row = mysqli_fetch_array($result)) {
$data[] = $row['field_name'];
}
// Return the data as JSON
echo json_encode($data);
?>
jQuery code for autocomplete:
$(document).ready(function(){
$("input[name='field_name']").autocomplete({
source: "fetch.php",
minLength: 2
});
});
HTML code:
<input type="text" name="field_name">
Note: Replace hostname, username, password, database_name, table_name, and field_name with your own values in the above code.