Link to home
Create AccountLog in
Avatar of Crazy Horse
Crazy HorseFlag for South Africa

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.

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>";	
		}

Open in new window


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 = '';
	  
  });
});

Open in new window


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');

Open in new window

SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Julian Hansen
Your jQuery seems to be code linked to a search function not the <li> click?
Avatar of Crazy Horse

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.
@ 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!
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:

<div class="form-group">
  <input type="text" name="name[]" autocomplete="off">
  <ul class="result"></ul>
</div>

Open in new window


to:

<div class="form-group">
  <input type="text" name="name[]" autocomplete="off">
<input type="hidden" name="lostId[]">
  <ul class="result"></ul>
</div>

Open in new window

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
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

$searchTxt = isset($_POST['searchIndex']) ? $_POST['searchIndex'] : false;
if ($searchTxt) {
  $query = "INSERT INTO tblSearches (searchID) SELECT id FROM tblSearchValues WHERE searchTerm = '{$searchTxt}'";
  ...
}

Open in new window

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.

 
$.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);

Open in new window


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>

Open in new window

This is the search result. It is showing $lost_time_desc in the results.

	echo "<li data-lost-id='".$lostId."'>" . $lost_time_desc. "</li>";

Open in new window


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');

Open in new window


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.
I did see it, but I don't understand it :(

I managed to get the ID like this in jQuery:

var lostId = $(data).attr('data-lost-id');

Open in new window


But now trying to get that into the hidden field is proving to be difficult.
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();
				
			}
		}
	}

Open in new window

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:

$stmt = $link->prepare("INSERT INTO `lost_time` (`ps_id`, `reason`) SELECT `lost_time_reason_id` FROM `lost_time_reasons` WHERE `` VALUES (?, ?)");

Open in new window


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?
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:

$_SESSION['last_id'] = $link->insert_id;

Open in new window


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.
You can see from my current insert that it leaves out the id values. User generated image
Posting more code will probably help...

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();
}

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks Julian, going to try that out in about an hour..
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.

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;
				
			}
		}
	}

Open in new window


I changed this:

$stmt->bind_param("ds", $ps_id, $search);

Open in new window

to

$stmt->bind_param("ii", $_SESSION['last_id'], $reasons);

Open in new window


as in my database they are both integers.
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!
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.