How to show, edit and save with PHP PDO MSSQL

[Darkstar0078]

Hello all together,

 

i want to realize a System database, where we will manage all our PCs, Notebooks aso.,

therfore I created an website in our intranet with php. This website is connecteg

with a MSSQL Database, where the informations will be stored.

 

All works fine, i have an separate site to add a new device, one to serach for an device

and one to edit a device.

 

Now I want to realize that I can use only one site, to handle all three action, show, edit and save.

 

Here is my working Code:

<?php
//Add new entry
require "dbconnect.php";
require "./Languages/_deDE.php";

if (isset($_POST["device"])) {
	
	$device=$_POST["device"];
	
	if ($device==null){
		
	   $department = $_POST['department'];
	   $country = $_POST['country'];
   
   			$statement =$conn -> prepare("INSERT INTO dbo.DeviceTable (Department, Country) VALUES (:department, :country)");
			$statement->bindParam(':department', $department);
			$statement->bindParam(':country', $country);

	
   if (!$statement->execute()) {

     $arr = $statement->errorInfo();
     print_r($arr);
     exit;
   }
   else {

     echo "Ok";

   }
	}
	// edit entry
	else {  
	
   $department = $_POST['department'];
   $country = $_POST['country'];
   
   	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $statement = $conn->prepare("SELECT Department, Country, FROM dbo.DeviceTable WHERE DevID = $device"); 
    $statement->execute();
    
    while ($result = $statement->Fetch(PDO::FETCH_ASSOC))
	$statement->bindParam(':department', $department);
	$statement->bindParam(':country', $country);

   if (!$statement->execute()) {

     $arr = $statement->errorInfo();
     print_r($arr);
     exit;
   }
   else {

     echo "Ok";

   }

}
}

?>  
 
<form action="?site=forum" method="post">
<br/>
<!--ComboBox select a entry to edit-->
<?php  echo $show_device; ?><br/>
<select name="device" id="device">  
<option selected="selected" disabled="disabled">Bitte auswählen...</option>

<?php
		$smt1 = $conn->prepare('select DevID, Office From DeviceView');
		$smt1->execute();
		$data = $smt1->fetchAll();?>
		<?php foreach ($data as $row): ?>
    	<option value ="<?=$row["DevID"]?>"><?=$row["DevID"];?><?=$row["Office"];?></option>
		<?php endforeach?>
</select><br/><br/>
<input type="submit" name="submit" value="<?php  echo "$show_search"; ?>">
<div style="float:left; width:200px">
<!--Eingabe der Informationen-->
<?php  echo $addnewdevice_department; ?><br/>
<input type= "text" name="department"><br/><br/>
<?php  echo $addnewdevice_office; ?><br/>
<input type= "text" name="office"><br/><br/>

<input type="submit" name="submit" value="<?php  echo $addnewdevice_submit; ?>">
</div>
</form>

With this code I can add a new device, but I can not show or open an already existing device to edit it and save it.

 

I´m an not a profi in php, but I´m learning every day a lot of, with a lot of fun.

 

Would be somebody so kind to support me with my problem.

 

That would be great.

 

Kindly regards

 

DarkStar 

[ginerjm]

You need to read your code and think about it. The first thing you do is check for something and if it is NOT present you proceed to add a record. Huh?

 

You should edit your inputs to ensure that they are not malicious and that they are the kinds of things you expect. Otherwise people are going to be stuffing your db with garbage or worse.

 

Is English your primary language? It's not clear from your post what with all the errors in it. I have to ask you to clarify something. What do you define a "site" as? You state that you have 3 sites. Normally I would suggest that you have one site with perhaps 3 or more screens to handle the different functions you need, although personally I would combine the add/edit functions to one screen.

Edited November 9, 2016 by ginerjm

[Darkstar0078]

Hello . Thank you for the answer.

 

No, english is not may native language, it is german.

 

With site I meant a website.

 

I´m always looking through my code :-), cause I´m learning.

The if is Null I had already deleted, it was only an earlier idea.

 

This databse will only used by my, because I´m in charge for the IT.

 

I have seen another thing, I have to use UPDATE not INSERT.

But before I can use this I need to see the entry that I want to update.

 

My problem is, what I have to do that the variable in the Form can

