0

I'm trying to make a website that allows members to register. I've got a database set up with multiple tables. Simplified it looks like the following:

Users has two columns: 'ID' and 'Username', where 'ID' is the primary key.

Messages has two columns: 'ID' and 'Message', where 'ID' is a foreign key that refers to Users.

UserRank has several columns as well, including an 'ID' that is also a foreign key to Users.

There's even more tables that use the same 'ID' to identify the user/member.

I use MySQL to send and store the data from the webpage to the database like this:

$sqlQuery = "INSERT INTO users (Username) VALUES 
('$username')";
$result=mysql_query($sqlQuery);

Users gets filled like it should, but the 'ID' field of Messages and UserRank remains empty. I want to fill them accordingly, i.e. for every user in Users (who all have an unique ID due to it being a primary key), there should be a record in the other tables as well.

I've tried to fill them manually with other queries but this doesn't seem to work:

$idQuery = mysql_query("SELECT ID FROM users WHERE Username='$username'");
$result = mysql_fetch_array( $idQuery );
$ID=$result['ID'];

$query= "INSERT INTO Messages (ID) VALUES ('$ID')";
$result=mysql_query($query);

I know it's a weird construction but I had to try something. Please point me in the right direction here.

halfer
  • 19,824
  • 17
  • 99
  • 186
Daniel
  • 43
  • 7
  • 1
    Don't you think users need a password? – Ali Dec 18 '13 at 22:02
  • Honestly it looks like you have some of your tables structured poorly. You wouldn't have any rows in messages for that user until they send or get one. And user rank can probably be IN the user table. – Jessica Dec 18 '13 at 22:03
  • That said, use mysql_insert_id() after inserting the user to get their ID. – Jessica Dec 18 '13 at 22:03
  • Like I said, this is a simplified version. I left various fields out because they're not relevant. – Daniel Dec 18 '13 at 22:05
  • @Jessica Never mind the table names, they are irrelevant to my question. I'm trying to fill in the foreign key 'ID' in multiple tables, my question is solely about that. – Daniel Dec 18 '13 at 22:09
  • It seems I've formulated my question wrong then, forgive me. To be clear now: I'm not asking for feedback on the database structure, above is a simplified vision of a complex structure with all key restraints set up properly. I've left most out because it is irrelevant to my problem. I'm merely trying to fill multiple tables because phpMyAdmin doesn't seem to automatically insert a record, like one would expect with foreign key constraints set up. – Daniel Dec 18 '13 at 22:21
  • @Daniel: is the relationship between `users` and `messages` a 1:1? That's what you have at the moment, but the table names imply (to me at least) that they ought to be 1:many. – halfer Dec 25 '13 at 13:00

2 Answers2

2

you are structuring things the wrong way. you need to get some Conceptual Data Model courses online to be able to make a well structured database, because you are missing a lot of fields and most of all you are missing the Foreign Keys

zerzer
  • 613
  • 2
  • 8
  • 20
  • Thanks for your reply. Again, this is a simplified vision of the database. I have left out various fields for simplicity, because they are irrelevant to my problem. Besides, I have set the foreign key constraints properly, like I stated. – Daniel Dec 18 '13 at 22:11
  • don't just suppose that the other entries are not important , make the full query to get a better error reporting to find out where the problem really is. and keep doing echo for your variables at each line when you get error to see which query doesn't return data; and don't forget mysql_query($query) or die(mysql_error()) ; to see what's going on – zerzer Dec 18 '13 at 22:18
1

The PHP syntax looks like it would run fine however your SQL queries are most probably failing. This is mostly due to your foreign key assignment and database structure.

However I would recommend after your queries for testing purposes using mysql_error();

$sqlQuery = "INSERT INTO users (Username) VALUES ('".$username."')";
$result=mysql_query($sqlQuery) or die(mysql_error());

It should stop and give you more information to why you're not getting what you expected.

Ollie
  • 51
  • 3
  • 2
    Please use parameterized queries. – p.s.w.g Dec 18 '13 at 22:38
  • No, that's still not a parameterized query. See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – p.s.w.g Dec 18 '13 at 22:55
  • It's rather off topic so I'm not going to include another line. But I understand your point. Calling mysql_real_escape_string() on each variable is one option if you want to avoid SQL Injection attacks. – Ollie Dec 18 '13 at 23:01
  • @Bludock, I don't think it's off-topic. Every answer to a database question should include the code to run queries safely, otherwise this resource site is propagating insecure practices, along with most of the web. We can do better here! – halfer Dec 25 '13 at 12:57