SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    canada
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is there a way to put a if statement within the where close??

    Hello

    I have the following MySQL statement and I was wondering how to go about creating a IF like statement round tbl_merchant.uniquemerchantid = tbl_questions.merchantid"); .

    looking at: AND tbl_merchant.uniquemerchantid = tbl_questions.merchantid");

    example
    if not empty ( tbl_questions.merchantid")
    { tbl_merchant.uniquemerchantid = tbl_questions.merchantid"}
    else
    {ignore}

    As of now the record is skipped due to the AND in the statement, not meeting the criteria.

    PHP Code:
    $stmt $this->dbcon1 ->query("SELECT tbl_merchant.merchantname,  tbl_questions.projectid, 
        tbl_questions.merchantid, tbl_questions.question, tbl_questions.date
            From tbl_questions, tbl_merchant 
            WHERE tbl_questions.projectid = '
    $this->projectId
            AND tbl_merchant.uniquemerchantid = tbl_questions.merchantid"
    );

            
    $this->questionArray $stmt->fetchAll(PDO::FETCH_ASSOC); 

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    allow me to introduce you to the left outer join
    Code:
    SELECT tbl_merchant.merchantname
         , tbl_questions.projectid
         , tbl_questions.merchantid
         , tbl_questions.question
         , tbl_questions.date 
      FROM tbl_questions
    LEFT OUTER
      JOIN tbl_merchant  
        ON tbl_merchant.uniquemerchantid = tbl_questions.merchantid
     WHERE tbl_questions.projectid = '$this->projectId'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    canada
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    allow me to introduce you to the left outer join
    Code:
    SELECT tbl_merchant.merchantname
         , tbl_questions.projectid
         , tbl_questions.merchantid
         , tbl_questions.question
         , tbl_questions.date 
      FROM tbl_questions
    LEFT OUTER
      JOIN tbl_merchant  
        ON tbl_merchant.uniquemerchantid = tbl_questions.merchantid
     WHERE tbl_questions.projectid = '$this->projectId'
    Hi r937

    it didn't work...
    the 4th record in tbl_question.merchantid is empty as it should be but the tbl.questions.comments(rec 4) seems to repeated itself 50 to 100 times.
    it happens if tbl_question.merchant field is empty.


    $stmt = $this->dbcon1 ->query("SELECT
    tbl_merchant.merchantname,
    tbl_questions.projectid,
    tbl_questions.merchantid,
    tbl_questions.question,
    tbl_questions.date
    FROM tbl_questions
    LEFT OUTER
    JOIN tbl_merchant
    ON tbl_merchant.uniquemerchantid = tbl_questions.merchantid
    WHERE tbl_questions.projectid = '$this->projectId' ");
    part of the output
    Array
    (
    [0] => Array
    (
    [merchantname] => Cakewalk
    [projectid] => 096675edc895d5d03667e15c47d94e6651559e5e8ec36
    [merchantid] => 666ee17c24f8c73d25be1dbf2e85ea6951537ab7da5f4
    [question] => 1-0How big is your kitchen?
    [date] => 2013-04-26 11:14:00
    )

    [1] => Array
    (
    [merchantname] => Borland
    [projectid] => 096675edc895d5d03667e15c47d94e6651559e5e8ec36
    [merchantid] => ecb6bfe5ae52b191689adc1e41cd875c51733f8108552
    [question] => 2-1 are there tiles installed in your kitchen and when where they installed?
    [date] => 2013-04-26 11:14:22
    )

    [2] => Array
    (
    [merchantname] => Borland
    [projectid] => 096675edc895d5d03667e15c47d94e6651559e5e8ec36
    [merchantid] => ecb6bfe5ae52b191689adc1e41cd875c51733f8108552
    [question] => 3-2 more test questions and answerthere tiles installed in your kitchen and when where they installed?
    [date] => 2013-04-26 11:14:41
    )

    [3] => Array
    (
    [merchantname] => Microsoft
    [projectid] => 096675edc895d5d03667e15c47d94e6651559e5e8ec36
    [merchantid] =>
    [question] => 4-3 replay from the person posted the project 1
    [date] => 2013-04-28 16:24:25
    )

    [4] => Array
    (
    [merchantname] => Adobe
    [projectid] => 096675edc895d5d03667e15c47d94e6651559e5e8ec36
    [merchantid] =>
    [question] => 4-3 replay from the person posted the project 1
    [date] => 2013-04-28 16:24:25
    )

    [5] => Array
    (
    [merchantname] =>
    [projectid] => 096675edc895d5d03667e15c47d94e6651559e5e8ec36
    [merchantid] =>
    [question] => 4-3 replay from the person posted the project 1
    [date] => 2013-04-28 16:24:25
    )


    ...

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    My guess is you have multiple rows in the merchant table with uniquemerchantid empty

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    canada
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes there are blanks in the database..

    solution?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by robin01 View Post
    yes there are blanks in the database..

    solution?
    i hate to have to be the one who says it...

    ... but the solution is to change the blanks to something else
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •