[ntsc]

i'm developing a registration system for my school and i'm currently working on the reporting part of the whole system which is progressing well. My only problem so far is with creating an attendance report on individual pupils. I have a database of users where the user's name is stored as a forename and surname. The user asks for the report to be generated through the use a select menu, called pupilname, and they choose the full name of whatever user they want a report on. This is all well and good until it needs to be processed since i want to use something like

select * from signin where `what do put in here`=$pupilname

You see the problem. I looked on the mysql site to see if there was a way to concatenate mysql column names in a where clause but there doesn't appear to be. I was wondering if i can solve my problem with something like indexes but i know very little about these.

Thanks for you help in advance

[skalie]

Need sleep but.......

= defines

== compares

....hope that was relevant

[Simon Mundy]

I wouldn't employ that approach, only for the reason that - in a rare and unpredictable world - there is every possibility that you may have a situation where you could have multiple identical records if you go by name alone.

You would be much better off if you used an 'ID' or similar unique identifier to perform a SELECT on such important data.

So your dropdown menu may contain a list of names, each of which contains the ID of each record, then just use the value of that dropdown in your query.

If that's going to cause you too much grief at this stage, the other way you could do it is:

select * from signin where CONCAT(signin.forename,' ',signin.surname)=$pupilname

This assumes you've a space between the first and last names.

[MadBrowser]

As Simon said, you most certainly want to have some "rec_num" integer field be your primary key and do these lookups using that. That is certainly the standard way to identify records. MySQL lets you set this field as auto-incrementing, so each INSERT automatically gets a new value in the sequence.

It is also much faster for indexing since it is a guaranteed unique value.

Then you'd do something like:

SELECT * FROM signin WHERE rec_num = $rec_num

There are a couple of good mysql books that cover basic database design. I would really recommend getting one and giving it a read... Stuff like this can have a dramatic effect on application performance.

[ntsc]

Cool, thanks for the tips guys. I will definitely look into the id column thing since the idea of duplicate names hadn't occurred to me but its obviously a serious problem.

Thanks again

[timster]

ALTER TABLE tablename ADD id primary key auto_increment FIRST

something like this should add a column of the auto-incrementing numbers, and the FIRST will make it the first column, so it's easier for you to read when you do outputs from the client.

I also believe that if you do this with currently existing data, it will automatically assign id numbers for each record, so you should be good to go.

I might also add that if you had two students with the same name, how would the program know which student to get the report for, if you were using their name as the basis to get the report.

This is why you would need to use ID numbers. Then you would be able to discern between the two.