2. PHPRunner General questions
  3. Querying Another Table
This topic is locked

Querying Another Table

 		2/6/2007 2:34:36 PM
PHPRunner General questions
I
 ictaylor author

Hi
Users register for my site and pick a username and password obviously. They will then eventually go to another page where they enter a competition - on this page I ask them to enter their site username and password into a a separate registration page which enters the info into another database table - I would like for when they click submit to enter the competition, for the username and password they have entered to be checked against my user table to see if they have first registered properly on my site.
For example joe bloggs registers for my site with the username 'joe' and password 'bloggs' her then goes to my competition page and enters his details as 'joe' and password as 'dimaggio' - I want this registration page to query the users table for verification before entering the new details into the competition table? - in this case it would fail as joe and dimaggio havent registered in my users table
J
 		Jane 2/7/2007

You can do it using Before record added event on the Events tab.

Use Check if specific record exist action as a sample.
I
 		ictaylor author 2/7/2007

Ok, the following code is suggested:

function BeforeRegister($userdata)

{
// Parameters:

// $userdata - Array.

// Each field on this form represented as 'Field name'-'Field value' pair
//********** Check if specific record exists ************

global $conn;

$strSQLExists = "select * from AnyTable where AnyColumn='AnyValue'";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

// if record exists do something

}

else

{

// if dont exist do something else

}
return true;
// return true if you like to proceed with registration

// return false in other case
}


What do I need to do to have this check my '_users' table to see if the username AND password match. If they do, I want the new record to be entered.
If they dont, I would like it to say something like 'This username and password have not registered for the site'.
Alexey admin 2/7/2007

Hi,
here is the sample code for Before record added event.

global $conn,$message;

$strSQLExists = "select * from _users where username='".$values["username"]."' and password='".$values["password"]."'";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

return true;

}

else

{

$message="username and password not exist";

return false;

}
return true;



where username and passwordare your actual field names.
I
 		ictaylor author 2/8/2007

Hi
This isnt working. I am using the following code:

function BeforeRegister($userdata)

{
// Parameters:

// $userdata - Array.

// Each field on this form represented as 'Field name'-'Field value' pair
//********** Check if specific record exists ************

global $conn,$message;

$strSQLExists = "select * from _users where Username='".$values["Username"]."' and Password='".$values["Password"]."'";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

return true;

}

else

{

$message="username and password not exist";

return false;

}
return true;


where the table to query is called '_users' and the username field is called 'Username' and the password field is called 'Password'. If these exist in the _users table they are then to be entered into the _enter table in the field names 'Site Username' and 'Site Password' respectively.
I currently get the following parse error:

Parse error: parse error, unexpected $ in /home/b/o/boxedclever_net/users/entry/include/events.php on line 29
J
 		Jane 2/8/2007

It seems that you've forgot } to close BeforeRegister function:

function BeforeRegister($userdata)

{
// Parameters:

// $userdata - Array.

// Each field on this form represented as 'Field name'-'Field value' pair
//** Check if specific record exists ****

global $conn,$message;

$strSQLExists = "select * from _users where Username='".$values["Username"]."' and Password='".$values["Password"]."'";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

return true;

}

else

{

$message="username and password not exist";

return false;

}
}
I
 		ictaylor author 2/8/2007

still doesnt work
following error:

PHP error happened

Technical information

Error type 8

Error description Undefined variable: values

URL www.boxedclever.net/users/entry/register.php?

Error file /home/b/o/boxedclever_net/users/entry/include/events.php

Error line 13

SQL query select count(*) from `_enter` where `Site Username`='boxedclever'
J
 		Jane 2/9/2007

On the BeforeRegster event you need to use $userdata variable instead of $values:

$strSQLExists = "select * from _users where Username='".[b]$userdata["Username"][/b]."' and Password='".$userdata["Password"]."'";
I
 		ictaylor author 2/9/2007

Ok
I dont get an error anymore, but the registration page doesnt seem to actually input any data into my' _enter' database table - the regisration page just refreshs
here is the full code I am using:

function BeforeRegister($userdata)

{
// Parameters:

// $userdata - Array.

// Each field on this form represented as 'Field name'-'Field value' pair
//********** Check if specific record exists ************

global $conn,$message;

$strSQLExists = "select * from _users where Username='".$userdata["Username"]."'and Password='".$userdata["Password"]."'";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

return true;

}

else

{

$message="you must first register for the site";

return false;

}
}
I
 		ictaylor author 2/9/2007

FIXED IT finally!!
should have been:

function BeforeRegister($userdata)

{
// Parameters:

// $userdata - Array.

// Each field on this form represented as 'Field name'-'Field value' pair
//********** Check if specific record exists ************

global $conn,$message;

$strSQLExists = "select * from _users where Username='".$userdata["Site Username"]."'and Password='".$userdata["Site Password"]."'";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

return true;

}

else

{

$message="you must first register for the site";

return false;

}

}