Saving changes from combobox in datagrid to mysql database issue...

[bwwelle]

I am new to the easyui datagrid and have loved it until needing to use the combobox with inline editing.  I have attempted to do the same thing a number of ways to no avail and have a feeling that it is something simple.  I am attempting to save the courseid and the videoid, both int types, into the vq_videocourse table but after saving, the data is never saved within the database.  The vq_videocourse table has three fields an auto_increment int "id" field, and the two int "courseid" and "videoid" fields.   I am able to see the video names and the course names in the combobox when editing, and once "saved" it looks like it should save, but never is actually saving to the database. The database saving etc. works with other datagrids that I am not using the combobox. I'm basing this all off the code found at http://www.jeasyui.com/tutorial/datagrid/datagrid12.php.  Any help is greatly appreciated.

HTML Code

Code:

<h2>Video-To-Course Details</h2>
	<div class="demo-info">
		<div class="demo-tip icon-tip">&nbsp;</div>
		<div>Click the edit button on the right side of row to start editing.</div>
	</div>
	
	<div style="margin:10px 0">
		<a href="javascript:void(0)" class="easyui-linkbutton" onclick="insert()">Insert Row</a>
	</div>
	
	<table id="videotocoursedg"></table>

JavaScript Code

Code:

<script>
   var courses = <?php
            $d = mysql_query( "SELECT id as courseid, coursename from vq_course" ) or die( mysql_error() );
            $courses = array();
            while( $r = mysql_fetch_assoc($d) ) {
                $courses[] = $r;
            }
            echo json_encode( $courses );
        ?>;
		
		var videos = <?php
            $d = mysql_query( "SELECT id as videoid, videoname from vq_video" ) or die( mysql_error() );
            $videos = array();
            while( $r = mysql_fetch_assoc($d) ) {
                $videos[] = $r;
            }
            echo json_encode( $videos );
        ?>;	
$(function(){
			$('#videotocoursedg').datagrid({
				title:'Video-To-Course Details',
				iconCls:'icon-edit',
				width:660,
				height:250,
				singleSelect:true,
				idField:'id',
				url:'get_videocourses.php',
				saveUrl:'save_videocourse.php',
				updateUrl:'update_videocourse.php',				
				columns:[[
				{field:'id',title:'ID',width:60},
					{field:'coursename',title:'Course Name',width:100,
						formatter:function(value){
							for(var i=0; i<courses.length; i++){
								if (courses[i].courseid == value) return courses[i].coursename;
							}
							return value;
						},
						editor:{
							type:'combobox',
							options:{
								valueField:'courseid',
								textField:'coursename',
								data:courses,
								required:true
							}
						}
					},
					{field:'videoname',title:'Video Name',width:100,
						formatter:function(value){
							for(var i=0; i<videos.length; i++){
								if (videos[i].videoid == value) return videos[i].videoname;
							}
							return value;
						},
						editor:{
							type:'combobox',
							options:{
								valueField:'videoid',
								textField:'videoname',
								data:videos,
								required:true
							}
						}
					},
					{field:'action',title:'Action',width:80,align:'center',
						formatter:function(value,row,index){
							if (row.editing){
								var s = '<a href="javascript:void(0)" onclick="saverow(this)">Save</a> ';
								var c = '<a href="javascript:void(0)" onclick="cancelrow(this)">Cancel</a>';
								return s+c;
							} else {
								var e = '<a href="javascript:void(0)" onclick="editrow(this)">Edit</a> ';
								var d = '<a href="javascript:void(0)" onclick="deleterow(this)">Delete</a>';
								return e+d;
							}
						}
					}
				]],
				onBeforeEdit:function(index,row){
					row.editing = true;
					updateActions(index);
				},
				onAfterEdit:function(index,row){
					row.editing = false;
					updateActions(index);
				},
				onCancelEdit:function(index,row){
					row.editing = false;
					updateActions(index);
				}
			});
		});
		function updateActions(index){
			$('#videotocoursedg').datagrid('updateRow',{
				index: index,
				row:{}
			});
		}
		function getRowIndex(target){
			var tr = $(target).closest('tr.datagrid-row');
			return parseInt(tr.attr('datagrid-row-index'));
		}
		function editrow(target){
			$('#videotocoursedg').datagrid('beginEdit', getRowIndex(target));
		}
		function deleterow(target){
			$.messager.confirm('Confirm','Are you sure?',function(r){
				if (r){
					$('#videotocoursedg').datagrid('deleteRow', getRowIndex(target));
				}
			});
		}
		function saverow(target){
		$('#videotocoursedg').datagrid('endEdit', getRowIndex(target));			 
			 
		}
		function cancelrow(target){
			$('#videotocoursedg').datagrid('cancelEdit', getRowIndex(target));
		}
		function insert(){
			var row = $('#videotocoursedg').datagrid('getSelected');
			if (row){
				var index = $('#videotocoursedg').datagrid('getRowIndex', row);
			} else {
				index = 0;
			}
			$('#videotocoursedg').datagrid('insertRow', {
				index: index,
				row:{
					status:'P'
				}
			});
			$('#videotocoursedg').datagrid('selectRow',index);
			$('#videotocoursedg').datagrid('beginEdit',index);
		}
	</script>

