59 users online (1 members and 58 guests)  


  Results 1 to 8 of 8

Related

  1. how to set up Perl with MYSQL    Forum: CGI Perl Forum
    Replies: 0
  2. Replies: 1
  3. Replies: 4
  4. Replies: 2
  5. Perl dbi with mysql problem    Forum: CGI Perl Forum
    Replies: 1
  1. #1
    me8042's Avatar
    New User

    Status
    Offline
    Join Date
    Jun 2009
    Posts
    4

    perl and mysql-compare 2 columns

    Hi everybody,
    I want to compare 2 tables contents. My tables are:
    Table1
    ------------------
    ID || Value
    ------------------
    1 || forum_list1
    2 || Message_list1
    3 || Search1
    4 || Search5
    5 || Search11
    6 || Log_out
    7 || LOG

    Table2
    --------------------------------------------
    ID || Value1 || Value2
    ---------------------------------------------
    1 || rd_forum_list1_no ||
    2 || wr_Message_list1_yes ||
    3 || rd_Search1_no ||
    4 || rd_Search5_no ||
    5 || rd_Search11_no ||
    6 || rd_Log_out_no ||
    7 || rd_LOG_no ||
    8 || wr_forum_list1_no ||
    9 || rd_Message_list1_yes ||
    10 || 123_Search1_456 ||
    11 || 123_Search11_456 ||

    I want to compare both the tables content and if the contents are similar then place the ID of table no. one in Table2.Value2 but discard all the rows with "wr_". Also note that in case of Search1 and Search11, I want the comaprison very exact as

    Table2
    --------------------------------------------
    ID || Value1 || Value2
    ---------------------------------------------
    1 || rd_forum_list1_no || 1
    2 || wr_Message_list1_yes ||
    3 || rd_Search1_no || 3
    4 || rd_Search5_no || 4
    5 || rd_Search11_no || 5
    6 || rd_Log_out_no || 6
    7 || rd_LOG_no || 7
    8 || wr_forum_list1_no ||
    9 || rd_Message_list1_yes || 2
    10 || 123_Search1_456 || 3
    11 || 123_Search11_456 || 5

    I tried implementing this by following code:
    $query = "SELECT Table1.value, Table2.value1 FROM Table1,Table2 WHERE Table2.value1 LIKE CONCAT('%',Table1.value,'%')";
    $result =mysql_query($query) or die(mysql_error());
    while(@row = mysql_ftech_array($result))
    {
    $updatetable2= UPDATE Table2.value2 SET Table2.value2 =(@Table1.value);
    }

    But its not working.. please help..
    thanks in advance

  2. #2
    jthayne's Avatar

    Status
    Offline
    Join Date
    Aug 2008
    Location
    Texas
    Posts
    508

    Re: perl and mysql-compare 2 columns

    Quote Originally Posted by me8042 View Post
    $query = "SELECT Table1.value, Table2.value1 FROM Table1,Table2 WHERE Table2.value1 LIKE CONCAT('%',Table1.value,'%')";
    $result =mysql_query($query) or die(mysql_error());
    while(@row = mysql_ftech_array($result))
    {
    $updatetable2= UPDATE Table2.value2 SET Table2.value2 =(@Table1.value);
    }
    Two things.

    First, when you declare something is not working, please tell us what is actually happening. Are you getting errors, unexpected results, or is nothing apparently happening at all? This will help us narrow down the issue more quickly.

    Second, are you wanting to write in Perl or PHP? The code above is PHP code (with some Perl syntax mixed in), but in the title you say you want it in Perl. Which are you wanting?

  3. #3
    me8042's Avatar
    New User

    Status
    Offline
    Join Date
    Jun 2009
    Posts
    4

    Re: perl and mysql-compare 2 columns

    1.) I want to write the code in perl language...
    2.) after compling the code, when i check the data in mysql db. i cant see anything. Either its not entering the while loop or am not able to pass the value correctly.
    $updatetable2= UPDATE Table2.value2 SET Table2.value2 =(@Table1.value);
    }

  4. #4
    jthayne's Avatar

    Status
    Offline
    Join Date
    Aug 2008
    Location
    Texas
    Posts
    508

    Re: perl and mysql-compare 2 columns

    Quote Originally Posted by me8042 View Post
    1.) I want to write the code in perl language...
    Good to know.

    Quote Originally Posted by me8042 View Post
    2.) after compling the code, when i check the data in mysql db. i cant see anything. Either its not entering the while loop or am not able to pass the value correctly.
    $updatetable2= UPDATE Table2.value2 SET Table2.value2 =(@Table1.value);
    }
    As I mentioned before, the reason for this is that you are using PHP functions which Perl will not recognize and has no idea what to do with. The quickest way to get this resolved is for you to post the code from the file. You need to make sure you are initializing the file correctly as well as calling the correct libraries. Once that is established, we can continue with correcting the code.

    A good introductory to getting this working is here.

  5. #5
    me8042's Avatar
    New User

    Status
    Offline
    Join Date
    Jun 2009
    Posts
    4

    Re: perl and mysql-compare 2 columns

    Thanks for ur reply..
    Still that problem is there.

    $query = "SELECT Table1.value, Table2.value1 FROM Table1,Table2 WHERE Table2.value1 LIKE CONCAT('%',Table1.value,'%')";
    $result =mysql_query($query) or die(mysql_error());
    while(@row = mysql_ftech_array($result))
    {
    $updatetable2= UPDATE Table2.value2 SET Table2.value2 =(@Table1.ID);
    }

    When i execute this.. it show an error that
    Global symbol "@Table1" requires explicit package name at ./filename

    Also i checked the syntax. It seems to be correct to my knowledge.
    Please guide..

  6. #6
    jthayne's Avatar

    Status
    Offline
    Join Date
    Aug 2008
    Location
    Texas
    Posts
    508

    Re: perl and mysql-compare 2 columns

    First things first.

    mysql_query(), mysql_error(), and mysql_fetch_array() are NOT Perl functions, they are PHP functions. The only exception to this is if an extension you are using sets up those functions (I have not seen one yet). So you will not get it working until you fix that and use Perl functions.

    Second, the error you are getting is a Perl error that is happening because you did not put your update query in quotes.

    All in all, the code needs to be rewritten from scratch. Go to the link I gave in my last post, and start there. Once you have that working, then we can alter it to do what you are looking for. Until that point, there are going to be way too many changes made to make either yours or my time worthwhile.

  7. #7
    me8042's Avatar
    New User

    Status
    Offline
    Join Date
    Jun 2009
    Posts
    4

    Re: perl and mysql-compare 2 columns

    Hi Jthayne,
    I tried to rewrite my code as:
    $query = "SELECT Table1.value, Table2.value1 FROM Table1,Table2 WHERE Table2.value1 LIKE CONCAT('%',Table1.value,'%')";
    $queryexecute=$dbh->query($query1); #line 123
    $rownumber = $queryexecute->numrows();
    $fieldnumber = $queryexecute->numfields();
    print "$rownumber, $fieldnumber";
    while (@results = $queryexecute->fetchrow())
    {
    $updatetable2= "UPDATE Table2.value2 SET Table2.value2 =(@Table1.ID)";
    }

    But while compiling it shows an error stating that:
    Cant locate object method "query" via package "DBI::db" at line 123.

    please guide..

  8. #8
    jthayne's Avatar

    Status
    Offline
    Join Date
    Aug 2008
    Location
    Texas
    Posts
    508

    Re: perl and mysql-compare 2 columns

    Please verify the following module is installed on your server:
    * Mysql

    Also, make sure your are "using" it at the top of the script as well:
    * use Mysql;



Tags for this Thread