Get data from a mysql database to use in jquery-ui autocomplete

Question

I have an input form and I need to populate the autocomplete with data from a database. My approach seems to be working BUT I am pretty sure it's not the most efricient way. (I'm new to php, javascript) Here's my code:

//get all organizations
$org_array = '';
$sql = "SELECT id, name FROM organizations";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$results = $stmt->fetchall(PDO::FETCH_ASSOC);
if (count($results) > 0) {
foreach ($results as $row) {
    $sql = "SELECT id, name FROM organizations WHERE id = :id";
    $stmt = $pdo->prepare($sql);
    $id = $row['id'];
    $stmt->bindValue(':id', $id);
    $stmt->execute();
    $org_id = $stmt->fetch(PDO::FETCH_ASSOC);
    //echo $org_id['name'];
    $org_array = $org_array . '"' . $org_id['name'] . '",';
}
}

And here is the JS part:

<input type="text" class="form-control autocompleteOrgs" name="newOrganization" id="newOrganization" aria-describedby="newOrganizationHelp" value="<?php echo 
$my_profile['organization']; ?>">

<script>
$(function() {
  var orgTags = [<?php echo $org_array ?>];
  $(".autocompleteOrgs").autocomplete({
    source: orgTags
  });
});
</script>

If the code works and you just want to know if there is a better way, you should post it in Code Review instead. — M. Eriksson
– M. Eriksson, Commented Feb 23, 2019 at 17:56

Andrii Filenko · Accepted Answer · 2019-02-23 18:30:34Z

1

You don't need to fetch organizations one more time, better approach would be:

$org_array = '';
$sql = "SELECT id, name FROM organizations";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$results = $stmt->fetchall(PDO::FETCH_ASSOC);
if (count($results) > 0) {
    $org_array = implode(',', array_column($results, 'name'));
}

answered Feb 23, 2019 at 18:30

Andrii Filenko

9847 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get data from a mysql database to use in jquery-ui autocomplete

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related