mysql insert into database only if user exists in datatabase?

Question

hi im looking for a way to implement a condition either in php or mysql that carry's out the below mysql query only if the 'from_user_id' exists in the database?

i have table called ptb_users that stores all the user id's, the other table that the below query inserts content into is ptb_wallposts.

users can write on other users walls, but before a user can write on a users wall/before the insert query runs i want the condition to cross reference with ptb_users to make sure the user sending the message/the from_user_id exists. so if user 1 is logged in and wants to send a message to user 2s wall then it first checks that the from_user_id matches the user_id in ptb_users.

so for instance i dont want people to sql inject data into the database by being able to have a from_user_id of 0, it must be matching with a user_id in the database or display and error message.

Can someone please advice me how i might do this. thanks.

my ptb_wallposts table looks like this

id   |     from_user_id   |  to_user_id  |  content

1               2                3            hello

my ptb_users table looks like this:

id   |     user_id   |  

1             2

mysql query:

$sql = "INSERT INTO ptb_wallposts (id, from_user_id, to_user_id, content) VALUES (NULL, '".$_SESSION['user_id']."', '".$profile_id."', '".$content."');";
            mysql_query($sql, $connection);

You can firstly `SELECT ... user` and in case you do not have a NULL value, do the `INSERT`. — zipser, Apr 02 '13 at 17:53
First, make sure to create a foreign key for from_user_id, to_user_id to user's table, then to display a friendly message, you can do a select as zipser mentioned — Akash, Apr 02 '13 at 17:53
how would i structure my select statement i have tried this: $sql = "SELECT user_id FROM ptb_users WHERE ptb_users.user_id=ptb_wallposts.from_user_id"; if(!$sql) $sql2 = "INSERT INTO ptb_wallposts (id, from_user_id, to_user_id, content) VALUES (NULL, '".$_SESSION['user_id']."', '".$profile_id."', '".$content."');"; mysql_query($sql, $connection); — James Pale, Apr 02 '13 at 18:01
This has been asked before: http://stackoverflow.com/questions/7854274/insert-rows-only-when-id-from-another-table-exists and other previous posts. — Revent, Apr 02 '13 at 18:04

score 1 · Answer 1 · edited May 23 '17 at 12:29

1

Use foreign keys and SQL does the job for you. First you need to add them like

ALTER TABLE `ptb_wallposts`
    ADD CONSTRAINT `users_wallposts_fk_1` FOREIGN KEY (`from_user_id`) REFERENCES `ptb_users` (`id`) ON DELETE CASCADE,
    ADD CONSTRAINT `users_wallposts_fk_2` FOREIGN KEY (`to_user_id`) REFERENCES `ptb_users` (`id`) ON DELETE CASCADE;

And now if you want to insert an id that is not in ptb_users you'll get a SQL error.

See this link for info on the meaning of ON DELETE CASCADE

edited May 23 '17 at 12:29

Community

1
1

answered Apr 02 '13 at 18:04

kero

10,647
5
41
51

#1452 - Cannot add or update a child row: a foreign key constraint fails (`ptb2013/#sql-22b9_26f4d4`, CONSTRAINT `users_wallposts_fk_1` FOREIGN KEY (`from_user_id`) REFERENCES `ptb_users` (`id`) ON DELETE CASCADE) – James Pale Apr 02 '13 at 18:08
Do you have invalid entries at the moment? ([Creating relations: #1452 - Cannot add or update a child row: a foreign key constraint fails](http://stackoverflow.com/questions/7274494/creating-relations-1452-cannot-add-or-update-a-child-row-a-foreign-key-cons) – kero Apr 02 '13 at 18:10

score -1 · Accepted Answer · answered Apr 02 '13 at 18:22

-1

You can try this:

if(mysql_num_rows(mysql_query("SELECT id  FROM ptb_users WHERE user_id='".$_SESSION['user_id']."'"))){
        //your insert statement 
}

else{
        //not allowed 
}

I see you are using $_SESSION variable I think you don't need to check whether the user_id exists in database because $_SESSION variables can't be manipulated by end user.

answered Apr 02 '13 at 18:22

Enn Fenn

400
2
5
15

would i be able to add to this to say AND WHERE ptb_wallposts.from_user_id is NOT NULL? so people can't hack it and add a user_id of 0? – James Pale Apr 02 '13 at 18:40

mysql insert into database only if user exists in datatabase?

2 Answers2