SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    not displaying results correctly

    I have a sql statement that concatenates 2 fields:

    $sql = "SELECT concat(t1.trainDate, ' & ', t2.trainDate) as trainDate FROM employees, training t1 LEFT JOIN training t2 ON t1.eid = t2.eid WHERE t1.training = 'level1' and (t2.training = 'level2' OR t2.training IS NULL) AND t1.eid = employees.id AND employees.empNo= " . $dbRow['empNo'];
    $dateResult = mysql_query($sql) or die(mysql_error());
    $dateRow = mysql_fetch_assoc($dateResult);

    Right now it's only displaying the record if there are dates for level1 and level2. shouldn't this part of the code (t2.training = 'level2' OR t2.training IS NULL) display all records for level1 date even though level2 is null?

  2. #2
    SitePoint Member
    Join Date
    Oct 2004
    Location
    Miami Lakes
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem lies here "concat(t1.trainDate, ' & ', t2.trainDate)" if one of the values is NULL, concat returns NULL. Which would be why it only shows records with both values.

  3. #3
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how would i correct this?

  4. #4
    SitePoint Member
    Join Date
    Oct 2004
    Location
    Miami Lakes
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would say just select the two columns separately like this:

    $sql = "SELECT t1.trainDate as t1trainDate, t2.trainDate as t2trainDate FROM employees.....

    and then join them with php.

    Inside your loop, when you ware echoing the results, you can just do:

    echo $dateRow['t1trainDate']." & ".$dateRow['t2trainDate'];

    or you could put a conditional in there and only echo the & if there was a second date.

  5. #5
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's not working...it does display the dates for records that have both levels, but then it will just display & for all the other records.


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
  •