INSERT if value doesnt already exist

[BuildMyWeb]

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() )

 

[Jacques1]

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.

[benanamen]

Forget your code a minute. What is the actual overall problem you are trying to solve or task you want to accomplish? I suspect you are going about solving it the wrong way.

Edited October 22, 2015 by benanamen

[BuildMyWeb]

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?

[benanamen]

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 October 22, 2015 by benanamen

[BuildMyWeb]

@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.

[BuildMyWeb]

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? 

[benanamen]

@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 October 22, 2015 by benanamen

[BuildMyWeb]

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. 

[benanamen]

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 October 22, 2015 by benanamen

[Jacques1]

Let's not argue about terms here.

 

Sure, the term “session” might not have been ideal in this context, but I think we all understand the situation now: Users can start surveys, but every user should only have one survey at a time.

 

Yes, this can be solved with a UNIQUE constraint.

[BuildMyWeb]

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"
            )

[benanamen]

Edited October 23, 2015 by benanamen