2. PHPRunner General questions
  3. Lookup
This topic is locked

Lookup

 		8/7/2007 8:28:19 PM
PHPRunner General questions
author

Hi!
I want to be able to look up a name in one field, get its ID number and insert it into another field. I tried the lookup table, viewed the tutorial, etc. but I'm not sure that it can do what I want.
I have:
ID RegName SireID Sire
I want to find the 'Sire' in the 'RegName' field, get its ID and put it in the 'SireID'.
I tried a mySql update query to get the 'ID' and update the 'SireID' Where The 'RegName' is the same as the 'Sire' but it thinks I am looking for an ID for a record where the RegName and the Sire are the same - which is of course impossible in the real world.
So far, I have been downloading the table to Excel and using it's 'Lookup" function to accomplish this but I want to be able to have it done automatically.
Please help! is this possible?? I would think if I can do it in Excel, I could do it in mySql and PHP>
J
 		Jane 8/8/2007

Hi,
I'm not sure that I understand you correctly.

You can't add value to another field using lookup wizard.
I think you need to use Before record added or Before record updated event for this purpose.

Here is a sample code:

global $conn,$strTableName;

$str = "select * from ".$strTableName." where RegName='".$values["Sire"]."'";

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

if ($data = db_fetch_array($rs))

$values["SireID"] = $data["ID"];
500410 8/27/2007

Hi,

I'm not sure that I understand you correctly.

You can't add value to another field using lookup wizard.
I think you need to use Before record added or Before record updated event for this purpose.

Here is a sample code:


Hi Jane: This is what I put in:
global $conn,$strPedigrees;

$str = "select ID from ".$strPedigrees." where RegName='".$values['Sire']."'";

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

if ($data = db_fetch_array($rs))

$values['SireID'] = $data['ID'];
I tried it as an event before added and after added and the error looks the same! Any suggestions? Thanks!!
Before Record Added:
256

Error description You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where RegName='Beechcroft's Magic Man Muddy'' at line 1

URL fortheloveoflabradors.com/labradors/Pedigrees_add.php'>fortheloveoflabradors.com/labradors/Pedigrees_add.php?

Error file /homepages/33/d208885282/htdocs/labradors/labradors/include/dbconnection.php

Error line 26

SQL query insert into `Pedigrees`
After Record Added
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where RegName='Boothgates Chief Producer'' at line 1

URL fortheloveoflabradors.com/labradors/Pedigrees_add.php'>fortheloveoflabradors.com/labradors/Pedigrees_add.php?

Error file /homepages/33/d208885282/htdocs/labradors/labradors/include/dbconnection.php

Error line 26
Before Record Added
Alexey admin 8/29/2007

Try using this code:

global $conn,$strTableName;

$str = "select * from ".$strTableName." where RegName='".mysql_escape_string($values["Sire"])."'";

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

if ($data = db_fetch_array($rs))

$values["SireID"] = $data["ID"];
500411 8/29/2007

Try using this code:


Thanks Alexey but I am getting [I think] the same error:

The name of the Sire - RegName is the new Sire I just input that I want the ID for so part of it must be working!!
Technical information

Error type 256

Error description You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where RegName='Langshott Gale Force From Kimvalley'' at line 1

URL fortheloveoflabradors.com/labradors/Pedigrees_add.php?

Error file /homepages/33/d208885282/htdocs/labradors/labradors/include/dbconnection.php

Error line 26

SQL query insert into `Pedigrees`

Solution This is a general error. It occurs when thereis an error in event code or in SQL.
501303 9/5/2007

I fixed it!!! this is what works:
global $conn;

$str = "select ID from Pedigrees where RegName='".mysql_escape_string($values["Sire"])."'";

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

if ($data = db_fetch_array($rs))

$values["SireID"] = $data["ID"];
return true;
Thanks for your help!!!