Need help with mysqli insert

PHP
1

Hi, I hopd someone will advise me as to where I’m going astray here. I’m trying to insert using PHP mysqli, using a form locally <a href="waitinsert-form.html">. Following is the code for the form (is the form flawed?) followed by the code for the insert.
I have tried following several online examples with no success.

the code for the form:

<HTML><HEAD>
<STYLE type=text/css>.highlight {
	BACKGROUND: #ff6699
}
.text {
	COLOR: #ffffff
}
.both {
	BACKGROUND: black; COLOR: white
}
</STYLE>

<STYLE>INPUT.bgyellow {
	BACKGROUND-COLOR: yellow
}
</STYLE></head>
<body bgcolor="ccffff">
<b>Waiting/transfers List input form<p>
<form action="waitinsert.php" name=Form method="post">

        		
<label for="entrytype">Entrytype:</label>
            <input type="text" name="entrytype" id="entrytype">
<SELECT name=entrytype> 
<OPTION  class=highlight value=W>W
<OPTION  class=highlight value=T>T
</OPTION></SELECT><br>  
<label for="appl">appl:</label>
            <input type="text" name="appl" id="appl"><br>
<label for="date">date:</label>
            <input type="text" name="date" id="date"><br>
<label for="time">time:</label>
            <input type="text" name="time" id="time"><br>
<label for="tenant">tenant:</label>
            <input type="text" name="tenant" id="tenant"><br>
<label for="racegend">racegend:</label>
            <input type="text" name="racegend" id="racegend">
<SELECT name=racegend>
 <OPTION class=highlight value=1f selected>White female
<OPTION class=highlight value=2f>Black female
<OPTION class=highlight value=3f>Asian female
<OPTION class=highlight value=4f>Indian female
<OPTION class=highlight value=5f>Hawaiian female
<OPTION class=highlight value=1m>White male
<OPTION class=highlight value=2m>Black male
<OPTION class=highlight value=3m>Asian male
<OPTION class=highlight value=4m>Indian male
<OPTION class=highlight value=5m>Hawaiian male
</OPTION></SELECT><BR>
<label for="ethnicity">ethnicity:</label>
            <input type="text" name="ethnicity" id="ethnicity">
<SELECT name=ethnicity> 
<OPTION class=highlight value=Y>Y
<OPTION class=highlight value=N selected>N
</OPTION></SELECT><BR>
<label for="laborhsg">laborhsg:</label>
            <input type="text" name="laborhsg" id="laborhsg">
<SELECT name=laborhsg> 
<OPTION class=highlight value=Y>N
<OPTION class=highlight value=N selected>X
</OPTION></SELECT><BR>
<label for="displ">displ:</label>
            <input type="text" name="displ" id="displ">
<SELECT name=displ> 			
<OPTION class=highlight value=Y>X
<OPTION class=highlight value=N selected>N
</OPTION></SELECT><BR>
<label for="incomelevel">incomelevel:</label>
            <input type="text" name="incomelevel" id="incomelevel">
 <SELECT name=incomelevel> 
<OPTION class=highlight value=VL selected>verylow
<OPTION class=highlight value=L>low
<OPTION class=highlight value=M>medium
</OPTION></SELECT> <BR>
<label for="brneeded">brneeded:</label>
            <input type="text" name="brneeded" id="brneeded">
<SELECT name=brneeded> 
<OPTION class=highlight value=1 selected>1
<OPTION class=highlight value=2>2
<OPTION  class=highlight value=3>3
</OPTION></SELECT><BR>
<label for="moveindate">moveindate:</label>
            <input type="text" name="moveindate" id="moveindate"><BR>
<label for="removaldate">removaldate:</label>
            <input type="text" name="removaldate" id="removaldate"><BR>
<label for="code">code:</label>
            <input type="text" name="code" id="code"><BR>
<label for="comments">comments:</label>
            <input type="text" name="comments" id="comments"><BR>
<P>
<INPUT type=submit value="submit data"><BR></FORM></B></BODY></HTML>

the code for insert:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "homedb";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
  
     $entrytype = $_POST['entrytype'];
     $appl = $_POST['appl'];
     $date = $_POST['date'];
     $time = $_POST['time'];
     $tenant = $_POST['tenant'];
     $racegend = $_POST['racegend'];
     $ethnicity = $_POST['ethnicity'];
     $displ = $_POST['displ'];
     $compday = $_POST['compday'];
     $compmoyr = $_POST['compmoyr'];
     $incomelevel = $_POST['incomelevel'];
     $brneeded = $_POST['brneeded'];
     $moveindate = $_POST['moveindate'];
     $removaldate = $_POST['removaldate'];
     $code = $_POST['code'];
     $comments = $_POST['comments'];
     $sql = "INSERT INTO waittbl (entrytype, appl, date, time, tenant, racegend, ethnicity, displ, 
