Feb 1, 2023

Level 20

how to insert foreign key value to the table with form? in php

when a user is registered and i want to get the registered user id and store it in the students table foreign key id that is ```users_id`` i am getting the data when a registerd user is logged in my code

<?php 
   require __DIR__ . "/../dbConnection.php";
   if($_SERVER['REQUEST_METHOD'] == 'POST')
   {
       $email = mysqli_real_escape_string($conn, $_POST['email']);
       $password = mysqli_real_escape_string($conn, $_POST['password']);   
            
       require __DIR__ . '/../validation/loginValidation.php';
       if(empty($errors))
       {
           $_SESSION['email'] = $emailRow['email'];
           $_SESSION['id'] = $emailRow['id'];
           $_SESSION['password'] = $emailRow['password'];
           $_SESSION['msg'] = "Welcome back!";
           header('location: /');
       } 
       
   }

and error when i try to echo it on the form page

Undefined array key "id" in
C:\laragon\www\php phonebook project\crud\School Management System\controller\studentController\store.php
on line

my code of form request and printing the session data

<?php
require __DIR__ . "/../../dbConnection.php";
if($_SERVER['REQUEST_METHOD'] == 'POST')
{   

    
    $user_id = $_SESSION['id'];
    echo $user_id;
    die();
    require __DIR__ . "/../../validation/studentValidation/create.php";
    if (empty($errors)) {
        $sql = "INSERT into `students`
            (user_id, first_name, last_name, email, dob, father_name, mobile_no, city, district, gender, state, nationality, image)
            values('$user_id', '$firstName', '$lastName', '$email', '$dob', '$fatherName', '$mobile_no', '$city',
                '$district', '$gender','$state', '$nationality', '$image'
            )";
        $query = mysqli_query($conn, $sql);

        $_SESSION['msg'] = "Stored Successfully!";
        header("location:/");
    }
}

tykus

3 years ago

Level 104

Which id key is being used on the store.php script referenced in the error message? It would help to know how $emailRow variable comes to be defined!

Shivamyadav

3 years ago

Level 20

@tykus here is code

 $filterEmail = filter_var($email, FILTER_VALIDATE_EMAIL);
        $sql = "SELECT * FROM `users` where email = '$email' ";
        $query = mysqli_query($conn, $sql);
        $emailRow = mysqli_fetch_assoc($query);

coming form this file require __DIR__ . '/../validation/loginValidation.php';

tykus

3 years ago

Level 104

@Shivamyadav I don't fully understand what you're trying to achieve and the flow between the two PHP scripts. You could describe how you get from the login script to the insert student script? Also, the INSERT query appears vulnerable; where are the variables $firstName etc. coming from?

Aside, you should ensure you actually get a query result at login (using mysqli_num_rows)

 $filterEmail = filter_var($email, FILTER_VALIDATE_EMAIL);
$sql = "SELECT * FROM `users` where email = '$email' ";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) === 0) {
    // no user found
    header("location:/");
    exit();
}
$emailRow = mysqli_fetch_assoc($result);

Shivamyadav

3 years ago

Level 20

@tykus I have reached near it .. now error is take a look and a user registered id is also printing 57 https://paste.pics/225e50709d56b8c0a64a542c288df283 and all the $first_name variable are coming from this file require __DIR__ . "/../../validation/studentValidation/create.php"; cod of this file is

<?php 
    require __DIR__ . '/../../dbConnection.php';

    $firstName = mysqli_real_escape_string($conn, $_POST['first_name']);
    $lastName = mysqli_real_escape_string($conn, $_POST['last_name']);
    $email = mysqli_real_escape_string($conn, $_POST['email']);
    $dob = mysqli_real_escape_string($conn, $_POST['dob']);
    $fatherName = mysqli_real_escape_string($conn, $_POST['father_name']);
    $mobile_no = mysqli_real_escape_string($conn, $_POST['mobile_no']);
    $city = mysqli_real_escape_string($conn, $_POST['city']);
    $district = mysqli_real_escape_string($conn, $_POST['district']);
    $gender =  isset($_POST['gender']) ? $_POST['gender'] : "";
    $state = mysqli_real_escape_string($conn, $_POST['state']);
    $nationality = mysqli_real_escape_string($conn, $_POST['nationality']);
    $image = isset(__FILE__["image"]["name"]) ? __FILE__['image']['name'] : "";
    // echo $image;
    $imageType = isset(__FILE__["image"]["type"]) ? __FILE__['image']['type'] : "";
    $imageSize = isset(__FILE__["image"]["size"]) ? __FILE__['image']['size'] : "";
    $imageTemp = isset(__FILE__["image"]["temp_name"]) ? __FILE__['image']['temp_name'] : "";

    $errors = [];
    $regex = "/['^0-9']/";
    $regexMatch = preg_match($regex, $firstName);
    if (strlen($_POST['first_name']) == 0) {
        $errors['first_name'] = "First name is required.";
    }
    else if (strlen($_POST['first_name']) < 3) {
        $errors['first_name'] = "First name cann't be less than 3 characters.";
    }
    else if ($regexMatch == 1) {
        $errors['first_name'] = "First name cann't includes a number.";
    } 
    //last Name Validation

    $regexFind = preg_match($regex, $lastName);
    if (strlen($_POST['last_name']) == 0) {
        $errors['last_name'] = "Last name is required.";
    }
    else if ($regexFind == 1) {
        $errors['last_name'] = "Last name cann't includes a number.";
    } 
    elseif (strlen($_POST['last_name']) > 70) {
        $errors['last_name'] = "Last name cann't be more than 70 characters.";
    }

    //email validation
    $emailFilter = filter_var($email, FILTER_VALIDATE_EMAIL);

    $emailUnique = "SELECT * from `students` where email = '$email' ";
    $emailQuery = mysqli_query($conn, $emailUnique);
    $emailRow = mysqli_num_rows($emailQuery);
    if (strlen($email) == 0) {
        $errors['email'] = "Email is required";
    }
    else if ($emailFilter == 0) {
        $errors['email'] = "Please  provide a valid email.";
    }
    else if ($emailRow > 0) {
        $errors['email'] = "Email is already exists.";
    }

    //mobile number validation
    $numberRegex = "/['^a-zA-Z']/";
    $pregMatch = preg_match($numberRegex, $mobile_no);


    if(strlen($mobile_no) == 0){
        $errors['mobile_no'] = "Mobile number is required.";
    }
    else if($pregMatch == 1){
        $errors['mobile_no'] = "Mobile number cann't be a character.";
    } 
    else if(strlen($mobile_no) < 9){
        $errors['mobile_no'] = "Mobile number cann't be less than 10 numbers.";
    } 
    //gender validation
    if ($gender != "Male" && $gender != "Female") {
        $errors['gender'] = "Please select gender.";
    }
    // //image Validation

    // if($imageType != 'jpg' && $imageType != 'png')
    // {
    //     $errors['image'] = "Please use image type of jpg/png.";
    // } else if($imageSize > 2000) {
    //     $errors['image'] = "Image should not be more than 2MB.";
    // } else {
    //     move_uploaded_file($imageTemp, __DIR__ . '/../../images/students');
    // }
    
?>

tykus

3 years ago

Level 104

@Shivamyadav is the FK set up correctly; it appears that the user_id foreign key constraint references students.id; it should be constrained to users.id, right?

Shivamyadav

3 years ago

Level 20

@tykus right.

tykus

3 years ago

Level 104

@Shivamyadav so you need to change that constraint on the database.

Shivamyadav

3 years ago

Level 20

@tykus done but not getting the rigth data on the table https://paste.pics/4bab7c6b10a3f8e12cf413855ec6cf90 here is fetch code for foreign key

$sql = "SELECT * from users Inner Join students on students.user_id = users.id";
$data = mysqli_query($conn, $sql);
$fetchRow = mysqli_num_rows($data);

and my databse table reocrd https://paste.pics/c7c36d28470608e57b76a120a0a23085

tykus

3 years ago

Level 104

@Shivamyadav you have a user_id 57 on the students table; but there is no matching record on the users table; where did 57 come from?

Shivamyadav

3 years ago

Level 20

@tykus in the users table id column 57 , see it here https://paste.pics/68e5c06afb85d0eae53f805de47a64a7

tykus

3 years ago

Level 104

@Shivamyadav ok, I see.

So the incorrect data is the repeating values under the Contact, Song` etc headings? I had assumed it was just dummy data 🤦‍♂️

