Member Avatar for Nikhil_4

This code was to create a simple database with a table and add records with the following. toIm having trouble with the 'id' auto_increment.

  <?php

$con=mysqli_connect("localhost","root","distortion","my_databs");

// Check connection

if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Create table

$sql="CREATE TABLE empl(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,FirstName CHAR(30),LastName CHAR(30), Age INT)";


// Execute query

if (mysqli_query($con,$sql)) {
  echo "Table persons created successfully";
} else {
 echo "Error creating table: " . mysqli_error($con);
}


// escape variables for security


$firstname  = mysqli_real_escape_string($con, $_POST['firstname']);
$lastname   = mysqli_real_escape_string($con, $_POST['lastname']);
$age        = mysqli_real_escape_string($con, $_POST['age']);


$sql="INSERT INTO Empl (ID ,FirstName, LastName, Age )
VALUES ( NULL,'$firstname', '$lastname', '$age')";


if (!mysqli_query($con,$sql)) {

 die( 'Error:' . mysqli_error($con));

}
echo"<br>";
echo " -> 1 record added";
  • 3 Contributors
  • 8 Replies
  • 4K Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by andrevanzuydam

Recommended Answers

All 8 Replies

Member Avatar for pritaeas

I think it should be:

CREATE TABLE empl (
    ID INT NOT NULL AUTO_INCREMENT,
    FirstName CHAR(30),
    LastName CHAR(30), 
    Age INT,
    PRIMARY KEY (id)
)
Member Avatar for Nikhil_4

Thanks for the comment,its both the same.
ignore the ending of the php..
"?>"...

Edited by Nikhil_4
Member Avatar for pritaeas

Ok, wasn't sure, so the error is in the insert query?

Can you echo $sql and show what gets executed here?

Member Avatar for Nikhil_4

can i just post the entire program right here?

Member Avatar for pritaeas

You can, if it's relevant. I still need to know the query you are executing, the code doesn't show that.

Member Avatar for Nikhil_4 
<?php

$con=mysqli_connect("localhost","root","distortion","my_databs");

// Check connection

if (mysqli_connect_errno()) {
 echo "Failed to connect to MySQL: " . mysqli_connect_error();
}



// Create table

$sql="CREATE TABLE empl( FirstName CHAR(30),LastName CHAR(30), Age INT)";


// Execute query

if (mysqli_query($con,$sql)) {
 echo "Table persons created successfully";
} else {
 echo "Error creating table: " . mysqli_error($con);
}


// escape variables for security


$firstname  = mysqli_real_escape_string($con, $_POST['firstname']);
$lastname   = mysqli_real_escape_string($con, $_POST['lastname']);
$age        = mysqli_real_escape_string($con, $_POST['age']);


$sql="INSERT INTO Empl (FirstName, LastName, Age )
VALUES ( '$firstname', '$lastname', '$age')";


if (!mysqli_query($con,$sql)) {

  die( 'Error:' . mysqli_error($con));

 }

echo " -> 1 record added";

 // Display In A Table


$result = mysqli_query( $con , " SELECT * FROM Empl " );

echo "<table border='5'>
 <tr>

<th> Firstname </th>
<th> Lastname  </th>
<th> Age       </th>

 </tr>";



while($row = mysqli_fetch_array($result)) {
 echo "<tr>";

 echo "<td>" . $row['FirstName'] .  "</td>";
echo "<td>" . $row['LastName'] .   "</td>";
 echo "<td>" . $row['Age'].         "</td>";

 echo "</tr>";
}


echo "</table>";


  mysqli_close($con);

 ?>

This code is linked to another(the above code is names as "insert.php"):

<!DOCTYPE html>
<html>
<body>

<div id= "container" style "width:500px">
<div id="header" style="background-color:#FFA500;">
<h1 style="margin-bottom:0;">
<h1  style="text-align:center;"><u>Employee Details</u></h1></h1></div>



<form action="insert.php" method="post">
<h3 style="text-align:center;">
<br>First Name:    <input type="text" name="firstname">
<br>Last Name:     <input type="text" name="lastname">
<br>Age:           <input type="text" name="age">
<br>               <input type="submit" value = "Save">
</form>



</div>
</body>
 </html>
Member Avatar for andrevanzuydam

My five cents are that the general rule of thumb with auto increment fields is that you leave them out of your insert statement, otherwise you are actually saying you want that primary key to be null, which it can't be as per your create statement.

Member Avatar for andrevanzuydam

On testing, use your current code with the table statement suggested by pritaeas, you should be able to get the results you were aiming for. Don't include the auto increment field in your insert statement, it is better to have some primary key in your table because you will find that deleting records or updating them may become impossible if you leave that out!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.