PHP + SQL - Select case when exists (...) - if 0 Insert row

Question

Using the SELECT CASE WHEN EXISTS () THEN CAST (1 AS BIT) etc query, then based on the result of that query requesting the insert, deletion, or no action. The code inserts and deletes as expected; the problem is it refuses to do nothing, adding a new row even if one already exists. Can someone suggest the reason why this is happening? The code below:

'java' is the name of a tick box. '$User' is the variable containing the $_SESSION["UserID"].

PHP:

if (isset($_POST['java'])){

                $sql = $con->query("SELECT CASE WHEN EXISTS 
                (SELECT * FROM userskills
                WHERE UserID = $User AND SkillID = 1)
                THEN CAST(1 AS BIT)
                ELSE CAST(0 AS BIT) END");

                if ($sql == "0"){

                $sql = $con->query("INSERT INTO userskills ( UserID, SkillID) VALUES  ($User, 1)");

            }} else{

                $sql = $con->query("SELECT CASE WHEN EXISTS 
                (SELECT * FROM userskills
                WHERE UserID = $User AND SkillID = 1)
                THEN CAST(1 AS BIT)
                ELSE CAST(0 AS BIT) END");

                if ($sql == "1"){

                $sql = $con->query("DELETE FROM userskills 
                WHERE UserID = $User AND SkillID = 1");

                }}

HTML:

<div class="RightBody">
    <form id="form1" name="form1" method="post" enctype="multipart/form-data">
      <div class="FormElement">
        <input name="FirstName" type="text" class="TField" id="FirstName" placeholder="First Name" value="<?php echo $_SESSION["FirstName"]; ?>">
      </div>
      <div class="FormElement">
        <input name="LastName" type="text" class="TField" id="LastName" placeholder="Last Name" value="<?php echo $_SESSION["LastName"]; ?>">
      </div>
      <div class="FormElement">
        <input name="Email" type="email" class="TField" id="Email" placeholder="Email Address" value="<?php echo $_SESSION["Email"]; ?>">
      </div>
      <div class="FormElement">
        <input name="JobRole" type="text" class="TField" id="JobRole" placeholder="Job Role" value="<?php echo $_SESSION["Role"]; ?>">
      </div>
      <div class="FormElement">
        <input name="Password" type="password" class="TField" id="Password" placeholder="Password" required="requried">
      </div>
      <div class="FormElement">
        <input type="file" name="file">
        <br>
        <br>
      </div>
      <p>
        <label>
          <input type="checkbox" name="java" value="checkbox" id="CheckboxGroup1_0">
          Java</label>
        <br>
        <label>
          <input type="checkbox" name="CheckboxGroup1" value="checkbox" id="CheckboxGroup1_1">
          Checkbox</label>
        <br>
        <label>
          <input type="checkbox" name="CheckboxGroup1" value="checkbox" id="CheckboxGroup1_2">
          Checkbox</label>
        <br>
      </p>
<div class="FormElement">
        <input name="Update" type="submit" class="button" id="Update" value="Submit Changes">
      </div>
    </form>
  </div>

Edit:

When I run this, I receive the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIT) ELSE CAST(0 AS BIT) END' at line 4

That's the problem, it doesn't change the outcome using =, ==, or === — user2258597, Jan 19 '16 at 18:58
either way `if ($sql = "1")` that assigns instead of comparing. — Funk Forty Niner, Jan 19 '16 at 18:59
So in other words `$sql = $con->query("SELECT CASE WHEN EXISTS` never fires, correct? — Funk Forty Niner, Jan 19 '16 at 19:10
*"'$user' is the variable containing the $_SESSION["UserID"]."* - Ohhhh ok. well `$user` and `$User` are 2 different animals here (if `$user` is a variable use elsewhere) and did you start the session? we also don't know what your html form looks like. — Funk Forty Niner, Jan 19 '16 at 19:12
It seems $sql always equals 0 as it will always insert when the tick box is ticked, and it will not remove the existing rows when the tick box is unticked. Suggesting your correct; the $sql = $con->query("SELECT CASE WHEN EXISTS isn't firing. — user2258597, Jan 19 '16 at 19:15
Add error reporting to the top of your file(s) right after your opening PHP tag for example ` — Funk Forty Niner, Jan 19 '16 at 19:18
The error -- "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIT) ELSE CAST(0 AS BIT) END' at line 4" — user2258597, Jan 19 '16 at 19:24
user2258597: Mind telling that guy below that his *now UNdeleted* answer is still wrong. What does he not get or expecting to do here. Overstep @JayBlanchard Jay's comments? — Funk Forty Niner, Jan 19 '16 at 19:36
The only other thing that comes to mind is that you might need an alias: `END AS foobar` — Jay Blanchard, Jan 19 '16 at 19:39
that `AS BIT` thing concerns me and being used as an alias. http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql/ *"MySQL has supported the BIT data type for a long time, but only as a synonym for TINYINT(1) until version 5.0.3. Once the column was created, MySQL no longer knew it had been created with BIT columns."* TBH, I don't know what else to do here. What are your column types? — Funk Forty Niner, Jan 19 '16 at 19:47
see this answer http://stackoverflow.com/a/6918959/ where they are using `SELECT cast(myIntThatIsZeroOrOne as bit(1)) AS myBit` casting it in `bit()`. your `AS BIT` is failing you so try using another alias also. — Funk Forty Niner, Jan 19 '16 at 19:54
My columns types are int(11). I will check the other answer and try it. Is there another way of determining if a row containing certain values already exists in a table and scrap the BIT query all-together? — user2258597, Jan 19 '16 at 21:41
you can ask his *now undeleted other answer* http://stackoverflow.com/a/34885660/1415724 which I flagged earlier as NOT an answer @user2258597 they deleted it then undeleted it. — Funk Forty Niner, Jan 19 '16 at 22:13

score 1 · Answer 1 · edited May 23 '17 at 10:27

1

In order to fix this problem I looked for other solutions to check if a row (WHERE...) exists, and report as true or false so I can go on to INSERT or DELETE or perform no action.

The following link contains an answer that enabled me to get my desired outcome working; Simple mysql Query to check if row exist

Thanks to all who tried to help answer my question. :)

edited May 23 '17 at 10:27

Community

1
1

answered Jan 19 '16 at 22:16

user2258597

113
1
10

Hey, that's great news. Glad to see you were able to find your solution, *cheers* – Funk Forty Niner Jan 19 '16 at 22:51
Want to know the strangest thing though? I was talking it over with Jay earlier and told him that you should be using `num_rows()` for it. I should have followed my instinct on this one. I'd of sent you to one of my answers here http://stackoverflow.com/a/22253579/1415724 - Oh well, problem solved and that's what counts. – Funk Forty Niner Jan 19 '16 at 22:53

PHP + SQL - Select case when exists (...) - if 0 Insert row

1 Answers1