Validate existing user against custom database server side

HI,
I have searched the forums with no solution, have come close, but editing the syntax has not worked. The closest I came to a solution is: http://www.chronoengine.com/forums.html?cont=posts&f=7&t=12958#p25673

The goal: To get a single input box to validate against a table of values (server side) before submitting.
Details:
I have a table of membership numbers in the following table(jos_chrono_contact_num)

I have a form with the input name = member_number and a submit button

I need the user to input their member number, hit submit, if invalid, the user gets a message, otherwise the form submits.

I envision the flow as: the user types the correct number, hits submit, the $member_number is checked against the column member_number from the table jos_chrono_contact_num, and if they match, the form submits.

The following is what I have tried to put in the Server Side validation portion under the 'validation' tab without success:

<?php
$db =& JFactory::getDBO();
$text =& JRequest::getString('member_number');
$sql = "
    SELECT COUNT(*)
        FROM 'jos_chrono_contact_num'
        WHERE 'member_number' = ".$db->quote($text).";";
$db->setQuery( $sql );
   if ($text != $sql )
     {echo "incorrect member number";}
   else
     {}
?>

Any help would be greatly appreciated.

Hi cjcrun,

You are missing a line:

$db->setQuery( $sql );
$count = $db->getResult(); // <-- add this line
    if ( !$count ) // <-- edit this line

I think that should get it working. I've also replaced $sql in the last two lines (but not the first) with $count.

Bob

Thanks Greyhead, your help is always greatly appreciated.

I tried the solution you suggested:

but got:
Fatal error: Call to undefined method JDatabaseMySQL::getResult() in C:\wamp\www\components\com_chronocontact\libraries\chronoform.php(258) : eval()'d code on line 9

So after searching through, found joomla would prefer a loadResult()

<?php
$db =& JFactory::getDBO();
$text =& JRequest::getString('member_number');
$sql = "
    SELECT COUNT(*)
        FROM 'jos_chrono_contact_num'
        WHERE 'member_number' = ".$db->quote($text).";";
$db->setQuery( $sql );
 $count = $db->loadResult();
  if (!$count)
     {echo "incorrect member number";}
   else
     {}
?>

If I print the variables, it appears as though the sql is doing nothing...

"count: ,
sqL: SELECT * FROM 'jos_chrono_contact_num' WHERE 'member_number' = '456';,
text: 456 "

The issue with this is it doesn't seem to check against the dbase. Regardless of any values put in to the $member_number input, it gives access to the submit url.

If anyone knows where I may be going wrong, I'd appreciate your help.

Hi cjrun,

Double Oops, apologies for my mistake and I didn't spot that you have some single quotes in here where you need backticks (or possibly nothing at all):

        FROM `jos_chrono_contact_num`
        WHERE `member_number` = ".$db->quote($text).";";

The MySQL quoting rules are - in short - table & column names need backticks `` or nothing at all if they have no spaces or special characters.

Bob

Thanks again mate, that was definately one of the issues.

Although that wasn't the only one. After slogging my way through more forums (my $result was returning a ResourceID#127) especially regarding the SQL portion, I have finally built a successful script and it looks like this:

<?php
$db =& JFactory::getDBO();                            //connect to database
$text =& JRequest::getString('member_number');        //get the entered member number
$sql = "
    SELECT column_name                               //select table column
        FROM #_table                                //from table
        WHERE column_name = '$text'";                 //compare the entered member no with the database
$result=mysql_query($sql);                             //set the result
if (mysql_num_rows($result) >= '1'){                    
while ($row = mysql_fetch_row($result)){
$try = $row[0];
}}                                        //convert the result into a variable                                                   
 
  if (!$try)                  //if the variable $try (result) is empty
     {echo "incorrect member number";}
   else
     {}//submit form
?>

This code is for validating a single entry field against a column of values in a database.
For the clean, complete code, use the following:

<?php
$db =& JFactory::getDBO();                           
$text =& JRequest::getString('input_field_name');       
$sql = "
    SELECT your_column_name                               
        FROM your_database_table                   
        WHERE your_column_name = '$text'";                
$result=mysql_query($sql);                             
if (mysql_num_rows($result) >= '1'){                    
while ($row = mysql_fetch_row($result)){
$try = $row[0];
  }
}                                       
if (!$try)                
     {echo "incorrect member number";}
   else
     {}
?>

EDIT:Since adding the code I have realised a couple of things.
1. the variable in the SQL statement should be in '$text' if you don't want errors getting thrown if there is text and numbers. (test without to see results, enter text only, or numbers only)
2. I had this form redirecting to another URL when numbers were successfully matching. I tried this through Chronoforms redirect and submit urls, but I was redirected regardless of validation. I achieved the redirect by adding the code in the last else statement and keeping submit and redirect urls clear:

echo "<meta http-equiv='refresh' content='0;url=http://www.your_redirect.com/'>";

Thanks again Greyhead for all your help, couldn't have done it without you.

Hi cjcrun,

Looks good, I've taken the liberty of re-writing your code for other readers in a slightly more Joomla! format.

<?php
$db =& JFactory::getDBO();                           
$text =& JRequest::getString('input_field_name', '', 'post');       
$sql = "
    SELECT COUNT(`your_column_name`)                               
        FROM `your_database_table`                   
        WHERE `your_column_name` = ".$db->Quote($text);
";
$db->setQuery($sql);         
$result = $db->loadResult();     
if ( !$result ) {
  return "Incorrect member number";
}
?>

This code goes in the Sever-side validation box.

Bob

I would very much like to know how to set up a validation with two variables in the table!
Hope somebpdy can help me with this pls. pls.

In the mean time solved!

I've got the same problem. I want to validate if a number entered through an input field exists in the database. My table name is _jos_new_installations. My field input name is sup. The column name in the database is sup_number. I am using the following code in the server side validation code box.

<?php
    $db =& JFactory::getDBO();                           
    $text =& JRequest::getString('sup', '', 'post');       
    $sql = "
        SELECT COUNT(`sup_number`)                               
            FROM `_jos_new_installations`                   
            WHERE `sup_number` = ".$db->Quote($text);
    ";
    $db->setQuery($sql);         
    $result = $db->loadResult();     
    if ( !$result ) {
      return "Incorrect SUP number";
    }
?>

It doesn't validate though. Am I doing anything wrong?

I am on Joomla 1.5.26 and Chronoforms 3.2

It works now. Instead of

WHERE `sup_number` = ".$db->Quote($text);

I used

WHERE `sup_number` = ".$db->Quote($text).";

so the whole code is

<?php
    $db =& JFactory::getDBO();                           
    $text =& JRequest::getString('sup', '', 'post');       
    $sql = "
        SELECT COUNT(`sup_number`)                               
            FROM `_jos_new_installations`                   
            WHERE `sup_number` = ".$db->Quote($text).";
    ";
    $db->setQuery($sql);         
    $result = $db->loadResult();     
    if ( !$result ) {
      return "Incorrect SUP number";
    }
?>

Thanks