MartinB MartinB - 2 months ago 11
MySQL Question

how to create several autocomplete fields with html5 and mysql

I want to create a simple html input page to enter data into mysql db for later use. In order to save time on input I would like to have some fields as autocomplete fields.
I found a script here and after adjusting some values it looks like this

<?php
//connect to mysql database
$connection = mysqli_connect("localhost","user","password","autocomplete_test")
or die("Error " . mysqli_error($connection));

//fetch data from database
$sql = "select distinct First_Name from data";
$result = mysqli_query($connection, $sql) or die("Error " . mysqli_error($connection));

?>
<!DOCTYPE html>
<html>
<head>
<title>Autocomplete Textbox in HTML5 PHP and MySQL</title>
</head>
<body>
<label for="fname">First Name</label>
<input type="text" list="firstname" autocomplete="off" id="fname">
<datalist id="firstname">
<?php while($row = mysqli_fetch_array($result)) { ?>
<option value="<?php echo $row['First_Name']; ?>"><?php echo $row['First_Name']; ?></option>
<?php } ?>
</datalist>
<?php mysqli_close($connection); ?>
</body>
</html>


This works great for one field but when I try to add a second field I cannot figure out how to make the second field autocomplete too. In my example I want a second field for 'Last_Name'. It's in the database and I can call all values for First_Name and Last_Name with while and echo as a simple display of what's there before the html part.
Whatever I try I get autocomplete for first field or for second field or just the first entry from db for both fields.
Please help. Thanks.

Answer

There's nothing stopping you from simply querying the database twice, and outputting two inputs:

<?php
//connect to mysql database
$connection = mysqli_connect("localhost","user","password","autocomplete_test")
or die("Error " . mysqli_error($connection));

//fetch data from database
$sql1 = "select distinct First_Name from data";
$result1 = mysqli_query($connection, $sql1) or die("Error " . mysqli_error($connection));
$sql2 = "select distinct Last_Name from data";
$result2 = mysqli_query($connection, $sql2) or die("Error " . mysqli_error($connection));

?>
<!DOCTYPE html>
<html>
<head>
<title>Autocomplete Textbox in HTML5 PHP and MySQL</title>
</head>
<body>
<label for="fname">First Name</label>
<input type="text" list="firstname" autocomplete="off" id="fname">
<datalist id="firstname">
    <?php while($row = mysqli_fetch_array($result1)) { ?>
        <option value="<?php echo $row['First_Name']; ?>"><?php echo $row['First_Name']; ?></option>
    <?php } ?>
</datalist>
<label for="lname">Last Name</label>
<input type="text" list="lastname" autocomplete="off" id="lname">
<datalist id="lastname">
    <?php while($row = mysqli_fetch_array($result2)) { ?>
        <option value="<?php echo $row['Last_Name']; ?>"><?php echo $row['Last_Name']; ?></option>
    <?php } ?>
</datalist>
<?php mysqli_close($connection); ?>
</body>
</html>  
Comments