Skip to main content
P
Prince Mike
Inspiring
December 6, 2017
Answered

Check if a value exist before posting

  • December 6, 2017
  • 2 replies
  • 2930 views

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

This topic has been closed for replies.
Correct answer Prince Mike

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>


Hello osgood_ and entire contributors,

First may I apologize for my no response all these days. We were running out of time in completing the assignment for the week so I decided to hold on the code since its not a priority at that time. Please accept my apologies.

I had before now, precisely 2 years ago, written a similar code for another project. So I located the code, edited it to capture the fields of the current project and tried it several times and it worked both offline and online.

Here is the screenshot of the page

And here is the correct code for anyone interested. You may need to edit it to fit your own project.

---------------------------------------------------------------------------------------------------------

<?php

$posted = false;

  if( $_POST ) {

    $posted = true;

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

{

mysql_connect('localhost','root','password');

mysql_select_db('dbname');

$incsrc = $_POST['incsrc'];

$taxidno = $_POST['taxidno'];

        $salary = $_POST['salary'];

        $gratuity = $_POST['gratuity'];

        $health = $_POST['health'];

$housing = $_POST['housing'];

        $assurance = $_POST['assurance'];

        $pension = $_POST['pension'];

        $allowances = $_POST['allowances'];

$bonus = $_POST['bonus'];

        $taxyr = $_POST['taxyr'];

        $username = $_POST['username'];

$query=mysql_query("select * from tbl_tableassmt where taxidno='".$taxidno."' AND taxyr='".$taxyr."'") or die(mysql_error());

$duplicate=mysql_fetch_row($query);

if($duplicate > 0)

    {

        $message = "Error: Dear user with TIN '.$taxidno.', you have already posted your assessment for the year '.$taxyr.'";

    }

}

    else

    {

   if($duplicate==0)

    {

      $query1=mysql_query("insert into tbl_tableassmt (incsrc, taxidno, salary, gratuity, health, housing, assurance, pension, allowances,  bonus, taxyr, username)

VALUES ('$incsrc', '$taxidno', '$salary', '$gratuity', '$health', '$housing', '$assurance', '$pension', '$allowances',  '$bonus', '$taxyr', '$username')") or die(mysql_error());

}

header("location: my_asssmnts");

}

}

?>

---------------------------------------------------------------------------------------------------------

Once more thank you so much

2 replies

O
osgood_
Legend
December 6, 2017

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;

P
Prince MikeAuthor
Inspiring
December 6, 2017

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.

P
Prince MikeAuthor
Inspiring
December 6, 2017

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.


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

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

$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

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'];

  }

B i r n o u
B i r n o u
Legend
December 6, 2017

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices