PDO insert SQL DB verify name doesnt exist

#21

:sob::sob::sob: Now it’s saying every entry i add is in the DB when in fact i know its not… & i cannot work out why. GRRR spent days on this.

#22

I’m quite certain that EngineersName is not a valid html input type:
https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input
I suppose that i could be wrong.

also, a closing tag is not necessary but xhtml will expect a closing slash:

<input class="form-control" type="text" name="EngineersName" />
1 Like
#23

johnphpnewb thanks, in my code changes to try and get this WORKING i have corrected this.

1 Like
#24

Since you have made a number of changes to your code, you should post the current code so that members aren’t trying to solve problems that no longer exist.

1 Like
#25

yes, please :slight_smile:

In the meantime, start here:

include 'nav.php';
require 'ConnectionDB.php';

then be certain that you only process a post request:

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['submit'])) {
  $result = create_eng();
}

you can filter a post individually or many post values via array:

$eng_name = trim(filter_var($_POST['EngineersName'], FILTER_SANITIZE_STRING));

no need to set an empty display message:

$msg = '';
<?php display_msg($msg, $result);?>

in fact, you should set $msg to $valid which will hold the return value of the function (true or error):

$valid = validate_eng($eng_name);
if ($valid !== true) { display_msg($valid); }

function validate_eng($post_eng_name) {
  if ($post_eng_name === '') { return 'Please enter the engineers name'; }
  if (!is_string($post_eng_name)) { return 'Invalid engineers name'; }
  return true;
}

let a function serve its purpose to perform a duty with a return value/output.
PHP does not have sub procedures but sanitizing post input is not good use of a function.

i have no idea why one would bypass a built-in reset for a a button and javascript:

<button type="input" class="btn btn-primary default" onclick="job_reset()">Reset Form</button>
<script>
function job_reset() {
  document.getElementById("job_reset").reset();
}
</script>

simply hire the reset input type to do its job:

<input type="reset" value="Reset">

https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/reset

#26

Nope, still doesnt work… here’s the code. I am thinking of starting AGAIN. :sob::sob::sob::sob:

 <!DOCTYPE html>
 <html lang="en">
<head>
<title>Add A New Engineer | DDS Dashboard</title>
<meta charset="utf-8"  />
<link rel="icon" type="image/png" href="../images/favicon-16x16.png"/>
<?php 
include('nav.php');
include('ConnectionDB.php');

$msg = '';
//$valid = validate_eng($eng_name);
//if ($valid !== true) { display_msg($valid); }

$result = false;

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

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['submit'])) {
 $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" action="<?php $server ?>" method = "post">
<div class="form-group"> 
     <!--display_msg($msg, $result);-->
  <label for="EngineerName" class="col-sm-2 control-label">Engineer Name:</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="EngName" name="EngineersName" placeholder="Enter Engineer's Name"></input>
<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>
<input name="reset" type="Reset" value="Reset" class="btn btn-primary default"></input>
 </div>
 </form>
  <br><br>
    <?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>
<?php
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;
 }
 }

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

$sql_select = "select * from [engineers]
where [engineer] = ('$eng_name')";

$stmt = $conn->query($sql_select);

if($stmt === FALSE) {
$msg = 'Error querying engineer table';
return null;
}

$results=$stmt->fetchAll(PDO::FETCH_ASSOC);

If($results !== FALSE){
$msg = "The engineer $eng_name exists in the database.";
return TRUE;
}else
return TRUE;
  }

 //Add to Database
      function insert_eng($eng_name){ 
   global $conn, $msg;
    //SQL insert statement
      $sql = "insert into [Engineers](Engineer) values ('$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;
}
 }

 //create engineer function
  function create_eng(){
 //validate Enginner
 $eng_name = validate_eng();

if ($eng_name){	
//check if engineer exists
if(!eng_exist($eng_name)){
//insert engineer
return insert_eng($eng_name);
}
}
return FALSE;
}

//<?php display_msg($msg, $result);


 //function validate_eng($post_eng_name) {
 //if ($post_eng_name === '') { return 'Please enter the engineers name'; }
 //if (!is_string($post_eng_name)) { return 'Invalid engineers name'; }
