Jump to content

INSERT if value doesnt already exist


BuildMyWeb
Go to solution Solved by BuildMyWeb,

Recommended Posts

ive tried more than one approach to this problem and i cannot get any of them working.  tried IF conditional with subquery and the WHERE NOT EXISTS as below.  all keep triggering a sql syntax error:

'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE NOT EXISTS(SELECT id FROM sessions WHERE author_id=2' at line 1'

i have a 'sessions' table with a field entitled, 'author_id'.  each author_id can only have one session entry at a time so if a user's author_id already exists in the 'session' table, the insert should not fire.
 

if( $stmt_session = $db_connect->prepare("INSERT INTO sessions ( id, name ) VALUES (?, ?) WHERE NOT EXISTS(SELECT id FROM sessions WHERE author_id=?") )
        {
            $id = 0;
            $author_id     = $obj_user->get_id();
            $name        = $obj_user->get_name(); 
            
            $stmt_session->bind_param('isi', $id, $name, $author_id);
            
            if( $stmt_session->execute() )

 

Link to comment
Share on other sites

SQL doesn't work like this.

 

If you want the author_id to be unique, then add a UNIQUE constraint to that column:

ALTER TABLE sessions ADD CONSTRAINT UNIQUE (author_id);

This may be problematic in the context of sessions, though, because if the session isn't terminated (for whatever reason), you'll not be able to insert a new session. It might more sense to overwrite the old session or keep multiple sessions with only one marked as active.

  • Like 1
Link to comment
Share on other sites

i suspect you are right benanamen :)

 

as noted above, i have a 'sessions' table.  users of the application are permitted only one 'session' at a time.  so when they create a session, i am storing their user ID in the session table in the author_id column/field.

 

so when a user attempts to create a new session, i want to verify that there exists no record in the sessions table with author_id value = user ID.  i can do this easily enough in two queries, the first selecting FROM sessions WHERE author_id = user_id.  if no results are returned, process the new session request and INSERT INTO the sessions table as my second query.  but id like to do it all in one query, if possible.

 

does that make more sense?

Link to comment
Share on other sites

You didnt quite grasp my question. I dont want to know how you are trying to solve something. I want to know WHAT you are solving, the BIG Picture.

 

 

The answer to my question lies in your answer to : "users of the application are permitted only one 'session' at a time"

 

Why? What is the purpose for the "session"

 

 

"so when a user attempts to create a new session"

 

HOW? How are they creating a new session? By logging in again???? By someone else logging in as that user?

 

It kinda sounds like you are only wanting one person logged in with that user id at the same time.

Edited by benanamen
Link to comment
Share on other sites

@benanamen.  oh no. thats not my issue of concern.

 

users must log in successfully to access the application at all.  the client insisted on the use of the term 'session' and that might be confusing things.  when users login to their account, they can create an instance of a survey.  the client wants to call this instance a 'user session'.  each user (already registered and validated) can only create one survey session at a time.  so if theyve already created one, it must be deleted (by the user) before they can create a subsequent one.

Link to comment
Share on other sites

SQL doesn't work like this.

 

If you want the author_id to be unique, then add a UNIQUE constraint to that column:

ALTER TABLE sessions ADD CONSTRAINT UNIQUE (author_id);

This may be problematic in the context of sessions, though, because if the session isn't terminated (for whatever reason), you'll not be able to insert a new session. It might more sense to overwrite the old session or keep multiple sessions with only one marked as active.

 

 

is this the best way to go about it? 

Link to comment
Share on other sites

@benanamen.  oh no. thats not my issue of concern.

 

users must log in successfully to access the application at all.  the client insisted on the use of the term 'session' and that might be confusing things.  when users login to their account, they can create an instance of a survey.  the client wants to call this instance a 'user session'.  each user (already registered and validated) can only create one survey session at a time.  so if theyve already created one, it must be deleted (by the user) before they can create a subsequent one.

 

 

Ok, thats exactly what we really needed to know. And your use of programming terms with specific meaning to us will absolutely throw us off.

 

Ok, now just explain this more clearly:

 

"they can create an instance of a survey"

 

What exactly does that mean? When you say to a programmer about creating an "instance" it has a whole other meaning.

 

* It is your job to explain to the client he cant use programming words to describe other things.

Edited by benanamen
Link to comment
Share on other sites

Ok, thats exactly what we really needed to know. And your use of programming terms with specific meaning to us will absolutely throw us off.

 

Ok, now just explain this more clearly:

 

"they can create an instance of a survey"

 

What exactly does that mean? When you say to a programmer about creating an "instance" it has a whole other meaning.

 

* It is your job to explain to the client he cant use programming words to describe other things.

 

 

im not sure how else or what else to explain.  there will be a survey class.  its a multiple choice survey, in concept.  an instance of this survey class can be created by a user and when it is, the properties are posted to a db.  my client does not want a user to be able to post properties of more than one survey instance to the db at a time.  so i have a table in the db, called sessions.  (i have explained why he *shouldn't* use programming terms, but i disagree on *can't*.  its his project.  he's paying for it.  i advise and then do what he wants.)  each session in the table has a field/column named author_id.  this author_id corresponds to the primary/unique field called 'id' in the user_accts table. 

Link to comment
Share on other sites

Ok, you really need to stop using programming words to describe your situation. It is not helping at all.

 

survey class - OOP Programming term

instance of this survey class - OOP programming term 

the properties - OOP programming term

survey instance - OOP programming term

session - programming term

 

All the above have specific meanings to a programmer. You are going to continue to have problems getting help if you keep doing that.

 

If I wanted you to hand me a hammer but I told you to hand me a screwdriver, you wouldn't be reaching for the hammer.

 

At this point I am going to leave it up to someone else to decipher your problem.

Edited by benanamen
Link to comment
Share on other sites

  • Solution

In case this thread gets searched out, this is the query that worked for me.  thanks to help from Jacques1

            $stmt_session = $db_connect->prepare(
                "INSERT INTO sessions ( id, author_id, token, name )
                SELECT ?, ?, ?, ?
                FROM sessions
                WHERE NOT EXISTS (SELECT author_id FROM sessions WHERE author_id=?) LIMIT 1"
            )
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.