2. PHPRunner General questions
  3. Lookup Wizard with Database table and Views
This topic is locked

Lookup Wizard with Database table and Views

 		6/5/2025 9:21:26 AM
PHPRunner General questions
A
 anthonywalker author

Hi All,

Im wondering if someone can point me in the right direction with this issue/lack of knowledge im having! :)

I have a sign in page (below) when clicking the name the member number and membership type are automatically pulled from the same table (which works great) the problem im having is the section for "What firearms are you shooting today" this is a seperate table with a link between both using a member number. No matter what I put in the lookup wizard section to put the info from the memberFirearms table it just keeps showing ALL the entires in the table and not just the ones for that certain member.

img alt

I think I have to use a WHERE statement but im no query expert (not even a novice!) so im strugling the get this to work. When I do put a where statement in I get a PHP error on the page which im guessing because the member name/member number has not been entered yet..

Any help would be greatly appreciated!
Regards
Anthony
D
 		DUKE 6/6/2025

Hi Anthony,

I would use somthing like this.

This will set up the database tables. No foreign keys in the database itself. You can then manage the relationships in PHPRunner.

-- 1. Members Table
CREATE or replace TABLE Members (
member_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20),
membership_date DATE NOT NULL,
dob DATE NOT NULL,
license_number VARCHAR(50)
);

-- 2. Firearms Table
CREATE or replace TABLE Firearms (
firearm_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
type VARCHAR(50) NOT NULL,
caliber VARCHAR(50) NOT NULL,
available BOOLEAN DEFAULT TRUE,
serial_number VARCHAR(100) UNIQUE NOT NULL,
manufacturer VARCHAR(100)
);

-- 3. ShootingSessions Table
CREATE or replace TABLE ShootingSessions (
session_id INT PRIMARY KEY AUTO_INCREMENT,
member_id INT NOT NULL,
date DATETIME NOT NULL,
range_number INT,
supervised_by VARCHAR(100)
);

-- 4. SessionFirearms Table
CREATE or replace TABLE SessionFirearms (
session_firearm_id INT PRIMARY KEY AUTO_INCREMENT,
session_id INT NOT NULL,
firearm_id INT NOT NULL,
rounds_used INT DEFAULT 0,
duration_minutes INT
);

-- 5. AmmunitionLog Table (Optional)
CREATE or replace TABLE AmmunitionLog (
ammo_log_id INT PRIMARY KEY AUTO_INCREMENT,
session_id INT NOT NULL,
caliber VARCHAR(50) NOT NULL,
rounds_used INT NOT NULL
);
D
 		DUKE 6/6/2025

The SQL code will create the tables. Then sync your project. You can run the sql code in a mysql program like MySQL Workbench.
A
 		anthonywalker author 6/6/2025

Hi DUKE,

I Appriciate your reply, im may of not explained my issue properly as I think your code would basically create all new tables?

Im happy with the way ours is currently laid out (as its taken some time to get this far with the local FEO)

What I need os the the "What firearms are you shooting today?" to only show the items that the member has, for example above only the HOWA, Marlin and Taurus should show for member 002.

As you can see from this screen shot the info is correct in the backend "members" sections.

img alt

I think that I need a where statement for the firearms list and something to refresh the list when the member number is updated above it?

Thanks
Anthony
A
 		anthonywalker author 6/6/2025

After further messing, I have the following:

If I use MemberNo = 002 the "What firearm are you shooting today?" shows the correct entries for member 002

If I use MemberNo = :member_number the "What firearm are you shooting today?" is blank.

Im thinking that as Member_Number is empty when the form loads the firearms list is blank, so im guessing I need to "refresh" the firearms list AFTER the member_number has been updated/changed?

If this is correct how do I go about doing the refresh only after the Member_Number has been updated?

Thanks!