Multi-dimensonal arrays....

Anon

Hi,

Ok, this is a bit complicated to explain so bear withe me...

I have several tables in a mySQL database which I need to grab some information from. The criteria is to find out all jobs that haven't been flagged, but this can't be done with just one select statement.

So, I've got one query that'll give me all jobs, and another query will give me all jobs that have been flagged. The idea being to take the results of each query, and take the flagged jobs from the total jobs to leave unflagged jobs?

I was going to do this using a multi dimensional array, so it'd go (in theory) something like this....

SQL Query results for open jobs
Loop
Add jobid number to array1
Add all field information to array3
End Loop

SQL Query results for open flagged jobs
Loop
Add jobid number to array2
Add all field information to array 3
End Loop

Compare array1 to array2, whatever's left will be unflagged jobs.

Then pull out from array3 all that's left over from the above step, and print into a HTML table.

I'm having problems creating the array3 to contain all the data coming in from mysql. If anyone has any ideas on how to do this, or if there's a quicker/easier way for a php newbie like me to achieve this then it'd be much appreciated 🙂

Many Thanks,
Craig

Anon

Any reason you can't just query for open unflagged jobs directly from the database? Just have something similar to query 2, except:

select * from table where open = true AND flagged != true;

That should do it, unless there is something I don't know. There has to be some relation so if you gave me the table descriptions it would be easier to create and answer.

Chris King

Anon

I can't see any way of querying it for unflagged jobs, because a flagged job is a job that has a comment. Comments are stored in a table called "comments", so the only different between flagged and not flagged is an entry (or several) in the comments table. So unless there's a way to say "select * where a record with matching id doesn't exist in a table" then I don't know how to do it?

I'll get description of the tables from the mysql server so you can take a look, but I can't see how it can be done all from a select statement.

Anon

It can be done as follows, assuming:

the record table has a field recordid and other descriptive fields, and the comment table has a foreign key recordid associated with that the recordid in the record table.

select * from records LEFT JOIN comments ON comments.recordid = records.recordid WHERE ISNULL(comments.recordid);

That should pull what you want.

Hope that helps.

Chris King

Anon

Ok, so here's the tables:

Table: Comments
job_id
tstamp
analyst
summary
detail

Table: Jobs
job_id
analyst
summary
detail
state

Table: Job_track
job_id
tstamp
analyst
type

So this is the query I'm using to select all jobs with comments...

select jobs.job_id as 'id', job_track.tstamp as 'opened', job_track.type as 'type', comments.tstamp, comments.summary from jobs,
job_track, comments where job_track.job_id = jobs.job_id and comments.job_id = jobs.job_id and jobs.state = 'open' and
job_track.tstamp < '20010108100000' group by id order by id,opened;

Then to get all jobs without comments, it'd be something like....

select jobs.job_id as 'id', job_track.tstamp as 'opened', job_track.type as 'type', comments.tstamp, comments.summary from jobs,
job_track, comments where job_track.job_id = jobs.job_id and ISNULL(comments.job_id) and jobs.state = 'open' and
job_track.tstamp < '20010108100000' group by id order by id,opened;

It can't be that easy? 😉

Thanks,
Craig