Forum Moderators: coopster
For most of the fields there isn't a problem- user fills in an input or select or textarea. The problem is that some of the fields need to hold table data, or at least data that outputs as a table. My predecessor had the users basically creating tables in HTML and inputting the whole thing into the field, which meant a load of extra data in the db (i.e <table><tr><td> all over the place). I don't want the db to be filled up with junk HTML, just the raw data.
One example is a table with a load of columns and rows, each cell holding data. There are about a hundred cells in this data and I don't want to have to create an input and a variable for each one. Is there an easy way to get all the required data into the field as a single variable, WITHOUT including the table formatting, and having that output display on a different page as a formatted table?
I can't seem to figure a way of doing this- any ideas?
First, storing raw HTML is dangerous at best, is an opening for XSS. But that's another story, and you're hoping to get rid of it.
Here's how I'd approach this. It may seem complex but once you get a system down to output the forms and collect the data, it can be modified to suit.
It should be easy to visualize a database table to HTML table relationship:
id¦unique_id¦cell1¦cell2¦cell3¦cell4
1¦1234¦John¦Doe¦123-4567¦here@example.com
1¦1234¦Mary¦Doe¦765-4321¦there@example.com
1¦1235¦Steve¦Smith¦444-3333¦anywhere@example.com
So when generating forms, you automate the process: get the field names of just the cells 1,2,3,4, generate a text field for each. You would create each field's form field with a unique id, like
for ($i=0;$i<=$num_cols;$i++) {
$name = 'cell' . $i . '_' . $row['id'];
}
So when input, you split on _ to see which rows to update.
Have a separate form below that for "new values" to add.
Output is easy too, and gives you absolute control:
select * from table where unique_id=1234;
And you add the HTML data on output. Strip all HTML on input, done.
So, theoretically, you could write something like "value(value,value,value,value),value(value,value,value,value)" etc, and on output you can parse it and turn it back into an array.
Not sure how I'd do the gathering of data, as the data really needs to be stored from individual inputs filled in by the site admins.
Guess I should start from the data input process and work through to explain exactly what we need to happen.
What we have is an "Add a new car" page with a form for admins to use. Part of this form (or maybe a separate one, doesn't matter) needs to be an HTML table with two columns, Date and Service History, and a load of rows (really we want admins to be able to click somewhere to have an extra row added if need be, but that's a separate issue).
So they type in the first row, a date, and a corresponding service history. Then they move to the second row, fill in another date and another service history, and so on.
Each <td> contains an input for them to enter either the date or the service history. We need to collect the data in each input and pass it through to a variable in the PHP that handles inserting the data into the table.
So for example, taking just one row of the HTML table, if we have two inputs, _Date1 and _ServiceHistory1, these are passed to $date1 and $servicehistory1.
Then we need to connect to the db and run the INSERT, e.g
$query = "INSERT INTO servicehistorytable (date, servicehistory) VALUES ('$date1, $servicehistory1)";
Problem is that there are many other dates and service histories that need to be added at the same time, so how can we do this? (without having huge numbers of extra db table fields, e.g date2 to hold a value for $date2, and so on)
And if we just dump all the inputted dates into the date field, and all the inputted service histories into the servicehistory field, how then can we output them separately in the HTML output table?
What we have is an "Add a new car" page with a form for admins to use. Part of this form (or maybe a separate one, doesn't matter) needs to be an HTML table with two columns, Date and Service History, and a load of rows (really we want admins to be able to click somewhere to have an extra row added if need be, but that's a separate issue).
I'm going to use raw post/POST variables for simplicity - keep in mind these need to be cleansed. the preg_match helps for num_rows, but anyway . . . Note that on error, this enabled you to return to the form with values populated. Typed out on the fly, may need debugging.
First, a toggle of logic.
header("content-type:text/html"); // or include your header
if (isset($_POST['add_rows']) and ($_POST['add_rows'] == 1)) { add_to_db(); }
else { output_rows(); }
ouput_rows:
if (isset($_POST['num_rows']) and preg_match('/^\d+$/',$_POST[num_rows])) {
$out = '
<form method="post" action="yourscript.php">
<input type="hidden" name="num_rows" value="' . $_POST['num_rows'] . '">
<input type="hidden" name="add_rows" value="1">
<table width="75%" align="center">
<tr><td class="tbl-head">Date</td><td class="tbl-head">Service History</td></tr>
';
for ($i=1;$i<=$_POST['num_rows'];$i++) {
$dt_name='date_' . $i;
$svc_name='date_' . $i;
$dt_exists = (isset($_POST[$dt_name]))?$_POST[$dt_name]:'';
$svc_exists = (isset($_POST[$svc_name]))?$_POST[$svc_name]:'';
$out .= '
<tr><td>input type="text" name="' . $dt_name .
'" id="' . $dt_name . '" value="' . $dt_exists . '"></td>' .
<td>input type="text" name="' . $svc_name .
'" id="' . $svc_name . '" value="' . $svc_exists . '"></td></tr>';
}
$out .= '
<tr><td colspan="2" class="center-align"><hr><input type="submit" value="Add Entries"><hr></td></tr>
</table>
</form>
';
}
else {
$out = '
<form method="post" action="yourscript.php" onSubmit="return chkSelect(this);">
<p class="center-align">Enter the number of rows you need:</p>
<p class="center-align"><select name="num_rows" id="num_rows"><option value="0">Select<option>
';
for ($i=1;$i<20;$i++) {
$out .= '<option value="' . $i . '">' . $i . '</option>';
}
$out .= '
</select></p>
<p class="center-align"><hr><input type="submit" value="Next >>"><hr></p>
</form>
';
<script type="text/javascript">
function chkSelect(form) {
if (form.num_rows.selectedIndex==0) { alert(\'Select the number of rows you need.\'); }
else { form.submit(); }
return false;
}
</script>
';
}
echo $out;
You now have as many rows as you need, a convoluted "add a row" is not necessary.
The one thing I'd change here is instead of a text field for date, write up a little function to return three select lists for month, day, year. Your object naming would follow some convention, like
date_1_month
date_1_day
date_1_year
Then we need to connect to the db and run the INSERT, e.g.
add_to_db:
for ($i=1;$i<=$_POST['num_rows'];$i++) {
$dt_name='date_' . $i;
$svc_name='date_' . $i;
// Cleanse, cleanse, cleanse! Also use mysql_real_escape_string for quoting
if (isset($_POST[$dt_name]) and isset($_POST[$svc_name])) {
$query = "insert into servicehistorytable (date, servicehistory) " .
"values ('" . mysql_real_escape_string($_POST[$dt_name])) . "', '" .
mysql_real_escape_string($_POST[$svc_name]) . "'";
mysql_query("$query") or die("Could not add new row: " . mysql_error());
}
}
output_response();
A note, I would not use a field name date or datetime, etc. You can using backticks, but you should just avoid reserved words.
To extract the data,
$tablecontent=NULL;
$query = "select * from servicehistorytable";
$result=@mysql_query($query);
if (!$result) { die ("no results"); }
while ($row=mysql_fetch_array($result)) {
$dt = $row[0]; // or $row['date'];
$svc = $row[1];
$tablecontent .= '<tr><td>' . $dt . '</td><td>' . $svc . '</td></tr>';
}
mysql_free_result($result);
if ($tablecontent) {
$out = '
<table width="75%" align="center">
<tr><td class="tbl-head">Date</td><td class="tbl-head">Service History</td></tr>
' . $tablecontent . '</table>';
}
else { $out = '<p class="center-align">No results to display.</p>'; }
echo $out;
}
There should be probably more fields, such as a job or mechanic id or whatever so you can change the select and extract only the records for that employee, but that's the basic concept.
This is what I have for the data inputting table:
<?phpinclude ('../inc/dbconnect.php');
if (isset($_POST['add_rows']) and ($_POST['add_rows'] == 1))
{
add_to_db();
}
else { output_rows(); }
if (isset($_POST['num_rows']) and preg_match('/^\d+$/',$_POST[num_rows]))
{
$out = '<form method="post" action="addnewservicedetails_addtodb.php"> <input type="hidden" name="num_rows" value="' . $_POST['num_rows'] . '"><input type="hidden" name="add_rows" value="1"> <table width="600"> <tr><td class="tbl-head">Date</td><td class="tbl-head">Service History</td></tr>';
for ($i=1;$i<=$_POST['num_rows'];$i++)
{ $dt_name='date_' . $i; $svc_name='date_' . $i; $dt_exists = (isset($_POST[$dt_name]))?$_POST[$dt_name]:''; $svc_exists = (isset($_POST[$svc_name]))?$_POST[$svc_name]:'';
$out .= ' <tr><td>input type="text" name="' . $dt_name . '" id="' . $dt_name . '" value="' . $dt_exists . '"></td>' . '<td>input type="text" name="' . $svc_name . '" id="' . $svc_name . '" value="' . $svc_exists . '"></td></tr>';
}
$out .= ' <tr><td colspan="2"><hr><input type="submit" value="Add Entries"><hr></td></tr> </table> </form> ';
}
else
{ $out = '<form method="post" action="addnewservicedetails_addtodb.php" onSubmit="return chkSelect(this);"> <p>Enter the number of rows you need:</p> <p><select name="num_rows" id="num_rows"><option value="0">Select<option> ';
for ($i=1;$i<20;$i++)
{
$out .= '<option value="' . $i . '">' . $i . '</option>';
}
$out .= '</select></p> <p><hr><input type="submit" value="Next >>"><hr></p></form> ';
echo '<script type="text/javascript"> function chkSelect(form)
{ if (form.num_rows.selectedIndex==0) { alert(\'Select the number of rows you need.\'); } else { form.submit(); } return false;
}
</script>'; }
echo $out;
?>
(The Javascript was giving a syntax error so I wrapped this in echo ' which at least stopped the error, but didn't achieve much else...)
For the script that the form points to I've got:
<?php
for ($i=1;$i<=$_POST['num_rows'];$i++)
{
$dt_name='date_' . $i; $svc_name='date_' . $i; // Cleanse, cleanse, cleanse! Also use mysql_real_escape_string for quoting if (isset($_POST[$dt_name]) and isset($_POST[$svc_name]))
{
$query = "insert into servicehistorytable (servicedate, servicehistory) " . "values ('" . mysql_real_escape_string($_POST[$dt_name])) . "', '" . mysql_real_escape_string($_POST[$svc_name]) . "'"; mysql_query("$query") or die("Could not add new row: " . mysql_error());
}
}
output_response();
?>
So I've changed the name of the date field inside the db table to "servicedate".
The input page just isn't showing anything though- not giving an error, just not displaying the table...?
Can you view source? Is there output that is just not displaying? Include a proper header and footer so you have <doctype><head><title></title></head><body> (output) </body></html>
The only problem with the echo is you are storing in "$out" then echo the JS, so JS will output before $out, and probably in the wrong place. You're best off to debug it as it was, but your call. Reason I say that is the syntax error is probably still there, it's just you hidden it somehow . . .can't see a direct error in the original JS, so it's probably a missing quote somewhere.
No errors to screen, probably, what about error_logs? When cornered just do the trial and error solution:
if (condition) { print "OK"; }
else { print "oops condition missed"; }
Then slowly re-add your code until it resurfaces.
