Need assistance with custom verification script

@Boris 

Thank you.

Here is the link for the Pastebin. I have not used this app before so I hope it was worth the effort.

It is not a lot of code but I included an image of the tables required. The real login data is blanked out.

http://pastebin.com/r6K4cYVh

Robert

Hi Robert,

Unfortunately, we are unable to view the script on that URL. It says the Pastebin ID is private and it requires a login. Would you mind sharing it again? I believe it requires public permission so we can view it from here.

We'll await your reply. Thanks!

Hi, 

Sorry, it is now public.

TNX

Hi Robert,

 

If I understand your concern correctly, you want to check the USERS.ISAPPROVED value of a given $memberID. If this is the case, you can try this MySQL code to get the value of the USERS.ISAPPROVED from a given $memberID.

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

$memberData = $db->select('SELECT MP.MembershipID, P.LastName, U.IsApproved                 
                            FROM membershipperson MP
                            INNER JOIN person P
                                P.PersoinID = MP.PersonID
                            INNER JOIN users U
                                U.MembershipID = MP.MembershipID
                            WHERE MP.MembershipID = '. $memberID);

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

Do let us know if you need further assistance.

@AA_C

Or you can try the following in medoo format.

$memberData = $db->select('membershipperson',[

        '[>]person' => ['PersonID' => 'PersonID'],

        '[>]users' => 'MembershipID'               

    ],

    [

        'membershipperson.MembershipID',

        'membershipperson.PersonID',

        'users.IsApproved',

        'person.LastName'  

    ],

    [

        'AND' => [

            'membershipperson.MembershipID' => $memberID,                    

            //'IsApproved' => "1" //this filter the result only if approved (1)

        ]

    ]);

This should give you output like the following in json format.

[{"MembershipID":"65","PersonID":"300","IsApproved":"0","LastName":"Smith"}]

If you need further assistance, let us know.

Thanks!

Just FYI, I believe there's a typo on "PersoinID" column under person table in your sample database. Thanks :)

@ Chriistian 

Thank you, I think I will go with the medoo format as that is the method I have now.

@ EltonCris

Thank for the script.

I do notice one thing that I 'm not sure about.

In the previous script we used;

$memberID = isset( $_POST['memberid']) ? $_POST['memberid'] : '';

I  believe we still keep this above your script, yes / no ?

TNX Robert.

PS... Yes it is a typo, Thanks for noticing. 

Thank you for the update. Yes that line should still remain in the beginning of the script.

If you have any other question or issue, please do not hesitate contacting us. We will be glad to help.

I tried this script and nothing happens when I click on my Verify button. I have a test page:

http://www.aac.ca/en/forms/dogreg/index-1.html

I created two new test files for it: verify.js and verify.php.

I changed the js code to use verify.php

FYI

If you use Last Name Cooper and membership number 32 it should indicate Valid Membership number

A different Last Name or number it should indicate Invalid Membership number

Here is the complete php script.

<?

require_once "medoo.php";

$db = new medoo(array(

//  *connection script here* //

));

$memberID = isset($_POST['memberid']) ? $_POST['memberid'] : '';

$memberData = $db->select('membershipperson',[

        '[>]person' => ['PersonID' => 'PersonID'],

        '[>]users' => 'MembershipID'          

    ],

    [

        'membershipperson.MembershipID',

        'membershipperson.PersonID',

        'users.IsApproved',

        'person.LastName'  

    ],

    [

        'AND' => [

            'membershipperson.MembershipID' => $memberID,

            //'IsApproved' => "1" //this filters the result only if approved (1)

        ]

    ]); 

$resp = json_encode($memberData);

print_r($resp);  

 ?>

Sorry for this inconvenience. Unfortunately, since we do not have complete access to your page and code. It is really hard to analyze what is wrong. The best suggestion at the moment would be to get in contact with one of your developers and try to trouble shoot your code.

We can only give you possible suggestion. But with out testing it, we have no way of knowing it will work.

Please do not apologize. I was so very pleased that you gave it any thought.

I'm sure the script is so very close or I messed up somewhere so I will try and have a closer look.

Again you guys went beyond the call of duty to support a customer.

Thank you all.

Robert

Thanks and no worries :)

Don't you worry, we'll check this out during our spare time. We could not promise you anything yet, but we'll surely let you know once we come up with a solution.

I believe the medoo php works perfectly since I tried those code, once it catches the valid memberID, it'll fetch the relevant data from the database. We'll see if there's a need to correct the javascript code.

Thank you

@AA_C 

You can download the complete source code here https://shots.jotform.com/elton/verify.zip

The zip contains the following files.

verify.js

verify.php

index.html (your form)

medoo.php

