mysql - PHP Query inserting data from multiple tables -
i have table a(predictions) consisting of columns table b(fixtures) , table c(user registration table). each time new user registers want add username table along data data table b.
when register new user inserts users data table a(predictions) , first row table b(predictions).
what want achieve is; when new user registers username name added username column along along fixtures season, therefore username inserted in each row each fixture:
pred_id | fix_id | home_team | home_score | away_score | away_team | username
1 -----------1 -------man u------------------- 7-------------0-----------------arsenal------b.rubble
2 -----------1 -------man u------------------- 5-------------0----------------arsenal-------b.rubble
3 -----------2 -------chelsea------------------0-------------1----------------leister-------new.user
4 -----------2 -------chelsea------------------ 0------------3----------------leister--------new.user
using insert function
public function insert($table, $fields = array()) { $keys = array_keys($fields); $values = ''; $x = 1; foreach ($fields $field) { $values .= '?'; if ($x < count($fields)) { $values .= ', '; } //are @ end of defined fields $x++; } $sql = "insert {$table} (`" . implode('`, `', $keys) . "`) values ({$values})"; if (!$this->query($sql, $fields)->error()) { return true; } return false; } im using following updatepredtable() function
public function updatepredtable($fields=array()){ if(!$this->_db->insert('predict', $fields)) { throw new exception('there problem creating account'); } } register.php
<?php require_once 'core/init.php'; include 'navbar.php'; $prediction = db::getinstance()->query("select * fixtures"); if (!$prediction->count()) { echo 'no preds'; } else { foreach ($prediction->results() $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 can log in!'); //like: //header('location: index.php'); redirect::to('index.php'); } catch (exception $e) { die($e->getmessage()); } //echo 'passed'; } else { foreach ($validation->errors() $error) { echo $error, '<br>'; //print_r($validation->errors()); } } } } } } ?>
i think need @ loop on predictions result set. looks on first iteration redirecting index.php hence 1 row added try looping on resultset below
<?php require_once 'core/init.php'; include 'navbar.php'; $prediction = db::getinstance()->query("select * 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() $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 can log in!'); //like: //header('location: index.php'); redirect::to('index.php'); } catch (exception $e) { die($e->getmessage()); } //echo 'passed'; } else { foreach ($validation->errors() $error) { echo $error, '<br>'; //print_r($validation->errors()); } } } } } i haven't tested point in right direction
Comments
Post a Comment