How are you actually writing that data to the table?

Shivamyadav

3 years ago

Level 20

@tykus here is the code

require __DIR__ . "/../../dbConnection.php";
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
    require __DIR__ . "/../../validation/studentValidation/create.php";
    if(isset($_SESSION['id'])){
        $user_id = $_SESSION['id'];
        // echo $user_id;
    
    
        if (empty($errors)) {
            $sql = "INSERT into `students`
                (user_id, first_name, last_name, email, dob, father_name, mobile_no, city, district, gender, state, nationality, image)
                values('$user_id', '$firstName', '$lastName', '$email', '$dob', '$fatherName', '$mobile_no', '$city',
                    '$district', '$gender','$state', '$nationality', '$image'
                )";
            $query = mysqli_query($conn, $sql);

            $_SESSION['msg'] = "Stored Successfully!";
            
        }
        header("location:/");
    }
}

tykus

3 years ago

Level 104

@Shivamyadav from the screenshot, it doesn't look like you are inserting the wrong values, but whenever you fetch to data for display; it is rendered incorrectly. Show how that view is being rendered.

Shivamyadav

3 years ago

Level 20

@tykus here it is

<?php
require(__DIR__ . '/../../dbConnection.php');
require(__DIR__ . '/../../components/header.php');
require(__DIR__ . '/../../components/navbar.php');
// require (__DIR__ . '/../../controller/studentController/store.php');