one time show the values of an entry and other time can forward them

to the database. I´m sitting on this problem since 2 days and I

think that I am very near the solution :-).

 

Best regards

 

DarkStar 

[ginerjm]

Yes - site is the name for a website. My question was - are you really creating multiple websites? To be blunt - I do believe you are creating an 'application' on ONE 'website' that may have multiple scripts and screens.

 

From your questions I would suggest that you do some reading up on how to code on the web. Html and PHP manuals/guides/articles would be very helpful. Google is your friend. You appear to need quite a bit of instruction at this point.

 

One can use the sql syntax of :

 

insert into table (fld1, fld2, fld3, ...) 
      values('$val1', '$val2', ...)
   on duplicate key update fld1='$val1',fld2='$val2', ...";

which would do an update when the primary key already exists on the table. Of course all of those values need to be sanitized before putting them into this query, or be substitute parms in a prepared query statement such as:

insert into table (fld1, fld2, fld3, ...) 
      values(:val1a, :val2a, ...)
   on duplicate key update fld1=:val1b, fld2=:val2b, ...";

If you don't know what the above is, then you definitely need to do some more reading.

[Darkstar0078]

Hello ginerjm,

 

thank you very much for your support.

 

You are right, I have one main php page where I include the different pages to work with the database.

 

Okay, I understand the SQL Code and I know how to handle it.

 

But, how can I use one variable in the html form to show (select), edit (update) or create (insert) an entry in the database,

or is it impossible?

 

When I take different variables do this it works well, when I want to use the same it doesn´t work.

[ginerjm]

I have no idea what your client is looking at so it is impossible to answer your last question. One could design a screen that asks the user to select an activity and then he would be directed to the specific screen to do that thing. Or one could design that same screen to offer the client any of the choices in multiple forms so that all the actions could be accomplished on that one screen. Your choice, but until we see what you "design" we can't answer your question.

Edited November 11, 2016 by ginerjm

[Darkstar0078]

Hello all together,

 

since my last problem I learned a lot of about PHP, PDO, SQL and CRUD. So I have redone my project

and it is now nearly perfect.

 

I have now a problem with an update function, I have made an edit.php website, on this website you can

edit existing data. This works fine, when I only use the <input></input> method.

 

But I want to use a ComboBox to select a value from a table from the sql database and there is my problem.

 

Example:

 

On an index.php website I have a list with all devices, in each row I can say edit, if i click edit a new

website will be loaded where you see already the added data.

 

There I have one Combo Box and two text fields like <input type='text' name='1' ></input>

When I only want to update an textfield and not the combo box, and I press my update button

the textfield will be stored correct, but the combo box will be stored empty and this is normal 

because there is no value preselected. Here is my ComboBox:

<select   name="computername" id="computername" class='form-control' >  
<option    selected="selected" disabled="disabled"><?php echo $ComputerName; ?></option > 
<?php
$smt = $DB_con->prepare('select ComID, ComputerName  From ComputerNameView ORDER BY ComputerName');
$smt->execute();
$data = $smt->fetchAll();
foreach ($data as $row): ?>
<option  value ="<?=$row["ComID"]?>"><?=$row["ComputerName"]?></option >
<?php endforeach?>
</select>

In the first option there will be shown the value wich is actually written in the database, in the 

second option you can select a new value.

 

How can I realize it, that when I will not change the comboBox, and then say update, that

the old value will be saved in the database and not NULL.

 

I hope I have written not to complicated :-)

 

Thanks a lot

 

BR  Sven

[kicken]

Don't add an extra item at the top to represent the currently selected item. Just add a selected attribute to the appropriate option value.

 

You should also clean up your spacing in your html tags some, like remove the space between value and =, it'll read better and look nicer.

 

<?php
$smt = $DB_con->prepare('select ComID, ComputerName  From ComputerNameView ORDER BY ComputerName');
$smt->execute();
?>
<select name="computername" id="computername" class='form-control'>  
<?php foreach ($smt as $row): ?>
    <option value="<?=$row["ComID"]?>" <?=($ComID==$row['ComID'])?'selected':''?>><?=$row["ComputerName"]?></option>
<?php endforeach; ?>
</select>

