Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts

    Comparing two similar rows from a table

    Here's the problem. I have a database which has two unique keys. One is page id, the other is user ID. Then there are 60 columns of variables that need to be compared. For example if the rows looked like this:

    id | user id | page id | var1 | var2 | var3 | var4 | --- | var60 |
    5 | 20 | null | A | B | null | D | --- | DD |
    36 | null | 125 | null| B | C | null | --- | DD |

    I would like to echo 'true'

    But if var2 was a null and var60 was a null (so there were no common columns) I would echo 'false'

    I can do this with a huge group of if statements but I would like to figure out how to get an if statement that simply said:

    PHP Code:
     if (row id 5 has any columns that match row id 36){
        {echo 
    'true'; }
       else 
        echo 
    'false'
    Isolating each row is easy, this is how I did it. I have a currentUserRS (record set) that returns the user ID for any logged in user from the session variable, and a pageRS (current page RS) that returns the page id. There is a table that sets variables for each user and for each page in the table docSecurity. I just need to see if any of the 60 variables match.

    This code reliably identifies the page ID and the current user ID. I'm hoping to get the results I need without writing 60 if statements and then checking to see if any of them are true. BTW, each of the variable fields is unique, for example, var1 is always A or null. No other values are permitted in the table.

    Here's my code that grabs the user and page id's.

    PHP Code:
    $checkVal $row_currentUserRS['id'];
        
    $pageChkVal =  $row_pageRS ['id'];
         
    $result mysql_query("select * FROM docSecurity WHERE securityID = $checkVal");
         
    $result2 mysql_query("select * FROM docSecurity WHERE pageID = $pageChkVal");
         
    $row mysql_fetch_array($result );
         
    $row2 mysql_fetch_array($result2);
            echo 
    'User Security ID: '$row['id'], ' / Page Security ID'$row2['id']; 

  • #2
    Junsee
    Guest
    I have not used either but Soundex or the Levenshtein distance

    here is something I found to get you started
    http://stackoverflow.com/questions/3...is-it-possible

  • #3
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    I think I'm getting closer but I have a bunch of problems left to solve. The first 5 columns of my row are not related to this search. I've got the loop working for column 6 through 60 but I'd like to automate that. What I'm doing now is running a query for two conditions and returning $result1 and $result2. I then fetch the result into $row1 and $row2. This makes it possible to easily check for a match between the first column of $row1 and $row2 by using code like this:
    PHP Code:
     if ($row1[6] <> $row2[6]) {$checkMatch FALSE;
     if (
    $row1[7] <> $row2[7]) {$checkMatch FALSE;
     if (
    $row1[8] <> $row2[8]) {$checkMatch FALSE;
     if (
    $row1[9] <> $row2[9]) {$checkMatch FALSE;
     
    // repeated until I get to row 60 then code that says if $checkMatch = echo 'Match Found'

    I've searched all over the web trying to find a solution that would loop through column 6 through column 60 then stop the loop when it found a match without having to write 60 if statements. I'm hoping there's a way to write something that functions like this in plain English:

    For each column in $result1 starting with column 6 check the same column in $result2 until you find a match then end the loop and report a $checkMatch is TRUE. If no match is found end the loop at column 60.

    I'd appreciate any ideas. The code that i've written works just fine but it's very bloated and slow.

  • #4
    Junsee
    Guest
    Roughly like this

    PHP Code:
    $sqlfirst "SELECT * FROM `table` ; ";
    $rsfirst mysql_query($sqlfirst$conn) or die ("error with sql query ".$sqlfirst);

    $sqlsecond "SELECT * FROM `table` ; ";
    $rssecond mysql_query($sqlsecond$conn) or die ("error with sql query ".$sqlsecond);

    for (
    $i=0$i 60$i++){
        
    $first mysql_result($rsfirst,$i,'FieldName');
        
    $second mysql_result($rssecond,$i,'FieldName');
        
        
    #CODE to match $first and $second



  •  

    Posting Permissions

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