compday, compmoyr, incomelevel, brneeded, moveindate, removaldate, code, comments)
     VALUES ('$entrytype','$appl','$date','$time','$tenant','$racegend','$ethnicity','$displ',
'$compday','$compmoyr','$incomelevel','$brneeded','$moveindate','$removaldate','$code','$comments')";
     if (mysqli_query($conn, $sql)) {
			echo "New record created successfully".'<br>';
		} else {
		echo "Error: " . $sql. "<br>" . mysqli_error($conn);
	}
}
?>

the result:

'; } else { echo "Error: " . $sql. "
" . mysqli_error($conn); } } ?>
2

Do you get an error message? If so, what is it? What happens that should not, or does not happen that should? Does it fail every time, or only with certain data? If only with certain data, what test data will make it fail? Could it be that one of your values includes a single-quote character? In short, what does “no success” mean? If the text you showed as “the result” is displaying on your screen, that suggests a quoting issue, I’d say.

You have several form fields with the same name - "entrytype" and "racegend" being two examples - a text field, and a select both with the same name. You also have a field called "laborhsg" which isn’t used in your PHP code.

You need to use Prepared Statements instead of just sticking form values directly into your query like that - it will help with all sorts of things, including handling quotes inside strings.

There’s some strange HTML - your select options look OK, but at the end of each list you have </OPTION> which seems strange to me, though I am not a HTML expert.

Is the content of $_POST what you expect it to be when you display it for debugging purposes?

3

Each option should have an opening and a closing tag.
What we see is each option having just the opening tag, then just one closing tag at the end.
This is invalid, though the browser will likely correct the error.
But still the overall structure of the form is wrong and should be corrected.
When you have a select input, it should not have a text input with it at the beginning.
So instead of:-

<label for="entrytype">Entrytype:</label>
            <input type="text" name="entrytype" id="entrytype">	
<SELECT name=entrytype> 
<OPTION  class=highlight value=W>W
<OPTION  class=highlight value=T>T
</OPTION>
</SELECT>
<br>

It should be:-

<div>
  <label for="entrytype">Entrytype:</label>	
  <select name="entrytype" id="entrytype"> 
     <option  class=highlight value="W">W</option>
     <option  class=highlight value="T">T</option>
  </select>
</div>

Note that for certain data types there are more specific input types than text that you can use. This will help with client side validation. But you still need to address server side validation in your PHP.

<div>
  <label for="appl">appl:</label>
            <input type="text" name="appl" id="appl">
</div>
<div>
<label for="date">date:</label>
            <input type="date" name="date" id="date">
</div>
<div>		
<label for="time">time:</label>
            <input type="time" name="time" id="time">
</div>

Once your HTML is straightened out, you may eliminate that form any problem that remians.

The problem with this can be that online examples can be wrong. I lot of people post “How to” examples of code in complete ignorance of the fact that they don’t themselves know how to do it (correctly).

2 Likes
4

The symptom of displaying some of the raw php code in the browser (if you do a ‘view source’ in the browser you will probably see all the php code) is either due to php not being installed and working on your web server or that you requested the form page (and the form’s action page) using a file system path, instead of a URL on a web server.

What URL did you use in your browser’s address bar for the form? For a locally hosted system, it should be something like http://localhost/waitinsert-form.html

Note: the form and the form processing code should be on the same page. Therefor, the form should not be on a page ending with a .html extension.

Your code for any page should be laid out in this general order -

  1. initialization
  2. post method form processing
  3. get method business logic - get/produce data needed to display the page
  4. html document
1 Like
5

Besides the already mentioned issues there are 2 inputs in your processing query that are not in your form.

$compday = $_POST['compday'];
$compmoyr = $_POST['compmoyr'];

I do not know what these “comp” inputs are used for but saving these values in different database fields is going to be problematic. A single database field of TYPE date will store values in the standard YYYY-MM-DD format, which allows you to sort and search records by date.

You also have an input called time and again, this date and time should be stored in a single field of TYPE datetime.

I would remove any inputs that do not require user input. For example, IF those 2 “comp” inputs are used for saving the date and time when the form is processed then I would remove those inputs and use now() in the query statement, which will give you the date and time value in the standard datetime format, YYYY-MM-DD hh:mm:ss into a single database field.

You have a number of date inputs and users tend to mess these up so at the very least use placeholders to show the current date correctly formatted in every input. For example:

<input type="text" name="moveindate" id="moveindate" placeholder="<?php echo date('Y-m-d');?>" >

All user input should be checked and date inputs in particular need to be checked that they are formatted correctly. Searching the web will show you a number of ways to check the date formatting. This example uses a function() to format the input date into the desired format and then compare this to the input date. The function will return true or false it they match or not.

