PHP查询从多个表插入数据

Question

我有一个表 A(预测)，其中包含表 B(灯具)和表 C(用户注册表)中的列。每次新用户注册时，我都想将他们的用户名以及来自表 B 的所有数据的数据添加到表 A。

当我注册一个新用户时，它只将用户数据插入表 A(预测)和表 B(预测)的第一行。

我想要实现的是；当新用户注册时，他们的用户名名称将与本赛季的固定装置一起添加到用户名列中，因此用户名将插入到每个固定装置的每一行中:

预测ID |修复ID |首页_团队 |首页_分数 |客场得分 |离开_团队 |用户名

1 ------------1 --------人U-------------------- 7----- ------0----------------阿森纳------B.Rubble

2 ------------1 --------人U-------------------- 5----- ------0----------------阿森纳--------B.Rubble

3 ----------2 ------切尔西----------------0 ------ ------1----------------莱丹--------New.user

4 ----------2 ------切尔西 ------------------ 0 ------ -----3----------------Leister--------New.user

使用插入函数

public function insert($table, $fields = array()) {

    $keys   = array_keys($fields);
    $values = '';
    $x      = 1;

    foreach ($fields as $field) {
        $values .= '?';
        if ($x < count($fields)) {

            $values .= ', ';
        } //are we at end of defined fields

        $x++;
    }


    $sql = "INSERT INTO {$table} (`" . implode('`, `', $keys) . "`) VALUES ({$values})";

    if (!$this->query($sql, $fields)->error()) {
        return true;
    }


    return false;
}

我正在使用以下 updatePredTable() 函数

public function updatePredTable($fields=array()){
    if(!$this->_db->insert('predict', $fields)) {
        throw new Exception('There was a problem creating an account');
    }
}

注册.php

<?php
require_once 'core/init.php';

include 'navbar.php';

$prediction = DB::getInstance()->query("SELECT * FROM fixtures");

if (!$prediction->count()) {
    echo 'No preds';
} else {  
    foreach ($prediction->results() as $row) {

        $row = get_object_vars($row);
        echo $row['Home_Team'], '<br>';

        if (Input::exists()) {
            if (Token::check(Input::get('token'))) {

                var_dump(Token::check(Input::get('token')));


                //echo 'I have been run'; 
                $validate   = new Validate();
                $validation = $validate->check($_POST, array(

                    'username' => array(

                        'required' => true,
                        'min' => 2,
                        'max' => 20,
                        'unique' => 'users'
                    ),
                    'password' => array(

                        'required' => true,
                        'min' => 6


                    ),
                    'password_again' => array(

                        'required' => true,
                        'matches' => 'password'
                    ),
                    'name' => array(

                        'required' => true,
                        'min' => 2,
                        'max' => 50
                    )

                ));

                if ($validation->passed()) {

                    //Session::flash('Success', 'You registered successfully!');
                    //header('Location: index.php');

                    $user = new User();
                    $salt = Hash::salt(32);

                    try {

                        $user->create(array(

                            'username' => Input::get('username'),
                            'password' => Hash::make(Input::get('password'), $salt),
                            'salt' => $salt,
                            'name' => Input::get('name'),
                            'joined' => date('Y-m-d H:i:s'),
                            'group' => 1   
                        ));

                        $user->updatePredTable(array(
                            'username' => Input::get('username'),
                            'fixture_ID' => $row['Fixture_ID'],
                            'date' => $row['Date'],
                            'home_Team' => $row['Home_Team'],
                            'away_Team' => $row['Away_Team']
                        ));

                        Session::flash('home', 'You have been registered you can now log in!');
                        //like:
                        //header('Location: index.php');

                        Redirect::to('index.php');  
                    }
                    catch (Exception $e) {
                        die($e->getMessage());
                    }
                    //echo 'Passed';
                } else {
                    foreach ($validation->errors() as $error) {
                        echo $error, '<br>';
                        //print_r($validation->errors());
                    }
                }
            }
        }
    }  
}


?>

Answer 1

我认为您需要查看循环遍历预测结果集的位置。看起来在第一次迭代中你重定向到 index.php 因此只添加了一行尝试循环遍历结果集，如下所示

 <?php
require_once 'core/init.php';

include 'navbar.php';

$prediction = DB::getInstance()->query("SELECT * FROM fixtures");

if (!$prediction->count()) {
    echo 'No preds';
} else {  


        if (Input::exists()) {
            if (Token::check(Input::get('token'))) {

                var_dump(Token::check(Input::get('token')));


                //echo 'I have been run'; 
                $validate   = new Validate();
                $validation = $validate->check($_POST, array(

                    'username' => array(

                        'required' => true,
                        'min' => 2,
                        'max' => 20,
                        'unique' => 'users'
                    ),
                    'password' => array(

                        'required' => true,
                        'min' => 6


                    ),
                    'password_again' => array(

                        'required' => true,
                        'matches' => 'password'
                    ),
                    'name' => array(

                        'required' => true,
                        'min' => 2,
                        'max' => 50
                    )

                ));

                if ($validation->passed()) {

                    //Session::flash('Success', 'You registered successfully!');
                    //header('Location: index.php');

                    $user = new User();
                    $salt = Hash::salt(32);

                    try {

                        $user->create(array(

                            'username' => Input::get('username'),
                            'password' => Hash::make(Input::get('password'), $salt),
                            'salt' => $salt,
                            'name' => Input::get('name'),
                            'joined' => date('Y-m-d H:i:s'),
                            'group' => 1   
                        ));

foreach ($prediction->results() as $row) {

        $row = get_object_vars($row);
        echo $row['Home_Team'], '<br>';

                        $user->updatePredTable(array(
                            'username' => Input::get('username'),
                            'fixture_ID' => $row['Fixture_ID'],
                            'date' => $row['Date'],
                            'home_Team' => $row['Home_Team'],
                            'away_Team' => $row['Away_Team']
                        ));
}
                        Session::flash('home', 'You have been registered you can now log in!');
                        //like:
                        //header('Location: index.php');

                        Redirect::to('index.php');  
                    }
                    catch (Exception $e) {
                        die($e->getMessage());
                    }
                    //echo 'Passed';
                } else {
                    foreach ($validation->errors() as $error) {
                        echo $error, '<br>';
                        //print_r($validation->errors());
                    }
                }

        }
    }  
}

我还没有对此进行测试，但希望它能为您指明正确的方向