I have an SQL-database with many tables. Now I would like to create an input-form to be able to get data into the db without writing the entire sql-code every time. And this should work as follows:
All table names are listed in a drop-down menu. After having selected a table name, a new table with 4 columns is created automatically:
The first column of this table simply contains an increasing number. The second column contains the field-names of the selected table. In the third column there are empty input fields to enter the values for the database. Only in the third line (=product name) there is a drop-down menu with all product names from the main-table of the db. The fourth column contains the data type (e.g. int or varchar)
All tables in the database have the same structure in the first 3 columns: the first column contains the table-id, the second column the foreign-key (=master_id) and the third column the product_name.
Up to this point, the script works well with the following 2 php-files (javasql.php and getuser.php):
javasql.php:
enter code here
<!DOCTYPE html>
<html>
<head>
<script>
function showUser(str) {
if (str=="") {
document.getElementById("txtHint").innerHTML="";
return;
}
if (window.XMLHttpRequest) {
// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
} else { // code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function() {
if (this.readyState==4 && this.status==200) {
document.getElementById("txtHint").innerHTML=this.responseText;
}
}
xmlhttp.open("GET","getuser.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>
<form>
<select name="users" onchange="showUser(this.value)">
<option value="" class="optdrugs">please select</option>
<?php
include("files/zugriff.inc.php"); // database Access
$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'product'";
$result = mysqli_query($db, $sql);
while ($row = mysqli_fetch_assoc($result)) {
echo '<option class="optdrugs" value="'. $row['TABLE_NAME'] . '">' .
$row['TABLE_NAME']. '</option>';
echo '<br>';
}
?>
</select>
</form>
<br>
<div id="txtHint"><b>Bitte Tabelle auswählen:</b>
<br>
<?php
if (isset($_POST["submit"])) {
$sent = $_POST['sent'];
$q = $_POST['tablename'];
$column_passed = unserialize($_POST['column']); // content of array
$column is passed from getuser.php
foreach ($_POST["insertvalue"] as $key => $value) {
echo $value . "<br>";
$werte[] = "'$value'";
}
$sql="INSERT INTO $q ($column_passed) VALUES (" .
implode(", ", $werte) . ")"; // data entry
mysqli_query($db, $sql);
if (mysqli_affected_rows($db) > 0) {
echo "<h3 style='color:blue'>successful</h3>";
} else {
echo "<h3 style='color:red'>not
successful</h3>";
}
}
?>
</div>
</body>
</html>
enter code here
getuser.php:
<!DOCTYPE html>
<html>
<head>
<style>
table {
width: 100%;
border-collapse: collapse;
}
table, td, th {
border: 1px solid black;
padding: 5px;
}
th {text-align: left;}
</style>
</head>
<body>
<form id="formdatabase" name="formdatabase" action="javasql.php"
method="post">
<input type="hidden" name="sent" value="yes">
<?php
$q = strval($_GET['q']);
$con = mysqli_connect('localhost','root','','product');
if (!$con) {
die('Could not connect: ' . mysqli_error($con));
}
mysqli_select_db($con,"ajax_demo");
$sql="SELECT * FROM $q";
$result = mysqli_query($con,$sql);
$numcols = mysqli_num_fields($result); // gets number of columns in result table
$field = mysqli_fetch_fields($result); // gets the column names from the result table
$data_type_array = array(
1=>'tinyint',
2=>'smallint',
3=>'int',
4=>'float',
5=>'double',
7=>'timestamp',
8=>'bigint',
9=>'mediumint',
10=>'date',
11=>'time',
12=>'datetime',
13=>'year',
16=>'bit',
252=>'text',
253=>'varchar',
254=>'char',
246=>'decimal'
);
$data_type_array = array_flip($data_type_array);
echo "<table>";
echo "<tr>";
echo "<th>" . 'Nr' . "</th><th>" . 'Column names' . "</th>
<th>" . 'Values for db-entry' . "</th><th>" . 'Type' . "</th>";
echo "</tr>";
echo "<tr>";
$nr = 1;
for($x=0;$x<$numcols;$x++):?>
<td><?= $nr; ?></td>
<td><?= $field[$x]->name; ?></td>
<?= $column[] = $field[$x]->name; ?>
<td>
<?php
if ($field[$x]->name == 'Name') { // if-Beginn
?>
<select name="insertvalue[<?= $x; ?>]" id="insertvalue<?=
$x; ?>" size="1" onchange = "javascript:getSelectedRow()">
<?php
include("files/zugriff.inc.php");
$sql = "SELECT * FROM product_main ORDER BY Name";
$result = mysqli_query($db, $sql);
while ($row = mysqli_fetch_assoc($result)) {
echo '<option class="optdrugs" value='. $row['Name'] . '>' .
$row['Name'] . '</option>';
echo '<br>';
}
?>
</select>
<?php
$name_option = "";
} else {
$name_option = "<input type='text' id='insertvalue" . $x . "'
name='insertvalue[" . $x . "]' size='50'>";
echo $name_option;
}
?>
</td>
<?php
$key = array_search($field[$x]->type, $data_type_array);
if($key !== false){
echo "<td>" . $key . "</td>";
}else{
echo "<td>" . $field[$x]->type . "</td>";
}
?>
<td><?= $field[$x]->type; ?></td>
<?= $nr = $nr + 1; ?>
</tr>
<?php endfor;
echo "</table>";
mysqli_close($con);
?>
<input type="hidden" name="tablename" value="<?= $q; ?>">
<input type="hidden" name="column" value="<?php echo htmlentities
(serialize($column)); ?>">
<input type="submit" value="Enter values" name="submit">
</form>
</body>
</html>
Since I need the master_id (= foreign key) in addition to the product-name for database entry, I would like to extend my script, so that the respective master_id is automatically sent to the input field in line 2, when a product-name is selected in line 3 ... without clicking a button. I tried to do this with javascript but it didn´t work. As far as I know, the solution would be to use AJAX but unfortunately, I am not very used to AJAX. I would be more than happy, if someone could help me to solve this problem!