save_videocourse.php

Code:

<?php

$courseid = $_REQUEST['courseid'];
$videoid = $_REQUEST['videoid'];

        include '../db/database.php';
        include '../db/opendb.php';

$sql = "insert into vq_videocourse(courseid,videoid) values('$courseid','$videoid')";
@mysql_query($sql);

include '../db/closedb.php';

?>

update_videocourse.php

Code:

<?php

$id = $_REQUEST['id'];
$videoid = $_REQUEST['videoid'];
$courseid = $_REQUEST['courseid'];

        include '../db/database.php';
        include '../db/opendb.php';


$sql = "update vq_videocourse set videoid='$videoid', courseid = '$courseid' where id=$id";
@mysql_query($sql);
echo json_encode(array('success'=>true));

?>

get_videocourses.php

Code:

<?php

include '../db/database.php';
include '../db/opendb.php';
$rs = mysql_query('SELECT vc.id, c.id as courseid, c.coursename, v.id as videoid, v.videoname FROM vq_videocourse vc INNER JOIN vq_video AS v ON v.id = vc.videoid INNER JOIN vq_course AS c ON c.id = vc.courseid');
$result = array();
while($row = mysql_fetch_object($rs)){
	array_push($result, $row);
}

echo json_encode($result);

?>

[bwwelle]

I just want to add, this is probably some real easy issue that I'm missing and think its with the saverow function.  Please help as I've been struggling with this for the past couple days...I will answer any questions you may have.  Thanks!

[stworthy]

Please check if you are using edatagrid plugin.

Code:

$('#videotocoursedg').edatagrid({
...
})

[bwwelle]

Thanks for the suggestion as I was not.  I changed all of the references to "('#videotocoursedg').datagrid(" to "('#videotocoursedg').edatagrid(" but it still doesn't updat the database when attempting to update a row of data, or insert a new record. Below is my new updated code as again, any assistance is GREATLY appreciated:

Code:

