I've been reading and gathering information for 2 days already and I give up. I have no clue why my piece of simple code is not succeeding.
I want to insert data from one form into two tables and YES I know there are exactly same problems described here and there, but as I said I'm familiar with them and also need to ask more questions.
The problem is in my query somewhere, at least this is what I believe it is.
Here it goes:
unset($err);
//Variables
$host = 'my.server.com';
$user = '123';
$pass = 'password';
$dbname = '123';
$err = array();
$error_form = false;
$img = "sth/sth.jpg";
//Connecting to the database using mysqli application programming interface
$con = mysqli_connect($host, $user, $pass, $dbname);
if (!validate()) {
if (!$con) {
echo "Connection failed : <br />" . $new_con->connect_errno . "<br />" . $new_con->connect_error;
exit;
} else {
echo "Connected! <br />";
}
var_dump($name);
echo "<br />";
var_dump($email);
echo "<br />";
var_dump($img);
echo "<br />";
$query= "START TRANSACTION;
INSERT INTO `123`.`table1` (`name1`,`name2`)
VALUES ('". $name . "','". $email ."');
INSERT INTO `123`.`table2` (`table1_id`,`name3`,`name4`)
VALUES (LAST_INSERT_ID(),'". $story . "','". $img ."');
COMMIT;";
var_dump(mysqli_query($con,$query));
echo "<br />";
$_POST["name"] = "";
$_POST["email"] = "";
$_POST["story"] = "";
}
//Form validation
function validate() {
global $name, $email, $story, $err, $error_form;
if($_SERVER['REQUEST_METHOD']=="POST") {
if(isset($_POST["name"]) && !empty($_POST["name"])) {
$name = htmlspecialchars($_POST["name"]);
} else {
$err[0] = "Name is missing.";
$error_form = true;
}
if(isset($_POST["email"]) && !empty($_POST["email"])) {
if (filter_var($_POST["email"], FILTER_VALIDATE_EMAIL)) {
$email = htmlspecialchars($_POST["email"]);
} else {
$err[1] = "Email was verified as incorrect.";
$error_form = true;
}
} else {
$err[1] = "Email is missing.";
$error_form = true;
}
if(isset($_POST["story"]) && !empty($_POST["story"])) {
$story = htmlspecialchars($_POST["story"]);
} else {
$err[2] = "Your story does not contain any characters, it can't be submited.";
$error_form = true;
}
}
return $error_form;
}
Everything what confuses me happens here:
$query= "START TRANSACTION;
INSERT INTO `123`.`table1` (`name1`,`name2`)
VALUES ('". $name . "','". $email ."');
INSERT INTO `123`.`table2` (`table1_id`,`name3`,`name4`)
VALUES (LAST_INSERT_ID(),'". $story . "','". $img ."');
COMMIT;";
var_dump(mysqli_query($con,$query));
I've tried to SELECT the id FROM the table1 table and SET it as a @value instead of LAST_INSERT_ID(). I've tried to run two queries...many different solutions.
I found out when I dump mysqli_query($con,$query) it gives false every time unless I don't use transaction, so just simple queries, but I need them.
Last thing is should I use PDO instead of mysqli? Why?
and
Why to use mysqli object oriented style instead of procedural one?
Every help is appreciated. I would like more to understand than just to achieve the effect here.
Be aware this is my first post here, but not the first visit.
You can only do one query at a time with
mysqli_query
Look atmysqli_multi_query()
http://www.w3schools.com/php/func_mysqli_multi_query.asp