asked on insert ID of database value into database instead of text value from text field
I had this question after viewing Getting search results to display ul for text field being inputted into.
I am having a hard time trying to figure this out. If I INSERT the data into the database using a foreach loop, it stores the actual text values of the search into the database. I would rather use the ID.
To try explain better, I have a table with reasons for lost time e.g.:
id reason
1 watching movies
2 Sleeping
3 personal emails
When clicking on the search results it enters the text reason into the text field which is correct. But when submitting to the database it should insert the id number and not the text that is displayed.
This ID can only be obtained after the ajax call has been made and the database has been queried, just like the text value.
The php part for the ajax call echo's out the <li> where I have attempted to ad the ID for that record.
And here is the existing jQuery:
I tried to create a variable with the id in it and console log that out when clicking on one of the <li>'s but it gives me an error, '$lostId is not defined. '
I am having a hard time trying to figure this out. If I INSERT the data into the database using a foreach loop, it stores the actual text values of the search into the database. I would rather use the ID.
To try explain better, I have a table with reasons for lost time e.g.:
id reason
1 watching movies
2 Sleeping
3 personal emails
When clicking on the search results it enters the text reason into the text field which is correct. But when submitting to the database it should insert the id number and not the text that is displayed.
This ID can only be obtained after the ajax call has been made and the database has been queried, just like the text value.
The php part for the ajax call echo's out the <li> where I have attempted to ad the ID for that record.
while($row = $result->fetch_assoc()) {
$lost_time_desc = sanitize($row['lost_time_desc']);
$lostId = sanitize($row['lost_time_reason_id']);
echo "<li data-id='".$lostId."'>" . $lost_time_desc. "</li>";
}
And here is the existing jQuery:
$(function() {
$("input").keyup(function() {
// GET A REFERENCE TO THE <input> THAT TRIGGERED THE EVENT
var jqo = $(this);
// GET THE VALUE
var input = jqo.val();
jqo.addClass('loader').prop({disabled: false});
// FIND THE result <div> FOR THIS <input>
var parent = jqo.closest('div');
var result = parent.find('.result');
if (input.length > 3) {
// jqo.addClass('loader').prop({disabled: true});
// GET DATA FROM SERVER
$.ajax({
type: 'POST',
url: 'lost-units-search.php',
data: {
name: input
}
})
// .success IS ON ITS WAY OUT
// I PREFER promise INTERFACE
.done(function(data) {
// data IS PLAIN HTML SO ADD IT TO OUR
// result WINDOW
result.html(data);
result.data('input', jqo);
jqo.removeClass('loader').prop({disabled: false});
})
}
// THE REST IS THE SAME
if (input.length < 1) {
result.html("");
jqo.removeClass('loader').prop({disabled: false});
}
});
$('.result').on('click', function(evt) {
$(this).data('input').val(evt.target.textContent);
this.textContent = '';
});
});
I tried to create a variable with the id in it and console log that out when clicking on one of the <li>'s but it gives me an error, '$lostId is not defined. '
var $lostId = $(this).data('lost-id');
PHPAJAX
Last Comment
Julian Hansen
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Your jQuery seems to be code linked to a search function not the <li> click?
ASKER
@ Julian,
Sorry, you've lost me there. When you start typing in the input, an ajax call is made whereby it searches the database for anything LIKE what you are typing. The <li> then appear and whatever I click on ends up in the text field which is 100% what I want. The jQuery code is pretty much all your code from the previous question :)
I am probably just not understanding your question.
Sorry, you've lost me there. When you start typing in the input, an ajax call is made whereby it searches the database for anything LIKE what you are typing. The <li> then appear and whatever I click on ends up in the text field which is 100% what I want. The jQuery code is pretty much all your code from the previous question :)
I am probably just not understanding your question.
ASKER
@ Ray,
Yeah, I think we did discuss a similar idea previously where I was using drop downs which was easier because I could put the database id as the value in a option tag inside the select element. The thing that put me off doing that was the fact that there will be approximately 40 options to choose from and if 3-4 are generated, then the user has to go through 3 or 4 sets of dropdown menus with 40 odd records each!
That is why I want to use this ajax search approach instead. I will drive people crazy with drop downs!
Yeah, I think we did discuss a similar idea previously where I was using drop downs which was easier because I could put the database id as the value in a option tag inside the select element. The thing that put me off doing that was the fact that there will be approximately 40 options to choose from and if 3-4 are generated, then the user has to go through 3 or 4 sets of dropdown menus with 40 odd records each!
That is why I want to use this ajax search approach instead. I will drive people crazy with drop downs!
ASKER
I wonder if it would work for me to put a hidden field in the echoed out forms? It would be empty though and when I clicked on a <li> it would have to put a value into it, just like with the text input.
Change from this:
to:
Change from this:
<div class="form-group">
<input type="text" name="name[]" autocomplete="off">
<ul class="result"></ul>
</div>
to:
<div class="form-group">
<input type="text" name="name[]" autocomplete="off">
<input type="hidden" name="lostId[]">
<ul class="result"></ul>
</div>
The AJAX search (autocomplete design pattern) makes plenty of sense to me. But I would not add that part to the application until I knew that the HTML and PHP could work together correctly.
Also, I agree with the promise idea, as long as your population of client browsers will support it, or a similar polyfill. Good ref here:
https://developers.google.com/web/fundamentals/getting-started/primers/promises
Also, I agree with the promise idea, as long as your population of client browsers will support it, or a similar polyfill. Good ref here:
https://developers.google.com/web/fundamentals/getting-started/primers/promises
to put a hidden field in the echoed out forms?Sure, that's a sensible thing. I use exactly that design in the show/hide password logic in this article. See Example #3: Like Apple Devices, ...
https://www.experts-exchange.com/articles/19779/Passwords-in-HTML-Forms-Allow-the-Client-to-Show-or-Hide.html
Ok I see - the <li> is the search results popup.
The hidden field is the only way to do it if you are submitting the form using the standard approach.
If you do an AJAX submit you can build the POST data from custom data attributes on the search field.
For the values to be available on a standard submit though they have to be in included in the form as a named form control.
Having said that there is always scope to use the database to do the work for you
The hidden field is the only way to do it if you are submitting the form using the standard approach.
If you do an AJAX submit you can build the POST data from custom data attributes on the search field.
For the values to be available on a standard submit though they have to be in included in the form as a named form control.
Having said that there is always scope to use the database to do the work for you
$searchTxt = isset($_POST['searchIndex']) ? $_POST['searchIndex'] : false;
if ($searchTxt) {
$query = "INSERT INTO tblSearches (searchID) SELECT id FROM tblSearchValues WHERE searchTerm = '{$searchTxt}'";
...
}
ASKER
What I am trying to do is repeat the excercise of the text field with the hidden field. So, when you click on the <li>, the text value goes into the text field and the id value goes into the hidden field. But I can't seem to define the id variable in jQuery. It has to be done after the ajax call because that is where the data gets fetched.
Yes, that is why I changed the html to include a hidden field so that when a <li> is clicked, that value is also populated.
$.ajax({
type: 'POST',
url: 'lost-units-search.php',
data: {
name: input
}
})
// .success IS ON ITS WAY OUT
// I PREFER promise INTERFACE
.done(function(data) {
// data IS PLAIN HTML SO ADD IT TO OUR
// result WINDOW
result.html(data);
result.data('input', jqo);
var $lostId = // here is the problem?
console.log(lostId);
Yes, that is why I changed the html to include a hidden field so that when a <li> is clicked, that value is also populated.
<div class="form-group">
<input type="text" name="name[]" autocomplete="off">
<input type="hidden" name="lostId[]">
<ul class="result"></ul>
</div>
ASKER
This is the search result. It is showing $lost_time_desc in the results.
So, I guess what I am trying to ask is, how do I get $lostId into a variable in jQuery?
I tried something like this:
but when console logging it out I just get "undefined".
echo "<li data-lost-id='".$lostId."'>" . $lost_time_desc. "</li>";
So, I guess what I am trying to ask is, how do I get $lostId into a variable in jQuery?
I tried something like this:
var $lostId = $(this).data('lost-id');
but when console logging it out I just get "undefined".
Did you see my post on using the text field to find the id from the DB - given there is a 1:1 relationship between them you don't have to send both.
ASKER
I did see it, but I don't understand it :(
I managed to get the ID like this in jQuery:
But now trying to get that into the hidden field is proving to be difficult.
I managed to get the ID like this in jQuery:
var lostId = $(data).attr('data-lost-id');
But now trying to get that into the hidden field is proving to be difficult.
ASKER
This is my current INSERT
if(isset($_POST['submit']) && isset($_SESSION['last_id'])) {
foreach($_POST['name'] as $reasons) {
if(empty($reasons)) {
echo error_message("Please complete all fields");
break;
} else {
$stmt = $link->prepare("INSERT INTO `lost_time` (`ps_id`, `reason`) VALUES (?, ?)");
$stmt->bind_param("ii", $_SESSION['last_id'], $reasons);
$stmt->execute();
$stmt->close();
}
}
}
ASKER
If you are saying what I now think you are saying then that would be much easier! I am struggling with one part of the insert though:
The WHERE value..
$stmt = $link->prepare("INSERT INTO `lost_time` (`ps_id`, `reason`) SELECT `lost_time_reason_id` FROM `lost_time_reasons` WHERE `` VALUES (?, ?)");
The WHERE value..
Your SELECT in the INSERT only has one value,
Remember with a select you don't have to specify a field you can specify a value
In your query you have to values you are inserting
ps_id
lost_time_reason_id
With respect to your interface - where do the values come from?
You have a search term that is text - that we use to find the reason_id - is that the second value ? If so where does the first value come from?
Remember with a select you don't have to specify a field you can specify a value
In your query you have to values you are inserting
ps_id
lost_time_reason_id
With respect to your interface - where do the values come from?
You have a search term that is text - that we use to find the reason_id - is that the second value ? If so where does the first value come from?
ASKER
Allow me to explain....
On the first screen, there is a radio button with 2 choices. These are the production targets. Below that is a drop down with values from 1-30 which is their actual total produced.
So, the user will choose their target and their actual. The difference is then calculated and those values are inserted into the database and an auto increment id id created i.e.: ps_id.
I put ps_id into a session like this:
They then click "Next".
The code then takes the target vs the actual and works out the difference. From that difference, the text inputs are generated. So, if the actual vs total is equal to 3, then 3 text fields will be generated whereby the user needs to explain the reason for each.
So, when they start typing in field 1, it will query the database (lost time reason) which will have an id and a text value (the one that is selected in the <li>. Once they have searched and selected for all text fields, they then click submit and insert that data into the database.
On the first screen, there is a radio button with 2 choices. These are the production targets. Below that is a drop down with values from 1-30 which is their actual total produced.
So, the user will choose their target and their actual. The difference is then calculated and those values are inserted into the database and an auto increment id id created i.e.: ps_id.
I put ps_id into a session like this:
$_SESSION['last_id'] = $link->insert_id;
They then click "Next".
The code then takes the target vs the actual and works out the difference. From that difference, the text inputs are generated. So, if the actual vs total is equal to 3, then 3 text fields will be generated whereby the user needs to explain the reason for each.
So, when they start typing in field 1, it will query the database (lost time reason) which will have an id and a text value (the one that is selected in the <li>. Once they have searched and selected for all text fields, they then click submit and insert that data into the database.
ASKER
You can see from my current insert that it leaves out the id values. ![missingId]()
ASKER
Posting more code will probably help...
This is the ajax call to the php search:
This is the ajax call to the php search:
if(isset($_POST['name'])) {
$search = "{$_POST['name']}%";
$stmt = $link->prepare("SELECT `lost_time_desc`, `lost_time_reason_id` FROM `lost_time_reasons` WHERE `lost_time_desc` LIKE ?");
$stmt->bind_param("s", $search);
$stmt->execute();
$result = $stmt->get_result();
$numRows = $result->num_rows;
if($numRows > 0) {
while($row = $result->fetch_assoc()) {
$lost_time_desc = sanitize($row['lost_time_desc']);
$lostId = sanitize($row['lost_time_reason_id']);
echo "<li data-lost-id='".$lostId."'>" . $lost_time_desc. "</li>";
}
} else {
echo "<li>Other</li>";
}
$stmt->close();
}
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks Julian, going to try that out in about an hour..
ASKER
Thanks Julian,
This is a step in the right direction but there are a few issues I am facing. Firstly, when doing the insert, it inserts 9 records instead of 3. I am not sure if the issue is in my foreach loop or why it is doing that?
If I do use break then it only inserts 3 but I don't think I should be using that in this case.
Secondly, no matter what options I choose, it always inserts IDs 4,5 and 6 in that order. If I choose 4,4,6 for example, it still enters 4,5,6. If I choose options 5,5,5 then it still inserts 4,5,6.
I changed this:
as in my database they are both integers.
This is a step in the right direction but there are a few issues I am facing. Firstly, when doing the insert, it inserts 9 records instead of 3. I am not sure if the issue is in my foreach loop or why it is doing that?
If I do use break then it only inserts 3 but I don't think I should be using that in this case.
Secondly, no matter what options I choose, it always inserts IDs 4,5 and 6 in that order. If I choose 4,4,6 for example, it still enters 4,5,6. If I choose options 5,5,5 then it still inserts 4,5,6.
if(isset($_POST['submit']) && isset($_SESSION['last_id'])) {
foreach($_POST['name'] as $reasons) {
if(empty($reasons)) {
echo error_message("Please complete all fields");
break;
} else {
$stmt = $link->prepare("INSERT INTO `lost_time` (`ps_id`, `reason`) SELECT ?, `lost_time_reason_id` FROM `lost_time_reasons` WHERE `lost_time_desc` = ?");
$stmt->bind_param("ii", $_SESSION['last_id'], $reasons);
$stmt->execute();
$stmt->close();
break;
}
}
}
I changed this:
$stmt->bind_param("ds", $ps_id, $search);
$stmt->bind_param("ii", $_SESSION['last_id'], $reasons);
as in my database they are both integers.
ASKER
Okay, no wait. I got it to work. I changed $reason back to a string as opposed to an integer. I was confused by this because in the database I am inserting an integer for the reason ID but you had it set to a string. $reason is actually a string as it is what was entered into the name text field.
It's also just a bit confusing as I have never come across using INSERT and SELECT together like this but it sure is a lot less fiddly than trying to do with jQuery!
Thanks a million. I am just going to play around with it a bit more to make sure and then will give you some points. :)
Also, changing it made the inserting 9 records instead of 3 problem go away. Awesome!
It's also just a bit confusing as I have never come across using INSERT and SELECT together like this but it sure is a lot less fiddly than trying to do with jQuery!
Thanks a million. I am just going to play around with it a bit more to make sure and then will give you some points. :)
Also, changing it made the inserting 9 records instead of 3 problem go away. Awesome!
Good - post back if you need more help.
Using the database where possible to do the work is always first prize. INSERT can take VALUES from a VALUES clause or from a SELECT - as long as the number of values in the latter matches the specified fields for the INSERT.
Using the database where possible to do the work is always first prize. INSERT can take VALUES from a VALUES clause or from a SELECT - as long as the number of values in the latter matches the specified fields for the INSERT.