I have a table that works like this: http://www.datatables.net/examples/api/editable.html without the header sorting, page changing, and search. I have another functionality that allows me to add a row. All of this is done on the same page. The data is drawn directly from a database. I wrote the code generic so it could be used for any table I want to display.
However, I have came across a problem. Let's say an end-user wants to see a list of houses. This list would be drawn from a houses database. Each house has an owner. There is also an owners table. Each owner has an id (primary_key). In the houses table the owner field uses the owner's id to identify the proper owner. Here is where the problem arises. Once the data from the houses table is displayed the owner, for instance, shows up as an id number. Obviously, to the end-user it either is meaningless or at least annoying. I would like to have, in this case the owner's name, the field that is in question to show instead of a "seemingly" meaningless field. I'm posting the relevant code for my predicament.
Also, can I change mySQL booleans through jQuery? What I mean by that is if, for example, a house is not up for rent so the for_rent flag is set to 0 for FALSE. The table will show 0, as that is what is in the table. Can I change that through jQuery? (Find the 0s or 1s and make them say true or false? Any suggestions as to a direction for answering these questions would be great. Thanks.
Here is the relevant code:
PHP to display table:
public function displayTable($table)
{
//connect to DB
$con = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
echo "<table id='table' border='1'>"; //start an HTML table
$dbtable = $table;
$fields =array();
$result = mysqli_query($con, "SHOW COLUMNS FROM ".$dbtable);
//fill fields array with fields from table in database
while ($x = mysqli_fetch_assoc($result))
{
$fields[] = $x['Field'];
}
$fieldsnum = count($fields); //number of fields in array
//create table header from dbtable fields
foreach ($fields as $f)
{
echo "<th>".$f."</th>";
}
//create table rows from dbtable rows
$result = mysqli_query($con, "SELECT * FROM ".$dbtable);
while ($row = mysqli_fetch_array($result))
{
$rowid = $row[$fields[0]];
echo "<tr class='edit_tr' id='".$rowid."'>";
foreach ($fields as $f)
{
echo "<td class='edit_td' data-field='".$f."'><span id='".$rowid."' class='text'>".$row[$f]."</span>
<input type='text' value='".$row[$f]."' class='editbox' id='".$rowid."' data-field='".$f."'/> </td>";
}
$rowid++;
echo "</tr>";
}
echo "</table>"; //close the HTML table
$recordid = $rowid;
//close connection
mysqli_close($con);
}
jQuery to live edit table:
$(document).ready(function()
{
$(".edit_td").click(function()
{
$(this).children(".text").hide();
$(this).children(".editbox").show();
}).children('.editbox').change(function()
{
var table = $('body').attr('id');
var id=$(this).closest('tr').attr('id');
var field=$(this).data('field');
var text=$(this).val();
var dataString = {table:table, id:id, field:field, text:text};
if (field != text)
{
$.ajax({
type: "POST",
url: "classes/table_edit_ajax.php",
data: dataString,
cache: false,
success: function(html)
{
window.location.reload(true);
}
});
}
else
{
alert('Enter something.');
}
});
// Edit input box click action
$(".editbox").mouseup(function()
{
return false
});
// Outside click action
$(document).mouseup(function()
{
$(".editbox").hide();
$(".text").show();
});
});
jQuery to live add row:
$(document).ready(function()
{
$(".add").click(function()
{
var fieldArray = [];
var $table = $("#table");
var $lastRow = $table.find("tr:last");
var $dataFields = $lastRow.find("td");
$dataFields.each(function() {
fieldArray.push($(this).attr("data-field"));
});
$("#table").each(function()
{
var $table = $(this);
var id=$('#table tr:last').attr('id');
var $tr = $("#table").children('tr');
var tablename = $('body').attr('id');
var n = $('tr:last td', this).length;
var tds = '<tr class="edit_tr" id="' + id++ + '">';
for(var i = 0; i < n; i++)
{
tds += '<td class="edit_td" data-field="' + fieldArray[i] +
'"><span id="'+ id +'" class="text"> </span><input type="text" class="editbox" id="' +
id + '" data-field="' + fieldArray[i] + '"/> </td>';
console.log('id: ' + id);
}
tds += '</tr>';
var dataString = {table:tablename, id:id};
if($('tbody', this).length > 0)
{
$('tbody', this).append(tds);
$.ajax({
type: "POST",
url: "classes/table_new_ajax.php",
data: dataString,
cache: false,
success: function(html)
{
window.location.reload(true);
}
});
}else {
$(this).append(tds);
}
});
});
});
you will probably want to extend your generic function for generating the html table to include a joined db table if necessary, though that would get messy, so, create a new function for when you need to join 2 db tables. The sql for retrieving the owners name into the list of houses would go something like (with a guess at what your field names are): select a.housename,a.street,a.for_rent,b.name from houses a, owners b where a.owner_id=b.id