Hi I am very new to SQL server and I have a somewhat basic question. I have created a program keeps a track of user performance. There are two tables involved - DeviceInformation which contains information regarding the device being used and what hand is used along with an autoincrementing Primary Key which is DeviceID. The other table is ResultsInformation which contains information such as time, and where was clicked and ResultsID Primary Key. So far data is being sent to both of these tables. However I was unsure about the best way to link these tables so that I can check how the user with DeviceID 1 has performed and how a user with DeviceID 2 has performed and so on and so forth. Any help would be appreciated.
You really need to provide DDL and Sample data so we can help. Use temp tables so we don't muck up our dbs trying to help with the solution.
Create Table #a (Field1, Field2, etc...)
insert into #a values (1,2,etc...)
For your example, how do the 2 table relate? Is DeviceID in both tables? if so, then join on DeviceID
Sorry I don't know if this will be of any useor not. Basically the way how I have it at the moment is with DeviceID being the primary key in deviceInformation and the foreign key in userResults. However this is always appearing as NULL in the userResults and I am unsure of how to fix this. My PHP at the moment for inserting into userResults is:
$sql = "INSERT INTO UserResults2(Xpos, Ypos, StartID, RandomID, TimeTaken ) VALUES ('$Xpos', '$Ypos', '$StartID', '$Random', '$Time')";
However I am unsure if I am going about this in the correct way. Any help or advice will be appreciated, many thanks
I'd actually say that you should try doing a Join statement on the device ID # (i'm assuming that is the same data that is the primary key on both tables)
Meaning you'd get back
Device 1 : all the device info; all the user info
However, in looking at the tables you've put up, the Device ID NULL is very bad... otherwise, there's no way to tell which device belongs to which person...
You need some way to identify the device being used by the user - so that when you update the results table you have the defined device ID.
Not sure how you are building your device table...but you really need something on that device that is unique.
Thanks for the reply and feedback. This is sort of the part where I get confused on how to proceed and if I am doing things correctly as I'm unsure of what the correct php/sql syntax for getting the DeviceID put into the userResults would be. At the moment this DeviceID is just a autoincrementing integer. I've tried putting a Select statement but that results in getting nothing back at all.
Hi thanks for the reply. DeviceID is an autoincrementing int and should be the Primary Key in the deviceInformation table that helps to identify the user/device being used. I've also made this be the foreign key in the userResults table. The main issue I'm having at the moment is with getting this sent to the userResults table and ensuring that it corresponds.
I would recommend looking at whatever information the device itself is actually sending to identify itself... that's where I would start. However the device sends information (or what information) is what you're going to want to put in the device information table.
Once you know which device is sending the info (that corresponds to the user) then you can start combining the data
in none technical terms and without referring to any schemas and tables etc, in plain english what is it you are trying to capture? With No table references.
Basically I would like one table to take the unique identifier of another table so I can see which device has given which results if that makes sense
This doesn't make sense - where is the user definition? Do you have the user assigned to a specific device - or do you assign the device to the user when that user accesses the system?
You have to be able to define the device - and associate it with a user before you can apply that to your insert/update statement for the results.
For example - if I accessed your system today from my laptop, how would you assign that device in your system and identify it as my laptop? Then, tomorrow I access your system from my desktop - which is a different device - how do you now assign that desktop to my user?
And further - what if next week I move and I am now using the device that another user was using previously? How can you differentiate between my account using that device - and the other user using that device? Or better yet...first shift I use a device and second shift someone else uses that device?
You need to identify the device...using something unique to that device.
1 Like
You are still making references to tables etc. Describe your requirements please without using any technical terms, imagine I am your non technical family memeber. 
I think the problem is that you want it to enter data, but you're not sure of which data...
A program can only run what it's told... if you don't know how the data gets entered, can't help with the program... 
this below is all guess work
use sqlteam
go
if OBJECT_ID('devicetypes') is not null
drop table devicetypes;
create table dbo.devicetypes(devicetypeid int identity(1,1),
devicetype nvarchar(50))
insert into devicetypes
select 'Phone' union
select 'Tablet' union
select 'laptop'
if OBJECT_ID('devices') is not null
drop table devices;
create table dbo.devices(deviceid int identity(1,1),
screenwidth int,
screenheight int, devicetypeid int)
insert into devices
select 1536, 864, 1 union
select 1536, 864, 2
if OBJECT_ID('touchpoints') is not null
drop table touchpoints;
create table dbo.touchpoints(touchpointid int , touchpoint nvarchar(50))
insert into touchpoints
select 1, 'Left hand' union
select 2, 'Right hand' union
select 3, 'Both Hands' union
select 4,'Stylus' union
select 5,'Nose' union
select 6,'Feet toe'
if OBJECT_ID('results') is not null
drop table results;
create table dbo.results(resultid int identity(1,1), xpos int, ypos int,
startid int, randomid int, timetaken int, touchpointid int,
deviceid int)
insert into results(xpos,ypos,startid,randomid,timetaken,touchpointid,deviceid)
select 48 xpos, 48 ypos, 0 startid, 11 randomid, 1293 timetaken, 3 touchpointid, 1536
Sorry I'll try to explain better haha - basically the user will enter their device and the dimensions, width and height, of this device will be stored. From this the user will then use their device to click a series of targets where there performance will be monitered.
Basically the device won't be tied to a specific user and there may be more than one device that is the same but may have different results. There is an auto-incrementing primary key linked to the devices called the DeviceID that will be used to identify the device
So when the user enters their device - do you return the device ID to the application? If so - then you have that value you can then use when entering the results. If the user has to enter the device every time - then they can select their device or create a new one...
At the moment the DeviceID will only remain in the deviceInfo table, however the issue I am having is sending this DeviceID to the userResults table to allow for myself to distinguish between all the results
Show us the code that inserts into that table
Here is the sql that is inside a PHP page, all the other parts are getting sent to the database apart from the last part.
$sql = "INSERT INTO UserResults2(Xpos, Ypos, StartID, RandomID, TimeTaken, DeviceID)
VALUES ('$Xpos', '$Ypos', '$StartID', '$Random', '$Time', SELECT DeviceID FROM deviceInfo);