Cannot login using view as user table

PHPMaker General Discussion (PHPMaker)
1

PHPMaker v2026.3

Database: MSSQL

After migrating from my old project, I can’t login anymore with HTTP 500 error. After debugging, I noticed there is an error in the query:

SELECT t0.USERID AS USERID_1
,t0.PASSWORD AS PASSWORD_2
,t0.USERNAME AS USERNAME_3
,t0.USEREMAIL AS USEREMAIL_4
,t0.USERLEVELID AS USERLEVELID_5
,t0.ACTIVE AS ACTIVE_9
,t0.AVATAR AS AVATAR_10
,t0.LOGINCONTROL AS LOGINCONTROL_11
,t0.LASTLOGIN AS LASTLOGIN_13
,t0.CREATED_BY AS CREATED_BY_17
,t0.CREATED_AT AS CREATED_AT_18
,t0.UPDATED_BY AS UPDATED_BY_19
,t0.UPDATED_AT AS UPDATED_AT_20
,t0.id AS id_21
FROM dbo.VIEW_USERS t0
WHERE t0.USERID = ?
ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

It added “,t0.id AS id_21” in the select statement when my tables does not have one. Do I need to add an auto-increment ID in my USERS table now? In v2025 or before it does not need one.

Below is my table structure which I’ve been using since v12 until now.

CREATE TABLE [dbo].[USERS](
	[USERID] [nvarchar](16) NOT NULL,
	[PASSWORD] [nvarchar](256) NULL,
	[USERNAME] [nvarchar](100) NULL,
	[USEREMAIL] [nvarchar](50) NULL,
	[USERLEVELID] [int] NULL,
	[ACTIVE] [tinyint] NULL,
	[AVATAR] [varchar](255) NULL,
	[LOGINCONTROL] [varchar](max) NULL,
	[LASTLOGIN] [datetime2](0) NULL,
	[CREATED_BY] [nvarchar](100) NULL,
	[CREATED_AT] [datetime2](0) NULL,
	[UPDATED_BY] [nvarchar](100) NULL,
	[UPDATED_AT] [datetime2](0) NULL,
 CONSTRAINT [PK_USERS] PRIMARY KEY CLUSTERED 
(
	[USERID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Also I noticed that now the “Profile field (long text)” now not long working if the field type is “Text” as you can’t select it in the list. It only selectable after changing to varchar(max).

2

No, you don't need auto-increment ID. You only need a primary key for the user table. However, you use the view dbo.VIEW_USERS as user table, which does not have a primary key, so it does not work. Why did you use the view as user table, it is recommended that you use dbo.USERS as user table directly.

1 Like
3

Thanks. It was due to the primary key. My usual practice is to create views for all my table, including any view-only modules/reports that need use custom views so that everything is in one place instead of mix of tables and views. Need to remind myself to set the primary keys for all the views that I loaded.