How to include primary in another table

PHP
1

Hello all,
I have run into a problem that appears to not be a problem for anyone else. I believe I have narrowed the problem down to this issue. I have a php class that inserts an array of items into a table. I also need it to insert the primary key (id) from a users table into a foreign key (user_id) in an aboutuser table. I am able to successfully do EITHER an insertion of the data, OR an insertion of the primary key. I can not seem to do them both. I have experimented with ‘transactions’ (shown below) and a slew of other things, but I am still unable to complete both tasks. According to mysql manual, I must use LAST_INSERT_ID() simultaneously with the insertion of the input data, otherwise the resulting user_id will be zero(which I unfortunately had a longterm problem with). Can anyone help with this issue? I will paste the PHP class script below. If more code is needed for you to investigate please let me know what you need.

public function insert_aboutuser($table, $fields = array()) {
			try {
				$this->_mypdo->beginTransaction();
				
				$keys = array_keys($fields);
				$values = '';
				$x = 1;			

				foreach($fields as $field) {
					$values .= '?';
					if($x < count($fields)) {
						$values .= ', ';
					}
					$x++;
				}			
  //These are the two queries that need to be completed together
				$sql = "INSERT INTO aboutuser (`" . implode('`, `', $keys) . " , user_id`) VALUES ({$values})";					
				$sql2 = "INSERT INTO aboutuser (`user_id`) VALUES (LAST_INSERT_ID())";

				$this->_mypdo->commit();
				
			} catch (PDOException $e) {
				$this->_mypdo->rollBack();
				die($e->getMessage());
			}			
			
			if(!$this->query($sql, $fields)->error()) {
				return true;
			}			
			
			return false;
		}
2

The obvious problem here is that you create a string variable ($sql), then immediately overwrite it with another string.

4

Hello SamA74, thanks for the reply. Even if I make the second one $sql2, the same problem will occur. Do you know what the issue is? I changed the original so it won’t show as the problem. Thanks

5

LAST_INSERT_ID() returns last inserted autogenerated id for this connection.

I mean, if you have somewhere: $this->_mypdo->query('INSERT INTO user...'); and execute it in current request before your insert_aboutuser(), then LAST_INSERT_ID() returns primary key, that you need.

6

Hello igor_g, Thank you for your reply. I have another identical class that inputs the data for the users table. This script appears to work fine. It inserts all the initial data into the users table. This is the main (primary key) table in which I want to link to. The aboutuser table has different data but it needs to link to the user in the users table. Do you know how to fix this issue?
Thanks

...
foreach($fields as $field) {
				$values .= '?';
				if($x < count($fields)) {
					$values .= ', ';
				}
				$x++;
			}
			
			$sql = "INSERT INTO users (`" . implode('`, `', $keys) . "`) VALUES ({$values})";	
...
7

Are data in users and in aboutuser inserted in same request and by same PDO-object?

8

Hello igor_g,
The data is collected in two separate arrays.

...
$user->create_user(array(
						'country_id' => escape(Input::get('country')),
						'state_id' => escape(Input::get('state')),
						'city_id' => escape(Input::get('city')),						
						'email' => escape(Input::get('email')),						
						'username' => escape(Input::get('username')),
						'password' => escape(Hash::make(Input::get('password'),
						
					));
					
					$aboutuser->create_aboutuser(array(							
						'intro' => escape(Input::get('intro'))
					));
...

In my users class I have two identical methods…one for users (insert), and the other for aboutusers (insert_aboutuser). I just pasted in one of them.



      public function create_user($fields = array()) {			
		if(!$this->_connection_here->insert('users', $fields)) {				
			throw new Exception('Error');
		}
	}
9

$this->_mypdo and $this->_connection_here are different instances?

10

They are in two separate class files . One is in a database class(file) and the other one is a users class(file). The user class gets an instance “getInstance()” from the database but the variable is different. Thanks

11

Look…

If you have…

$singlePDO->query('INSERT INTO users...');

$singlePDO->query('INSERT INTO abouteuser VALUES(LAST_INSERT_ID())');

…this will work.

But if you have…

$firstPDO->query('INSERT INTO users...');

$secondPDO->query('INSERT INTO abouteuser VALUES(LAST_INSERT_ID())');

…this will not work.

12

Is this suppose to work? This is absolute first thing that I have tried. Do you have any other ideas that may help? Thanks

13

Thanks all…I figured it out myself.

14

Care to post the solution, in case it helps anyone else?

1 Like
15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.