Problem with PHP connection to MySQL tables

Question

My problem is this:

(1) on the back-end I have database in MySQL. It contains 7 tables. (2) On the front-end I have a HTML form. (3) The data entered from the user must populate these 7 tables. (4) I use PHP and link the form to database with mysqli.

For some reason the form populated only the first table and the other 6 received no data. Any idea how I can connect the form to all tables at once? I don’t want one sql table, because the HTML form has over 300 input fields. Thanks you.

edited

Here is the code

<?php
$host="localhost";
$user="root";
$pass="";
$db="ao db";

$conn = new mysqli ($host, $user, $pass, $db);

if ($conn->connect_error) { 
die("Connection failed: " . $conn->connect_error);
}

$firstname = $_POST["first_name"];
$lastname = $_POST["name"]; 
$female = $_POST["female"];

$stmt = $conn->prepare("INSERT INTO table_demo (Vorname, Nachname, female) VALUES (?, ?, ?)");
$stmt->bind_param("ssi", $firstname, $lastname, $female);

$stmt->execute();
$stmt->close();
echo "New records created in Demo";
$conn->close();

$link = new mysqli ($host, $user, $pass, $db);

if ($link->connect_error) { 
die("Connection failed: " . $link->connect_error);
}

$height = $_POST["height"];
$weight = $_POST["weight"];

$stmt=$link->prepare("INSERT INTO table_pre (height, weight) VALUE (?, ?)");
$stmt->bind_param("ii", $height, $weight);

$stmt->execute();

$stmt->close();
echo "New records created in Pre";
$link->close();

>

It's possible to use SQL join queries, but please provide what you've done so far so we can comment on it and assist. — FreedomPride, Dec 18 '18 at 06:20
So you want to perform multiple insert to multiple tables? Am I right? — Hafiz K, Dec 18 '18 at 07:21
@HafizK yes. I want to make multiple inserts in multiple tables in one database. I have 1 form and 1 submit button. The tables in dB are connected. The first one (table_demo) hold the primary_key and the other 6 tables are related to it with a foreign_key. For some reasons only the table with the primary key received data and the other tables stairs empty. — pap, Dec 18 '18 at 09:37
@Pussyfer, perhaps [this](https://stackoverflow.com/questions/4565195/mysql-how-to-insert-into-multiple-tables-with-foreign-keys) can give you some idea on how to perform those multiple insert. — Hafiz K, Dec 18 '18 at 09:48

score 1 · Answer 1 · answered Dec 18 '18 at 06:33

1

For you code first you have to create db connection objects.

`

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}`

for inserting into table. With help this you can insert details in all your seven tables.

$sql = "INSERT INTO MyGuests (firstname, lastname, email)VALUES ('John', 'Doe', 'john@example.com')";
fetching detail from table using this. With help of this you can select from all your seven tables.

$sql = "SELECT id, firstname, lastname FROM MyGuests"; $result = $conn->query($sql);

answered Dec 18 '18 at 06:33

bimal sharma

170
8

Please read the question first! And when you used a w3school reference, please add your source! – pap Dec 18 '18 at 06:54
As per your question you are unable to insert details into your table. Here I am give you suggestion for inserting and selecting data. – bimal sharma Dec 18 '18 at 07:09
Please read the question first. It is clear enough. 7 is a number that shows I have SEVEN tables and SIX them can not received data. I need a solution for population SEVEN tables AT ONE. Together. Not One table. – pap Dec 18 '18 at 09:40
please send me your entire db and code on sharma.bimal226@gmail.com. I will look into it and provide you solution. – bimal sharma Dec 19 '18 at 13:03

score 0 · Accepted Answer · edited Dec 30 '18 at 08:15

I figured it out! There where 2 issues:

Issue one: the tables were not created correctly and the foreign key was wrongly connected. I have a problem with creating tables with foreign key through phpmyadmin menu. Maybe it is better to use the SQL database and table creation syntax:

CREATE DATABASE IF NOT EXISTS database_name;

USE database_name;

CREATE TABLE table_1
(
    table_1_id int not null auto_increment primary key,
    variable_1 varchar(255) not null,
    variable_2 int(11),
    ...
    variable_to_be_used_as_foreign_key text,
    variable_n text,
) ENGINE=InnoDB;

CREATE TABLE table_2
(
    table_2_id int not null auto_increment primary key,
    variable_1 varchar(255) not null,
    variable_2 int(11),
    ...
    variable_n text,
    variable_to_be_used_as_foreign_key text,

    FOREIGN KEY fk_table_2(variable_to_be_used_as_foreign_key)
         REFERENCES table_1(variable_to_be_used_as_foreign_key)
              ON UPDATE CASCADE
              ON DELETE RESTRICT
) ENGINE=InnoDB;

PLEASE NOTE: the foreign key variable must be added in both tables (I know it is obvious, but still).

Issue #2: there was a failure in the php code. I missed in the prepared statement the input in the foreign key column in the second table, namely:

$stmt=$link->prepare("INSERT INTO table_pre (height, weight) VALUE (?, ?)");
$stmt->bind_param("ii", $height, $weight);

instead of:

$stmt=$link->prepare("INSERT INTO table_pre (height, weight, female) VALUE (?, ?, ?)");
$stmt->bind_param("iii", $height, $weight, $female);

Problem with PHP connection to MySQL tables

2 Answers2