2. PHPRunner General questions
  3. Select from 2 different tables and add...
This topic is locked

Select from 2 different tables and add...

 		2/17/2007 1:07:49 PM
PHPRunner General questions
M
 mmponline author

I need to draw info from 2 tables and add it on the add page event.
When I use this code only with the one table, it works fine

$str = "select * from _Tournaments where TournamentName='".$values["TournamentName"]."'";


But if I add the 2nd table's code, it works but insert nothing:

There is something wrong with this code, I'm not sure how to fix it:

$str = "select * from _Tournaments where TournamentName='".$values["TournamentName"]."' and _PlayerInformation where Name ='".$values["Name"]."'";
L
 		larsonsc 2/17/2007

Do those two tables share a common field? If not, you are looking at needing to do two separate qeuries I think since you are trying to get results on two different criteria. If they do share a common field though, you could do something like this:

select * from _Tournaments, _PlayerInformation where commonfield = submittedvalue;


I'm not sure, but you might even be able to get away with a subquery, but I can't recall if PHPR supports subqueries or not.
T
 		thesofa 2/18/2007

Subqueries here

HTH

G
M
 		mmponline author 2/18/2007

Let me try in simple explanation:
Table 1

1ID

1FieldA

1FieldB
Table 2

2ID

2FieldA

2FieldB
RESULT should be:

Table 3 (Contains above fields)

1ID

2ID

1FieldA

2FieldA

1Field B

2FieldB
On before record added on table 3: (My effort after a whole weekend)
Works retrieving 1 Table's info:

$str = "select * from Table1 where 1ID='".$values["1ID"]."'";

$rs = db_query($str,$conn);

$data = db_fetch_array($rs);

$values["1FieldA"] = $data["1FieldA"];

$values["1FieldB"] = $data["1FieldB"];


Does not retrieve both table's info:

$str = "select * from Table1, Table2 where 1ID='".$values["1ID"]."' and 2ID='".$values["2ID"]."'";

$rs = db_query($str,$conn);

$data = db_fetch_array($rs);

$values["1FieldA"] = $data["1FieldA"];

$values["1FieldB"] = $data["1FieldB"];

$values["2FieldA"] = $data["2FieldA"];

$values["2FieldB"] = $data["2FieldB"];
T
 		thesofa 2/18/2007

Does not retrieve both table's info:


$str = "select * from Table1, Table2 where 1ID='".$values["1ID"]."' and 2ID='".$values["2ID"]."'";

$rs = db_query($str,$conn);

$data = db_fetch_array($rs);

$values["1FieldA"] = $data["1FieldA"];

$values["1FieldB"] = $data["1FieldB"];

$values["2FieldA"] = $data["2FieldA"];

$values["2FieldB"] = $data["2FieldB"];



sorry about the obvious question, where are you getting $values["1ID"]."' and '".$values["2ID"] from?
I think I am right in saying that a query like that will fail if either value is NULL

you seem to be trying to find from Table1where 1id and select from table2 where 2 id.

To my mind you are trying to do 2 seperate queries in one go.

Since you do not specify which table is being queried with which variable, it must return NULL because one of the answers will be NULL..

I think you need

$str1 = "select * from Table1 where 1ID='".$values["1ID"]."'";

$rs1 = db_query($str1,$conn);

$data1 = db_fetch_array($rs1);

$values["1FieldA"] = $data1["1FieldA"];

$values["1FieldB"] = $data1["1FieldB"];
and
$str2 = "select * from Table2 where 2ID='".$values["21ID"]."'";

$rs2 = db_query($st2r,$conn);

$data2 = db_fetch_array($rs2);

$values["2FieldA"] = $data2["2FieldA"];

$values["2FieldB"] = $data2["2FieldB"];


HTH

G
M
 		mmponline author 2/19/2007

I get a parse error?
My complete code:

function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

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

global $conn;

$strSQLExists = "select * from _TournamentsEntered where LoginID='".$values["LoginID"]."' and TournamentName='".$values["TournamentName"]."'";
$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

// if record exists do something
//********** Display a message on the Web page ************

header("Location: sorry.htm");

return false;
}

else

{

global $conn;

$str1 = "select * from _Tournaments where TournamentName='".$values["TournamentName"]."'";

$rs1 = db_query($str1,$conn);

$data1 = db_fetch_array($rs1);

$values["TournamentName"] = $data1["TournamentName"];

$values["TourID"] = $data1["TourID"];

$values["VenueClub"] = $data1["VenueClub"];

$values["Town"] = $data1["Town"];

$values["Holes"] = $data1["Holes"];

$values["DateFrom"] = $data1["DateFrom"];

$values["DateTo"] = $data1["DateTo"];

$values["EntryClosingDate"] = $data1["EntryClosingDate"];

$values["EntryFee"] = $data1["EntryFee"];

$values["MaximumHandicap"] = $data1["MaximumHandicap"];

$values["OtherRequirements"] = $data1["OtherRequirements"];

$values["Directions"] = $data1["Directions"];

$values["Map"] = $data1["Map"];
and
$str2 = "select * from PlayerInformation where Name='".$values["Name"]."'";

$rs2 = db_query($st2r,$conn);

$data2 = db_fetch_array($rs2);

$values["PlayID"] = $data2["PlayID"];

$values["Name"] = $data2["Name"];

$values["Surname"] = $data2["Surname"];

$values["Handicap"] = $data2["Handicap"];

$values["ClubName"] = $data2["ClubName"];

$values["ProvinceUnion"] = $data2["ProvinceUnion"];

$values["CityTown"] = $data2["CityTown"];

$values["RegistrationDate"] = $data2["RegistrationDate"];

$values["`ContactNo`"] = $data2["`ContactNo`"];

$values["E-mail"] = $data2["E-mail"];
//********** Redirect to another page ************

header("Location: entered.htm");
return true;

}
// return true if you like to proceed with adding new record

// return false in other case
}
T
 		thesofa 2/19/2007

The 'and' between the two blocks of code should not have been in the code quote, sorry.
M
 		mmponline author 2/19/2007

Now I get an "Undefined variable: st2r" error message
I get this error message on a similar code provided by Jane.
T
 		thesofa 2/19/2007

Now I get an "Undefined variable: st2r" error message

I get this error message on a similar code provided by Jane.



should be $str2
M
 		mmponline author 2/21/2007

Fixed, thanks