Query Boolean error

Question

I have a simple query, but I'm looking for the error for some time now. The only thing I can think of that it would have a problem with connecting to the database, but that can't be right, because it is working on other parts of the website.

This is the db-connection:

$dbwebwinkel = mysql_connect("localhost", "abcdef", "123456") or die(mysql_error()); 
$db1 =mysql_select_db('abcdef', $dbwebwinkel) or die(mysql_error());

This is the error message:

There is something wrong in the search-query ( SELECT prod_omschrijving.producttitel, prod_omschrijving.idproduct AS 'idproductom', producten.idproduct AS 'idproductprod', producten.productlink FROM prod_omschrijving FULL OUTER JOIN producten ON producten.idproduct=prod_omschrijving.idproduct WHERE prod_omschrijving.idproduct IS NULL OR producten.idproduct IS NULL)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN producten ON producten.idproduct=prod_omschrijving.idproduct W' at line 8

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/s010485/domains/jcsl.nl/public_html/medewerkers/paginas/boekingen/producten.php on line 61

This is the script.

$qry = "
SELECT
    prod_omschrijving.producttitel,
    prod_omschrijving.idproduct AS 'idproductom',
    producten.idproduct AS 'idproductprod',
    producten.productlink
 FROM
    prod_omschrijving
 FULL OUTER JOIN 
    producten 
 ON 
    producten.idproduct=prod_omschrijving.idproduct
 WHERE
    prod_omschrijving.idproduct IS NULL
 OR
    producten.idproduct IS NULL";

$sql = mysql_query($qry, $dbwebwinkel);
$sql = mysql_query($qry, $dbwebwinkel);
if($sql === false)
{
    echo (" Er gaat iets mis in de zoekquery (".$qry .")".mysql_error($dbwebwinkel));
}
else
{
    WHILE ($lijstje = mysql_fetch_array($sql))
    {
        echo $lijstje['idproductom'];
    }
}

i don't read that language the error is in. we are an english site. — Daniel A. White, May 24 '14 at 23:17
Sorry Er gaat iets mis in de zoekquery Means There is something wrong in the searchquery — Coolen, May 24 '14 at 23:19
There is no FULL OUTER JOIN in mysql - and note that php's mysql_ API is deprecated — Strawberry, May 24 '14 at 23:26
thank you, well I have a serious problem about that mysql. I think I have about 10.000 used on my site. Going to be some work to alter them all. I did not know full outer join doesn't work, it was not a very important script, so better leave it alone then. — Coolen, May 24 '14 at 23:33

score 1 · Accepted Answer · answered May 24 '14 at 23:44

You can do what you want with a union all and two outer joins:

SELECT o.producttitel, o.idproduct AS idproductom,
       p.idproduct AS idproductprod, p.productlink
FROM prod_omschrijving o LEFT OUTER JOIN 
     producten p
     ON p.idproduct = o.idproduct
WHERE p.idproduct IS NULL
UNION ALL
SELECT o.producttitel, o.idproduct AS idproductom,
       p.idproduct AS idproductprod, p.productlink
FROM prod_omschrijving o RIGHT OUTER JOIN 
     producten p
     ON p.idproduct = o.idproduct
WHERE o.idproduct IS NULL;

I added table aliases to make the query more readable.

Query Boolean error

1 Answers1