Jump to content

AnalyzeThis

Members
  • Posts

    17
  • Joined

  • Last visited

AnalyzeThis's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. Each course refered in tanning_attendance_records refers to a id of a unique course. Each uniquie course contains a name which may contain either the words "GRAIN", "VEGITABLE", or "FRUIT". If it does contain those words then they have predermined expirations dates. I want to return only the most recent GRAIN, VEGITABLE, and FRUIT for each users and all the unclassified ('?'). If they have the words GRAIN, VEGITABLE, FRUIT then they have a spefific expiration date. I could also rework the tanning_courses database to feature a `expiration_date` field, which is making more sense now.
  2. Also there is a relationship between `tanning_attendance_records`.`course` and `tanning_courses`.`ID`
  3. tanning_attendance_records ===================================== ID (Primary Key) | name | group | course | date | etc. tanning_courses ===================================== ID (Primary Key) | name | description | title | etc. Person attends a course on a date. The course is only valid for a certain amount of time (VEGITABLE 10 years, GRAIN 20, FRUIT 30 years). I want to return the most recent of each type of course taken by the same person and let them know when that course is going to expire and have it ordered to show that the most recent expiration dates. Because the course will be titled "GRAIN VTE 100" or "GRAIN VVT 100" there is a if statement. Please let me know if you need anything else. Thanks in advance.
  4. I am trying to return the correct ID, the persons name, their , their most recent date taking a tanning course, for each type of "tanning course" . What I have below works only when the tanning courses are entered in correct order. This is not always the case however. What I think I need is something like WHERE `ID` =(SELECT MAX(...) FROM ...). But am really unsure how to go about it due to the "complex" statement. SELECT `tanning_courses`.`name` AS `course_name`,max(`tanning_attendance_records`.`ID`) AS 'cnid', `tanning_attendance_records`.`group`, `tanning_attendance_records`.`name` AS `person_name`, DATE_FORMAT(max(`date`), '%m/%d/%Y' ) AS `date`, IF(`tanning_courses`.`name` LIKE '%VEGITABLE%', DATE_ADD(max(`date`),INTERVAL 10 YEAR), IF(`tanning_courses`.`name` LIKE '%FRUIT%', DATE_ADD(max(`date`),INTERVAL 30 YEAR), IF(`tanning_courses`.`name` LIKE '%GRAIN%', DATE_ADD(max(`date`),INTERVAL 20 YEAR), '?'))) AS `expiration` FROM `tanning_attendance_records` LEFT JOIN `tanning_courses` ON `tanning_attendance_records`.`course` = `tanning_courses`.`ID` WHERE `group` = '{$client["ID"]}' AND passed = 1 GROUP BY `person_name`, `course_name` ORDER BY `expiration` ASC LIMIT {$set_limit}, {$limit}" Thank you in advance for any assisstance!
  5. Good morning, I am working on the database structure of a video system (basically ). This system would keep track of clients views of a video and list who watched the video. Accounts Database ========================= Accounts | type | name | etc. Video Database ========================= Name | title | type | etc. Only videos and viewers of the same time will have access to the video. I want to be able to see if a viewer has watch a video and list all viewers. What what I am wondering is where to put the views records? Should I create a view databases Views database ========================= account | video | Thanks for any assistance.
  6. I wanted to make certain this seemed to of work: $DB02->Query("SELECT `a`,`b`.`ID` AS 'c' , `d` , `e` , `f` , DATE_FORMAT( max(`g`), '%m/%d/%Y' ) AS `date` FROM `h` LEFT JOIN `i` ON `a`.`b` = `x`.`ID` WHERE `a` = '{$x["ID"]}' AND y = 1 GROUP BY `a`, `b` ORDER BY `c` ASC LIMIT {$set_limit}, {$limit}");
  7. I'm thinking it maybe something like: Select name, type, max(timestamp) GROUP BY name, type
  8. Here is the database (basically ) =================== | name | type | timestamp ==================== | Brain | Blue Car | 1/2/2012 | Brain | Blue Car | 2/2/2012 | Brain | Red Car | 3/2/2013 | Fred | Blue Car | 2/2/2012 | Fred | Red Car | 2/2/2012 | Fred | Red Car | 1/2/2010 | Brenda | Red Car | 2/2/2012 | Wendy | Red Car | 1/2/2013 I want to return a list where only the distinct name and type is returned with only the most recent timestamp e.g. =================== | name | type | timestamp ==================== | Brain | Blue Car | 2/2/2012 | Brain | Red Car | 3/2/2013 | Fred | Blue Car | 2/2/2012 | Fred | Red Car | 2/2/2012 | Brenda | Red Car | 2/2/2012 | Wendy | Red Car | 1/2/2013 I do not want to delete any old records or create a new table. I just want to run a query within php to obtain this. I'm a little brain dead at the moment and googling isn't providing much use. Thank you!
  9. Still need to optmize it but I figured it out. $i = 0; $count = 0; $gen = array($records[0]); $temp = array_keys($gen[0]); for($x=0; $x<count($temp); $x++){ $gen[0][$temp[$x]] = NULL; } while ($mydate < $timespan["end"]){ $mydate = strtotime ("+$i month", $timespan["start"]); if ($records[$count]["Month"] == date("F", $mydate)){ $count++; } else { $gen[0][0] = date("F", $mydate); array_splice($records, $count, 0, $gen); } $i++; }
  10. Hi, I am trying to insert a custom record into a assocative array made from a mysql_fetch_assoc. The amount of rows differs depending on what is fetched sometimes there is very few. I need to insert a row into the fetch where there is none. I.e. there are no records for Oct or Dec so we need to make some up and place them into the array. So I was taking a array from it, i.e. Nov and then trying to modify that array to be "Jan." and all "0"s and then array_splice($records, $count, 0, $gen) it back into the array where needed; which is working fine. What I am having trouble with is changing the values within the one line that I took to insert back after modified. +-------+----------+------- Month | bla | bla | etc.. +-------+----------+------- | Oct | 0 | 0 | Nov | 1 | 0 | Dec | 0 | 0 +-------+----------+------- The assocative array print_r looks something like: gen = Array ( [0] => Array ( [0] => January [Month] => January [1] => 2012 [Year] => 2012 [2] => 0.103813 [topping] => 0.103813 [3] => 91.52665 [topping1] => 91.52665 [4] => 33.31605 [topping2] => 33.31605) How would I go about changing all the fields in the array to "0" and the Month field to "xxx"? I am having trouble working with this data type due to its structure.
  11. I used a loop for each "SHOW COLUMNS FROM" to make it dynamic. I understand why third normal makes sense. However, it is a square peg in a round whole for this project.
  12. Thanks for all the asstance. I figured it out in PHP then went back through and figured it out in MYSQL. As for those who said there is no SQL way to do it... SELECT CONCAT_WS(',', IF(COUNT(`Materials`.`topping1` )>0, '`Materials`.`topping1`' ,NULL), IF(COUNT(`Materials`.`topping2` )>0, '`Materials`.`topping2`' ,NULL), IF(COUNT(`Materials`.`topping3` )>0, '`Materials`.`topping3`' ,NULL), etc... ) FROM {fields} The run query on results. SELECT {results} from {fields} *silence*
  13. Hmmm I see what your are saying. I disagree with the database being incorrectly designed, it has gone through a normalization process. But then again I am telling you "toppings" when it really isn't so simple as "toppings." I currently trying a PHP excluded array by field function to select which columns to include, having troubles but pushing through. What I was really wondering is there SQL way to exclude all NULL columns from a search. IFNULL, <>, WHEREs do not seem to work?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.