Crazy Horse
asked on
insert ID of database value into database instead of text value from text field
I have asked a very similar question to this which was answered but the circumstances are a little different this time and I can't quite get it to work.
On the previous question I was generating any number of text fields dynamically. Now, I only have one text field. I am using jQuery/ajax and PHP to search the database for employee names. When the name I am looking for comes up, I click on it and that name then shows in the text field. However, when I submit the form and insert data into the database, it should insert the ID for that record into the DB, not the text value.
employee-search.php:
My insert code:
I have just added 3 employees to my employee table for test data and the problem is that when I add a warning, it adds for all 3 employees at once instead of just for the one I have chosen.
On the previous question I was generating any number of text fields dynamically. Now, I only have one text field. I am using jQuery/ajax and PHP to search the database for employee names. When the name I am looking for comes up, I click on it and that name then shows in the text field. However, when I submit the form and insert data into the database, it should insert the ID for that record into the DB, not the text value.
<div class="row">
<div class="col-md-6">
<div class="form-group">
<label class="control-label">Employee</label>
<input type="text" class="form-control" name="last_name" placeholder="Search by surname" id="last_name" autocomplete="off">
<div style="display:none" id=loading><img src="../plugins/images/ajax-loader.gif" /></div>
<div id="result"></div>
</div>
</div>
</div>
<script>
$(document).ready(function() {
$("#last_name").focus();
$("#last_name").keyup(function() {
$("#loading").show();
var last_name = $("#last_name").val();
if (last_name.length > 2) {
$.ajax({
type: 'POST',
url: 'functions/employee-search.php',
data: {
last_name: last_name
},
success: function(data) {
if (!data.error) {
$("#result").html(data);
$("#loading").hide();
}
}
});
}
if (last_name.length < 1) {
$("#result").html("");
$("#loading").hide();
}
});
$("#result").on('click', function(evt) {
$("#last_name").val(evt.target.textContent);
this.textContent = '';
});
});
</script>
employee-search.php:
if($_POST) {
$search = "{$_POST['last_name']}%";
$stmt = $link->prepare("SELECT `first_name`, `last_name`, `id` FROM `employees` WHERE `last_name` LIKE ? LIMIT 7");
$stmt->bind_param("s", $search);
$stmt->execute();
$result = $stmt->get_result();
$numRows = $result->num_rows;
if($numRows > 0) {
while ($row = $result->fetch_assoc()) {
$first_name = sanitize($row['first_name']);
$last_name = sanitize($row['last_name']);
$id = sanitize($row['id']);
echo "<li last-name-id='".$id."'>" . $last_name . " , " . $first_name . "</li>";
}
} else {
echo "No results found";
}
$stmt->close();
}
My insert code:
$stmt = $link->prepare("INSERT INTO `warnings` (`w_type`, `employee_id`) SELECT ?, `id` FROM `employees` WHERE `last_name` = ?");
$stmt->bind_param("si", $_POST['w_type'], $_POST['last_name']);
I have just added 3 employees to my employee table for test data and the problem is that when I add a warning, it adds for all 3 employees at once instead of just for the one I have chosen.
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`contact_no` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
CREATE TABLE `warnings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`employee_id` varchar(255) NOT NULL,
`w_type` varchar(255) NOT NULL,
`incident_date` date NOT NULL,
`issue_date` date NOT NULL,
`expire_date` date NOT NULL,
`reason` text NOT NULL,
`issued_by` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8
Use an hidden input to store the id which will require switching to a JSON response so you can parse the name and id from the returned data
https://jonsuh.com/blog/jquery-ajax-call-to-php-script-with-json-return/
https://jonsuh.com/blog/jquery-ajax-call-to-php-script-with-json-return/
Couple of thoughts...
If you only match on last name and you don't use a LIMIT clause, you could have name collisions.
In the auto-complete part of the script (an AJAX call) why not return a JSON string with the database ID as well as the last name and first name. Then when the client chooses one of the names, look up the ID in the JSON and just send that ID to the UPDATE script.
If you only match on last name and you don't use a LIMIT clause, you could have name collisions.
In the auto-complete part of the script (an AJAX call) why not return a JSON string with the database ID as well as the last name and first name. Then when the client chooses one of the names, look up the ID in the JSON and just send that ID to the UPDATE script.
ASKER
Since I suck at explaining in words, I have added a picture to better explain myself. The search will be by last name and yes, there are people at the company who have the same surname. So, if that was to happen you would see this:
But, they have different id's. So, if I click on any one of those and then submit to insert data into the database, the employee id would either be 1 or 2, depending on which one I click on. I have just added the red text for illustration purposes. The user wouldn't actually see that.
Not sure if that helps or not?
But, they have different id's. So, if I click on any one of those and then submit to insert data into the database, the employee id would either be 1 or 2, depending on which one I click on. I have just added the red text for illustration purposes. The user wouldn't actually see that.
Not sure if that helps or not?
ASKER
The ID should come from the php search results:
echo "<li last-name-id='".$id."'>" . $last_name . " , " . $first_name . "</li>";
So the ID is already present in the dropdown list in the last-name-id attribute
ASKER
@Nicholas,
Yes, that's correct. What I am doing is searching the employee table by last name. Then I click on the name and it then removes the search results and shows the name in the text field. I then fill in the rest of the details like what type of warning it is and the dates. When I submit the form it should store the data in the warnings table. In the employee_id column of the warnings table, the employee ID should be stored. So, it doesn't; matter if many employees happen to have the same last name because they all have unique employee ID's.
Yes, that's correct. What I am doing is searching the employee table by last name. Then I click on the name and it then removes the search results and shows the name in the text field. I then fill in the rest of the details like what type of warning it is and the dates. When I submit the form it should store the data in the warnings table. In the employee_id column of the warnings table, the employee ID should be stored. So, it doesn't; matter if many employees happen to have the same last name because they all have unique employee ID's.
Then when you select an item add the id as a property to the <input>
In your select
I can't see from what you have posted what the process is you are using to select an item but it would be something like this
In your select
I can't see from what you have posted what the process is you are using to select an item but it would be something like this
$('#last_name').data('id', selected.attr('id'));
In your AJAX here var id = $('#last_name').data('id');
...
data: {
last_name: id
},
ASKER
@ Julian,
This is the code I have that when you click on the <li>, it then removes the search items and places what you clicked on into the text field.
This is the code I have that when you click on the <li>, it then removes the search items and places what you clicked on into the text field.
$( "#result" ).on('click', function(evt) {
$( "#last_name" ).val(evt.target.textContent);
this.textContent = '';
Add hidden input to the form to hold the ID and then the jquery
Just adapt to your current click event
$("body").on("click","li",function(){ //may need an identifier for the UL
$("hiddeninputid").val($(this).attr("last-name-id"))
})
Just adapt to your current click event
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
However, when I submit the form
The ID already exists in the drop down list
ASKER
I think this is still an issue because instead of inserting one record, it is inserting as many records as there are employees. So, if there are 5 employees, it inserts 5 records.
$stmt = $link->prepare("INSERT INTO `warnings` (`w_type`, `employee_id`) SELECT ?, `id` FROM `employees` WHERE `last_name` = ?");
$stmt->bind_param("si", $_POST['w_type'], $_POST['last_name']);
If you are now getting the ID from the POST values then why are you still using the name value?
ASKER
Nicholas, all I am doing at this point is confusing the hell out of myself. I think I need to start again and take this step by step.
So, all I am trying to do now is get the id value to alert when I click on a name in the <li>, but that is not happening. Once I can get that, then I will close this out and ask a related question to try get to my ultimate goal.
This is now the php for the ajax POST.
I have left the jQuery relatively unchanged besides the bottom part.
So, all I am trying to do now is get the id value to alert when I click on a name in the <li>, but that is not happening. Once I can get that, then I will close this out and ask a related question to try get to my ultimate goal.
This is now the php for the ajax POST.
if ($_POST) {
$search = "{$_POST['last_name']}%";
$stmt = $link->prepare("SELECT `first_name`, `last_name`, `id` FROM `employees` WHERE `last_name` LIKE ? LIMIT 7");
$stmt->bind_param("s", $search);
$stmt->execute();
$result = $stmt->get_result();
$numRows = $result->num_rows;
if ($numRows > 0) {
echo "<ul id='myid'>";
while ($row = $result->fetch_assoc()) {
$first_name = sanitize($row['first_name']);
$last_name = sanitize($row['last_name']);
$id = sanitize($row['id']);
echo "<li data-id='$id'>" . $last_name . " , " . $first_name . "</li>";
}
echo "</ul>";
}
else {
echo "No results found";
}
$stmt->close();
}
I have left the jQuery relatively unchanged besides the bottom part.
$(document).ready(function(){
$( "#last_name" ).focus();
$( "#last_name" ).keyup(function(){
$( "#loading" ).show();
var last_name = $( "#last_name" ).val();
if(last_name.length > 2) {
$.ajax({
type: 'POST',
url: 'functions/employee-search.php',
data: {last_name: last_name},
success: function(data) {
if(!data.error) {
$( "#result" ).html(data);
$( "#loading" ).hide();
}
}
});
}
if(last_name.length < 1) {
$( "#result" ).html("");
$( "#loading" ).hide();
}
});
$("#myid li").click(function() {
alert($(this).attr('id'));
});
});
alert($(this).attr('id'));
should be
alert($(this).data('id'));
should be
alert($(this).data('id'));
ASKER
Thanks, Nicholas. I tried that but still no alert.
$("#myid li").click(function() {
alert($(this).data('id'));
Since it's dynamic it needs to be
$('#myid li').on('click', function() {
alert($(this).data('id'));
ASKER
Thank you. Tried that. Still nothing.
Got a demo page to look at? Or can you create one
This is just a basic example of it working
https://codepen.io/anon/pen/VbMvxE
This is just a basic example of it working
https://codepen.io/anon/pen/VbMvxE
ASKER
Not really, this is on localhost and to get it live will be difficult as I will have to create a database etc. Is the issue maybe not with my new php?
Just create the page online and for employee-search.php just return some sample html of the LI's - doesn't need to do actual db stuff
ASKER
@Nicholas, you make that sound really easy, haha.
Sadly, my experience with json is little to none. I know I am not being very helpful. Not my intention!
Sadly, my experience with json is little to none. I know I am not being very helpful. Not my intention!
I changed my comment, it just needs some sample html of the LI's returned, doesn't matter what it is as long as its the same format as your code returns e.g.
<li data-id='1'>john</li><li data-id='2'>James</li><li data-id='3'>Mark</li><li data-id='4'>David</li><li data-id='5'>Paul</li>
I am confused as to why you are sending the last_name to the PHP script - especially when in your script you are using a LIKE to extract data - if you have a duplicate last name in your database you will have horribly unpredictable results.
If the ID is available as part of the data structure used to populate the typeahead - then simply save that value when it is selected and in your AJAX send that value instead of the last_name as per my post above.
https://www.experts-exchange.com/questions/29020279/insert-ID-of-database-value-into-database-instead-of-text-value-from-text-field.html?anchorAnswerId=42119197#a42119197
If the ID is available as part of the data structure used to populate the typeahead - then simply save that value when it is selected and in your AJAX send that value instead of the last_name as per my post above.
https://www.experts-exchange.com/questions/29020279/insert-ID-of-database-value-into-database-instead-of-text-value-from-text-field.html?anchorAnswerId=42119197#a42119197
ASKER
Hi Julian,
Okay, I am going to do my best to explain what I am trying to do.
The user is presented with a text field. The purpose is to search for an employee that they wish to issue a warning to.
When they start typing in the text field, the database search should begin. Since they are typing in a surname, the surname field in the database needs to be checked because they aren't searching for an ID, but the employees surname. If they search for "Smith" for example, there may be 3 different "Smith's" which will then display under the text field in a <li>. In my code, you can see I have concatenated the first name so that the user will not just see 3 x "Smith".
example:
Smith, Ben
Smith, Mary
Smith, Joe
When they click on one of those names, it should then show in the text field as a selected choice and they can move to the next field and choose from the dropdown. Once they have filled in the form, they submit and the details are stored in a warnings table. But I don't want the surname and first name saved into the database, I want the employee's id. So, they will see the name in the text field, but the value will be the ID.
Not sure if this makes it any clearer?
Okay, I am going to do my best to explain what I am trying to do.
The user is presented with a text field. The purpose is to search for an employee that they wish to issue a warning to.
When they start typing in the text field, the database search should begin. Since they are typing in a surname, the surname field in the database needs to be checked because they aren't searching for an ID, but the employees surname. If they search for "Smith" for example, there may be 3 different "Smith's" which will then display under the text field in a <li>. In my code, you can see I have concatenated the first name so that the user will not just see 3 x "Smith".
example:
Smith, Ben
Smith, Mary
Smith, Joe
When they click on one of those names, it should then show in the text field as a selected choice and they can move to the next field and choose from the dropdown. Once they have filled in the form, they submit and the details are stored in a warnings table. But I don't want the surname and first name saved into the database, I want the employee's id. So, they will see the name in the text field, but the value will be the ID.
Not sure if this makes it any clearer?
Can you provide the HTML of the page from View Source in your browser
ASKER
<div class="row">
<div class="col-md-6">
<div class="form-group">
<label class="control-label">Employee</label>
<input type="text" class="form-control" name="last_name" placeholder="Search by surname" id="last_name" autocomplete="off">
<div style="display:none" id=loading><img src="../plugins/images/ajax-loader.gif" />
</div>
<div id="result"></div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-6">
<div class="form-group">
<label class="control-label">Type</label>
<select class="form-control" tabindex="1" name="w_type">
<option value="">Please select</option>
<option value="Verbal">Verbal</option>
<option value="Written">Written</option>
<option value="Final Written">Final Written</option>
</select>
</div>
</div>
</div>
<div class="row">
<div class="col-md-6">
<div class="form-group">
<label class="control-label">Incident Date</label>
<input type="text" class="form-control" name="incident_date" placeholder="Incident date" id="date">
</div>
</div>
</div>
<div class="row">
<div class="col-md-6">
<div class="form-group">
<label class="control-label">Issue Date</label>
<input type="text" class="form-control" name="issue_date" placeholder="Issue date" id="issuedate">
</div>
</div>
</div>
<div class="row">
<div class="col-md-6">
<div class="form-group">
<label class="control-label">Reason</label>
<textarea class="form-control" name="reason" rows="5"></textarea>
</div>
</div>
</div>
<div class="form-actions">
<button type="submit" name="submit" class="btn btn-success"> <i class="fa fa-check"></i> Add</button>
</div>
Change the alert function to
$(document).on("click","#myid li",function() {
alert($(this).data('id'));
});
Your whole click even would be this (with the added hidden input for emp_id
$(document).on("click","#myid li",function() {
$("#last_name").val($(this).text())
$("#emp_id").val($(this).data('id'));
});
ASKER
That works, Nicholas!
ASKER
Hmm. Bad news though. Apparently it is a bad idea to delegate events to the document?
No - you should NOT bind all delegated event handlers to the document object. That is probably the worst performing scenario you could create.
First off, event delegation does not always make your code faster. In some cases, it's is advantageous and in some cases not. You should use event delegation when you actually need event delegation and when you benefit from it. Otherwise, you should bind event handlers directly to the objects where the event happens as this will generally be more efficient.
Second off, you should NOT bind all delegated events at the document level. This is exactly why .live() was deprecated because this is very inefficient when you have lots of events bound this way. For delegated event handling it is MUCH more efficient to bind them to the closest parent that is not dynamic.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is working as well :)
ASKER
Now I just have to get that ID that is being alerted to POST to the insert record. I think I should ask a related question for that is this is getting long...
Using my code above just add an hidden input with the id of emp_id and whatever name you choose
Then you can use it in your PHP code to insert directly in to the DB - no need for the name
Then you can use it in your PHP code to insert directly in to the DB - no need for the name
ASKER
Using my code above just add an hidden input with the id of emp_id
Do you mean a jQuery hidden input or html one?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your patience. This is now working just how I wanted it to.
Not sure why I really struggle to comprehend jQuery. It seems to be my achilles heel.
Not sure why I really struggle to comprehend jQuery. It seems to be my achilles heel.
Not sure if this makes it any clearer?Yes it does - I was on board with everything except the means by which you were submitting your form - which I assumed was AJAX when it fact you were just using AJAX for the lookup.
Here is a sample that I think does what you want
HTML
<form action="reflect.php" method="post">
<input type="hidden" name="empid" id="empid" />
<div class="row">
<div class="col-md-6">
<div class="form-group">
<label class="control-label">Employee</label>
<input type="text" class="form-control" placeholder="Search by surname" id="last_name" autocomplete="off">
<div style="display:none" id=loading><img src="../plugins/images/ajax-loader.gif" /></div>
<ul id="result"></ul>
</div>
</div>
</div>
<input type="submit" />
</form>
jQuery
<script>
$(function(){
$( "#last_name" ).focus();
$( "#last_name" ).keyup(function(){
$( "#loading" ).show();
var last_name = $( "#last_name" ).val();
if(last_name.length > 2) {
$.ajax({
type: 'POST',
url: 't2396.php',
data: {last_name: last_name},
success: function(data) {
if(!data.error) {
$( "#result" ).html(data);
$( "#loading" ).hide();
}
}
});
}
if(last_name.length < 1) {
$( "#result" ).html("");
$( "#loading" ).hide();
}
});
$("#result").on('click', 'li', function() {
$('#empid').val($(this).data('id'));
$('#last_name').val($(this).text());
$('#result').empty();
});
});
</script>
Working sample here
EDIT
There are a few changes to the code - which you can see if you go through it.
When you submit the form it posts to a reflect script which shows the emp id
ASKER
Thank you Julian. I will certainly give that a try either tomorrow or over the weekend as if I don't go to bed now I will probably collapse!
Working half day boet ... ag shame!
@Julian
Whats the point of copying my code?
Whats the point of copying my code?
Whats the point of copying my code?Not intentional - this thread has a lot of posts I have not read all of them. Tracking back I see the code is the same - but that is because it is the obvious solution. I was trying to put something together for the author to see a working copy - that is what came out. It was not my intention to rain on your parade.
@Black Sulphur,
Please do not include the above solution in the points allocation as the code it contains was already presented by Nicholas.
Is lastname unique - if not how do you distinguish when you select it?