$(function(){
			$('#videotocoursedg').edatagrid({
				title:'Video-To-Course Details',
				iconCls:'icon-edit',
				width:660,
				height:250,
				singleSelect:true,
				idField:'id',
				url:'get_videocourses.php',
				saveUrl:'save_videocourse.php',
				updateUrl:'update_videocourse.php',				
				columns:[[
				{field:'id',title:'ID',width:60},
					{field:'coursename',title:'Course Name',width:100,
						formatter:function(value){
							for(var i=0; i<courses.length; i++){
								if (courses[i].courseid == value) return courses[i].coursename;
							}
							return value;
						},
						editor:{
							type:'combobox',
							options:{
								valueField:'courseid',
								textField:'coursename',
								data:courses,
								required:true
							}
						}
					},
					{field:'videoname',title:'Video Name',width:100,
						formatter:function(value){
							for(var i=0; i<videos.length; i++){
								if (videos[i].videoid == value) return videos[i].videoname;
							}
							return value;
						},
						editor:{
							type:'combobox',
							options:{
								valueField:'videoid',
								textField:'videoname',
								data:videos,
								required:true
							}
						}
					},
					{field:'action',title:'Action',width:80,align:'center',
						formatter:function(value,row,index){
							if (row.editing){
								var s = '<a href="javascript:void(0)" onclick="saverow(this)">Save</a> ';
								var c = '<a href="javascript:void(0)" onclick="cancelrow(this)">Cancel</a>';
								return s+c;
							} else {
								var e = '<a href="javascript:void(0)" onclick="editrow(this)">Edit</a> ';
								var d = '<a href="javascript:void(0)" onclick="deleterow(this)">Delete</a>';
								return e+d;
							}
						}
					}
				]],
				onBeforeEdit:function(index,row){
					row.editing = true;
					updateActions(index);
				},
				onAfterEdit:function(index,row){
					row.editing = false;
					updateActions(index);
				},
				onCancelEdit:function(index,row){
					row.editing = false;
					updateActions(index);
				}
			});
		});
		function updateActions(index){
			$('#videotocoursedg').edatagrid('updateRow',{
				index: index,
				row:{}
			});
		}
		function getRowIndex(target){
			var tr = $(target).closest('tr.datagrid-row');
			return parseInt(tr.attr('datagrid-row-index'));
		}
		function editrow(target){
			$('#videotocoursedg').edatagrid('beginEdit', getRowIndex(target));
		}
		function deleterow(target){
			$.messager.confirm('Confirm','Are you sure?',function(r){
				if (r){
					$('#videotocoursedg').edatagrid('deleteRow', getRowIndex(target));
				}
			});
		}
		function saverow(target){
		
			 $('#videotocoursedg').edatagrid('endEdit', getRowIndex(target));
			 
		}
		function cancelrow(target){
			$('#videotocoursedg').edatagrid('cancelEdit', getRowIndex(target));
		}
		function insert(){
			var row = $('#videotocoursedg').edatagrid('getSelected');
			if (row){
				var index = $('#videotocoursedg').edatagrid('getRowIndex', row);
			} else {
				index = 0;
			}
			$('#videotocoursedg').edatagrid('insertRow', {
				index: index,
				row:{
					status:'P'
				}
			});
			$('#videotocoursedg').edatagrid('selectRow',index);
			$('#videotocoursedg').edatagrid('beginEdit',index);
		}

[stworthy]

Please refer to this tutorial http://www.jeasyui.com/tutorial/app/crud2.php

[bwwelle]

This is where I'm at as I found a syntax error in the update_videocourse.php file so it would update correctly.  The only thing that is not working is that the initial values that are loaded are the videoid and courseid values in the combobox boxes when the page loads or when the value in the comboboxes are saved.  When I change the field="videoid" to field="videoname" and field="courseid" to field="coursename", it doesn't save or update anymore but the videoname and coursename are in the combobox when the page loads.  Please provide some assistance as I have tried so many options to no avail!

html code:

Code:

 <table id="videocoursedg" title="Video-to-Course Details" style="width:550px;height:250px"
    toolbar="#videocoursetoolbar" idField="id"
    rownumbers="true" fitColumns="true" singleSelect="true">
    <thead>
    <tr>
    <th field="courseid" id="course" width="50" editor="{
							type:'combobox',
							options:{
								valueField:'courseid',
								textField:'coursename',
								url:'get_coursesforvideocourses.php',
								required:true}}">Course Name</th>
    <th field="videoid" id="video" width="50" editor="{type:'combobox',
							options:{
								valueField:'videoid',
								textField:'videoname',
								url:'get_videosforvideocourses.php',
								required:true}}">Video Name</th>
    </tr>
    </thead>
    </table>
    <div id="videocoursetoolbar">
    <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-add" plain="true" onclick="javascript:$('#videocoursedg').edatagrid('addRow')">New</a>
    <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-remove" plain="true" onclick="javascript:$('#videocoursedg').edatagrid('destroyRow')">Destroy</a>
    <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-save" plain="true" onclick="javascript:$('#videocoursedg').edatagrid('saveRow')">Save</a>
    <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-undo" plain="true" onclick="javascript:$('#videocoursedg').edatagrid('cancelRow')">Cancel</a>
    </div>

