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

[robin01]

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.

$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); 

[r937]

allow me to introduce you to the left outer join

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'

[robin01]

allow me to introduce you to the left outer join

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
)

...

[guido2004]

My guess is you have multiple rows in the merchant table with uniquemerchantid empty

[robin01]

yes there are blanks in the database..

solution?

[r937]

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