How to get a value from a database based on a value from the same row, but different column - pls help!!!!!!!!!!

Hi. I have created a custom login page, where users sign in, which has an email and password input. After the user has logged in, I want to take the email that they inputted, and find it in a database, titled Teachers, where I store all my member’s information. In my teachers database there is also an ID field which is connected to a dynamic page, which acts like the members profile page. With the email, I want to find the corresponding ID of the user that can take them to the profile page. I will paste the code I have so far below. Please help, I have been stuck on this for weeks, and I really need to figure this out.


I have tried to a different method of using the user’s email in the dynamic page to avoid searching things up in the database because that always doesn’t work, but it’s not that secure. Again, thank you for any help. I desperately need it. I will add a picture of my database below?

You need to create a function that you can call to check for the email and then return the dynamic page URL you need, from your table.

Here is the sample function you would need to put in a backend jsw file.

import wixData from 'wix-data';

export async function checkUserExists(email) { 
    if (typeof email !== 'undefined' && email !=="") {
        let options = {
            "suppressAuth": true,
            "suppressHooks": true
        };
        
        // convert email to lowercase and remove all spaces
        var _email = email.toLowerCase().split(" ").join("");
        
        var url = "";
        return wixData.query("Teachers")
            .eq('email', _email)
            .find( options )
            .then( (results) => { 
                if (results.items.length !== 0) { 
                    // we have an existing record for this email
                    // only return the first successful row, if 
                    // multiple rows with same email are found
                    url = results.items[0].teachersProfile(Id); // your fieldname here
                    return url;
                } else {
                    // there was no database row with this email
                    return "Error: User not setup. Contact admin for help.";
                };
            })
            .catch( (error) => {
                // error occurred trying to read from the table, 
                // or it does not exist?, or permissions error?
                console.log("Error reading from table: Teachers.\n"+error.message);
                return "Error:  Fetch error trying to get data from table Teachers.";
            });
    } else {
        // error email parameter was not passed in, or is null/invalid
        return "Error: Email parameter passed in was invalid.";
    }
}

You need to then call this backend function from your code on your page. This also makes it more secure, since the backend code is not accessable by users and hides the table names and field names.

Lets say you call the backend file backend/BE_functions.jsw
In your page code you need to insert at the top of your code a reference to the function and the file location where it can be found in. I didn’t have your complete code, since you only provided a snippet, but this is the idea and should get you up and running.

import { authentication } from 'wix-members';
import wixLocation from 'wix-location';
import {checkUserExists} from 'backend/BE_functions';

let userEmail="";

$w.onReady(function() {
}


export async function loginTeacher_click(event) {
    // convert email to lowercase and strip out any spaces
    userEmail = $w('#emailLogin').value.toLowerCase().split(" ").join("");
    let password = $w('#passwordLogin').value;
    
    try {
        await authentication.login(userEmail, password);
        console.log("Member is logged in");
        let newlink = await checkUserExists(userEmail);
        if (newlink.substring(0,6) !== "Error:") {
            let realLink = "/teachers/profile/" + newlink;
            console.log(realLink);
            wixLocation.to(realLink);
        } else {
            console.log("Error occurred: \n" + newLink);
        }            
    } catch (error) {
        console.log(error);
    }
}


When looking up values such as emails, and comparing them to values in a database always convert them to one case, either all uppercase or all lowercase. It is a very good idea to have whomever is responsible for entering in the emails and other info into the table, always type in the columns being used for searching, in the same consistent alpha case.

In this way, if users type in various combinations for their email, you can always successfully search for them. Searches are case sensitive, so this will avoid mistakes and failed lookup errors, if your user types in the email address in all uppercase or all lowercase or any combination thereof.

As well I’ve tried to handle the various errors that can occur when using the backend function. Therefore you will see various tests and error messages in the backend function.

Review the function and change the tablename and fieldname to match your exact needs. To get the correct fieldname that you need for your function, go into the content manager, select the table. Click on the column you need the name for and then select properties. The value called ‘Field Key’ is the fieldname you need to use in the function.

I personally don’t like to embed events in the onReady() function. It makes it look messier and harder to follow. I only put initialization code into the onReady() function. Things I need to deal with, once the page has loaded. My personal preference is to use the Wix editor and click on the button object, and then add an on_click() event from within the editor (bottom right). This creates a separate event function and does not embed it within the initial onReady() function. I then edit this newly created event as needed.

I had to add async to the beginning of the event function to let it know that I will be using an await within the function. This await will force the function to pause, until the data from the backend function is returned and available, before continuing to process the remainder of the function.

Good luck.

@Paul Krzyz - Thank you, Paul for taking the time to explain in detail how to fix Kumar’s problem. Although I’m not having trouble with the same problem, it gave me information that could be helpful.
Thanks again

Hello @Paul Krzyz. Thank you for your help. This is what I have in my backend code:
import wixData from ‘wix-data’ ;
export async function checkUserExists ( email ){
if(typeof email !== ‘undefined’ && email !== “” ){
let options = {
“suppressAuth” : true ,
“suppressHooks” : true ,

    }; 
    //convert to lowercase and remove spaces 
    **var**  _email  =  email . toLowerCase (). split ( " " ). join ( "" ); 

    **var**  url  =  "" ; 
    **return**  **await**  wixData . query ( "Teachers" ) 
        . eq ( 'email' ,  _email ) 
        . find ( options ) 
        . then ( ( results ) =>{ 
            **if**  ( results.items.length  != 0 ) { 
                //returns the first row, if multiple rows with the same email are found 
                url  =  results.items [ 0 ]. link-teachers-title ; 
                **return**  url ; 
            }  **else** { 
                **return**  "Error: User not setup. contact admin for help." ; 
            } 
        }) 
        . **catch** (( error ) =>{ 
            console . log ( "Error reading from table: Teachers.\n"  +  error.message ); 
            **return**  "Error: Fetch error trying to get data from table Teachers" ; 
        }); 
        } 
        **else** { 
            **return**  "Error: Email parameter passed in was invalid" 
        } 

}

This is what I put in my login page
import wixUsers from ‘wix-users’ ;
import wixLocation from ‘wix-location’ ;
import { checkUserExists } from ‘backend/login_functions’

let userEmail = “” ;

$w . onReady ( function (){

})
export async function loginTeacher_click ( event ){
userEmail = $w ( ‘#emailLogin’ ). value . toLowerCase (). split ( " " ). join ( “” );
let password = $w ( ‘#passwordLogin’ ). value ;
wixUsers . login ( userEmail,password )
. then ( () => {
let newLink = await checkUserExists ( userEmail );
if ( newLink . substring ( 0,5 ) !== “Error” ){
let realLink = “/teachers/profile/” + newLink ;
console . log ( realLink );
wixLocation . to ( realLink );
} else {
console . log ( “Error occured: \n” + newLink );
}
})
. catch (( error ) =>{
console . log ( error );
})
}

However, when I run the backend program, it gives me this message: Error reading from table: Teachers. teachers is not defined
Also await is underlined in read saying it can only be used in an async function, but I believe I am using it in an async function

In your description, you indicated your table was called ‘Teachers’. If your table is another name, just go into the backend code and change the table name from ‘Teachers’ to the actual name of the table.

The line in the backend code:

return await wixData.query("Teachers")

must be changed to the name of the actual table, if it is not ‘Teachers’.
Also, double check the name of the column (field) of the table to ensure that the field name is correct. See my note earlier to verify the ‘Field Key’ is being used for your field name.

url = results.items[0].link-teachers-title;

I have an issue in that I can’t run the code I gave you because I don’t have your tables.
The second issue you stated is that the error indicates that you can only use an await within an async function.

I wonder if it is being caused by the .then( () => } line.

wixUsers.login(userEmail,password)
.then( () => {

Try and put an async in front of the () and let me know if that solved the problem.
Eg: .then( async () => {

1 Like

If you like it you can upvote it with a like !!!

2 Likes

Hi @Paul Krzyz

There was something wrong the field key, even though I copied and pasted it from the database, it wouldn’t go throught, so I created a different ID with phone number. I will paste it below, and the code worked. However the code on my login page still isn’t working. It’s giving me error -19976.

The code from my backend:

import wixData from ‘wix-data’ ;
export async function checkUserExists ( email ){
if(typeof email !== ‘undefined’ && email !== “” ){
let options = {
“suppressAuth” : true ,
“suppressHooks” : true ,

    }; 
    //convert to lowercase and remove spaces 
    var  _email  =  email . toLowerCase (). split ( " " ). join ( "" ); 

    var  url  =  "" ; 
    **return**  **await**  wixData . query ( "Teachers" ) 
        . eq ( 'email' ,  _email ) 
        . find ( options ) 
        . then ( ( results ) =>{ 
            if  ( results.items.length  != 0 ) { 
                //returns the first row, if multiple rows with the same email are found 
                url  =  results.items [ 0 ]. phoneNumber ; 
                **return**  url ; 
            }  **else** { 
                **return**  "Error: User not setup. contact admin for help." ; 
            } 
        }) 
        . catch (( error ) =>{ 
            console . log ( "Error reading from table: Teachers.\n"  +  error.message ); 
            **return**  "Error: Fetch error trying to get data from table Teachers" ; 
        }); 
        } 
        **else** { 
            **return**  "Error: Email parameter passed in was invalid" 
        } 

}

code from my login page:
import wixUsers from ‘wix-users’ ;
import wixLocation from ‘wix-location’ ;
import { checkUserExists } from ‘backend/login_functions’

let userEmail = “” ;

$w . onReady ( function (){

})

/**

  • Adds an event handler that runs when the element is clicked.
    Read more
  • @param {$w.MouseEvent} event
    */
    export function loginTeacher_click ( event ) {
    // This function was added from the Properties & Events panel. To learn more, visit Velo: Working with the Properties & Events Panel | Help Center | Wix.com
    // Add your code for this event here:
    let email = $w ( “#emailLogin” ). value ;
    let password = $w ( “#passwordLogin” ). value ;
    userEmail = email . toLowerCase (). split ( " " ). join ( “” );
    wixUsers . login ( email , password )
    . then ( async () => {
    let newLink = await checkUserExists ( userEmail );
    if ( newLink . substring ( 0,6 ) !== “Error:” ){
    let realLink = “/teachers/profile/” + newLink ;
    console . log ( realLink );
    wixLocation . to ( realLink );
    } else {
    console . log ( “Error occurred: \n” + newLink );
    }
    })
    . catch (( error ) => {
    console . log ( error );
    });
    }
    This is some information from my database:


This is the field key for Teachers Profile (phone number) - link-teachers-title
I just used the field key for Phone number, which was phoneNumber and got the number and added it to the /teachers/profile

Some helpful hints when creating field names for your tables:

  • when naming your table fields keep the name short (15 characters or less)
  • no spaces in field names, you can use ‘_’ character instead, if you need to space the words of the name
  • only alphanumeric characters in the field name, with the exception of ‘_’ and use it, only if absolutely needed
  • enter the field name all in lowercase if possible, makes it easier when using the names in your velo code. Wix changes some of the uppercase characters to lowercase, which can cause you grief and your code won’t work. (Look at ‘Field Key’) to see what Wix did to your field name and always use that to refer to the field name in your code.

As well once you have created the field name, the ‘Field Key’ stays permanently. Even if you rename the field, you will notice the ‘Field Key’ will not change. The only way to get the ‘Field Key’ to change to a new name, is to create a new field with the new name. Once you have the new field created, then cut/paste the contents from the old field into the new field. Once you have moved the data into the new field, then delete the old field.

In your login page, can you determine where the error is, on what line is occurring. Does the console.log lines work, do you see the realLink in the console? Add more console.log lines to let you know where you are in the code and help you debug. Remember to remove them once you have completed your debugging.

Eg: console.log(“calling Wix login now.”)
console.log(“calling checkUserExists function now.”)
console.log(“returned from checkUserExists function.”)

etc.

Also ensure that there is a space before and after the ‘async’ word you added.

    .then( async () => {

I added the spaces and some console.log statements.

The error is coming from this line:
. catch (( error ) => {
//console.log()
console . log ( error );
});
The code isn’t going to the checkUserExists function

I am assuming this is coming from the front end? What is the last successful line before the .catch( (error) => {.

@avanthi-senthilk I see in your backend code that you are incorrectly using both await and .then() for your call to wixData.query(“Teachers”) . To handle a returned Promise, you should use either await or .then() - not both . You can just delete the await . You want this:

return wixData.query("Teachers")

I modified the front end code. The API documents indicated that the wixlogin is deprecated and they showed a newer way to login the user.

Don’t forget the async at the beginning of the function. I didn’t see it in your example.

export async function loginTeacher_click(event) {

As far as I can tell, he doesn’t need async for loginTeacher_click() as he’s calling await checkUserExists from a .then() function that has async.

@yisrael-wix You are correct, but I had updated his front end example and removed the .then() clause. The docs indicated that Wixlogin was deprecated, and I revised the login using the newer authentication() function in ‘wix-members’, and thus removed the then(). Therefore it is my understanding that the await clauses will need the async before function name.
@avanthi-senthilk indicated that the then() clause was failing and falling through to the .catch(). He didn’t indicate which statement was failing. He did indicate that the backend function was working properly.

@pekrzyz I think we’re saying the same thing, but just to clarify Re: "Therefore it is my understanding that the await clauses will need the async before function name. "…

An await requires an async for the “containing” function. Therefore, something like this would be needed if using an await in a .then() function :

.then( async () => {
   let obj = await getObject(); // call to function returning a Promise
}

The function that contains the above code wouldn’t need an async, unless it itself calls a function using await.

@yisrael-wix Yes, i totally agree with you. As you have written the .then() with the async, the function containing the code would not need it.

I have updated my code to also use the new login way and it is now working in the editor. However, when I publish the site, it says I don’t have permission to access the page I want to go to.

This is my updated code:
import wixData from ‘wix-data’ ;
export async function checkUserExists ( email ){
if(typeof email !== ‘undefined’ && email !== “” ){
let options = {
“suppressAuth” : true ,
“suppressHooks” : true ,

    }; 
    //convert to lowercase and remove spaces 
    var  _email  =  email . toLowerCase (). split ( " " ). join ( "" ); 

    var  url  =  "" ; 
    **return**  wixData . query ( "Teachers" ) 
        . eq ( 'email' ,  _email ) 
        . find ( options ) 
        . then ( ( results ) =>{ 
            if  ( results.items.length  != 0 ) { 
                //returns the first row, if multiple rows with the same email are found 
                url  =  results.items [ 0 ]. phoneNumber ; 
                **return**  url ; 
            }  **else** { 
                **return**  "Error: User not setup. contact admin for help." ; 
            } 
        }) 
        . catch (( error ) =>{ 
            console . log ( "Error reading from table: Teachers.\n"  +  error.message ); 
            **return**  "Error: Fetch error trying to get data from table Teachers" ; 
        }); 
        } 
        **else** { 
            **return**  "Error: Email parameter passed in was invalid" 
        } 

}

Login page:
import { authentication } from ‘wix-members’
import wixLocation from ‘wix-location’ ;
import { checkUserExists } from ‘backend/login_functions’

let userEmail = “” ;

$w . onReady ( function (){

})

/**

  • Adds an event handler that runs when the element is clicked.
    Read more
  • @param {$w.MouseEvent} event
    */
    export async function loginTeacher_click ( event ) {
    // This function was added from the Properties & Events panel. To learn more, visit Velo: Working with the Properties & Events Panel | Help Center | Wix.com
    // Add your code for this event here:
    let email = $w ( “#emailLogin” ). value ;
    let password = $w ( “#passwordLogin” ). value ;
    userEmail = email . toLowerCase (). split ( " " ). join ( “” );
    console . log ( userEmail );
    authentication . login ( email , password )
console . log ( "Logging in" ); 
let  newLink  =  **await**  checkUserExists ( userEmail ); 
console . log ( newLink ); 
if ( newLink . substring ( 0,5 ) !==  "Error" ){ 
    let  realLink  =  "/teachers/profile/"  +  newLink ; 
    console . log ( realLink ); 
    wixLocation . to ( realLink ); 
} **else** { 
    console . log ( "Error occurred: \n"  +  newLink ); 
} 


    //console.log() 

}

This is the page that appears when on the published site

I have set the page settings so that everyone can view it, but it still gives me this page

Your code looks different from mine. You didn’t include the try { } catch{} block
As well, I didn’t see the await infront of the line:

authentication.login(email, password)

I’m not sure if this is causing you grief because without the await, the code may be trying to run, even though you haven’t logged in yet. The await is needed to allow the processing of you being logged in by Wix. You need this to allow the login process to complete before the rest of the code executes.