javascript code:

Code:

<script>
	    $('#videocoursedg').edatagrid({
    url: 'get_videocourses.php',
    saveUrl: 'save_videocourse.php',
    updateUrl: 'update_videocourse.php',
    destroyUrl: 'destroy_videocourse.php'
    });
	
	
	</script>	

save_videocourse.php

Code:

<?php

$courseid = intval($_REQUEST['courseid']);
$videoid = intval($_REQUEST['videoid']);

        include '../db/database.php';
        include '../db/opendb.php';

$sql = "insert into vq_videocourse(courseid,videoid) values($courseid,$videoid)";
@mysql_query($sql);

include '../db/closedb.php';

echo json_encode(array(
'id' => mysql_insert_id(),
'courseid' => $courseid,
'videoid' => $videoid
));

?>

get_videosforvideocourses.php code:

Code:

<?php

include '../db/database.php';
include '../db/opendb.php';
$rs = mysql_query('select id as videoid, videoname from vq_video');
$result = array();
while($row = mysql_fetch_object($rs)){
	array_push($result, $row);
}

echo json_encode($result);

?>

get_coursesforvideocourses.php code:

Code:

<?php

include '../db/database.php';
include '../db/opendb.php';
$rs = mysql_query('select id as courseid, coursename from vq_course');
$result = array();
while($row = mysql_fetch_object($rs)){
	array_push($result, $row);
}

echo json_encode($result);

?>

update_videocourse.php code:

Code:

<?php

$id = intval($_REQUEST['id']);
$videoid = intval($_REQUEST['videoid']);
$courseid = intval($_REQUEST['courseid']);

        include '../db/database.php';
        include '../db/opendb.php';


$sql = "update vq_videocourse set videoid=$videoid, courseid = $courseid where id=$id";
@mysql_query($sql);
echo json_encode(array(
'id' => $id,
'videoid' => $videoid,
'courseid' => $courseid
));

?>

destroy_videocourse.php code:

Code:

<?php

$id = intval($_REQUEST['id']);

include '../db/database.php';
include '../db/opendb.php';

$sql = "delete from vq_videocourse where id=$id";
@mysql_query($sql);
echo json_encode(array('success'=>true));
?>

[stworthy]

To display the names corresponding to 'courseid' and 'videoid' fields, the 'formatter' function must be defined.

Code:

<table id="videocoursedg" title="Video-to-Course Details" style="width:550px;height:250px"
		toolbar="#videocoursetoolbar" idField="id"
		rownumbers="true" fitColumns="true" singleSelect="true">
    <thead>
    <tr>
    <th width="50" data-options="
				field:'courseid',
				formatter:function(value,row){
					return row.coursename;
				},
				editor:{
					type:'combobox',
					options:{
						valueField:'courseid',
						textField:'coursename',
						url:'get_coursesforvideocourses.php',
						required:true
					}
				}
			">Course Name</th>
    <th width="50" data-options="
				field:'videoid',
				formatter:function(value,row){
					return row.videoname;
				},
				editor:{
					type:'combobox',
					options:{
						valueField:'videoid',
						textField:'videoname',
						url:'get_videosforvideocourses.php',
						required:true
					}
				}
			">Video Name</th>
    </tr>
    </thead>
</table>

The returned data from 'get_videocourses.php' looks like this:

Code:

[
{"id":"id1","courseid":"c1","coursename":"cname1","videoid":"v1","videoname":"vname1"},
{"id":"id2","courseid":"c2","coursename":"cname2","videoid":"v2","videoname":"vname2"}
]

[bwwelle]

Brilliant!  It shows the names when I open the page, saves/updates/destroys at the database level.  The only issue now is that after saving, the original videoname/coursename is displayed in the row and not the changed coursename/videoname, even though it was saved correctly to the database.  It has to be one small little thing.  THANK YOU SO MUCH SO FAR...This is HUGE!

html code:

