combobox with large amount of data

[guidonts]

Hello everyone

 

I'm trying to fill in a combobox with a large amount of data (from a table containing about 100 000 names of patients).

 

I can fill the box but it takes about 20 seconds before I can see the first items.

 

Is there a way to do it very fast?

 

I was using Micrsoft Access and there it is no problem to see all the names at once.

 

Thank you very much.

 

Guido

[Chikwado]

You can do it with a programming supported to php.

[justsomeguy]

How are you creating the combobox? Is that using Javascript?

[guidonts]

I'm not using javascript, because I don't know how to do it.

 

this is the code I use

 

$sql="SELECT * FROM Patienten WHERE PATNR LIKE '$my_data%'";

$result = mysqli_query($conn,$sql) or die(mysqli_error());

if($result)

{

while($row=mysqli_fetch_array($result))

{

$naam=STR_PAD($row['PATNAAM'],20,".",STR_PAD_RIGHT);

$voornaam=STR_PAD($row['PATVNAAM'],20,".",STR_PAD_RIGHT);

echo $row['PATNR']."....".$naam.$voornaam."n";

$_SESSION["NAAM"]=$naam;

$_SESSION["PATNR"]=$row['PATNR'];

}

}

$my_data = the input in the field

Edited September 16, 2015 by guidonts

[justsomeguy]

How does that code relate to the combo box? That loops through database results and just prints the data one row per line, where is that getting printed to on the page? Where is the combo box code?

[guidonts]

I have changed it to an input field with this code

 

this is the input field:

 

<input name="tag" type="text" id="tag" size="60"/>

 

 

this is the script

 

 

<script>

$(document).ready(function(){

$("#tag").autocomplete("autocomplete.php", {

selectFirst: true

});

});

</script>

the autocomplete;php :

$q=$_GET['q'];

$my_data=mysqli_real_escape_string($conn,$q);

//$mysqli=mysqli_connect('localhost','root','','autofield') or die("Database Error");

$sql="SELECT * FROM Patienten WHERE PATNR LIKE '$my_data%'";

$result = mysqli_query($conn,$sql) or die(mysqli_error());

if($result)

{

while($row=mysqli_fetch_array($result))

{

$naam=STR_PAD($row['PATNAAM'],20,".",STR_PAD_RIGHT);

$voornaam=STR_PAD($row['PATVNAAM'],20,".",STR_PAD_RIGHT);

echo $row['PATNR']."....".$naam.$voornaam."n";

$_SESSION["NAAM"]=$naam;

$_SESSION["PATNR"]=$row['PATNR'];

}

}

?>

this fills my inputfield in about 15 seconds; but it is still too slow

Guido

[dsonesuk]

Do you really need to search ALL fields SELECT * FROM Patienten

[guidonts]

I need to select all the fields from the table.

 

There are just name, firstname and patientnumber

 

Is it better to specify the fields in stead of *?

[dsonesuk]

It will go though each of those fields, even though you want only to show 3, and since you have 100 000 imagine the possible time wasted going through these unwanted fields.

[guidonts]

it is faster with the fields by name in stead of * but it is still about 10-15 seconds

[dsonesuk]

It will alway be slow when working with so many records, remember access database works directly from your o/s, on the web its communicating browser to server, database and back. That is why most of the time you will see a pagination of 20 - 50 - 100 pages at a time.

[guidonts]

I have add "LIMIT 30" in my query and now it is what I want

 

Thank you all

Edited September 17, 2015 by guidonts