Jump to content


Photo

Dynamic input fields for mysql table with jquery, ajax and php


  • Please log in to reply
8 replies to this topic

#1 Rupom

Rupom

    New member

  • New Members
  • 5 posts

Posted 17 February 2012 - 07:16 PM

In this tutorial we are going to show you how to use Jquery, Ajax and PHP to create a simple script that dynamically update a MySQL database each time a user types in an input field. By using this method the user isn't forced to refresh the page for simple changes while reducing the load on the server.
LIVE DEMO | DOWNLOAD


HERE THE CODE
The PHP

The second file, ajax-update.php performs only two functions:


<?php

/*
 * DATABASE CONNECTION
 */

// DATABASE: Connection variables
$db_host		= "127.0.0.1";
$db_name		= "ex_database";
$db_username	= "ex_user";
$db_password	= "ex_pass";

// DATABASE: Try to connect
if (!$db_connect = mysql_connect($db_host, $db_username, $db_password))
	die('Unable to connect to MySQL.');
if (!$db_select = mysql_select_db($db_name, $db_connect))
	die('Unable to select database');

// DATABASE: Clean data before use
function clean($value)
{
	return mysql_real_escape_string($value);
}

/*
 * FORM PARSING
 */

// FORM: Variables were posted
if (count($_POST))
{
	// Prepare form variables for database
	foreach($_POST as $column => $value)
		${$column} = clean($value);

	// Perform MySQL UPDATE
	$result = mysql_query("UPDATE user_table SET ".$col."='".$val."'
		WHERE ".$w_col."='".$w_val."'")
		or die ('Unable to update row.');
}

?>

The PHP

The first file, ajax-form.php



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-US">
<head>
	<title>Ajax / PHP Form Example</title>
	<meta http-equiv="content-type" content="text/html; charset=utf-8" />
	<meta name="description" content="This is an example form that uses Ajax to submit data, and PHP to retrieve it."/>
	<meta name="keywords" content="ajax, form, example, php" />
	<!-- JQUERY -->
	<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
	<script type="text/javascript">
	// JQUERY: Plugin "autoSumbit"
	(function($) {
		$.fn.autoSubmit = function(options) {
			return $.each(this, function() {
				// VARIABLES: Input-specific
				var input = $(this);
				var column = input.attr('name');
	
				// VARIABLES: Form-specific
				var form = input.parents('form');
				var method = form.attr('method');
				var action = form.attr('action');

				// VARIABLES: Where to update in database
				var where_val = form.find('#where').val();
				var where_col = form.find('#where').attr('name');
	
				// ONBLUR: Dynamic value send through Ajax
				input.bind('blur', function(event) {
					// Get latest value
					var value = input.val();
					// AJAX: Send values
					$.ajax({
						url: action,
						type: method,
						data: {
							val: value,
							col: column,
							w_col: where_col,
							w_val: where_val
						},
						cache: false,
						timeout: 10000,
						success: function(data) {
							// Alert if update failed
							if (data) {
								alert(data);
							}
							// Load output into a P
							else {
								$('#notice').text('Updated');
								$('#notice').fadeOut().fadeIn();
							}
						}
					});
					// Prevent normal submission of form
					return false;
				})
			});
		}
	})(jQuery);
	// JQUERY: Run .autoSubmit() on all INPUT fields within form
	$(function(){
		$('#ajax-form INPUT').autoSubmit();
	});
	</script>
	<!-- STYLE -->
	<style type="text/css">
		INPUT { margin-right: 1em }
	</style>
</head>
<body>

<!-- CONTENT -->
<h1>My Ajax Form</h1>

<?php

/*
 * DATABASE CONNECTION
 */

// DATABASE: Connection variables
$db_host		= "127.0.0.1";
$db_name		= "ex_database";
$db_username	= "ex_user";
$db_password	= "ex_pass";

// DATABASE: Try to connect
if (!$db_connect = mysql_connect($db_host, $db_username, $db_password))
	die('Unable to connect to MySQL.');
if (!$db_select = mysql_select_db($db_name, $db_connect))
	die('Unable to select database');

/*
 * DATABASE QUERY
 */

// DATABASE: Get current row
$result = mysql_query("SELECT * FROM user_table WHERE user_id=9");
$row = mysql_fetch_assoc($result);

?>
<form id="ajax-form" class="autosubmit" method="POST" action="./ajax-update.php">
	<fieldset>
		<legend>Update user information</legend>

		<label>Company:</label>
			<input name="user_company" value="<?php echo $row['user_company'] ?>" />

		<label>Name:</label>
			<input name="user_name" value="<?php echo $row['user_name'] ?>" />

		<label>E-mail:</label>
			<input name="user_email" value="<?php echo $row['user_email'] ?>" />

		<input id="where" type="hidden" name="user_id" value="<?php echo $row['user_id'] ?>" />
	</fieldset>
</form>

<p id="notice"></p>

</body>
</html>


LIVE DEMO | DOWNLOAD

MORE DETAILS
  • 0

#2 kidAjax

kidAjax

    New member

  • New Members
  • 2 posts

Posted 13 July 2012 - 04:32 AM

Great tutorial, but can you help!
I have altered the height of the input text boxes but can not get the text to start at the top left corner or wrap within the text box? At the moment it starts streaming the text in the middle of the box and disappears (the text box does not wrap the text to show large paragraphs). Any help would be greatly appreciated
  • 0

#3 Ben

Ben

    Administrator

  • Administrators
  • 5,638 posts
  • LocationChico, CA

Posted 13 July 2012 - 10:42 AM

Make sure you're using the correct type of form field. If you want a one line text input, you use <input type="text">. If you want a multiline text box, you need to use a <textarea>.
  • 0
Benjamin Falk
Falken Creative : Twitter

#4 kidAjax

kidAjax

    New member

  • New Members
  • 2 posts

Posted 18 July 2012 - 03:58 AM

Hi Ben, thanks for getting back to me. I have used <textarea>, the problem is that the text that I am calling in from a mySQL database is being shown in the middle of the text area, it also only shows a section of the text as it does not automatically wrap within the textarea.

here is the form section of the code, showing a call to the database to display the information within the textarea:

<form id="ajax-form" class="autosubmit" method="POST" action="./formUpdate.php">

<legend>Update user information</legend>

<label>Company:</label>

<input name="title_1" style="width:100px; height:150px;" value="<?php echo $row['text_box_1'] ?> " />

<label>Name:</label>
<input name="title_2" value="<?php echo $row['title_2'] ?>" />

<label>E-mail:</label>
<input name="title_3" value="<?php echo $row['title_3'] ?>" />

<input id="where" type="hidden" name="page_id" value="<?php echo $row['page_id'] ?>" />

</form>

Any help would be greatly appreciated
  • 0

#5 Ben

Ben

    Administrator

  • Administrators
  • 5,638 posts
  • LocationChico, CA

Posted 18 July 2012 - 09:58 AM

There aren't any textareas in the code sample above.

This:

<input name="title_1" style="width:100px; height:150px;" value="<?php echo $row['text_box_1'] ?> " />

needs to use a <textarea> instead. Setting a width/height won't cause it to wrap the text into multiple lines. http://www.w3schools...ag_textarea.asp
  • 0
Benjamin Falk
Falken Creative : Twitter

#6 mpages

mpages

    New member

  • New Members
  • 1 posts

Posted 30 July 2013 - 08:53 AM

Hi, thanks for your tutorial, it's very useful.

I'm new in coding, and I don't know how to make it update while writing in the field, not after clicking another field. Is there any way to do it?

Thanks!
  • 0

#7 xdawg

xdawg

    New member

  • New Members
  • 1 posts

Posted 02 January 2014 - 01:36 PM

Thank you for the time and effort it took to post this. It works great, however I am interested in updating multiple rows on the same page. I have tried to adapt this code various ways to get it to work, but it only updates the current row. Any suggestions?
  • 0

#8 WDH

WDH

    New member

  • New Members
  • 1 posts

Posted 09 February 2014 - 04:23 AM

Now is very simple to display and edit ( in different ways: text , textarea, select , radio button, date , slider, checkbox, switch on/off button, map, video, colorpicker, password, image, file, html editor ) every field you want from mysql database with EDFF or EDFP library.

1 Include EDFF or EDFP ( Edit Database Field PRO ) library into your project.

2 Just generate code with WDH CodeGenerator.

You can download EDFF or EDFP library and find CodeGenerator here : http://codegenerator.wdh.im

You can find live demo here : http://labs.wdh.im/example.php

Hope that help you !
  • 0

#9 phoxlabs

phoxlabs

    New member

  • New Members
  • 1 posts

Posted Yesterday, 11:11 AM

Thanks for the tutorial. I was trying to take this script and modify it by adding a submit button instead of the autosubmit. I've tried and tried but can't get it to work. 

 

I like your script compared to others that usually pass all the post data in a string, yours passes the input names and values of the data and passes straight to mysql that matches the table columns. (which I like)

<form id="form1" method="POST">
	
		<!-- start id-form -->
		<table border="0" cellpadding="0" cellspacing="0"  id="id-form">
		<tr>
			<th valign="top">Business Name:</th>
			<td><input type="text" name="company_name" class="inp-form" /></td>
			<td></td>
		</tr>
		<tr>
			<th valign="top">Address 1:</th>
			<td><input type="text" name="address_1" class="inp-form" /></td>
			<td></td>
		</tr>
        <tr>
			<th valign="top">Address 2:</th>
			<td><input type="text" name="address_2" class="inp-form" /></td>
			<td></td>
		</tr>
     

	
	<tr>
		<th>&nbsp;</th>
		<td valign="top">
        		<input id="where" type="hidden" name="customer_id" value="1" />
<button id="myBtn">Save</button>
			
		</td>
		<td></td>
	</tr>
	</table>
	<!-- end id-form  -->
    </form>
    
var myBtn = document.getElementById('myBtn'); 
 myBtn.addEventListener('click', function(event)
{   updateform('form1'); }); 

function updateform(id){
return $.each(this, function() {
				// VARIABLES: Input-specific
				var input = $(this);
				var column = input.attr('name');
	
				// VARIABLES: Form-specific
				var form = input.parents('form');
				var method = form.attr('method');
				//var action = form.attr('action');

				// VARIABLES: Where to update in database
				var where_val = form.find('#where').val();
				var where_col = form.find('#where').attr('name');
	
				// ONBLUR: Dynamic value send through Ajax
				//input.bind('blur', function(event) {
					// Get latest value
					var value = input.val();
					// AJAX: Send values
					$.ajax({
						url: "/ajax/update_company_info.php",
						type: method,
						data: {
							val: value,
							col: column,
							w_col: where_col,
							w_val: where_val
						},
	
						success: function(data) {
							// Alert if update failed
							if (data) {
								alert(data);
							}
							// Load output into a P
							else {
								$('#alert').text('Updated');
								$('#alert').fadeOut().fadeIn();
							}
						}
					});
					// Prevent normal submission of form
					return false;
				})
			
		
	}(jQuery);

  • 0




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users