1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Need help to rewrite query for XML

Discussion in 'PHP' started by tempex, Jul 10, 2012.

  1. #1
    Hello everybody,

    I have a little problem to display Japanese characters with Varchar type data
    All the characters seem to completely disappeared.
    So i tried to use XML as shown in 2nd code below.

    $query="SELECT * FROM XMLTABLE(
    'db2-fn:xmlcolumn(\"TERM.TERM\")/term'
    COLUMNS
        TERM [COLOR=#0000ff]VARCHAR (5000)[/COLOR] PATH 'term' ,
        DEFINITION [COLOR=#0000ff]VARCHAR (5000)[/COLOR] PATH 'definition',
        ) AS T
    Where LOWER(term) like '%$keyword%' 
    ";
    
    $stmt = db2_prepare($conn, $query);
    $result = db2_execute($stmt);
    while($row = db2_fetch_both($stmt)){
    print "\t<tr><td>$row[0]</td><td>$row[1]</td></tr>\n";}
    
    
    Code (markup):

    $query="SELECT * FROM XMLTABLE(
    'db2-fn:xmlcolumn(\"TERM.TERM\")/term'
    COLUMNS
        TERM [COLOR=#ff0000]XML[/COLOR] PATH 'term' ,
        DEFINITION [COLOR=#ff0000]XML[/COLOR] PATH 'definition',
        ) AS T
    Where LOWER(term) like '%$keyword%' 
    ";
    $stmt = db2_prepare($conn, $query);
    $result = db2_execute($stmt);
    while($row = db2_fetch_both($stmt)){
    print "\t<tr><td>$row[0]</td><td>$row[1]</td></tr>\n";
    
    Code (markup):
    And I've got this error message.
    Error message
    Warning: db2_execute(): Statement Execute Failed in .php on line
    Warning: db2_fetch_both(): Column information cannot be retrieved in.php on line

    There is no problem if I remove the
    Where LOWER(term) like '%$term%'
    Code (markup):
    , and the Japanese characters ca be displayed well.

    I guess I have to change the
    Where LOWER(term) like '%$term%'
    expression to go with XML type.

    Any idea how to do that?
    Thanks in advance.
     
    tempex, Jul 10, 2012 IP