0

Im fairly new to PHP and MySql, Currently I am struggling to understand how I will change a foreign keys input if it is a username to its ID from its parent table.

Currently I have this:

$sql ="INSERT INTO Car(Name,UserID,TypeID) VALUES ('$_POST[Name]','$_POST[Username]','$_POST[Type]')";

Just to clarify the Username and UserID if from another table, but I want the username to be able to match with its ID and be inserted instead of this into the child table

ababusa
  • 240
  • 2
  • 10
  • Your question is not clear? What's the meaning of "Changing Username"? What's your table primary key? what's the structure of the other table related to the user table? – SaidbakR Dec 09 '14 at 19:39
  • 1
    you're not understanding foreign keys. you don't insert a user's NAME in child tables. you insert the user's ID. the id never changes, while names can/will change. – Marc B Dec 09 '14 at 19:41
  • `I want the username to be able to match with its ID and be inserted instead of this into the child table` what does that mean – meda Dec 09 '14 at 19:42
  • I want to be able to match the Username with its ID and insert the ID NOT the username into the table. This is because should the user be expected to remember their ID when making an input? – ababusa Dec 09 '14 at 19:50
  • _“because should the user be expected to remember their ID when making an input?”_ – no; your script is supposed to _look up_ the user id if it gets the username. So go and implement that. – CBroe Dec 09 '14 at 19:54
  • This is why I came here, because I cant find a clear answer how – ababusa Dec 09 '14 at 19:57
  • so, you have a form where a user types his/her username when "making an input"? – DrSpy Dec 09 '14 at 19:58
  • Can you please share the create statement (table definition) for the table "bird" and the child table? – Valerie Parham-Thompson Dec 09 '14 at 20:00
  • Also, on the page the user is submitting this from, is the user already known? That is, has the user already logged in or have you somehow already retrieved the userID? – Valerie Parham-Thompson Dec 09 '14 at 20:01
  • I have the user logged on using sessions...this info for the user comes from the parent table, then is used as a fk in the bird table – ababusa Dec 09 '14 at 20:03
  • **WARNING**: This is terrifyingly insecure because those parameters are not [properly escaped](http://bobby-tables.com/php). You should **NEVER** put `$_POST` data directly into the query: it creates a gigantic [SQL injection bug](http://bobby-tables.com/). `mysql_query` is an obsolete interface and should not be used, it's being removed from PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). A guide like [PHP The Right Way](http://www.phptherightway.com/) explains best practices. – tadman Dec 09 '14 at 21:04
  • I have started MySql and PHP only short while ago, so at this moment I just want assistance with my problem, the SQL injection has already been mentioned – ababusa Dec 09 '14 at 21:13

1 Answers1

2

First, I must say that your query is vulnerable to SQL injection. This is because you are essentially trusting the user's input, by setting the post variables in the query. You can see how to prevent injections below.

How can I prevent SQL injection in PHP?

I am not sure why you would want to overwrite a user's ID with it's username. I suggest having a user table, where each user has a row, and the ID of that table would then become the user's ID.

EDIT

You wouldnt have to worry about the user remembering their userID. You can use the username submitted from the front end to find out the userID for backend processing:

$result = SELECT userID FROM users WHERE username = $username

You can then use the userID from the result to perform the user actions and then report to the user using either their first name, or their username.

EDIT

Upon user creation, we do not need to implement the use of the username within the birds table (as we are just creating the user).

Once a user is logged in (by querying the user table) to verify that the username and password is a match, you could create a $user global variable that stores the username. When a user goes to the form to submit their favorite bird, you can use that global $user variable containing the username to insert into the bird table.

This is where you would do:

$result = "INSERT INTO birds (column1,column2,column3,...) VALUES (value1,value2,value3,...)";

Keeping in mind the id on the table will increment itself.

For a MySQL table, you NEED some sort of unique identifier. For the purpose of the birds table, you could have a simple id that just automatically increments everytime a bird is added.

Hope this helps.

Community
  • 1
  • 1
Anthony
  • 107
  • 5
  • This is a comment, not an answer - but it is a great comment! – Strawberry Dec 09 '14 at 19:47
  • 1
    Thank you. To clarify, I am still looking to see why he would want to make his user ID the same as his username... Otherwise, I would go with the suggestion that I have provided in my second paragraph. – Anthony Dec 09 '14 at 19:50
  • Thanks for your response Anthony, I have a table for users and the UserID is a FK in a booking type table. The reason for wanting to replace the Username with its related ID is because the user shouldnt have to remember their UserID,surely? – ababusa Dec 09 '14 at 19:52
  • 1
    @MisterDood, sorry about that. Ill do that from now on. – Anthony Dec 09 '14 at 20:34
  • Thanks again for the help Anthony, my problem is the username is in the form and the ID is a requirement of the child table to complete a submission. I understand the ID can be queried at a later stage, but I need it in this case for the Username to be matched with its ID and inserted instead of it – ababusa Dec 09 '14 at 21:16
  • 1
    I think I am beginning to understand your issue. Is this a custom made application? Do two tables contain all of the information for a user? – Anthony Dec 09 '14 at 21:21
  • I do apologise for being such a pain, like I said, completely new here...perhaps if I explain the scanerio a bit better will help. The central table is birds with UserID as a FK, another table is to store the users. The user must record their birds in the form, with their ID attached. My predicament is im wanting to store the ID based on the Username. I hope this helps, its awkward I know – ababusa Dec 09 '14 at 21:25
  • 1
    No worries. I think we have an understanding now. I will edit my answer above. – Anthony Dec 09 '14 at 21:26
  • thanks once again for the help but this didnt really solve my problem mate. I edited my OP with a image link of my ERD to clarify...I am using phpmyadmin and auto incre is already established. The bird table is created as such but my issue is relating the username to the FK UserID upon insertion – ababusa Dec 09 '14 at 21:37
  • 1
    Alright. I suppose I didn't understand what you mean. I wish you luck then. – Anthony Dec 09 '14 at 21:39
  • Hey Anthony, your advice on creating a variable from the session did actually solve my problem, for that ill mark it as solved. thanks dude – ababusa Dec 09 '14 at 22:13
  • Great to hear! Good luck with everything! – Anthony Dec 10 '14 at 14:03