Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Thumbs up Unanswered: Error in SQL syntax

    Hi at all,

    Unfortunately I am not a specialist and now am not getting on anywhere.
    Can someone help me? I wish that the query works in this sense.
    Thank's a lot for your help!

    Error:
    MySQL meldet: Dokumentation
    #1064 - 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 '
    ELSE
    round(ps_product.price-(ps_product.price*ps_specific_price.reduction/100,2'

    The Complete Script:

    <?php
    header('Content-type:text/plain');
    //tiny script to generate an export for Domain....
    include('config/......php');
    $dbprefix=_DB_PREFIX_;
    $myshopUrl=".......";
    $myDbConnect = mysql_connect(_DB_SERVER_,_DB_USER_,_DB_PASSWD_);
    if (!$myDbConnect) {
    die('keine Verbindung zur DB möglich: ' . mysql_error());
    }
    $dbSelected = mysql_select_db(_DB_NAME_);
    if (!$dbSelected) {
    die('DB nicht gefunden');
    }
    $result = mysql_query("select ps_product.id_product,ps_manufacturer.name,ps_prod uct.reference,ps_product_lang.name,

    ps_specific_price.reduction_type, (CASE WHEN ps_specific_price.reduction_type = amount THEN
    round(ps_product.price-ps_specific_price.reduction,2),
    ELSE
    round(ps_product.price-(ps_product.price*ps_specific_price.reduction/100,2))END,
    available_now,ean13
    FROM ps_product,ps_product_lang,ps_manufacturer,ps_spec ific_price
    WHERE ps_product_lang.id_product = ps_product.id_product
    AND id_lang = 3 AND
    ps_product.id_manufacturer = ps_manufacturer.id_manufacturer
    AND ps_specific_price.id_product = ps_product.id_product
    AND ps_product.active = 1;");
    )))END,
    if (!$result) {
    die('Ungültige Abfrage: ' .mysql_error());
    }
    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    printf ("$myshopUrl/product.php?id_product=%s~~%s~~%s~~%s~~%s~~%s~~%s\ n", $row[0], $row[1],$row[2],$row[3],$row[4],$row[5],$row[6]);
    }
    mysql_close($myDbConnect);

    ?>

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --
    Code:
    $result = mysql_query("select ps_product.id_product,ps_manufacturer.name,ps_product.reference,ps_product_lang.name,
    
    ps_specific_price.reduction_type, (CASE WHEN ps_specific_price.reduction_type = amount THEN
    round(ps_product.price-ps_specific_price.reduction,2),
    ELSE
    round(ps_product.price-(ps_product.price*ps_specific_price.reduction/100,2))END,
    available_now,ean13
    FROM ps_product,ps_product_lang,ps_manufacturer,ps_specific_price
    WHERE ps_product_lang.id_product = ps_product.id_product
    AND id_lang = 3 AND
    ps_product.id_manufacturer = ps_manufacturer.id_manufacturer
    AND ps_specific_price.id_product = ps_product.id_product
    AND ps_product.active = 1;");
    )))END,
    to this --
    Code:
    $result = mysql_query("SELECT ps_product.id_product
         , ps_manufacturer.name
         , ps_product.reference
         , ps_product_lang.name
         , ps_specific_price.reduction_type
         , CASE WHEN ps_specific_price.reduction_type = amount 
                THEN ROUND(ps_product.price - 
                             ps_specific_price.reduction ,2)
                ELSE ROUND(ps_product.price - 
                            (ps_product.price *
                             ps_specific_price.reduction / 100) ,2)
            END
         , available_now
         , ean13
      FROM ps_product
    INNER
      JOIN ps_product_lang
        ON ps_product_lang.id_product = ps_product.id_product
       AND ps_product_lang.id_lang = 3
    INNER
      JOIN ps_manufacturer
        ON ps_manufacturer.id_manufacturer = ps_product.id_manufacturer
    INNER
      JOIN ps_specific_price
        ON ps_specific_price.id_product = ps_product.id_product 
     WHERE ps_product.active = 1;");
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Posts
    2
    Thank you very mutch for your quick and great answer !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •