22 Replies Latest reply on Dec 8, 2017 2:09 AM by osgood_

    Check if a value exist before posting

    Prince Mike Level 1

      Hello friends,

       

      Please could someone point me to my error in the code below.

       

      I want to post an assessment for the previous year (2016). However, before the assessment is posted, I want the system to check if I have posted an application for that year 2016 by checking my UserID (uid) and the year. If I have done that already, it should popup a message alerting me that I have already posted for the previous year.

       

      taxyr is the year field while DATE_SUB(CURDATE(), INTERVAL 1 YEAR) is used to calculate the previous year in MySQL. However, I am not getting any response whatsoever. The data is still posted even though the previous  year 2016 is existing.

       

      if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "add_assessment_frm")) {

       

         $taxyr =    strtolower($_POST['taxyr']);

         

         $query = sprintf("select taxyr from tbl_tableassmt where uid='".$uid."' AND taxyr = DATE_SUB(CURDATE(), INTERVAL 1 YEAR)",  mysql_real_escape_string($email));

         

         $result = mysql_query($query);

         

         $num_rows = mysql_num_rows($result);   

          

          if($num_rows > 0)

          {

              $message = "Error: You have already sent your assessment for the previous year.";

          }

          elseif($num_rows == 0)

      {

        $insertSQL = sprintf("INSERT INTO tbl_tableassmt (`uid`, incsrc, taxidno, salary, txxamt, gratuity, health, housing, assurance, pension, bizprft, allowances, obenefts, bonus, gaincome, conallowances, chgincome, taxyr, username) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

                             GetSQLValueString($_POST['uid'], "int"),

                             GetSQLValueString($_POST['incsrc'], "text"),

                             GetSQLValueString($_POST['taxidno'], "text"),

                             GetSQLValueString($_POST['salary'], "double"),

                             GetSQLValueString($_POST['txxamt'], "double"),

                             GetSQLValueString($_POST['gratuity'], "double"),

                             GetSQLValueString($_POST['username'], "text"));

       

        mysql_select_db($database_XXXXX_DB, $XXXXX_DB);

        $Result1 = mysql_query($insertSQL, $XXXXX_DB) or die(mysql_error());

       

        $insertGoTo = "upd_my_tax_assessment";

        if (isset($_SERVER['QUERY_STRING'])) {

          $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

          $insertGoTo .= $_SERVER['QUERY_STRING'];

        }

        header(sprintf("Location: %s", $insertGoTo));

      }

      }

       

      Thank you

        • 1. Re: Check if a value exist before posting
          B i r n o u Adobe Community Professional

          have you try using < sign insteal of equal... which will only look for date 1 yeare earlier, day for day...

           

            $query = sprintf("select taxyr from tbl_tableassmt where uid='".$uid."' AND taxyr < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)",  mysql_real_escape_string($email));

          • 2. Re: Check if a value exist before posting
            osgood_ Level 8

            https://forums.adobe.com/people/Prince+Mike  wrote

             

            Hello friends,

             

            Please could someone point me to my error in the code below.

             

            I want to post an assessment for the previous year (2016). However, before the assessment is posted, I want the system to check if I have posted an application for that year 2016 by checking my UserID (uid) and the year. If I have done that already, it should popup a message alerting me that I have already posted for the previous year.

             

            taxyr is the year field while DATE_SUB(CURDATE(), INTERVAL 1 YEAR) is used to calculate the previous year in MySQL. However, I am not getting any response whatsoever. The data is still posted even though the previous  year 2016 is existing.

             

            if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "add_assessment_frm")) {

             

               $taxyr =    strtolower($_POST['taxyr']);

               

               $query = sprintf("select taxyr from tbl_tableassmt where uid='".$uid."' AND taxyr = DATE_SUB(CURDATE(), INTERVAL 1 YEAR)",  mysql_real_escape_string($email));

               

               $result = mysql_query($query);

               

               $num_rows = mysql_num_rows($result);   

                

                if($num_rows > 0)

                {

                    $message = "Error: You have already sent your assessment for the previous year.";

                }

                elseif($num_rows == 0)

            {

              $insertSQL = sprintf("INSERT INTO tbl_tableassmt (`uid`, incsrc, taxidno, salary, txxamt, gratuity, health, housing, assurance, pension, bizprft, allowances, obenefts, bonus, gaincome, conallowances, chgincome, taxyr, username) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

                                   GetSQLValueString($_POST['uid'], "int"),

                                   GetSQLValueString($_POST['incsrc'], "text"),

                                   GetSQLValueString($_POST['taxidno'], "text"),

                                   GetSQLValueString($_POST['salary'], "double"),

                                   GetSQLValueString($_POST['txxamt'], "double"),

                                   GetSQLValueString($_POST['gratuity'], "double"),

                                   GetSQLValueString($_POST['username'], "text"));

             

              mysql_select_db($database_XXXXX_DB, $XXXXX_DB);

              $Result1 = mysql_query($insertSQL, $XXXXX_DB) or die(mysql_error());

             

              $insertGoTo = "upd_my_tax_assessment";

              if (isset($_SERVER['QUERY_STRING'])) {

                $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

                $insertGoTo .= $_SERVER['QUERY_STRING'];

              }

              header(sprintf("Location: %s", $insertGoTo));

            }

            }

             

            Thank you

             

            Are you saying the message doesn't pop up or are you saying the code you have so far doesnt work?

             

            At the moment you have only assigned the 'message' to a variable named $message if the number of results returned is more than 0:

             

            if($num_rows > 0)

                {

                    $message = "Error: You have already sent your assessment for the previous year.";

                }

             

             

             

            To actually see a message onscreen you need to echo the variable out:

             

             

            if($num_rows > 0)

                {

                    $message = "Error: You have already sent your assessment for the previous year.";

                    echo $message;

                }

             

             

             

            or if you are wanting to echo the message in a specific location on your page use:

             

            <?php

            if(isset($message)) {

            echo $message;

            }

            ?>

             

             

            or if you want a pop up 'alert' message

             

             

            <?php if(isset($message)) { ?>

            <script>

            alert('Error: You have already sent your assessment for the previous year.');

            </script>

            <?php } ?>

             

             

             

            First I would check if anything 'positive' is being returned from the query by echoing out $num_rows:

             

             

            $num_rows = mysql_num_rows($result);

             

            echo $num_rows;

            • 3. Re: Check if a value exist before posting
              Prince Mike Level 1

              Dear osgood_,

               

              Thank you for your response.

               

              Yes the code is not working in the sense that it not checking if I have already posted my assessment for the previous year even when I have posted it more than once which ought not be. It is supposed to check that before I submit.

               

              I also presumed that because the year checking code is not working, the message alert wouldn't work.

               

              Thanks for the message alert. I have noted your modifications and would use them as appropriate but the but the core of the work is to check if the previous year has been entered already. Please could you help more on this.

               

              Thank you in anticipation.

              • 4. Re: Check if a value exist before posting
                B i r n o u Adobe Community Professional

                so the < proposal doesn't work ? isn't it ?

                • 5. Re: Check if a value exist before posting
                  osgood_ Level 8

                  First do as Birnou suggests and replace = with < in your query, it might be as simple as that.

                   

                   

                  If that doesnt work then as a test hard code a date you know is in the database::::

                   

                  $taxyr = "2016-05-01';

                   

                  plus echo out  the $num_rows variable:

                   

                  $num_rows = mysql_num_rows($result);

                   

                  echo $num_rows;

                   

                  If you get 0 for the $num_rows variable then something is not right with your query, assuming the id and the date in the database is match.

                   

                  Also I presume you are getting the $uid variable from somewhere to use in the query as I cant see it in the code you posted?

                   

                  $uid

                  • 6. Re: Check if a value exist before posting
                    Prince Mike Level 1

                    Hi Birnou,

                     

                    "so the < proposal doesn't work ? isn't it ?" Yes its not working because the taxyr must be equal to the previous year.

                     

                    The taxyr (tax year) is 2016 and DATE_SUB(CURDATE(), INTERVAL 1 YEAR)  is also 2016.

                    .

                    • 7. Re: Check if a value exist before posting
                      Prince Mike Level 1

                      Hi osgood_

                       

                      OK I will do as you suggested and let you all know ASAP

                      • 8. Re: Check if a value exist before posting
                        osgood_ Level 8

                        https://forums.adobe.com/people/Prince+Mike  wrote

                         

                        Hi Birnou,

                         

                        "so the < proposal doesn't work ? isn't it ?" Yes its not working because the taxyr must be equal to the previous year.

                         

                        The taxyr (tax year) is 2016 and DATE_SUB(CURDATE(), INTERVAL 1 YEAR)  is also 2016.

                        .

                         

                        How you are checking the data slightly concerns me.

                         

                        In your database you have a column called 'taxyr' which takes the type of 'date' to check against?

                         

                        So if you posted an application on the 6th December 2016 and its stored in the database column 'taxyr' in this format: 2016-12-06 then the below query should return a positive result as the application was posted exactly a year ago today but why are you only checking an 'exact' date and not a date which is over a year old? You are only giving yourself a 24hr period to check, right?

                         

                         

                        $query = sprintf("select taxyr from tbl_tableassmt where uid='".$uid."' AND taxyr = DATE_SUB(CURDATE(), INTERVAL 1 YEAR)",  mysql_real_escape_string($email));

                         

                         

                         

                        Also where is the $taxyr variable getting its value from (a date picker?) and why are you using strtolower case? The format should be passed to the database in the date format: 2016-12-06 so it can be checked.

                        • 9. Re: Check if a value exist before posting
                          Prince Mike Level 1

                          Hi Osgood_ and all who contributed to my question,

                           

                          I post to say thank you. Your contributions helped me resolve my issue.

                           

                          To Osgood_, her are responses to your questions;

                           

                          Q. In your database you have a column called 'taxyr' which takes the type of 'date' to check against?

                           

                          Ans: Yes I have a field called ‘taxyr’ that obtains its values from a drop box with years populated by php year code from the previous year to other past years like this

                          <?php echo date("Y",strtotime("-1 year")); ?>,

                          <?php echo date("Y",strtotime("-2 year")); ?>,

                          <?php echo date("Y",strtotime("-3 year")); ?>,

                          etc

                          years_image.jpg

                          So depending on the year the person wants to pay for he will select from the drop down.

                           

                          So if you posted an application on the 6th December 2016 and its stored in the database column 'taxyr' in this format: 2016-12-06 then the below query should return a positive result as the application was posted exactly a year ago today but why are you only checking an 'exact' date and not a date which is over a year old? You are only giving yourself a 24hr period to check, right?

                           

                          ANS: No. It doesn’t store a full day but only the year like this 2016, 2015, 2015, etc.

                           

                          See attached images

                          taxyear.jpg

                           

                          $query = sprintf("select taxyr from tbl_tableassmt where uid='".$uid."' AND taxyr = DATE_SUB(CURDATE(), INTERVAL 1 YEAR)",  mysql_real_escape_string($email));

                           

                          Here is the correct query

                           

                          $query = sprintf("select * from tbl_taxpayerassmt where taxidno='".$taxidno."' AND taxyr='".$taxyr."'", mysql_real_escape_string($taxidno));

                           

                           

                          Q: Also where is the $taxyr variable getting its value from (a date picker?) and why are you using strtolower case? The format should be passed to the database in the date format: 2016-12-06 so it can be checked.

                           

                          Ans: As earlier said, the taxyr is getting it from pull down. Please see attached images

                          taxyear.jpg

                           

                          Here is the complete code for those with similar issue

                           

                          if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "add_assessment_frm")) {

                                    $taxyr =    strtolower($_POST['taxyr']);

                                    $taxidno =    $_POST['taxidno'];   

                             $query = sprintf("select * from tbl_tableassmt where taxidno='".$taxidno."' AND taxyr='".$taxyr."'", mysql_real_escape_string($taxidno));

                             $result = mysql_query($query);

                             $num_rows = mysql_num_rows($result);   

                                    echo $num_rows;

                                    if($num_rows > 0)

                              {

                                  $message = "Error: You have already sent your assessment for the previous year.";

                              }

                                   

                              elseif($num_rows == 0)

                                    {

                            $insertSQL = sprintf("INSERT INTO tbl_tableassmt (`uid`, incsrc, taxidno, salary, txxamt, gratuity, health, housing, assurance, pension, bizprft, allowances, obenefts, bonus, gaincome, conallowances, chgincome, taxyr, username) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

                          GetSQLValueString($_POST['uid'], "int"),

                          GetSQLValueString($_POST['incsrc'], "text"),

                          GetSQLValueString($_POST['taxidno'], "text"),

                          GetSQLValueString($_POST['salary'], "double"),

                            GetSQLValueString($_POST['txxamt'], "double"),

                          GetSQLValueString($_POST['gratuity'], "double"),

                          GetSQLValueString($_POST['health'], "double"),

                          GetSQLValueString($_POST['housing'], "double"),

                          GetSQLValueString($_POST['assurance'], "double"),

                          GetSQLValueString($_POST['pension'], "double"),

                          GetSQLValueString($_POST['bizprft'], "double"),

                          GetSQLValueString($_POST['allowances'], "double"),

                          GetSQLValueString($_POST['obenefts'], "double"),

                          GetSQLValueString($_POST['bonus'], "double"),

                          GetSQLValueString($_POST['gaincome'], "double"),

                          GetSQLValueString($_POST['conallowances'], "double"),

                          GetSQLValueString($_POST['chgincome'], "double"),

                          GetSQLValueString($_POST['taxyr'], "date"),

                          GetSQLValueString($_POST['username'], "text"));

                           

                            mysql_select_db($database_CRIRS_DB, $CRIRS_DB);

                            $Result1 = mysql_query($insertSQL, $CRIRS_DB) or die(mysql_error());

                           

                            $insertGoTo = "upd_my_tax_assessment";

                            if (isset($_SERVER['QUERY_STRING'])) {

                              $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

                              $insertGoTo .= $_SERVER['QUERY_STRING'];

                            }

                          • 10. Re: Check if a value exist before posting
                            osgood_ Level 8

                            https://forums.adobe.com/people/Prince+Mike  wrote

                             

                             

                            Here is the correct query

                             

                            $query = sprintf("select * from tbl_taxpayerassmt where taxidno='".$taxidno."' AND taxyr='".$taxyr."'", mysql_real_escape_string($taxidno));

                             

                             

                             

                            That is why I was a bit concerned about how you were checking the data because you posted the taxyr in the forum as 2016 and NOT in a correct date format like 2016-12-07. Your previous query using DATE_SUB did not work because it will only work if the information is in a date format. Mysql doesnt know that 2016, 2017 are dates/years, it just treats them as numbers.

                             

                            Glad you now have it working the way you need it to..

                            • 11. Re: Check if a value exist before posting
                              Prince Mike Level 1

                              Hello Friends,

                               

                              Please I still need your help in this codes. I stated earlier that the issues has been resolved.

                               

                              The code is running perfectly well offline on my localhost, I mean here my PC (see inserted image) but when I run it online, it doesn't work. It continues to posted assessments even when an assessment has been posted for the same year.

                              inserterror.jpg

                               

                              I even used fixed valued like $query = sprintf("select * from tbl_taxpayerassmt where taxidno='1007360900-0001' AND taxyr='2016'",  mysql_real_escape_string($taxyr));, yet the error continues.

                               

                              Please your help is appreciated

                               

                              Thank you

                              • 12. Re: Check if a value exist before posting
                                osgood_ Level 8

                                  That is because you are saying in your code below 'if a record is found' then set the Error message:

                                 

                                       if($num_rows > 0)

                                    {

                                        $message = "Error: You have already sent your assessment for the previous year.";

                                    }

                                 

                                You dont want that to be set IF a result is found, right?

                                • 13. Re: Check if a value exist before posting
                                  Prince Mike Level 1

                                  Not really. Whatever will check and inform the user that he/she has posted assessment for the previous year (2016) or years (2015, 2014, 2013), that will be great.

                                   

                                  But what's is the difference between 'RECORD" and  "RESULT"?? Whether RECORD or RESULT, does that really matter?...

                                   

                                  Please I am bit confused here....

                                   

                                  Osgood, can you check from your end if its the same

                                  • 14. Re: Check if a value exist before posting
                                    osgood_ Level 8

                                    record/result same thing.

                                     

                                    I may have been reading this wrong. Your code is doing as you ask it to do locally but not when online, thats a bit more tricky to resolve because normally if you test something locally it should work when you remotely run it.

                                     

                                    I dont really use the old mysql extension any longer or the DW produced stuff.

                                     

                                    Have you any other servers available to test this on? When something like this happens I usually test it out on another server to determine whether its my code that is wonky or the server set up that is wonky.

                                     

                                     

                                     

                                    • 15. Re: Check if a value exist before posting
                                      B i r n o u Adobe Community Professional

                                      just a few ideas from the top of my head

                                      • are online and local databases encoded in the same way (fields, structures and Interclassement)
                                      • does online database newer (mariadb for example) and encoding the date differently
                                      • is the piping used in utf-8 in case mysql_query ("SET NAMES 'utf8'");

                                      ???

                                      • 16. Re: Check if a value exist before posting
                                        osgood_ Level 8

                                        Just to add further I have tested the code out locally this end and it works as it should do, so I suspect its something to do with your remote server set up.

                                         

                                        This is why its best to have a least another server, if not more, to test on because if it works on that one/those then you can pretty much be sure that the one it doesnt work on is wonky and believe me there are a lot of hosts out their that are clueless when it comes to setting up servers, they wait for you to tell them their server is not performing as desired.

                                        • 17. Re: Check if a value exist before posting
                                          Prince Mike Level 1

                                          Hi osgood_,

                                           

                                          Thank you so much.

                                           

                                          I will revise my code once more and see how it goes.

                                           

                                          Its really funny how the system functions well on local server and fails online.

                                           

                                          I will keep you posted.

                                          • 18. Re: Check if a value exist before posting
                                            Prince Mike Level 1

                                            Hi Birnou,

                                             

                                            Thank you for your thoughts.

                                             

                                            Based on your questions

                                             

                                            Are online and local databases encoded in the same way (fields, structures and Interclassement)? Yes they are. The two database are of the same. I uploaded the database from my WAMP server to my online server.

                                             

                                            Does online database newer (mariadb for example) and encoding the date differently. No they are the same, MYSQL to be precise

                                             

                                            Is the piping used in utf-8 in case mysql_query ("SET NAMES 'utf8'"); Yes

                                            • 19. Re: Check if a value exist before posting
                                              B i r n o u Adobe Community Professional

                                              on the first point, you say that the two db are the sames , but precisely, on mysql did you create a db then import the datas ? did you run a sql that creat the all things... are the build of ysql server the same ?

                                               

                                              I say that because often on web hotel the db are all ready created and on only import the data (table and records)... I had trouble on some project just because of it

                                              • 20. Re: Check if a value exist before posting
                                                Prince Mike Level 1

                                                Yes Birnou, I created the db on the online webs server with no tables and then imported all that I had in my local web server into the db I created on the online server. I had already developed the system offline with the DB already existing with data. I didnt run a separate sql to create anything.

                                                 

                                                Except otherwise, the only difference I know for now could be in the version of the MySQL in my hosting server and my local server.

                                                • 21. Re: Check if a value exist before posting
                                                  B i r n o u Adobe Community Professional

                                                  sometimes the auto created from remote db are latin-swedish myisam... and xampp build utf-8 innodb by default...

                                                  • 22. Re: Check if a value exist before posting
                                                    osgood_ Level 8

                                                    If you're still having difficulties then try a test with some mysqli and form code below. Copy and paste into a new DW file. I've placed 2 hidden fields in the form which passes information for txidno and username. If there is no record currently in the database for taxidno - 1007360900-0001 and taxyr - 2016 those 2 details txidno and username will be added to the database. If a record exists an error message will be shown........

                                                     

                                                    // Connect to database

                                                    // Change connection details to those of your own

                                                    $conn = new mysqli('server' , 'username' , 'password' , 'database_name');

                                                    ?>

                                                    <?php

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

                                                    // get info taxyr and taxidno

                                                    $taxyr =  $conn->real_escape_string($_POST['taxyr']);

                                                    $taxidno = $conn->real_escape_string( $_POST['taxidno']);

                                                    // check database to see in an entry exists taxyr and taxidno

                                                    $checkDetails = $conn->query("SELECT * from tbl_tableassmt where taxidno='".$taxidno."' AND taxyr='".$taxyr."'");

                                                    //get number of results

                                                    $num_rows = $checkDetails->num_rows;

                                                    echo $num_rows;

                                                    // if a result is found return an error message

                                                    if($num_rows > 0) {

                                                    $message = "Error: You have already sent your assessment for the previous year.";

                                                    echo "<script>

                                                    alert('$message')

                                                    </script>";

                                                    }

                                                    else {

                                                    // if no result found proceed and enter information into database

                                                    $txidno =  $conn->real_escape_string($_POST['txidno']);

                                                    $username = $conn->real_escape_string( $_POST['username']);

                                                    $conn->query("INSERT INTO tbl_tableassmt

                                                    (taxidno, username) VALUES ('$txidno', '$username')");

                                                    $message = "Record added successfully";

                                                    echo "<script>

                                                    alert('$message')

                                                    </script>";

                                                    }

                                                    }

                                                    ?>

                                                     

                                                     

                                                     

                                                     

                                                    <form name="tax_assessment_text" method="post" action="">

                                                    <p>

                                                    <label for="taxyr">Tax Year</label><br>

                                                    <input type="text" name="taxyr" value="2016">

                                                    </p>

                                                    <p>

                                                    <label for="taxidno">Tax ID Number</label><br>

                                                    <input type="text" name="taxidno" value="1007360900-0001">

                                                    </p>

                                                     

                                                    <input type="hidden" name="txidno" value="12345">

                                                    <input type="hidden" name="username" value="Mickey Mouse">

                                                     

                                                     

                                                    <input type="submit" name="submit" value="SUBMIT">

                                                     

                                                    </form>