I have a some PHP and it appears to be working, to insert a message based on if the first and last name and email match. The database has two tables.
The code appears to run smoothly but nothing is entered in the database.
I am using xampp mysql.
here is my code:
$servername = "localhost";
$username = "******";
$password = "******";
$dbname = "adrian";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
};
$f = $_POST['fname'];
$l = $_POST['lname'];
$e = $_POST['email'];
$t =$_POST['telephone'];
$m =$_POST['message'];
$sql = "SELECT firstName, lastName, email from clienttb where firstName = $f AND lastName = $l AND email = $e";
$result = $conn->query($sql);
$rows = $result->num_rows;
echo $rows;
if($rows == 0){
$conn->query("INSERT INTO clienttb(firstName, lastName, email, dateCreated)values($f, $l, $e, CURRENT_TIMESTAMP()");
}
$conn->query("insert into messagetb(message, clientid, messageDate) values('".$m."',
(select clientid from clienttb where firstName = ".$f." AND lastName = ".$l." AND email = ".$e."),CURRENT_TIMESTAMP ());");
if ($conn->multi_query($sql) === TRUE) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
Your code is wide open to an SQL Injection Attack. NEVER EVER put variables in a query, You need to use Prepared Statements. I suggest you use PDO. https://phpdelusions.net/pdo
Do not output internal System errors to the user. That info is only good to hackers.
Do not create variables for nothing.
You need to set a unique index on the columns you don’t want duplicated and then attempt the insert, capturing the duplicate error if any. What you have done creates a race condition.
There is no need to manually close the DB connection. Php does it automatically.
There is no need to over-code and check for true, the if statement already does that.
First remove echo $rows; and put in var_dump($rows); if you want to check if you get any result.
You have a bad logic here, if rows are 0 insert query ? Probably you meant if rows are more that 0.
Why you using this CURRENT_TIMESTAMP ()); you see the error here ?
Where is $sql variable here ? This already runs query here $result = $conn->query($sql); so i don’t see a point to put it here ($conn->multi_query($sql) === TRUE) since you run query already.
Try this
<?php
error_reporting(E_ALL);
$servername = "localhost";
$username = "******";
$password = "******";
$dbname = "adrian";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
};
// escape this post values !
$f = $conn->real_escape_string($_POST['fname']);
$l = $conn->real_escape_string($_POST['lname']);
$e = $conn->real_escape_string($_POST['email']);
$t = $conn->real_escape_string($_POST['telephone']);
$m = $conn->real_escape_string($_POST['message']);
$sql = "SELECT clientid, firstName, lastName, email from clienttb where firstName = '".$f."' AND lastName = '".$l."' AND email = '".$e."'";
$result = $conn->query($sql);
$data = $result->fetch_array(MYSQLI_BOTH); // will get you array for clientid
// if returned rows count more that 0, insert data
// put both querys here
if ($result->num_rows > 0) {
$query = $conn->query("INSERT INTO clienttb(firstName, lastName, email, dateCreated) VALUES ('".$f."', '".$l."', '".$e."', CURRENT_TIMESTAMP()");
$query .= $conn->query("INSERT INTO messagetb(message, clientid, messageDate) VALUES ('".$m."', '".$data['clientid']."' , CURRENT_TIMESTAMP()");
}
if ($query) {
echo "New records created successfully";
} else {
echo "Error: <br>" . $conn->error;
}
$conn->close();
?>
Put that piece of code like this, just after queries
// if returned rows count more that 0, insert data
// put both querys here
if ($result->num_rows > 0) {
$query = $conn->query("INSERT INTO clienttb(firstName, lastName, email, dateCreated) VALUES ('".$f."', '".$l."', '".$e."', CURRENT_TIMESTAMP()");
$query .= $conn->query("INSERT INTO messagetb(message, clientid, messageDate) VALUES ('".$m."', '".$data['clientid']."', CURRENT_TIMESTAMP()");
if ($query) {
echo "New records created successfully";
} else {
echo "Error: <br>" . $conn->error;
}
}
Just getting
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 1
Hi,
I found an error relating to a missing parenthesis in the first insert query where client exists.
This goes onto enter in data into ‘messagetb’ hoever when a new client is added it now produces an error:
Uh, excuse me, but your code is not using PDO. And you are still putting variables in your query and your still outputting internal system errors to hackers and YOU do not need to insert the current timestamp.The database can do it automatically AND you have a redundant query that runs in the IF and the ELSE.