SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 42 of 42
  1. #26
    SitePoint Enthusiast crazylegswilson's Avatar
    Join Date
    Nov 2004
    Location
    oregon
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    time is there so the admin guy can make forms which are only available between certain times as he sees fit.

    these are the relevant:

    Table names
    3 tables- homework_questions, homework_records, record_del_estudiante

    Field names in each table and Field datatype in each table and Sample data from each field in each table
    HQ- home_chapt_num (varchar 30) "1.1", clase_de_esp (varchar 20) "Spanish 131", begin (varchar 12) "1", to_time (varchar 12)"2145916800".

    HR- recid (varchar 30)"35", home_chapt_num_rec (varchar 30)"1.1".

    RDE - id (int - not null this is my index 'sequence' in PostgreSQL)"35", clase_de_esp (varchar 20)"Spanish 131".

    That's it. all the other fields and data really don't matter here as far as i can tell- let me know what i missed if anything!

  2. #27
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Try creating your SQL statement without the Time element first so that you can get some valid results.

    Regarding your tables, am I right in making the following assumptions? ...

    RDE - holds records of StudentID and the Course they are doing.
    HR - holds records of the StudentID and the Chapters they have completed.
    HQ - holds records of the Questions relating to each Course and the Time these questions are available.

    ... If so then we could start with ...

    SELECT * FROM homework_questions HQ
    INNER JOIN record_del_estudiante RDE
    ON HQ.clase_de_esp = RDE.clase_de_esp
    INNER JOIN homework_records HR
    ON HR.recid <> RDE.id
    WHERE RDE.id = $id

    ... wherein $id holds the StudentID value (e.g. 35). See if this gives you valid results - if it does then we can look at factoring the Time part.

    Note: Why is recid in HR a VARCHAR whereas id in RDE is INTEGER? Surely they should both be the same data type?
    Ian Anderson
    www.siteguru.co.uk

  3. #28
    SitePoint Enthusiast crazylegswilson's Avatar
    Join Date
    Nov 2004
    Location
    oregon
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok- I ran that query as you wrote it, I got the following error message:
    "pg_query(): Query failed: ERROR: column hq.clase_de_esp does not exist in" (that page).

    HQ, RDE and HR are just abbreviations I used to simplify my post... i hope that didnt screw you up in making that query.

    Why is recid in HR a VARCHAR whereas id in RDE is INTEGER? Surely they should both be the same data type?
    No reason- would it help to change the data type to int? Does it matter? It's just holding the same number, isnt it? I changed recid in HR to integer... but with my 'old' query, i still get undesireable results, and the same error with your query... I'm doing something wrong, but what?

  4. #29
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The error means exactly what it says - the field clase_de_esp does not exist in the homework_questions table. (I used HQ etc. as reference shortnames in the SQL statement to make it easier to read. This is normal SQL syntax and you will note that the error doesn't say that table HQ does not exist. ) Perhaps you should check the spelling - I simply used exactly what you gave me.

    As regards recid, making it an integer is better in that it maintains consistency across your database.
    Ian Anderson
    www.siteguru.co.uk

  5. #30
    SitePoint Enthusiast crazylegswilson's Avatar
    Join Date
    Nov 2004
    Location
    oregon
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I checked the spelling and changed a couple letters as needed, and got no error message, but I'm still seeing "tests to do" that have been done.

    here is the (spellchecked) query i'm using:
    PHP Code:
      $sql "SELECT * FROM homework_questions HQ
    INNER JOIN record_del_estudiante RDE
    ON HQ.clase_de_espana = RDE.clase_de_esp
    INNER JOIN homework_records HR
    ON HR.recid <> RDE.id
    WHERE RDE.id = 
    $id
    "

    I want to exclude all results where recid (in HR) is equal to $id- so where HR.recid<>RDE.id, don't output results... Now what?

    BTW- thank you for your help so far!

  6. #31
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I'll have a think and see if anything comes to mind ... HR.recid<>RDE.id was simply a stab in the dark.

    Your question is really a SQL issue and not a PHP issue.
    Ian Anderson
    www.siteguru.co.uk

  7. #32
    SitePoint Enthusiast crazylegswilson's Avatar
    Join Date
    Nov 2004
    Location
    oregon
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HR.recid<>RDE.id was simply a stab in the dark
    Well, I appreciate it all the same. I'm really happy to have your help. Is there a way to "AND NOT HR.recid=RDE.id"? also, do you think i really need to query RDE here since i already have that nice $id variable? I mean, I only need the one result, and if $id is already set to say, 35, then...? also, $clase_del_esp is already set... so i dont think i'd need to check that either- I defer to your wisdom, of course. Should I post this problem in the SQL forum?

  8. #33
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Still thinking. In terms of the variables, I was trying to remove the need to have to obtain both values before doing a SQL call (i.e. one call instead of two) but if they are both available then ...

    SELECT * FROM FROM homework_questions
    WHERE clase_de_espana='$clase_del_esp' AND
    home_chapt_num NOT IN(
    SELECT home_chapt_num_rec FROM homework_records
    WHERE recid=$id
    )

    ... is something you can try. This should give you all questions from HQ for class $clase_del_esp where there is no record in homework_records matching recid $id. If this works out then we can start factoring in the Time element.
    Ian Anderson
    www.siteguru.co.uk

  9. #34
    SitePoint Enthusiast crazylegswilson's Avatar
    Join Date
    Nov 2004
    Location
    oregon
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, something happened, but still not what I'm after
    Now no records display! I think you are on the right track! There are enough sample records in each table to provide sufficient info to make this happen, some tests still to do, some done. This is getting interesting- with the previous query, the return was all the records, done or not except the most recently done test- which equates to the first return in the query above it on the page- now it seems somehow the query is being too exclusive. FUN!

    (thank you again!)

  10. #35
    SitePoint Enthusiast crazylegswilson's Avatar
    Join Date
    Nov 2004
    Location
    oregon
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hold the phone!

    This query seems to be (= 'is') doing the right thing:
    Code:
    $sql = "SELECT * FROM homework_questions
    WHERE clase_de_espana='$clase_de_esp' AND
    home_chapt_num NOT IN(SELECT home_chapt_num_rec FROM homework_records WHERE recid = '$id')";
    YAY! That rocks! Now- as you said, can we attack the time element?

    what do you think of this:
    Code:
    $sql = "SELECT * FROM homework_questions
    WHERE clase_de_espana='$clase_de_esp' AND
    home_chapt_num NOT IN(SELECT home_chapt_num_rec FROM homework_records WHERE recid = '$id') AND begin<'$now' AND to_time>'$now'";
    i dont think too much of it becase it doesnt do it right... I set $now to strtotime ("now"), and i have a page that sets "begin" and "to_time" in homework_recods. I can't see how that won't work. the logic is simple enough, isn't it? in HR, begin and to_time are varchar (12)
    Last edited by crazylegswilson; Dec 9, 2004 at 14:40.

  11. #36
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The key is getting the time value (variable) in the same data format as what is stored in the begin and to_time fields.
    Ian Anderson
    www.siteguru.co.uk

  12. #37
    SitePoint Enthusiast crazylegswilson's Avatar
    Join Date
    Nov 2004
    Location
    oregon
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, hopefully it is... i set $now = strtotime ("now") earlier in the page, which returns a nice 10 digit number in Unix, and the db holds the same- Unix times from and to... but it doesn't want to work. Any thoughts as to why it might not work? Would sample data help? Does the data type in the homework_questions need to be something other than 'character varying (12)"?

  13. #38
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Ah! Yes it does. You can't do numeric/time comparisons in SQL with a VARCHAR field (as far as I am aware). You need to make it either a Date/Time type or (more likely for a Unix timestamp) an INTEGER field. If you do that then your existing SQL statement may well work.
    Ian Anderson
    www.siteguru.co.uk

  14. #39
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Did you get this sorted and working correctly?
    Ian Anderson
    www.siteguru.co.uk

  15. #40
    SitePoint Enthusiast crazylegswilson's Avatar
    Join Date
    Nov 2004
    Location
    oregon
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry- i had to take a break! I'm going to change the data type and give it a shot right now! Thanks! (I'll post when i see what the deal is)

  16. #41
    SitePoint Enthusiast crazylegswilson's Avatar
    Join Date
    Nov 2004
    Location
    oregon
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I changed the data type to integer, and what do you know? It worked like a charm! Hooray! This is outstanding!!!! Who would have known it would be this easy (you). You're awsome! Thank you!


  17. #42
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    You're welcome. Glad you got it sorted.
    Ian Anderson
    www.siteguru.co.uk


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •