So I'm trying to create an interface for the user where they can insert, delete and update a table they have in a sql db through the php UX. I've got the basics working, however i've ran into a wall trying to maintain a dynamic feel to it. So let's say hypotethically speaking, a new column is added to the particular table they are trying to update from the sql side of things. Now I've got it so that it will display the new column added, BUT how do I include that new column in my UPDATE sql query. I've got designated variables for each column (ie. $user, $pass, $email) but my question is what if theirs a new column being added, how do we set up the code so that it will automagically add a new variable and maintain the dynamics? I'm going crazy and really need someone's help cause it sounds so simple. (YES I AM QUITE NEW TO THIS).
Thank you for all your help guys! please let me know how I can clarify this question for anyone.
BELOW IS MY WORKING CODE FOR UPDATING A TABLE THAT HAS FIXED COLUMNS. PLEASE ADVISE WHAT I NEED TO ADD OR CHANGE.
<?php
$result = sqlsrv_query($con,"SELECT * FROM table");
$colresult = sqlsrv_query($con,"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table' AND TABLE_SCHEMA='schema'");
$serverName = xxx";
$connectionInfo = array("Database"=>"db", "UID"=>"user", "PWD"=>"pass", 'ReturnDatesAsStrings'=> true, "CharacterSet" => 'utf-8');
$con = sqlsrv_connect( $serverName, $connectionInfo);
$numfields = sqlsrv_num_fields($result);
if( $con ) {
echo "Connection established.<br />";
}
else{
echo "Connection could not be established.<br />";
die( print_r( sqlsrv_errors(), true));
}
if (isset($_POST['updatemarked']))
{
foreach($_POST["id"] AS $id){
**$user = $_POST["username"][$id];
$pass = $_POST["password"][$id];
$email = $_POST["email"][$id];**
if(!$user){
}
else{
$sqlupdate= sqlsrv_query($con,"UPDATE table SET username = '$user' WHERE id = $id");
}
if(!$pass){
//$pass = 'empty';//$row['password'];
}
else{
$sqlupdate= sqlsrv_query($con,"UPDATE table SET password = '$pass' WHERE id = $id");
}
if(!$email){
//$email = 'empty';//$row['email'];
}
else{
$sqlupdate= sqlsrv_query($con,"UPDATE table SET email = '$email' WHERE id = $id");
}
}
}
echo '
<form action="table.php" method="post">
<table table class="dynamicTable tableTools table table-striped table-bordered table- condensed table-white">';
echo '<tr>';
echo '<th width="50" align="center"><span style=color:black><strong>Select(s)</strong> </span></th>';
while($row = sqlsrv_fetch($colresult)){
$fieldname = sqlsrv_get_field($colresult, $i);
echo "<th>".$fieldname."</th>";
}
echo "</tr>";
while ($row = sqlsrv_fetch_array($result))
{
echo "<tr>";
echo '<td><input type="checkbox" name="id[]" value="'.$row['id'].'" /></td>';
echo "<td>".$row['id']."</td>";
echo '<td><input type="text" name="username['.$row['id'].']" placeholder="'.$row['username'].'"/></td>';
echo '<td><input type="text" name="password['.$row['id'].']" placeholder="'.$row['password'].'"/></td>';
echo '<td><input type="text" name="email['.$row['id'].']" placeholder="'.$row['email'].'"/></td>';
echo "</tr>";
}
echo'</table>';
echo'<br />';
echo '<div align="center">';
echo '<input type="submit" name="updatemarked" value="Update Selected" />';
echo '</div>';
echo '</form>';
?>