$sql = "SELECT * from users Inner Join students on students.user_id = users.id";
$data = mysqli_query($conn, $sql);
$fetchRow = mysqli_num_rows($data);
if($fetchRow > 0)
{ ?>
<div class="-mt-80 rounded bg-gray-400">
    <table class="table-auto ">
        <thead>
            <tr>
                <th class="px-2">Id</th>
                <th class="px-2">First Name</th>
                <th class="px-2">Last Name</th>
                <th class="px-2">Email</th>
                <th class="px-12">D.O.B</th>
                <th class="px-2">Contact</th>
                <th class="px-2">Song</th>
                <th class="px-2">Artist</th>
                <th class="px-2">Year</th>
                <th class="px-2">Song</th>
                <th class="px-2">Artist</th>
                <th colspan="" 2 class="px-2">Action</th>


            </tr>
        </thead>
        <tbody>
            <?php foreach($data as $students){ ?>
            <tr>
                <td class="px-2">
                    <?php echo $students['id']?>
                </td>
                <td class="px-2">
                    <?php echo $students['first_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['email']?>
                </td>
                <td class="px-6 ">
                    <?php echo $students['dob']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>


            </tr>
            <?php } ?>
        </tbody>
    </table>
</div>
<?php } ?>

<?php require(__DIR__ . '/../../components/footer.php'); ?>

tykus

3 years ago

Level 104

@Shivamyadav you see this, right:

                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>

You need to index into the $students hash with the correct key for the appropriate column(s)

Shivamyadav

3 years ago

Level 20

@tykus unable to understand ? :(

tykus

3 years ago

Level 104

@Shivamyadav you're echoing the last_name over and over; change it to the appropriate keys, e.g.

<?php
require(__DIR__ . '/../../dbConnection.php');
require(__DIR__ . '/../../components/header.php');
require(__DIR__ . '/../../components/navbar.php');
// require (__DIR__ . '/../../controller/studentController/store.php');

$sql = "SELECT * from users Inner Join students on students.user_id = users.id";
$data = mysqli_query($conn, $sql);
$fetchRow = mysqli_num_rows($data);
if($fetchRow > 0)
{ ?>
<div class="-mt-80 rounded bg-gray-400">
    <table class="table-auto ">
        <thead>
            <tr>
                <th class="px-2">Id</th>
                <th class="px-2">First Name</th>
                <th class="px-2">Last Name</th>
                <th class="px-2">Email</th>
                <th class="px-12">D.O.B</th>
                <th class="px-2">Contact</th>
                <th class="px-2">Song</th>
                <th class="px-2">Artist</th>
                <th class="px-2">Year</th>
                <th class="px-2">Song</th>
                <th class="px-2">Artist</th>
                <th colspan="" 2 class="px-2">Action</th>


            </tr>
        </thead>
        <tbody>
            <?php foreach($data as $students){ ?>
            <tr>
                <td class="px-2">
                    <?php echo $students['id']?>
                </td>
                <td class="px-2">
                    <?php echo $students['first_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['last_name']?>
                </td>
                <td class="px-2">
                    <?php echo $students['email']?>
                </td>
                <td class="px-6 ">
                    <?php echo $students['dob']?>
                </td>
                <td class="px-2">
                    <?php echo $students['contact']?>
                </td>
                <td class="px-2">
                    <?php echo $students['song']?>
                </td>
                <td class="px-2">
                    <?php echo $students['artist']?>
                </td>
                <td class="px-2">
                    <?php echo $students['year']?>
                </td>
                <td class="px-2">
                    <?php echo $students['song']?>
                </td>
                <td class="px-2">
                    <?php echo $students['artist']?>
                </td>


            </tr>
            <?php } ?>
        </tbody>
    </table>
</div>
<?php } ?>

<?php require(__DIR__ . '/../../components/footer.php'); ?>

Shivamyadav

3 years ago

Level 20

@tykus I think everything is fine last name is in the table students. but the problem is coming from the last_name?

tykus

3 years ago

Level 104

@Shivamyadav so what, are you saying that you actually want to have so many columns in the HTML table with the last name??? What is the actual problem you are (not) describing here

but not getting the rigth data on the table https://paste.pics/4bab7c6b10a3f8e12cf413855ec6cf90

What is actually the issue; were you expecting more records? You use an INNER JOIN; did you want an LEFT JOIN? I can't read your mind!

Shivamyadav

3 years ago

Level 20

@tykus i want a data of a currently login user data created by him not others users..

tykus

3 years ago

Level 104

@Shivamyadav then you need to constrain your query by the user's ID (from the Session?)

$sql = "SELECT * from users Inner Join students on students.user_id = users.id WHERE user_id = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, $_SESSION['user_id']);
$fetchRow = mysqli_stmt_execute($stmt);

Shivamyadav

3 years ago

Level 20

@tykus my new code

$id = $_SESSION['id'];
$sql = "SELECT * from students where id = '$id'";
$data = mysqli_query($conn, $sql);
$fetchRow = mysqli_num_rows($data);

the record is coming only 1 not all by this current user.. https://paste.pics/6ec86ebc660dff42149ce50499640fc9

tykus

3 years ago

Level 104

@Shivamyadav are you searching for students by id or by user_id?

i want a data of a currently login user data created by him not others users

In that case, the constraint should be on user_id, not id:

$sql = "SELECT * from students where user_id = '$id'";

tykus

3 years ago

Level 104

@shivamyadav did this issue really require you to create another thread?

programators

3 years ago

Level 17

IF you are stick to mysqli .. it is still ok . In the latest php (8.2) you can send variables into execute just like with PDO. first of all please read article about inserting https://phpdelusions.net/mysqli_examples/insert If you do not want to read "some" blog then just read documentation about method you are going to use https://www.php.net/manual/en/mysqli.real-escape-string.php Basically you need to use prepared statemts.

about your code trying to be helpful

you do not share how do you get emailRow variable i mean your query method

Please or to participate in this conversation.