PDO insert SQL DB verify name doesnt exist

#1

Hi, I have written this code in PHP that addeds a new engineer to my value to table engineers to my engineer column.

When i click create enginner button my page refreshes with no errors no PHP errros i get no success or warning errors. i have tried with existing name and new names.

CODE:

     <?php 
     include('nav.php');
    include('ConnectionDB.php');
     $msg = '';
      $result = false;

     $server = htmlentities($_SERVER['PHP_SELF']);

     if (isset($_POST['submit'])) {
	 //Create new enginner
	 $result = create_eng();
     }
     ?>
     <div class="jumbotron">
     <div class="container">

     <h1><br><img src="../images/clipboard-customer.png"/> Add New Engineer:</br></h1>
     </div>
     </div> 
    </head>
    <body>
    <form class="form-horizontal" role="form" id="add_eng">
	<div class="form-group" action="<?php $server ?>" method = "post"> 
	<?php display_msg($msg, $result);?>
	<label for="EngineerName" class="col-sm-2 control-label">Engineer Name:</label>
    <div class="col-sm-6">
    <input type="EngineerName" class="form-control" id="EngineersName">
    <small class="form-text text-muted">
	Enter the new engineer's name above to enter them into the database.
	</small>
	</div>
	<input name="submit" type="submit" value="Create Engineer" class="btn btn-primary default"></input>
    <button type="input" class="btn btn-primary default" onclick="job_reset()">Reset Form</button>
    </div>
</form>

	<?php
	$sql = "Select * from [Engineers] where [Engineer] not like '%\_%' Escape '\' order by [Engineer] asc";
			$stmt = $conn->prepare($sql);
			$stmt->execute();
			$results=$stmt->fetchAll(PDO::FETCH_ASSOC);
			?>
			<div class="containerenglist">
			<div class="col-sm-5">
			<table id="alleng" class="table table-bordered">
			<tr>
			<th>Active Engineer's Names:</th>
			</tr>
			<?php
			foreach ($results as $row) {
				echo "<tr><td>";
				echo $row['Engineer'];
				echo "</td>";
				echo "</tr>";
			}
			?>
			</table>
			</div>
			</div>
     <script>
     function job_reset() {
    document.getElementById("job_reset").reset();
    }
    </script>	
    </body>
    </html>		

    <?php
    //create enginner function
    function create_eng(){
	 //validate Enginner
	 $eng_name = validate_eng();

      if ($eng_name){	
	  //check if department exists
	  if(!eng_exist($eng_name)){
	  //insert enginner
	  return insert_eng($eng_name);
	  }
	  }
        return false;
         }



        //message creation
        function display_msg($msg,$type){
	 $type === true ? $ccClass = "alert-success" :
	  $cssClass = "alert-error";
	If($msg != ''){
      ?>
      <div class="alert <?php echo $cssClass; ?>">
	<?php echo $msg; }?>
    </div>
    <?php
    }



    //check if enginner exists
    function eng_exist($eng_name){
    global $msg, $conn;

       $sql_select = "select [enginner] from [enginners]
      where name = " . $conn->quote($eng_name) . "
      limit 1";

     $stmt = $conn->query($sql_select);
     if($stmt === false) {
	$msg = 'Error querying engineer table';
	return null;
     }

     $r = $stmt->fetch(PDO::fetch_Assoc);

     If($r !== false){
	 $msg = "Enginner with name $eng_name exists in Database.";
	 return true;
    }else
	return false;
     }



    //Add to Database
    function insert_eng($eng_name){ 
	   global $conn, $msg;
	   //SQL insert statement
	$sql = "insert into [Enginners](Engineer) values (" . $conn->quote($eng_name) .")";
	
	if($conn->exec($sql) === false) {
		$msg = 'Error inserting the engineer.';
		return false;
	}else{
		$msg = "The new engineer $eng_name has been created";
		return true;
	}
    }

    function validate_eng(){
	global $msg;
    $eng_name = $_POST['EngineersName'];

    if($eng_name != ''){
	$eng_name = filter_var($eng_name,FILTER_SANITIZE_STRING);
	return $eng_name;
    }else{
	$msg = 'Please enter the engineers name';
	return false;
    }
    }
    ?>

I hope its all copied across correctly and is readable.

#2

Use prepared statements, not whatever it is you are actually doing.

