[SOLVED] Inserting linked data into two tables

[jakeoh]

Hi,

 

I am quite new to PHP and MySQL, so sorry if this sounds like a stupid question. Here goes:

 

I have a registration page where a new user can enter his username, password, name, etc. He can also upload a picture to link to his account.

 

The picture is saved into a table in the database, while the user info is saved somewhere else. When I register the new user, I have the following query at the moment:

 

  $imageQuery = "INSERT INTO ".TBL_USER_AVATARS." VALUES (0, '$fileContent', '$avatar_type')";

    return mysql_query($imageQuery, $this->connection);

 

  $userinfoQuery = "INSERT INTO ".TBL_USERS." VALUES ('$username', '$password', '0', $ulevel, '$email', '$firstname', '$lastname', '$address', '$city', $time, '$birthday')";

      return mysql_query($userinfoQuery , $this->connection);

 

The info is updated allright, but of course the avatar is not linked to the user in any way. How would you proceed to make that happen?

 

Thanks!

[Barand]

Add a username column to the avatars table?

[jakeoh]

Thanks for the answer.

 

I thought of that, but the problem is that I'll have some default avatars later, that can be linked to more than one user; so I would prefer having a "avatarID" column in the user table; but I don't know how to add the avatarID to the users table, since it is auto incremented in the avatar table. How can I get the ID of the avatar that was just added so that I can link it to the user in the Users table?

 

EDIT: Can I use mysql_insert_id() to achieve this?

 

Also, how can I return the result of both inserts? What I need to do is something like this:

 

return [mysql_query($imageQuery, $this->connection)] AND [mysql_query($userinfoQuery, $this->connection)];

 

...but I'm pretty sure the above would not work...

[Barand]

EDIT: Can I use mysql_insert_id() to achieve this?

Yes. Insert the avatar, call mysql_insert_id() to get its id and write that to user record

 

return [mysql_query($imageQuery, $this->connection)] AND [mysql_query($userinfoQuery, $this->connection)];

 

...but I'm pretty sure the above would not work...

You're right, it won't. You could return an array though.