Existence search over multiple columns

PHPsites

Hello. Was hoping someone can help with the following....

Background: Have a table with a primary key and then numeric columns (ex. 1-30). Once row is found, need to test for the existence of a value within any of the 30 numeric columns.

1) What is the fastest way to accomplish this? Currently read row data into an array and use a loop to test each element's equality to the value. Slow.

2) Could someone recommend a better way to design the table?

Appreciate any help anyone can provide. Thanks a lot.

PHPsites

OK, so I discovered the in_array function. That answers question 1. Anyone have any better ways to design the table to avoid uneven columns? Really not a problem anymore but I am definitely interested in hearing others' responses / ideas. Thanks again.

cgraz

Could you post some information about what you are trying to store and the table structure you currently have? It's hard to propose a table structure based on the information given.

TheDefender

Elaborate on what you mean by "Once row is found"... How are you searching for the row? A properly normalized and indexed table structure will produce results very quickly from a direct query... As cgraz stated, we need more info to go on.

PHPsites

Thanks for the help. Here is some more information. I don't think this database is properly normalized but I am uncertain of how to store this type of information.

Employees are able to make 3 requests to change schedules with another employee which is either approved or declined. If request 1 is declined, employee can make request 2 and on up to 3 requests, 1 at a time. Each time an employee requests, a timestamp is recorded. A timestamp is recorded for each response and a 0 (declined) or 1 (approved). This is table1.

RequestindID ReceivingID Status Request1 Answer1 Request2 Answer2 Request3 Answer3

IDs are the corresponding IDs, status is 0 or 1, and the rest are corresponding timestamps.

In addition, Table 2 stores an ID for each employee (primary key) and IDs of employees that requested a switch with that employee and have not received a response. There can be up to 30 open IDs. The layout for table 2.

ID RequestID1 RequestID2 etc. up to RequestID30

Each time an employee replies, the corresponding RequestID is cleared and table 1 is updated.

I know this is a HORRIBLE way of doing this so would be open to any feedback. Any help would be great. This is my ugliest program and would love to clean it up. Thanks in advance.

Sxooter

If you've got a schema like this, then that's the root of your problem, and it's only going to continue to make your life more difficult. By breaking out answers etc to their own tables and joining them all together, you'll generally find it easier to write these types of queries.

OTOH, using lots of tables joined together can cause problems of their own, in terms of performance, but with proper indexing and tuning and usage, you should be ok.

Are you familiar with the concepts of one to one, one to many, and many to many relationships in a database and how to implement them?

PHPsites

Thanks for the answer. That is what I was afraid of....

I am familiar with db relationship concepts but do not have a huge db background. If i understand correctly, you are proposing a change to something of the sort.

Table 1
Queue (primary id, autoincrement) Requesting ID Receiving ID NumberRequests RequestID AnswerID

Request Table
RequestID (primary, autoincrement) TimeStamp

Answer Table
AnswerID (primary, autoincrement) Response (0,1) TimeStamp

Then, when doing queries,
search table 1 for desired requesting & receiving id
use join on answerid to see if response is 1
if so, exit, else
check numberRequests < 3
if so, allow another request - insert into requesttable &
insert request id into table 1

Is that the general idea? I see how that is much less confusing. Can this be improved even more than my understanding? I appreciate all the assistance.

Oh, one thing I did not solve....how would I best keep each request / answer for historical records, in case ever needed. That is, how can I confirm Employee A made request 1 on June 3rd, request 2 on July 5, and request 3 on August 30th? Seems I missed that piece.

Again, thanks a ton. I'm learning quickly but have a ton to go.

Sxooter

PHPsites wrote:
Thanks for the answer. That is what I was afraid of....

I am familiar with db relationship concepts but do not have a huge db background. If i understand correctly, you are proposing a change to something of the sort.

Table 1
Queue (primary id, autoincrement) Requesting ID Receiving ID NumberRequests RequestID AnswerID

Request Table
RequestID (primary, autoincrement) TimeStamp

Answer Table
AnswerID (primary, autoincrement) Response (0,1) TimeStamp

Then, when doing queries,
search table 1 for desired requesting & receiving id
use join on answerid to see if response is 1
if so, exit, else
check numberRequests < 3
if so, allow another request - insert into requesttable &
insert request id into table 1

Is that the general idea? I see how that is much less confusing. Can this be improved even more than my understanding? I appreciate all the assistance.

Yep. Except that you should be able to do all that in a single query actually, no need for php code except to check the output.

Oh, one thing I did not solve....how would I best keep each request / answer for historical records, in case ever needed. That is, how can I confirm Employee A made request 1 on June 3rd, request 2 on July 5, and request 3 on August 30th? Seems I missed that piece.

Again, thanks a ton. I'm learning quickly but have a ton to go.

Yeah, that's pretty much an audit job. In this case, there's no great need for normalization, you can just make a flat table and stick those values in it. Assuming the format doesn't need to vary, it should be pretty simple.

PHPsites

Sxooter wrote:
Yep. Except that you should be able to do all that in a single query actually, no need for php code except to check the output.

Thanks. That was my intention but I just laid out the logic. That is much cleaner and a much better idea. Thanks a lot Sxooter.

Learned a lot from this.

Sxooter

Be sure and post back when you've got it running, or if you get stuck.