I need to be able to put data from a mysql table into a dynamic html table - that’s not difficult but I need to also retrieve the column names, so for the data I can’t use <?php echo $field; ?>. I can get all the data into a table using the following code:
PHP Code:
<?php
function display_db_query($query_string, $header_bool) {
// perform the database query
$result_id = mysql_query($query_string)
or die("display_db_query:" . mysql_error());
// find out the number of columns in result
$column_count = mysql_num_fields($result_id)
or die("display_db_query:" . mysql_error());
if($header_bool) {
echo "<table width='850' border='0' align='center' cellpadding='5' cellspacing='1' class='entryTable'>";
echo "<tr class='entryTableHeader'>";
for($column_num = 0; $column_num < $column_count; $column_num++) {
$field_name = mysql_field_name($result_id, $column_num);
print("<td><center><b>$field_name</b></center></td>");
}
print("</tr>\
");
}
// print the body of the table
while($row = mysql_fetch_row($result_id)) {
print("<tr>");
for($column_num = 0; $column_num < $column_count; $column_num++) {
print("<td class='content' align='center'>$row[$column_num]</td>\
");
}
print("<td class='content' align='center'>Edit</td>\
");
print("</tr>\
");
}
print("</table>\
");
}
function display_db_table($tablename, $header_bool) {
$query_string = "SELECT * FROM products_orderable";
display_db_query($query_string, $header_bool);
}
?>
<HTML><HEAD><TITLE>Products Orderable table</TITLE></HEAD>
<BODY>
<TABLE><TR><TD>
<?php
$table = "table1";
display_db_table($table, //$global_dbh,
TRUE, "border='2'");
?>
</TD></TR></TABLE></BODY></HTML>
But then I need to be able to edit the items in the table or to delete any of them or add another. I am not sure how to do this without any reference to the item since I won’t know what the fields are. Can anyone help please?
thanks
P.S. I’m not allowed (by the spec) to use javascript
create a function which collects field names and key types from your table:
run a query: SHOW FIELDS FROM database.table and you will see how this query works.
and if KEY index is PRI, you can use that to create modify link, delete link.
I have used that query - SHOW FIELDS FROM - but I am not quite sure how that will help me. The first field in the table is the primary key but how do I identify that for editing purposes? A user might want to edit only one of the fields with that primary key so how would I identify that and how would I make the mysql query to update thta in the table? This particular table is a list of products with description, price etc etc so a user may only want to change the descripton or the price, for instance.
Sorry if I am being dense but I am learning PHP as I go along.
thanks for any help
I do know how to insert mysql data into a html table and create the links to do updates and have done that loads of times but never when I did not know the field names first. I can get the primary id and all the other attributes of the field names but I still do not know how to make the link in the data rows to select a row to update or delete and in the case of update how would I do an update query when I do not know the names of the fields to update?
If you could post a code sample to explain that would help.
thanks
OK I should not have got carried away! If you see the code at the top of the thread, I can execute a query to retrieve both the field names and data from a table and insert it into a html table. What I cannot get my head around is how to assign variables in order to make a form to submit. I have got a potential edit link at the end of each row and would like to link that with the primary id which is always the first field in any of our tables. How do I select what to edit??
You need another script for editing.
You don’t have to assign anything in this table. It will be pulled from database and assigned to input fields in that editing script.
So, you ought to go for it. For editing script. You have to done it first.
Right I have retrieved both the field names and the data and sent it to a html table but since I do not know the names of the fields before this and essentially never know (in terms of writing the query) how can I make an input field for the edit? Any of the data in the rows, apart from the primary id, can potentially be edited.
A dynamic updater is looks like this. Its creating input fields.
See, its not as easy as you think, but there are alternate ways for doing updaters. Learn the basics, write the field names into an array, and in a foreach loop you can make input fields. The shorter way is a bit simplier.
From this code the pager, delete(batch delete) order , batch edit , search and insert features are missing.
And its not always true that ‘id is always the first field’…
Oh, I never thought it was easy! That’s why I was having such a problem understanding how I should approach it. Thank-you so much for showing me, that code does what I want to do for the editing, now I can add the delete and add parts. Thank-you again, my php knowledge is growing!
I am using the code you gave me to do the updates on my table and have incorporated it into my system, everything works fine. However, try as I might I cannot work out how to do a delete on a whole row. This can either be done in the same file as the update or in a separate one i.e the delete and update do not have to be there at the same time. I have tried both ways but I just can’t get it to work. I also need to be able to ask the user to confirm that they want to delete a row before the mysql query is actioned. And re your last remark about Ajax - I wish I could use it but the spec I’ve been given is that NO javascript can be used - just to make things more difficult!
Thanks for any help, hints you can give
I understand how to do that but I am not sure how to incorporate it with the rest of the php I am using for the update - or would this go into a separate file for deleting? If so, the question again is how to incorporate it with the code to display all the field names and products with their primary IDs as in the code above. I have tried but failed miserably so far!
better to go for separate file.
later you can join files, but while learning it is much better to have separate files.
Also, it is much better for learning purpose to update and delete by one entry, not multiple.
Or you’ll stick for someone’s help forever. You’ll get it, yeah. But if you really want to learn, things must be learned distinctly. Step by step. From easy steps to more complicated. Multiple item forms are complicated.
I agree learning in small steps is the best way but I seem to make big leaps forward in some directions and in others a tiny step seems to be a huge problem!
As I said I used the code you suggested for the update, which works fine (the update I mean) but try as I might the best I can manage so far for adapting it for a delete is to get the Field names into a table but no field values and obviously no way of deleting what I cannot retrieve! It makes it very difficult not knowing the names of the fields and also not being able to use javascript for the confirmation of delete action. Can you help please?
That makes these tasks.