//return true;
//}
//message creation
function display_msg($msg,$type){
$type = TRUE ? $cssClass = "alert-success" :
  $cssClass = "alert-danger";
If($msg != ''){
?>

<div class="containerenglist">
<div class="alert <?php echo $cssClass; ?>">
<button type="button" class="close" data-dismiss="alert">&times;</button>
<?php echo $msg; }?>
</div>
 </div>

 <?php
 }
 ?>

I hope i have indented each page by 4 spaces to show correctly. thanks for all assistance but didnt think this project would be as difficult should :weary:

#27

My perspective is, you are trying to hard to simplify this into functions when you don’t understand what is needed to begin with.

Try something simple to get it working.

Is this a separate file? Or a continuation of one?

#28

the entire script is a mess. I suggest that you spend time reading about html because you are not implementing valid html. You cannot place div tags inside of the head of an html document. You will have to accept the fact that programming takes time. You are trying to hurry and force the script to work or you are hoping that someone will code it for you, which is unlikely, unless, you hire them to do so.

the first thing that you should do try to understand the post and get requests. Your script needs to be separated into post processing and get request.

<?php
  if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['submit'])) {
     require 'ConnectionDB.php';
     $result = create_eng();
  }
?>
<!--this is what is displayed if $_SERVER['REQUEST_METHOD'] == 'GET'. 'GET' it? -->
<!DOCTYPE html>
<html lang="en">
<head>
  <title>Add A New Engineer | DDS Dashboard</title>
  <meta charset="utf-8"  />
</head>
<body>

<?php include 'nav.php'; ?>
<div>all of your html code goes here</div>

</body>
</html>

<?php
  //you can add all of your functions here
  exit;
?>

the fact that you are unable to code html correctly means that database handling, error handling and post form processing is out of reach for you at the moment. Start over and take it step-by-step. Build a valid html document with a form. Then add the post processing code and test it. Then continue with the rest of the code.

#29

I don’t have the time right now, but I agree with John.

You are pushing ahead without making any progress, which will just slow you down, as you are seeing.

Start with a basic html page and the form.
Next, do the processing that will insert the data.

You have so much embedded, that you can’t keep track of each section currently. And the process flow is hindered by all of the needless functions, that I think you got the idea you needed to use to simplify things. Rather than simplification, it is making the process more convoluted.

1 Like
#30

And if you decide to seek a freelancer, then you can post in the freelance section of this site:

https://www.phphelp.com/c/php-freelancing

PHPHelp.com has many highly-skilled programmers (excluding myself. I’m a novice.) I think that the freelance section of this site is useful resource.

#31

Its a continuation of the script

#32

Thnaks of the advice i wont be using a freelancer as this is my project & i dont like being beaten. I will have a look a the html form and start basic.

#33
<?php

    if($_SERVER['REQUEST_METHOD'] == 'POST')
    {
    	try 
    	{
    		$sql = "INSERT INTO Engineer (EngineerName) VALUES (?)";
    		$stmt = $pdo->prepare($sql);
    		$stmt->execute([$_POST['engineer']]);
		
			if($pdo->rowCount() == 1) 
			{
				$msg = "Name Added";
			}
			
    	} catch(PDOException $ex) {
    		if($ex->getCode() == 1062)
    		{
    			$msg = "The entry already exists";
    		} else {
    			$msg = $ex->getMessage();
    		}
    	}
    }
?>    
<!DOCTYPE html>
<html lang="en">
<head>
  <title>Add A New Engineer | DDS Dashboard</title>
  <meta charset="utf-8"  />
</head>
<body>

<p><?php if(isset($msg)) echo $msg; ?></p>
<form method='post'>
	<p>Engineer Name: <input type='text' name='engineer'></p>
	<p><input type='submit' value='Add'></p>
</form>

</body>
</html>
#34

This relies on you making the name a unique constraint however.

#35

Thank you i have recreated my code taking in everyone’s comments. I am actually rewriting it ask we speak and will take what you have supplied onboard and re-adjust where needed appreciate all comments given as you all can tell i am a novice.