function validateDate($date, $format = 'Y-m-d'){
    $d = DateTime::createFromFormat($format, $date);
    return $d && $d->format($format) === $date;
}

You call a function by its name e.g. validateDate() with the input we wish to check between the parentheses, e.g. validateDate($_POST['moveindate']). Remember this will return true or false so you will want to write an IF condition that says if this returns false, display an error message. I usually place any error messages into an array which can hold all errors found. SO before processing I would define this array as empty.

$errors = array();

Your processing code should be within an IF condition so the processing code only is read and executed when the form is posted. I would add a name OTHER THAN submit to your submit input. Something like this.

<input type="submit" name="submit_data" value="Submit Data">

Now in your processing section you would make an IF condition looking for this name and wrap all processing code in this condition.

if($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['submit_data'])){
//All processing
}

You can then write that IF condition we talked about and define the error message to be displayed. It might look like this.

if(validateDate($_POST['moveindate']) == false):
	array_push($errors,"Please make sure the Move In Date is in the correct YYYY-MM-DD format.");
endif;

You will notice that array_push() is adding the message into the $errors array.

IS the input removaldate required? Probably not but if a date has been entered you’ll want to check it. The best way to check if an input has a value is to check for empty using the function empty(). In this case we want to check for NOT empty, which would be written as !empty(). So our IF condition is written as IF NOT empty removaldate AND (written as &&) date format check equals false create error message.

if(!empty($_POST['removaldate']) && validateDate($_POST['removaldate']) == false):
	array_push($errors,"Please make sure the Removal Date is in the correct YYYY-MM-DD format.");
endif;

Many inputs are selects without empty options so you probably don’t need to check those but the few text inputs left that are probably required would be “appl”, “tenant” and “code”. You could certainly check each input individually, but as some applications you might need check a multitude of inputs, learning to handle some of this with less code is a good thing to learn. We can place the input names into an array and then loop through them.Also input names are not always intuitive to the user so if you build your array with Key=>VALUE pairs you can have the input name and the word you want to tell the user.

$fields = array(
	 "appl" => "Applicant"
	,"tenant" => "Tenant"
	,"code" => "Code"
);

This allows you to write something this, which checks these inputs for empty.

foreach($fields as $key => $fieldname):	
	if(empty($_POST[$key])):
		array_push($errors,"Please enter ".$fieldname." field.");
	endif;	
endforeach;

Now that we have error being written into an array we need to display them to the user, so within the <body> tags, above the form you can add a bit of code that checks if $errors is not empty, echo a <div>and loop through and display the message.

<?php
if(!empty($errors)):
	echo '<div style="width:600px; text-align: center; margin:20px auto;">'."\r";
	foreach($errors as $error):
		echo '<span style="color:red">'.$error.'</span></br >'."\r";
	endforeach;
	echo '</div>'."\r";
endif;
?>

Now In your processing code, after all your checks there is only one variable $errors to check to know if the user input passed the checks you have defined. You simply say

if(empty($errors)):
	//Clear for mysql processing
endif;

I will not give you query sample just yet as I think you need to change your Database fields as I mentioned. Then you can change your connection method for processing using prepared statements.
$conn = new mysqli($servername, $username, $password, $dbname);

Also in most cases html is lowercase and any =value should be in quotes.
e.g.

<select name="racegend" id="racegend">
	<option class="highlight" value="1f" selected>White female</option>
	<option class="highlight" value="2f">Black female</option>
	<option class="highlight" value="3f">Asian female</option>
	<option class="highlight" value="4f">Indian female</option>
	<option class="highlight" value="5f">Hawaiian female</option>
	<option class="highlight" value="1m">White male</option>
	<option class="highlight" value="2m">Black male</option>
	<option class="highlight" value="3m">Asian male</option>
	<option class="highlight" value="4m">Indian male</option>
	<option class="highlight" value="5m">Hawaiian male</option>
</select><br>

I won’t even get into how that select (and the others) should be built with an array so it would be easy to identify the POST selected option so the user doesn’t loose all the previously entered values if an error is displayed. Anyway hope some of this is helpful.

2 Likes
7

Or even leave it out of the query totally and set the column to default to current_timestamp.

2 Likes
8

Good call.

9

This may also be used to render the HTML form in the first place.

I tend to go with check everything.
Again creating an array of select options is a good idea, first it lets you render all the options to the HTML form with a foreach, then in validation you may check if the given value is in_array(), if it’s not, it most probably indicates a bad actor and you can ditch the processing before any harm is done.

if(!in_array($_POST['racegend'], $racegendOpts)){
     // Nope!
}
10

EXACTLY!!
I often see people make a query to see if the user entered an acceptable answer. That a good way to get yourself into trouble. It is always better to check user input against an array of acceptable answers. So yes, really even the simple Y/N selects should be checked against a Y, N array.

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.