Professional Web Applications Themes

Not working?? - MySQL

Hi, Please can someone tell me why this isnt working? INSERT INTO `company link table` (ID,Sub_Product_Headings,Price_Bands) VALUES (677, ( SELECT Item_ID FROM `product index` WHERE CONCAT(`product index`.`Product_Headings`,' ', `product index`.`Sub_Product_Headings`) ='Apparel Leather/suede' ) ,'te') This code works fine (adapted for ADODB) on my test server internally. MySQL version on the live website is "4.0.27-standard". Am I missing something obvious? Thanks A...

  1. #1

    Default Not working??

    Hi,

    Please can someone tell me why this isnt working?

    INSERT INTO `company link table`
    (ID,Sub_Product_Headings,Price_Bands)
    VALUES (677,
    (
    SELECT Item_ID
    FROM `product index`
    WHERE CONCAT(`product index`.`Product_Headings`,' ',
    `product index`.`Sub_Product_Headings`) ='Apparel Leather/suede'
    )
    ,'te')

    This code works fine (adapted for ADODB) on my test server internally.
    MySQL version on the live website is "4.0.27-standard". Am I missing
    something obvious?

    Thanks

    A

    UKuser Guest

  2. #2

    Default Re: Not working??

    On 5 Mar, 10:11, "UKuser" <co.uk> wrote: 

    "Am I missing something obvious?"

    Yes, I think you are missing something obvious. It would seem fairly
    obvious to me that, in order for us to help you find out what is wrong
    with the above, you should tell us what you mean by "this isnt (SIC)
    working".

    Does it give a syntax error? If so what error?
    Does it put the wrong values in?
    What adaptations were done for ADODB that made it work?

    It isn't working, is as useful as a chocolate teapot!




    Captain Guest

  3. #3

    Default Re: Not working??

    Righty:

    ADODB version
    $up2 = $db->execute("
    INSERT INTO `company link table`
    (ID,Sub_Product_Headings,Price_Bands)
    VALUES (677,
    (
    SELECT Item_ID
    FROM `product index`
    WHERE Concat(`product index`.`Product_Headings`,' ',
    ".$db->IfNull('`product
    index`.`Sub_Product_Headings`',"''").") ='Apparel Leather/suede'
    )
    ,'te')
    ");

    Error message:
    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 'SELECT Item_ID
    FROM `product index`
    WHERE CONCAT(`product
    (610 ms taken)

    No entries are put in

    Thats hopefully more like a china teapot.

    A

    On 5 Mar, 11:01, "Captain Paralytic" <com> wrote: 





    >
    > "Am I missing something obvious?"
    >
    > Yes, I think you are missing something obvious. It would seem fairly
    > obvious to me that, in order for us to help you find out what is wrong
    > with the above, you should tell us what you mean by "this isnt (SIC)
    > working".
    >
    > Does it give a syntax error? If so what error?
    > Does it put the wrong values in?
    > What adaptations were done for ADODB that made it work?
    >
    > It isn't working, is as useful as a chocolate teapot![/ref]


    UKuser Guest

  4. #4

    Default Re: Not working??

    On 5 mar, 10:11, "UKuser" <co.uk> wrote: 

    Heres a solution i guess...

    INSERT INTO `company link table`
    (ID,Sub_Product_Headings,Price_Bands)
    VALUES
    (
    SELECT '677',Item_ID,'te'
    FROM `product index`
    WHERE CONCAT(`product index`.`Product_Headings`,' ',
    `product index`.`Sub_Product_Headings`) ='Apparel Leather/
    suede'
    )

    kkmigas@gmail.com Guest

  5. #5

    Default Re: Not working??

    On 5 Mar, 12:09, "UKuser" <co.uk> wrote:
     
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]




    > Righty:
    >
    > ADODB version
    > $up2 = $db->execute("
    > INSERT INTO `company link table`
    > (ID,Sub_Product_Headings,Price_Bands)
    > VALUES (677,
    > (
    > SELECT Item_ID
    > FROM `product index`
    > WHERE Concat(`product index`.`Product_Headings`,' ',
    > ".$db->IfNull('`product
    > index`.`Sub_Product_Headings`',"''").") ='Apparel Leather/suede'
    > )
    > ,'te')
    > ");
    >
    > Error message:
    > 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 'SELECT Item_ID
    > FROM `product index`
    > WHERE CONCAT(`product
    > (610 ms taken)
    >
    > No entries are put in
    >
    > Thats hopefully more like a china teapot.
    >
    > A
    >[/ref]

    Top posting fixed. Please don't top post.

    Hmm, a much better cup of tea!

    Try this instead:
    INSERT INTO `company link table`
    (ID,Sub_Product_Headings,Price_Bands)
    SELECT '677', Item_ID, 'te'
    FROM `product index`
    WHERE CONCAT(`product index`.`Product_Headings`,' ',
    `product index`.`Sub_Product_Headings`) ='Apparel Leather/
    suede'

    which complies with the INSERT SELECT syntax in the manual:
    http://dev.mysql.com/doc/refman/5.0/en/insert-select.html





    Captain Guest

  6. #6

    Default Re: Not working??

    On 5 Mar 2007 04:09:20 -0800, UKuser wrote: 
    >>[/ref]
    > Righty:
    >
    > ADODB version
    > $up2 = $db->execute("
    > INSERT INTO `company link table`
    > (ID,Sub_Product_Headings,Price_Bands)
    > VALUES (677,
    > (
    > SELECT Item_ID
    > FROM `product index`
    > WHERE Concat(`product index`.`Product_Headings`,' ',
    > ".$db->IfNull('`product
    > index`.`Sub_Product_Headings`',"''").") ='Apparel Leather/suede'
    > )
    > ,'te')
    > ");
    >
    > Error message:
    > 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 'SELECT Item_ID
    > FROM `product index`
    > WHERE CONCAT(`product
    > (610 ms taken)
    >
    > No entries are put in
    >
    > Thats hopefully more like a china teapot.[/ref]

    mysql doesn't support subqueries before version 4.1. The version on the
    production server is 4.0.27. What is the mysql version on your test
    machine?

    --
    52. I will hire a team of board-certified architects and surveyors to
    examine my castle and inform me of any secret passages and abandoned
    tunnels that I might not know about.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  7. #7

    Default Re: Not working??

    On 5 Mar, 13:21, "com" <com> wrote: 





    >
    > Heres a solution i guess...
    >
    > INSERT INTO `company link table`
    > (ID,Sub_Product_Headings,Price_Bands)
    > VALUES
    > (
    > SELECT '677',Item_ID,'te'
    > FROM `product index`
    > WHERE CONCAT(`product index`.`Product_Headings`,' ',
    > `product index`.`Sub_Product_Headings`) ='Apparel Leather/
    > suede'
    > )- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    The INSERT SELECT syntax does not have the VALUES keyword. See my post
    above.

    Captain Guest

  8. #8

    Default Re: Not working??

    On 5 Mar, 14:48, "Captain Paralytic" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > The INSERT SELECT syntax does not have the VALUES keyword. See my post
    > above.[/ref]

    thanks for your help Captain - that fixed it - wierd you have to
    remove the VALUES keyword.

    UKuser Guest

  9. #9

    Default Re: Not working??

    On 6 Mar, 12:27, "UKuser" <co.uk> wrote: 

    Guess it depends on your definition of "wierd". The manual says that
    that is the correct syntax for the INSERT ... SELECT instruction and
    that's good enough for me!

    Captain Guest

Similar Threads

  1. #38816 [Opn]: PHP code that was working perfectly recently stopped working.
    By mtoohee at gmail dot com in forum PHP Programming
    Replies: 0
    Last Post: September 13th, 09:41 PM
  2. Replies: 0
    Last Post: November 26th, 06:47 AM
  3. Data not working on Label but is working in Datagrid
    By KetanS in forum Macromedia Flex General Discussion
    Replies: 4
    Last Post: June 11th, 03:56 AM
  4. Working TableStyle Not Working on a Second DataGrid
    By psibertrip in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: May 7th, 04:57 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139