That is assuming you have a variable named $ComID that represents the currently selected ID. Change that variable to whatever is appropriate for your actual code.

[Darkstar0078]

Hello Kicken,

 

Thank you for your quick answer.

 

But it seems that this does the same like my code do.

 

Sorry for the chaos script, this is the result of many tests :-D.

 

So, for better understanding i will describe more deaply.

 

I have one main table which is called DeviceTable, in this table I have

two text fields and one int field and of course one index.

 

The text fields will be set by typing.

The int field will be filled with a number which points to another table.

 

This table is called ComputerName, here you will find the index/id and the ComputerName 

which is a text field.

 

When you now add a new device to the database, you have to select the ComputerName

from a Combo Box, and the rest information you have to type by hand.

The ComputerName is fixed because it is an AD name and unique and to beware for misstyping.

 

When you want to edit the data from a device the website will look so:

<?php
include_once 'dbconfig.php';
if(isset($_POST['btn-update-ome']))
{
		$id = $_GET['edit_ome_id'];
	 	$employee = $_POST['employee'];
   		$adminun = $_POST['adminun'];
   		$adminpw = $_POST['adminpw'];
  		$computername = $_POST['computername'];
  
	
	if($crud->updateome($id,$employee,$adminun, $adminpw,$computername))
	{
		$msg = "<div class='alert alert-info'>
				<strong>WOW!</strong> Record was updated successfully <a href='indexome.php'>HOME</a>!
				</div>";
	}
	else
	{
		$msg = "<div class='alert alert-warning'>
				<strong>SORRY!</strong> ERROR while updating record !
				</div>";
	}
}

if(isset($_GET['edit_ome_id']))
{
	$id = $_GET['edit_ome_id'];
	extract($crud->getIDome($id));
}

?>
<?php include_once 'header.php'; ?>

<div class="clearfix"></div>

<div class="container">
<?php
if(isset($msg))
{
	echo $msg;
}
?>
</div>

<div class="clearfix"></div><br />

<div class="container">
	 
     <form method='post' enctype="multipart/form-data" >
 
    <table class='table table-bordered'>
 			<tr>
          		<td style="font-size:20px" colspan="6">Main Information</td>
          	</tr>
        	<tr>
            	<td ><label>Device name</label></td>
            	<td >
					<?php
                    $smt = $DB_con->prepare('select ComID, ComputerName  From ComputerNameView ORDER BY ComputerName');
                    $smt->execute();
                    ?>
                    <select name="computername" id="computername" class='form-control'>  
                    <?php foreach ($smt as $row): ?>
                        <option value="<?=$row["ComID"]?>" <?=($ComID==$row['ComID'])?'selected':''?>><?=$row["ComputerName"]?></option>
                    <?php endforeach; ?>
                    </select>
			</td>
            	<td>Admin Username</td>
            	<td><input  name="adminun" id="position" class='form-control' value="<?php echo $AdminUsername;?>"></td>
       			<td>Admin Password</td>
 				<td><input type= "text" name="adminpw" class='form-control' value="<?php echo $AdminPassw;?>"></td>              
       		</tr>
        <tr>
            <td colspan="2">
            <button type="submit" class="btn btn-primary" name="btn-update-ome">
    		<span class="glyphicon glyphicon-plus"></span> Update Device
			</button>  
            <a href="indexome.php" class="btn btn-large btn-success"><i class="glyphicon glyphicon-backward"></i>   Back to Home</a>
            </td>
        </tr>
    </table>
</form>
     
</div>

<?php include_once 'footer.php'; ?>

I have already updated my script with your idea, but it works like the same like my code.

 

As I am writing I am getting an idea. 

 

I have to check which kind of device I actually want to edit, this is stored in the $id. With this $id I can

check the ComputerName (number) in the DeviceTable and this should be set as default in my combobox, or?

 

How can I realize this?

[Darkstar0078]

Hello,

 

I have found my solution, I have made some changes in my SQL View and I changed some variables and it works now perfect.

 

Thank you for your help, it was a very usefull help  .

 

Best regards

 

Sven :-)

[ginerjm]

Glad to hear that you have made such great success with your application. Keep it up!

You still need to understand what the meaning of the word "website" is and the meaning of "script".  You are writing scripts.  Not "websites".