And you misspelled engineer.

#3

Darn ok, i have renamed the incorrect spelling but same problem. I will also do some digging on prepard statements in PHP.

#4

I recommend looking at your html form tag and the html input tag, both are FUBAR.

#5

Thanks phdr, i will have a look what do you suggest?

#6

Can anyone assist i seem be still having issues even with altered code?

It does not seem to write to the DB or giving me the error message if i try a blank value.

I am trying to find a debugger i can run so i can watch the code run threw but not having any luck.

#7

Do you have error reporting turned on for PDO? It is off by default.
Do you have error reporting on for the server?

#8

I have error reporting turned on for the php side and that is clear. But will turn on PDO error reporting on my return to the office.

#9

Since you didn’t say you fixed what phdr mentioned, I’ll show some issues he was pointing out.

#10

I have edited my code to include the html name after reading the php manual or am i missing something else?

#11

Right, Thanks phdr and astoneciper after changing

 <form class="form-horizontal" role="form" id="add_eng" action="<?php $server ?>" method = "post">
	<div class="form-group" action="<?php $server ?>" method = "post"> 

    <input type="EngineerName" class="form-control" id="EngineersName"></input>
</form>

I know get these errors

[30-Apr-2019 09:55:18 UTC] PHP Notice: Undefined property: PDO::$quote in C:\inetpub\wwwroot\SupportDesk\php\Add_New_Engineer.php on line 104
[30-Apr-2019 09:55:18 UTC] PHP Fatal error: Uncaught Error: Undefined class constant ‘fetch_Assoc’ in C:\inetpub\wwwroot\SupportDesk\php\Add_New_Engineer.php:112
Stack trace:
#0 C:\inetpub\wwwroot\SupportDesk\php\Add_New_Engineer.php(143): eng_exist(‘Bob’)
#1 C:\inetpub\wwwroot\SupportDesk\php\Add_New_Engineer.php(19): create_eng()
#2 {main}
thrown in C:\inetpub\wwwroot\SupportDesk\php\Add_New_Engineer.php on line 112

Something to work with :smile:

#12

There is more clean up to do, but that’s progress.

I would advise you to separate out the parts. Programming follows the IPO process:

Input
Processing
Output

You also don’t want global variables. Pass the needed variables into the function and return them to the caller.
Are you using PDO? This doesn’t look correct,

if($conn->exec($sql) === false) {
#13

Hi Astonecipher,

I will have a read up off the parts you advise off yes i am trying to use PDO basically i am trying to achive this:

  1. Check DB to see if name exists
  2. If exists show errror and re-enter another choice
  3. If engineer doesnt exist create name in DB
  4. Advise of creation and & refresh table to show engineer and sheet ready for another entery.
#14

I think it looks like this line seems to be the culprit possibly

$r = $stmt->fetch(PDO::fetch_Assoc);

It keeps pulling the error: Uncaught Error: Undefined class constant ‘fetch_Assoc’

#15

It should be,

PDO::FETCH_ASSOC

But there is more than that going on.

#16
<?php

// form processing code
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
    

}



?>
<!-- Your form -->

I think you are getting overwhelmed and confusing yourself.

#17

I think i am getting getting overwhelmed and confused i am struggling to work out how to move forward and how best ot rewrite or correct the code so it works.

I am also trying to fit this in with my day2day job but do appricate the assitance guideance and help from everyone.

#18

https://www.phphelp.com/t/how-to-create-icrud/

#19

After alot of reading i can now add enginners but if a engineer exists i get this error [30-Apr-2019 15:58:59 UTC] PHP Fatal error: Uncaught Error: Undefined class constant ‘fetch_Assoc’ in C:\inetpub\wwwroot\SupportDesk\php\Add_New_Engineer.php:114
Stack trace:
#0 C:\inetpub\wwwroot\SupportDesk\php\Add_New_Engineer.php(145): eng_exist(‘James’)
#1 C:\inetpub\wwwroot\SupportDesk\php\Add_New_Engineer.php(19): create_eng()
#2 {main}
thrown in C:\inetpub\wwwroot\SupportDesk\php\Add_New_Engineer.php on line 114

grrrr so frustrating… :tired_face::tired_face::sleepy:

#20

For one thing, compare these two excerpts.

Notice the difference between the one that works and the one that raises an exception?