how do i check/ validate form data against current database records?

[Povey]

Hi,

I am sure there is a simple solution to what i am asking. I don't know PHP so need a bit of help on this.

I am working on a project where users will buy a unique code from our website. When they buy the code it will be entered into a mysql database called 'thecode' and table called 'travelcode'.

I want to display a form on my website where they have to enter there first name, last name and their unique code into different fields, i have used the field plugin for this. I already have this set up.

I just need to know how to validate the first name, last name & unique code. ie. check that it actually exists in the database and if it does - display a success message, if it doesn't exist - display a error message.

Any help with this would be awesome, also examples would be greatly appreciated.

thanks in advance

 

[sales2010]

Hello,

You might try this code into PHP validation:

$firstname = '{tablename___firstname}';
$lastname = '{tablename___lastname}';
$code = '{tablename___code}';
$value = '{tablename___your_value}';
$db =JFactory::getDBO();
$query=("SELECT firstname, lastname, code FROM table where field = '$value'");
$db->setQuery($query);
$response = $db->loadObjectList();
if ($response->table_firstname == $firstname) {
return true;
} else if ($response->table_lastname == $lastname) {
return true;
} else if ($response->table_code == $code) {
return true;
} else {
return false;
}

 

[Povey]

Hello,

You might try this code into PHP validation:

Hi, thanks for the quick response.

Do i run this as a PHP form plugin? or as a validation for an element?

I tried doing this as a plugin on my form, i have a record in my table where firstname = Bailey lastname = Povey and uniquecode =12345 . I put this data into my form on the frontend of my site to test it/ see if it would check the input data against the data in the table. But it gives me a SyntaxError: JSON.parse: unexpected character.

Also what would be best to use for process script ? end of form submission?

Below is the code i used. adapted from your example, please let me know if i have done somethig wrong with the code.

I have changed the table name and one of the elements:
the table name is: codes
elements are: firstname, lastname, uniquecode

$firstname = '{codes___firstname}';
$lastname = '{codes___lastname}';
$uniquecode = '{codes___uniquecode}';
$value = '{codes___your_value}';
$db =JFactory::getDBO();
$query=("SELECT firstname, lastname, uniquecode FROM table where field = '$value'");
$db->setQuery($query);
$response = $db->loadObjectList();
if ($response->table_firstname == $firstname) {
return true;
} else if ($response->table_lastname == $lastname) {
return true;
} else if ($response->table_uniquecode == $uniquecode) {
return true;
} else {
return false;
}

 

[sales2010]

Hello,

Try to use the code as PHP validation, but splitted for each element. Btw, there is a problem in my previous code, forgot to add foreach. But for now, go to Element then Validation Tab, add php validation and paste the following code (of course, replace the values with your own):

$firstname = '{tablename___firstname}';
$value = '{tablename___your_value}';
$db =JFactory::getDBO();
$query=("SELECT firstname FROM table where field = '$value'");
$db->setQuery($query);
$response = $db->loadResult();
if ($response == $firstname) {
return true;
} else {
return false;
}

 

[Povey]

Thanks for this.

I tried this code for my element named firstname.

Note:
My table name is travelcodes
My elements are firstname, lastname and travelcode

$firstname = '{travelcodes___firstname}';
$value = '{travelcodes___firstname}';
$db =JFactory::getDBO();
$query=("SELECT firstname FROM table where field = '$value'");
$db->setQuery($query);
$response = $db->loadResult();
if ($response == $firstname) {
return true;
} else {
return false;
}

i went to the form on my website www.travelcode.co.nz and entered in my firstname etc into the form. It shows the error message even though there is actually a record in my table matching what i wrote into the form.

Have i done the code slightly wrong again?

note: the "travelcode" element being entered into the form will be different everytime.

also, do i need to put any code into the box labeled "condition"? and do i select match or replace?

Sorry, i am a complete beginner, i didnt realize i needed to know a bit of php to make these things work.

Thanks again.

Bailey

 

[Povey]

the site is a brand new joomla install, i am testing the form on the homepage to try and get it working.

I dont mind setting up a user and giving you the login details for my joomla admin, if you dont mind helping me further on this. It might make it easier with my limited knowledge.

Bailey

 

[cheesegrits]

You didn't replace your actual table and field names in the query. You need to "SELECT firstname FROM thecode WHERE code = '$value'".

Also, when setting up $value, it needs to be the code, not the first name.

Unless I'm misunderstanding what you are doing, but I think you are having the user enter their code, and verifying it against the code stored in 'thecode' table?

-- hugh

 

[Povey]

Hi, Sorry i'm still confused.

I think i have been a bit unclear on all this. So i will explain from fresh.

People will buy a unique code. These codes will be sent to a database table called travelcodes

I will have a fabrik form on my website, with the elements firstname, lastname and travelcode

I need the form to check the database table called travelcodes to make sure their firstname, lastname & travelcode exists in the table.

I understand the way to do this is to add a seperate PHP validation to each element on the form e.g. firstname, lastname & travelcode.

Once the firstname, lastname & travelcode has been validated/checked and found to exist in the table (found to be true), i would like to use table joins to submit the validated unique code to other database tables. how would i go about this?

Since i don't know any PHP i am just having trouble understanding exactly what i need to put in the code.

If anyone could give me the actual code i need - useing the tablename & elements above i would be very very grateful.

even just an exact example of the code needed to validate one of the element, them i can do the others.

Thanks so much. . . .


Bailey

 

[rob]

Hugh's points are correct. Say your form has the following fields:

myform___firstname
myform___lastname
myform___travelcode

In the code below ensure that you have replaced those with your form's element's actual full names

I think you only want one validation, on the travelcode field.

It would be a PHP validation with code:

// Get the form field's value:
$firstname = '{myform___firstname}';
$lastname = '{myform___lastname}';
$travelcode = '{myform___travelcode}';
 
// Get the db object
$db =JFactory::getDbo();
 
// Quote the values for security reasons:
$firstname = $db->quote($firstname);
$lastname = $db->quote($lastname);
$travelcode  = $db->quote($travelcode );
 
// Build the query
$query = $db->getQuery(true);

$query->select('COUNT(*)')
->from('travelcodes')
->where('firstname = ' . $firstname . ' AND  lastname = ' . $lastname  . ' AND travelcode =' . $travelcode);
$db->setQuery($query);
 
// Run the query
$found = $db->loadResult();

if ($found == 0) {
   return false;
} else {
   return true;
}

 

[rongame]

hi bailey

may i know if this solution worked for you? or is this a solved thread?

we have a some sort of similar problem and i cant get mine to work. posted june 4 ,2013.
http://fabrikar.com/forums/index.php?threads/help-on-validation-before-accepting-user-input.34368/
i tried the solution above and still wont work on my end.

thanks
rongame

 

[Povey]

Hi rongame,

I am also new to php coding, i also couldn't get the above to work. The validation kept getting errors even though i thought i had everything perfect. My project has gone on hold at the moment. I will let you know if do get this worked out. thanks.

Bailey

 

[rongame]

hi bailey

im not getting any errors on my end. that is on the console by inspecting it on chrome
but on my form, its not submitting. it keeps getting an error...

anyway you can check my thread and see if it will work for you since im not getting any errors on the console.

goodluck