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

Check if a value exist before posting

  • December 6, 2017
  • 2 replies
  • 2919 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.

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

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

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