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.


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

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
[B]LEFT OUTER[/B]
  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
    )

[B][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
    )[/B]

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

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