How to retrieve two rows of same field in one table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
albertlive
Forum Newbie
Posts: 4
Joined: Mon Mar 28, 2011 11:25 am

How to retrieve two rows of same field in one table

Post by albertlive »

Hi Guys,

How to retrieve two rows of same field in one table on a single query in phpmyadmin or in php script.

For example: I want to get the top 2 firstname of the same lastname of the record, row1_firstname and row2_firstname.

James Bond
Mark Bond
Jason Bond

The result will be:
James Bond
Mark Bond

Thanks,
Albert
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: How to retrieve two rows of same field in one table

Post by social_experiment »

Code: Select all

<?php
 $qry = "SELECT firstname, lastname FROM your_tbl LIMIT 2";
?>
That will select the two latest entries in the database. If you want the first two on the database you can order them in an ascending fashion.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
fugix
Forum Contributor
Posts: 207
Joined: Fri Mar 18, 2011 8:01 pm

Re: How to retrieve two rows of same field in one table

Post by fugix »

to get the first two rows, im going to add a little to social experiments code...set the beginning limit to 0 or 1,

Code: Select all

<?php
 $qry = "SELECT firstname, lastname FROM your_tbl LIMIT 0, 2";
?>
albertlive
Forum Newbie
Posts: 4
Joined: Mon Mar 28, 2011 11:25 am

Re: How to retrieve two rows of same field in one table

Post by albertlive »

Thanks for the reply Guys, but still it didn't work.

I have lots of record and i want to get all firstname from each same lastname.

Example:
James Bond
Jeff Craig
Carter Love
Mark Bond
Josh Craig
Bob Love
Jason Bond
Kris Craig
Albert Love

The result will be:
James Bond
Mark Bond
Jeff Craig
Josh Craig
Carter Love
Bob Love

Thanks in advance,
Albert
rashidfarooq
Forum Newbie
Posts: 6
Joined: Wed Mar 30, 2011 7:39 am

Re: How to retrieve two rows of same field in one table

Post by rashidfarooq »

Use this code

Code: Select all

$qry = "Select firstname, lastname from your_tbl_name where lastname = "Bond"";

albertlive
Forum Newbie
Posts: 4
Joined: Mon Mar 28, 2011 11:25 am

Re: How to retrieve two rows of same field in one table

Post by albertlive »

Thanks rashidfarooq but the problem is i have lots of record and i can't filter all the surename to be use in the query.


Albert
rashidfarooq
Forum Newbie
Posts: 6
Joined: Wed Mar 30, 2011 7:39 am

Re: How to retrieve two rows of same field in one table

Post by rashidfarooq »

I have Understood your problem now. I Hope this code will work fin to solve your problem.

Code: Select all

Select firstname from tblname where lastname in (SELECT lastname FROM tblname GROUP BY lastname HAVING (COUNT(lastname) > 1 ))

albertlive
Forum Newbie
Posts: 4
Joined: Mon Mar 28, 2011 11:25 am

Re: How to retrieve two rows of same field in one table

Post by albertlive »

Thanks rashidfarooq but still it didn't work.

Albert
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: How to retrieve two rows of same field in one table

Post by McInfo »

If the full name is in a single field, you can use a query like this to get the first name. (It assumes that a person's first name does not contain a space.)

Code: Select all

SELECT SUBSTRING_INDEX(`full_name`, ' ', 1) AS `first_name` FROM `person_table` WHERE `full_name` LIKE '% Bond'
The SUBSTRING_INDEX() function returns a substring from the beginning of the first argument (the full_name field) up to the Nth occurrence of the second argument (a space) where N is the third argument (1).

The LIKE keyword matches the column before it against the pattern after it. In the pattern, the percent sign (%) is a wildcard character that matches a string of zero or more characters, so the pattern "% Bond" matches a full_name that ends with a space and the last name Bond.

If you frequently need to search for people by their first or last name, alter the table structure by splitting the full_name field into two fields: first_name and last_name. Then searching will be more efficient because MySQL won't have to do string operations on the full_name.

This article could be useful: ...xaprb.com/...firstleastmax-row-per-group...
Post Reply