StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSearching a Record from Middle of the Table
text
Body
copied!<p>I am facing issue in finding the data from MySql table.</p> <p><strong>Table A:</strong></p> <pre><code>+-------+-------------+------+-----+-------------------+-------+ | ID | Table_b_fk |Value | age | name | score | +-------+-------------+------+-----+-------------------+-------+ | 01 | 01 | 255 | 21 | Tom | 65 | | 02 | 02 | 36 | 20 | Peter | 95 | | 03 | 03 | 25 | 22 | John | 65 | | 04 | 04 | 36 | 20 | Bond | 95 |<<----First | 05 | 05 | 258 | 22 | Smith | 65 | | 06 | 06 | 420 | 20 | Robert | 95 | | 07 | 07 | 258 | 22 | Nisarg Patel | 65 | | 08 | 08 | 16 | 21 | Peter | 95 | | 09 | 09 | 25 | 23 | J0k | 65 | | 10 | 10 | 36 | 22 | Prodigitalson | 95 | | 11 | 11 | 205 | 22 | Silver | 65 |<<----Next | 12 | 12 | 37 | 20 | Json | 95 | | 13 | 13 | 285 | 23 | Villa | 65 | | 14 | 14 | 36 | 22 | Parker | 95 | +-------+-------------+------+-----+-------------------+-------+ </code></pre> <p><strong>Table B:</strong></p> <pre><code>+-------+-------------+------+-----+-------------------+-------+ | ID | Result | M1 | M2 | name | score | +-------+-------------+------+-----+-------------------+-------+ | 01 | Pass | 30 | 26 | Tom | 65 | | 02 | Pass | 30 | 20 | Peter | 95 | | 03 | Pass | 25 | 60 | John | 65 | | 04 | Pass | 100 | 100 | Bond | 95 |<<----First | 05 | Pass | 55 | 65 | Smith | 65 | | 06 | Pass | 80 | 95 | Robert | 95 | | 07 | Pass | 65 | 75 | Nisarg Patel | 65 | | 08 | Pass | 56 | 71 | Peter | 95 | | 09 | Pass | 90 | 96 | J0k | 65 | | 10 | Pass | 96 | 96 | Prodigitalson | 95 | | 11 | Pass | 100 | 100 | Silver | 65 |<<----Next | 12 | Pass | 47 | 92 | Json | 95 | | 13 | Pass | 82 | 73 | Villa | 65 | | 14 | Pass | 86 | 72 | Parker | 95 | +-------+-------------+------+-----+-------------------+-------+ </code></pre> <p>I am joining TableA & TableB, where in TableA <code>Table_b_fk</code> is foreign key to TableB.</p> <p>I am finding the record which matches the TableB column <code>M1 & M2 = 100</code>.</p> <p><strong>My Scenario: 1</strong></p> <p>I know the first occurrence of the match record ID : <code>04</code> in <code>TableA</code>. I want to do a search to find the next record with <code>M1 & M2 = 100</code>. (Record Id-11) But the search should not start from <strong>01</strong>. It should start from the last found record Id. That is from <strong>O4</strong> the search should start to find the next occurrence of the record.</p> <p>My Try: I tried to find using Limit but it didn't help me to find. Can some one help me in this?</p> <p><strong>Edit: 1</strong></p> <p><strong>My Scenario: 2</strong> In my second case my TableB has repeated Data and the ID was foreign in TableA. How can I fins the record. ? with the matching ID/M1/M2 values: I found a solution for that. I just want to find the Current Record FOREIGN KEY and Check for the next occurrence of the record in the same table and I can get the next record rite?</p> <p>In this case my TableB record are not as same as TableA records. In other words my TableA records will point to tableA. Many-to-one. Is this rite?</p> <p><strong>Edit: 2</strong></p> <p>Thanks for all your efforts and knowledge I found a solution for scenario:2 check it:</p> <pre><code>CREATE TABLE TableB ( ID Int, Result VARCHAR(20), M1 INT, M2 INT, name VARCHAR(20), Score INT); INSERT INTO TableB VALUES ( 11 , 'Pass' , 30 , 26 , 'Tom' , 65 ), ( 13 , 'Pass' , 30 , 20 , 'Peter' , 95 ), ( 80 , 'Pass' , 25 , 60 , 'John' , 65 ), ( 81 , 'Pass' , 100 , 100 , 'Bond' , 95 ), ( 90 , 'Pass' , 55 , 65 , 'Smith' , 65 ), ( 96 , 'Pass' , 80 , 95 , 'Robert' , 95 ), ( 97 , 'Pass' , 65 , 75 , 'Nisarg Patel' , 65 ), ( 98 , 'Pass' , 56 , 71 , 'Peter' , 95 ), ( 99 , 'Pass' , 90 , 96 , 'J0k' , 65 ), ( 100 , 'Pass' , 96 , 96 , 'Prodigitalson' , 95 ), ( 101 , 'Pass' , 10 , 10 , 'Silver' , 65 ), ( 103 , 'Pass' , 47 , 92 , 'Json' , 95 ), ( 201 , 'Pass' , 82 , 73 , 'Villa' , 65 ), ( 222 , 'Pass' , 86 , 72 , 'Parker' , 95 ) ; CREATE TABLE TableA (`ID` int, `Table_b_fk` int, `Value` int, `age` int, `name` varchar(13), `score` int) ; INSERT INTO TableA (`ID`, `Table_b_fk`, `Value`, `age`, `name`, `score`) VALUES (01, 11, 255, 21, 'Tom', 65), (02, 81, 36, 20, 'Peter', 95), (03, 80, 25, 22, 'John', 65), (04, 97, 36, 20, 'Bond', 95), (05, 81, 258, 22, 'Smith', 65), (06, 06, 420, 20, 'Robert', 95), (07, 81, 258, 22, 'Nisarg Patel', 65), (08, 08, 16, 21, 'Peter', 95), (09, 96, 25, 23, 'J0k', 65), (10, 101, 36, 22, 'Prodigitalson', 95), (11, 222, 205, 22, 'Silver', 65), (12, 12, 37, 20, 'Json', 95), (13, 201, 285, 23, 'Villa', 65), (14, 101, 36, 22, 'Parker', 95) ; </code></pre> <p>Solution for that is:</p> <pre><code>SELECT a.id FROM TableB b INNER JOIN TableA a ON a.Table_b_fk = b.id WHERE M1 = 100 and M2 = 100 AND a.ID>4 limit 1 </code></pre> <p>where the limit just limits the next record.. (answer is 5).</p> <p>I case of <code>Doctrine 2</code>: Use the below Query code.</p> <pre><code>$qry = $this->manager()->createQueryBuilder() ->select(array('e', 's')) ->from('YOUR_DOMAIN', 'e') ->Join('e.table_b_k', 's') ->where("s.m1 = ?", $valueone) ->andwhere("s.m2 = ?", $valuetwo) ->andwhere("e.id > ?", $currentrecord) ->setMaxResult(1); </code></pre> <p>Note: <code>YOUR_DOMAIN</code> here is the TableA. TableA and TableB should be joined through the Mapping so we dont need to Join/Reference int he Query.. directly as TableB. The will be done by the second line Join in the above example. It is not tested as of now.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload