atomicrabbit

After a bit of research, I found this: Source: http://dev.mysql.com/doc/refman/5.0/en/join.html So after modifying my query and adding the brackets around the "employees, videos", I got this: SELECT employees.id FROM (employees, videos) LEFT OUTER JOIN viewhistory ON viewhistory.employeeId = employees.id AND viewhistory.videoId = videos.id WHERE viewhistory.id IS NULL ... which worked like a charm! It returns all employees that have at least ONE unwatched video.

I've tried this based on a suggestion by someone, but I get an error: #1054 Unknown column 'employees.id' in 'on clause' SELECT employees.id FROM employees, videos LEFT OUTER JOIN viewhistory ON viewhistory.employeeId = employees.id AND viewhistory.videoId = videos.id WHERE viewhistory.id IS NULL No idea why!

Hey guys, I'm working on a small project. Basically employees need to watch specified videos and when they do, it will be noted in a table. But if they do not, they will get reminders of what videos they haven't watched. I have most of the project working, but what I can't figure out is how to query the database to return all the employees that have any unwatched videos. Check out the following tables. employees id firstName lastName email --------------------------------------------------- 1 John Smith a@b.com 2 Jane Doe b@c.com 3 Bob Ross c@d.com 4 Steph Smith d@e.com videos id name url --------------------------------------------------- 1 whatever http://domain.com/video1 2 something http://domain.com/video2 viewhistory id employeeId videoId watchDate --------------------------------------------------- 1 1 2 2011-03-17 2 2 2 2011-03-17 3 3 1 2011-03-17 4 2 1 2011-03-17 So obviously the employees table holds the employees names, the videos table holds the videos and the viewhistory table keeps entries whenever an employee watches a video. SO if they watch a video, and entry goes in with the employee ID and the video ID they watched. So what statement would I use to determine all the employees that have ANY unwatched videos Based on the above tables, the query should return John, Bob & Steph because Jane is the only employee that watched BOTH videos.

ok, sorry for bugging you guys. I figured out how to close the IE window after it was finished. Here's the final code: 'Create IE obect Set IE = CreateObject("InternetExplorer.Application") set WshShell = WScript.CreateObject("WScript.Shell") 'Go to webstie IE.Navigate "http://www.somewebsite.com/form.php" IE.Visible = True Wscript.Sleep 2000 'Submit form call IE.Document.Forms("formName").elements("Submit").click Wscript.Sleep 2000 'Close IE objIE.Quit Set objIE = Nothing

got it to work now. Not sure why but I had to make a slight change to the last line of code in order for the form to be submitted properly: call IE.Document.Forms("formName").elements("Submit").click Now all I need to figure out how to close the IE window after it's done. How do I do that?

Anyone have code to do this? I found this code, but for some reason it's not getting/submitting the form properly Set IE = CreateObject("InternetExplorer.Application") set WshShell = WScript.CreateObject("WScript.Shell") IE.Navigate "http://www.somewebsite.com/form.php" IE.Visible = True Wscript.Sleep 2000 call IE.Document.Forms(3).Submit() It is the 4th form on the page, but i get the following error: Object doesn't support this property or method 'IE.Document.Forms(...).Submit' Any ideas?

The thing I worry about is I will probably want to have a blob-type datatype, so if I string and entire row into one value, it may get quite big. Assuming I would like to query individual values, how would I structure the db??

Is this the best solution even if there will be a large amount of traffic and users?

I just found a user-made function in the comments of this page: http://php.net/manual/en/function.sort.php It sorts multidimensional arrays by specified keys. This might help me.

I'm trying to think how I would be able to programatically order the results if each row is one entry, delimited. I could split them by the delimiter into an array and maybe make a multidimensional array, but still, ordering it may be complicated in php.

Now that I read over my last post, I basically want to make a spreadsheet-like interface but definitely UNLIKE excel in terms of layout/style

Yea, I was thinking about throwing each entire row of info into 1 entry with delimiters, but, I want to be able to order the lists by any of the custom columns, so I don't know if that would work. The problem with separate rows for each column of each row is the table will get exponentially large, depending on the number of custom lists made. I also want to be able to order the list by a custom order. The structure of the database is extremely important for the functionality I want to add. That's why I want to put enough thought into it before even thinking about starting coding. Here is the functionlaity I want to add: - create custom lists with custom headings & datatypes - order the rows by column or custom order - be able to re-order the column headings Maybe I could potentially store each row with delimiters and programatically order them (in php) depending on the users preference. If I were to do that, and keeping the above points in mind, maybe I could do something like this for the list contents: ======================================= list_contents id contents --------------------------------------- 1 1=Bob|2=Blue|3=2|4=1909/12/12 2 1=Franke|2=Red|3=16|4=1970/10/20 3 1=Jane|2=Pink|3=7|4=1962/1/29 --------------------------------------- where x= is the id of the column heading. I would just have to trap for "=" and "|" in my code. What do you think?

Sorry for the PM, got a little anxious. So you're saying just store the data types in one table, and make another table that contains the column headers? Then what? How would you suggest each user's columns be stored? This is what I'm asking. I would like each user to be able to create multiple lists that only they have access to. This is what I was originally thinking before coming here, I just wasn't sure if this is the most efficient way to do it. Tables: lists: id, user_id, name users: id, name, etc column_types: id, type list_headers: id, list_id, column_type_id, name list_contents: id, list_header_id, content So then when I user goes to create a list 1) the list gets added to the lists table 2) then they make the column headings using the options available from the column_types table. Those headings get inputted to list_headers as separate rows identified by the list_id 3) They they enter the contents of the list which get inputted as separate rows to list_contents, identified by list_header_id. So for the sake of examples, if the user had a table like this: People ========================================= Name Colour Number Birthday ------------------------------------------------ Bob Blue 2 1909/12/12 Frank Red 16 1970/10/20 Jane Pink 7 1962/1/29 The tables would look like this ======================================= lists id user_id name --------------------------------------- 1 1 People --------------------------------------- ======================================= users id name --------------------------------------- 1 Someone --------------------------------------- ======================================= column_types id type --------------------------------------- 1 string 2 number 3 date --------------------------------------- ======================================= list_headers id list_id column_type_id name --------------------------------------- 1 1 1 Name 2 1 1 Colour 3 1 2 Number 4 1 3 Birthday --------------------------------------- ======================================= list_contents id list_header_id contents --------------------------------------- 1 1 Bob 2 2 Blue 3 3 2 4 4 1909/12/12 5 1 Franke 6 2 Red 7 3 16 8 4 1970/10/20 9 1 Jane 10 2 Pink 11 3 7 12 4 1962/1/29 --------------------------------------- Is this the best way of doing it?

what do you mean, can you elaborate?

Ok so I want users of a web app to be able to create lists of whatever with custom column headings. So they would make a list like Name Colour Number Year where the above four words are column headings for a custom list they make. The number of headings should be variable. And the user would be able to add rows to the columns they just made. How would I structure something like that in a database? If you have any questions just ask. I tried to explain it as best as possible.