Stelio Kontos Stelio Kontos - 7 days ago 5
Ajax Question

php : insert into database with ajax

actually working on Ubuntu (working PHP language),
I have a PDF file that I convert into text, then I preg_match in order to extract the data I need.

After this, I put my data lines into a drop-down list.

PROBLEM : I want, using Ajax (as far as I understood), to get the selected option and save it into my database.

I've read many topic about this issue, in vain...

Here's a piece of my code, it may be more concret !

My PHP File :

$file = fopen($fichier_txt, 'r+');

if ($file)
{
$lines = array();
$pattern_GC = '/N°.*\d{4}(\s?\s?[\w\s]*)(\d{5})\s?(\w+)\W+/isU';
$fichier_txt_content = file_get_contents($fichier_txt);
$regex_GC = preg_match_all($pattern_GC, $fichier_txt_content, $match_GC);
// Match regroupant nom/prenom + adresse
$match_un = explode(PHP_EOL, $match_GC[1][0]);
$match_nom_prenom = $match_un[2];
$match_adresse = $match_un[3];
// Match CP
$match_deux = $match_GC[2][0];
// Match ville
$match_trois = $match_GC[3][0];

$opt = array($match_nom_prenom, $match_adresse, $match_deux, $match_trois);
$i = 0;?>
<html>
<form>
<select name="selectBox" class="drop" id="Combobox1" onchange="saveToDatabase(this.value)">
<?php foreach($opt as $val) {?>
<option value="$opt[$i]"><?=$val?></option>
<?php } ?>
</select>
</form>
</html>


My formulaire_2_test.php file :

<?php
// Database connection to save form lines (PDO)
try
{
$PDO = new PDO('mysql:host=localhost;dbname=autofill_data', 'root', 'password');
}
catch (Exception $e)
{
die('Erreur : ' . $e->getMessage());
}

// Get selected option and save into database
$selectedOpt = $_POST['selected'];
//exit($selectedOpt); --> I put this line in comments since I don't use it everytime.


$req = $PDO->prepare("INSERT INTO data_lines(idDistributeur, numLigne, libelle) VALUES(1, 2, :selectedOpt)");
$req->bindParam(':selectedOpt', $selectedOpt);
$req->execute($selectedOpt);
$data = $req->fetchAll();
}

?>


And now here is my Ajax script (i'm new in JS and I know some enormeous mistakes may pop in front of you, sorry about that, and about my french naming...)

Ajax : (located in my php file)







<style>
.ui-autocomplete
{
cursor:pointer;
height:120px;
overflow-y:scroll;
}
</style>

<script>

function saveToDatabase(selectedValue)
{
var select = selectedValue;
select = $(this).serialize();
$('#Combobox1').on("change", function ()
{
// POST to php script
$.ajax
({
type: 'POST',
url: 'formulaire_2_test.php',
data:{selected:this.value}
}).then(function(data){alert(data)});
});
}

$(document).ready(function()
{
saveToDatabase();
});

</script>


I tested my PDO connection with rough values, and it does work, but I wonder how I could pass my php variable into it (I'm not sure about using $_POST to retrieve this data since I don't refresh any page...)
I also tried with INSERT into table VALUES(:name, 2, 3) but it didn't work either...
Am I heading in the right direction ?
How would you consider doing this ?

PS : My next step after this would be to remove the selected option from the following dropdown lists (in order to save the user some precious minutes as he fills a subscription form).

EDIT Nov 24th : I need my "Fais ton choix" option to appear on my dropdown as a default value, but not in the list options : autofill

My last issue : I want to remove the selected option of the dropdown, so it won't appear in another following dropdown.
Here's the code I tried (doesn't work) :

function removeSelected(value)
{
$('.drop').change('select', function ()
{
// Definition des variables
var value = this.value;
var id = this.id;
// We del selects with a != id containing options with the same value of the selected one.
$("select:not(#" + id + ") option[value='" + value + "']").hide()
});
}


I also tried with .remove() instead of .hide() without success !

Thanks in advance,

Regards,

Stelio Kontos.

Answer

Put the PHP code that follows this comment: // Database connection to save form lines (PDO) into a different file. From your jQuery ajax function, set the url to this new PHP file. Also change data: 'selected=' + select to data: {selected: select}.

Now in your PHP file (the new one) set $selectedOpt = $_POST['selected'];.

The last bit of your PHP code should look this this:

$req = $PDO->prepare("INSERT INTO data_lines(idDistributeur, numLigne, libelle) VALUES(1, 2, :selectedOpt)");
$req->bindParam(':selectedOpt', $selectedOpt);
$req->execute();

Edit: javascript fixes

Your JS should be:

$(document).ready(function () {
    $('#Combobox1').on('change', function () {
        $.ajax({
            method: 'POST',
            url: 'save-selection.php',
            data: {
                // this is a reference to the select box, which means
                // this.value is the value of the select box
                selected: this.value
            }
        }).then(function (data) {
            alert(data);
        });
    });
});

Regarding your updated requirement, you can just add $(this).children(':selected').remove(); under your ajax call. No need for another change listener. However, when a user selected an option, it will instantly remove it so the select box will only ever show the first option. Try it and you will see what I mean.

Comments