Code:

 <table id="videocoursedg" title="Video-to-Course Details" style="width:550px;height:250px"
    toolbar="#videocoursetoolbar" idField="id"
    rownumbers="true" fitColumns="true" singleSelect="true">
    <thead>
    <tr>
       <th width="50" data-options="
				field:'courseid',
				formatter:function(value,row){
					return row.coursename;
				},
				editor:{
					type:'combobox',
					options:{
						valueField:'courseid',
						textField:'coursename',
						url:'get_coursesforvideocourses.php',
						required:true
					}
				}
			">Course Name</th>
    <th width="50" data-options="
				field:'videoid',
				formatter:function(value,row){
					return row.videoname;
				},
				editor:{
					type:'combobox',
					options:{
						valueField:'videoid',
						textField:'videoname',
						url:'get_videosforvideocourses.php',
						required:true
					}
				}
			">Video Name</th>
    </tr>
    </thead>
    </table>
    <div id="videocoursetoolbar">
    <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-add" plain="true" onclick="javascript:$('#videocoursedg').edatagrid('addRow')">New</a>
    <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-remove" plain="true" onclick="javascript:$('#videocoursedg').edatagrid('destroyRow')">Destroy</a>
    <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-save" plain="true" onclick="javascript:$('#videocoursedg').edatagrid('saveRow')">Save</a>
    <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-undo" plain="true" onclick="javascript:$('#videocoursedg').edatagrid('cancelRow')">Cancel</a>
    </div>

javascript code:

Code:

<script type="text/javascript">
	    $('#videocoursedg').edatagrid({
    url: 'get_videocourses.php',
    saveUrl: 'save_videocourse.php',
    updateUrl: 'update_videocourse.php',
    destroyUrl: 'destroy_videocourse.php'
    });
	</script>	

update_videocourse.php code:

Code:

<?php
$id = intval($_REQUEST['id']);
$videoid = intval($_REQUEST['videoid']);
$courseid = intval($_REQUEST['courseid']);

        include '../db/database.php';
        include '../db/opendb.php';


$sql = "update vq_videocourse set videoid=$videoid, courseid = $courseid where id=$id";
@mysql_query($sql);

echo json_encode(array(
'id' => $id,
'courseid' => $courseid,
'videoid' => $videoid
));

?>

get_videocourses.php code:

Code:

<?php

include '../db/database.php';
include '../db/opendb.php';
$rs = mysql_query('SELECT vc.id, c.id as courseid, c.coursename, v.id as videoid, v.videoname FROM vq_videocourse vc INNER JOIN vq_video AS v ON v.id = vc.videoid INNER JOIN vq_course AS c ON c.id = vc.courseid');
$result = array();
while($row = mysql_fetch_object($rs)){
	array_push($result, $row);
}

echo json_encode($result);

?>

save_videocourse.php code:

Code:

<?php

$courseid = intval($_REQUEST['courseid']);
$videoid = intval($_REQUEST['videoid']);

        include '../db/database.php';
        include '../db/opendb.php';

$sql = "insert into vq_videocourse(courseid,videoid) values($courseid,$videoid)";
@mysql_query($sql);

echo json_encode(array(
'id' => mysql_insert_id(),
'courseid' => $courseid,
'videoid' => $videoid
));

?>

[bwwelle]

I finally got it to work...YAY!  I'd like to thank all those people who help out on this, stworthy, and stworthy, and God, and patience, and well here is the last part of the working code....enjoy as it was 6 days in the making...wow!  Have a blessed day!

save_videocourse.php code:

Code:

<?php

$courseid = intval($_REQUEST['courseid']);
$videoid = intval($_REQUEST['videoid']);

        include '../db/database.php';
        include '../db/opendb.php';

$sql = "insert into vq_videocourse(courseid,videoid) values($courseid,$videoid)";
@mysql_query($sql);

$id = mysql_insert_id();

$db_result = mysql_query("SELECT vc.id, c.coursename, v.videoname FROM vq_videocourse vc INNER JOIN vq_video AS v ON v.id = vc.videoid INNER JOIN vq_course AS c ON c.id = vc.courseid where vc.id = $id");
// Get the first row (in this case you'll only get one row)
$row = mysql_fetch_array($db_result, MYSQL_NUM);



echo json_encode(array(
'id' => $row[0],
'coursename' => $row[1],
'videoname' => $row[2]
));
?>