FYI, make sure to place the verify.js below the form source code if you are going to use your form source code file OR you can use my form version (index.html) which I have modified a bit that would display the members info once the membership ID is found and verified.

Example:

Let us know if you have further questions or if you come across any issues.

Cheers!

By the way, make sure to update your database credentials on verify.php. :)

@Elton

Received the zip file OK.... 

I was looking at the java script and I don't see where you collect the last name.

In a previous script we looked at the last name field for this.

//alert(memberid);

    var _memberid = jQuery('#input_26').val();

   var _lastname = jQuery('#last_3').val(); (This is what I wanted added to be verified)   

 I do see further down in the script where the Last Name is used in a query, so perhaps that is how you do it by comparing the given member number to the Last Name in the database. If they match then OK if no match then FAIL so the members Last Name field (#last_3) is not required using this method.

Perhaps I should have look further before making the statement above.

 

One other thing I noticed in the verify.php, previous script one more line at the very end which is likely not required but I would verification on it 

$resp = json_encode($memberData);

print_r($resp);

 

@Elton

On line 58 of the Java Script you have this script: jQuery("#memberinfo").html("");

I see this is showing the members information.  We really don't need all that there.

Members never use the PersonID number and don't even know it exists.

 

We had a function here that displayed _approvalMessage for 5 sec. 

//remove the approval message after 5 seconds

   (function (el) {

        setTimeout(function () {

            el.children().remove('h3');

        }, 5000);

    }(jQuery('#varify').append('

' + _approvalMessage + '

')))

 

I had created a div between id_37 & id_35, in the index.html to display the messages.

 

 

Thanks,

Robert

Thanks for your kind remarks, Robert. We greatly appreciate it. 

1. I only copied the scripts from here http://www.aac.ca/en/forms/dogreg/index-1.html.  Unfortunately, I can't find the following codes there.

var _lastname = jQuery('#last_3').val(); (This is what I wanted added to be verified)   

Perhaps I am looking on the older version? Anyways, we can append this script if you want to. :)

2. The verification method I used is base on the memberid only since this is what I see on your example.

3. Yes, the following is not required.

$resp = json_encode($memberData);

print_r($resp);

I simply replace it with the following, they both have the same output. 

echo json_encode($data);

4. Yes, you can omit that code jQuery("#memberinfo").html(""); as well as this jQuery("#memberinfo").html("<br/>Last Name: " + data[0].LastName + "<br/> Member ID: " + data[0].MembershipID + "<br/> Personal ID: " + data[0].PersonID); 

This will only show the members info in the form.

5. This doesn't seem to be on the above URL but you can stay on which one you prefer.

   (function (el) {

        setTimeout(function () {

            el.children().remove('h3');

        }, 5000);

    }(jQuery('#varify').append('

' + _approvalMessage + '

')))

May we know if you got it working so far? The script I sent you should verify the entered memberID only if it's approved (1) or not (0). Demo: http://tonix.xyz/verify/ you can enter 2573 memberID. (Disregard the member info shown once verified - added it for demo purposes). :)

If there's anything else you want me to check or if you need assistance with the script, let me know. We'd be happy to help.

Regards!

@Elton

I tried your form and it works fine.

When I try your files on my site it appears it keeps indicating number is not valid. 

I'm using 8888 and I know it is a good number, it is for a test account. Here is the test ip:

 http://www.aac.ca/en/forms/DogIDform/index.html

I have altered the verify.php for my db. 

One reason I prefered the other method is this gives away member info.

For example if I were to guess a number and it were valid you would then display the members Name and PID to go along with the guess.  The other way only right or wrong appears, if name and number are right, you get a Valid message, so you need both.

One other thing when I didn't see the link to the verify.js I placed at the top of the index.html. Then I found it at the bottom so i deleted it from there.

PS The index-1.html was for testing purposes and is not the live one.

Thank you,

Robert

Thanks for your reply, Robert.

I understand now as to why the last name is an important part of the verification so I have added it on the verify.js script. You can get it from here http://pastebin.com/raw/RGMqkK11. I have also removed the script where it displays the members info once the verification is successful.

Highlighted on the image below are the ones changed. So basically, this verifies the entered member ID and the Last Name from now on. So when the entered ID and the Last Name matches on the database, it will return a successful message shown on the console log.

See the updated demo: http://tonix.xyz/verify/ (You can use the same data from your database, e.g. Member ID: 2573 and Last Name: Jones)

So once verified, the console log should display true return as defined in your script.

---

By the way, your demo is showing an error in the console log once the verify button is clicked, I think it's something to do on the verify.php file. Kindly review. :)

Please update us with the result.

Regards! 

Fixing

@Elton

Current Java Script

ERROR

It seems that your last entry did not get posted correctly. Can you please re-post so we may analyze the notes that you have of the scripts.

Thank you for your patients.

Hi, Disregard that message please. Thank you.

@Elton

See the updated demo: http://tonix.xyz/verify/ (You can use the same data from your database, e.g. Member ID: 2573 and Last Name: Jones)

I did finally get this this link o work for me. My browser must have not reset properly. 

I will try this and let you know. 

Sorry about the delay.

Thank you

Robert

 

Glad to hear the good news. On behalf of my colleague, Elton, you are very welcome. If you have any additional question or issue, please do not hesitate contacting us. We will be glad to assist.

It's good to know that it is working on your end now. :)

I have also made a small update on the demo. I added your approval message script that shows once a verified member is found. Feel free to check the verify.js in the source code here http://tonix.xyz/verify/

(function (el) {

        setTimeout(function () {

            el.children().remove('h3');

        }, 5000);

    }(jQuery('#varify').append('

' + _approvalMessage + '

')))

@Elton

Thank you for your help.

I can get your demo to work perfectly but if I copy the script to my index.html and the javascript, it keeps indicating Invalid Membership number, when I know it's not.

What I need is a copy of the verify.php you are using to see how it compares with what I'm using, that could possibly be the issue.

There is one piece of the php code that has been commented out that has me asking why. In the original script we not only checked their number was valid but that their membership "IsApproved" value = 1, which means they are current member in good standing. We need to continue to do this.

[

        'AND' => [

            'membershipperson.MembershipID' => $memberID,                    

            //'IsApproved' => "1" //we  need to continue to verify that they are approved?

        ]

  Robert

I have forwarded this to my colleague. He should be able to check this further for you.

I notified my colleague, Elton, again about this. We should hear from him directly if he founds any issue on the script.

@Elton

Just to be sure that we are looking at the right tables correctly I did this query and I did get the FirstName I should. I used real numbers for the query in my db so I could be sure it worked.

I'm showing the numbers used in Elton's demo here. 

So in effect if we submit 2573  as a membershipID and Last Name: Jones and check that IsApproved =1 we would get a FirstName or in our case a Valid membership. 

 

SELECT `person`.`FirstName` 
FROM person, membershipperson, users
WHERE (

)
LIMIT 0 , 1

I hope this helps as I am thinking the query in the verify.php is not exactly what we need.

Thank you

Robert

Hi,

 

I have notified my colleague Elton about this. He will be able to check your code further.

Regards.

@AA_C

Apologies for the delayed response.

I think I haven't made any changes on the verify.php but anyway you can take it here. This has all the updated files. https://shots.jotform.com/elton/verify_2.zip

To explain the following code:

        'AND' => [

            'membershipperson.MembershipID' => $memberID,                    

            //'IsApproved' => "1" //we  need to continue to verify that they are approved?

]

It is actually not necessary to enable "IsApprove" check on PHP since this is on the verify.j, s in other words, enabling this would be redundant but you can enable it anyway if you want. This will give you a full proof user verification. So when you enable this, the member will be verified on the backend side (verify.php) and verified again on the front-end (verify.js).

This is the line of code in verify.js file that checks the result from verify.php. So when there's a value from verify.php, it is filtered for the second time along this line.

---

About the FirstName, I haven't seen any lines in your code that has FirstName. I'm not quite sure if I am following you correctly? Looking from the database table you've sent previously, there was no FirstName column in any of the tables so I am confused.

Overall, here's a short summary of what the script does. So basically, it was not working on the first hand which I have revised so it would verify the entered ID and the last name which I believe is performing accordingly.

1. The user enters his information (last name) and (member ID) on the form, then click on Verify Membership button.

2. The member ID will be captured and passed on verify.php to fetch the matching data from the db table.

3. If member ID isn't found, it will display "Invalid Membership number" message on the form.

4. However, if found and is approved (IsApproved = 1), the verify.php will output a json data of the user. After that, the verify.js will fetch the json data and check it again if it's approved and that the last name entered on the form matches from the database data. Once completed, it will display a "Valid Membership number" message on the form.

Please let me know in which part of the flow that needs to be modified or what changes you want so we can assist you further. 

Thanks and again my apologies for the delayed response.

Regards! :)

@Elton

Please do not apologize. This is a learning exercise for me and at 69 yrs old a bit of a chore. Memory an't what it ustobe.

My example sql code just illustrated a query I made to show flow. I used first name as a result of the query it just shows that if you use the criteria, lastname and memberid and isAproved to validate a member you will get the result you want. In my case I wanted a Firstname, for our exercise I want to trigger a message. The FirstName part is just for my code and is not required for our verification. 

As to the isApproved part, I was sure you had taken that into consideration I just wasn't sure just how you did it. Thank you for the explanation it is clearer now. 

Although the coding appears to do the right thing I cannot get a Valid membership message for any valid member. All show In valid number . On your demo it works perfectly on my site it fails.

That is why I wanted you to review the sql script I created to view the flow. If you go to my test page and try using Cooper for last name and 32 for number you will see what I mean. It should be a valid Member.

http://www.aac.ca/en/forms/DogIDform/index.html

The only part of the code I changed was the connection script, everything else is as you made it.

The script works on your demo but not on my server???

You have no idea how much I appreciate your help in this puzzle. Thank you again.

Have a GREAT DAY my friends at JF.

Robert

Thanks Robert.

In your verify.php, there was a syntax error on line 16. That's what I see when I load your verify.php file. Can you please review this line?

Screenshot:

By the way, what's your PHP version? Please note that PHP 5.3 and its earlier version does not support square brackets [] as a shortcut for arrays. This might be reason why it is throwing and error on your side. Is it possible that you can upgrade your PHP to its latest version or at least to 5.4? This should resolve the problem.

Thank you! 

Also, PHP 5.4+ is greatly recommended by medoo since all of its new codes are using square brackets []

Let us know how it goes. Thanks!

AH!... You nailed it!   php version... yes ... NO SQ BRACKETS ALLOWED, I had forgotten about that, old-man syndrome.

PHP Version 5.3.10 in use on our server.

I cannot upgrade the version I have no control over that although I can point it out to the folks who do, which I might add would be a waste of time. Unless they have a big outcry from there users nothing will change.

I went through that problem before. Sammy pointed it out to me.

line 16 is as follows:

$data = $db->select('membershipperson',[  (square bracket)

This link will tell you all you need to know about the server.

https://www.aac.ca/phpinfo.php

It is to bad that I never thought of that sooner. I had to change some code because of it before.

Sorry about this oversight.

Robert

I have notified Elton of your reply. Regards.

That's great, thanks for that information, Robert. 

Unfortunately, my test server is running on PHP 5.6 version. I don't have a test environment with that version, I suggest to do a trial and error with the following PHP script which I have modified a bit (instead of using shortcuts for arrays [], I replaced it with array()) so hopefully it will work on 5.3. This works fine on 5.6 too.

Try this: http://pastebin.com/raw/3ie4eWxM (Make sure to replace the database credentials)

What worries me though is the new medoo syntax for JOIN command which uses square brackets. Hopefully, this will not trigger an error.

Let us know how this goes. :)

@Elton

OK... I tried that new improved php and it is still indicating "Invalid Member"

http://www.aac.ca/en/forms/DogIDform/index.html

In the form below, medoo.php is used also and it works fine. If you enter for ex. 32 it will indicate VALID, if you enter 33 it will indicate INVALID . The only difference being the last name is not validated.

http://www.aac.ca/en/forms/dogreg/index.html

 

I think you must be very close to a solution. Trial and error, .... let's go!

Thanks again,

Robert

 

@Elton

Here is a link to the older verify.php used above. http://pastebin.com/k2qfw7Zf

Robert

@Elton

One other thing, I have submitted a ticket to Primus to upgrade php version. I did that last night and haven't heard back yet. Not sure if a late response is a good sign.

One other thing that is unrelated to this issue. Every time I try to edit an entry here it takes me back to the public Forum.

Robert

@Robert,

I will check this out later today.

We are just too busy this week. Thanks for your understanding. :)

Hello Robert,

I have downgraded my PHP version to 5.3.1 which I think the same version as yours and the validation is still working fine.

I haven't made any changes on the verify.php script. I used the same script I shared to you last January 13.

Looking at your example http://www.aac.ca/en/forms/DogIDform/index.html, this one is returning a "false" text on its verify.php which simply indicates that there is something that isn't working properly. It should suppose to return a square brackets as its default value (result when json_encode is empty).

While this thread is getting lengthy, if possible or if you're okay with this, please share to us a login with your FTP and MySQL so I can check these things for your convenience. If you don't want to share it here, you can share it to support@jotform.com with a subject "Custom Verification Script" so I can easily track it from email.

Thank you!

Email sent... I would prefer a privet email address though.

I have just been informed that there will be no php upgrade on the server.

RESOLVED …

 

Thank you to all the JF staff in particular Sammy and Elton for going that extra Km to help get it done.

 

You guys are great.

 

Best Regards

Robert

Agility Association of Canada

 

VE3TKC

We're glad that the problem has now been resolved, Robert. In behalf of my colleagues that helped you, you are most welcome.

Should you need any assistance again, please feel free to visit us here in the forum.

Cheers!