How to select in a table rows that containts same datas dynamically?

Hi guys ,
i have this table scheme

and i want to select " Couples of customers who live in the same area(region)." But dynamically
Dynamically means : i dont know in advance the name of regions.

How are you interacting with the DB? Is it that you’re looking to write a function that will return two results based on region, where the region is determined at runtime? If so, this is how you would do it in PHP:

public function getByRegion($region){
	$stmt = $this->db->prepare("SELECT * FROM client WHERE :region = region"); //the colon indicates the param to be bound.
	$stmt->bindParam(':region', $region, PDO::PARAM_STR);
	return $stmt->fetch(PDO::FETCH_OBJ);

Obviously, you’ll need your own logic to determine which two clients to return. I’m not entirely sure if this is what you’re looking for though.

I’m using the MCLC.
So i’ll not write any php script.

What is this? A command line utility? A google search didn’t show up any results pertaining to databases for MCLC. If it is a command line tool, then would you not know the region you are looking for when you type it? Since you’ll be typing the new query every time. Something like:
SELECT * FROM client WHERE region = "Europe" LIMIT 2;

Or do you want to query the database and return a pair of records that have the same region, regardless of what region it is? Something more along the lines of:
SELECT * FROM client WHERE region IN (SELECT region FROM client GROUP BY region HAVING count(*) > 1) LIMIT 2

That should return the first two records of the most used region. That’s the best I could think of from the command line, I’m sure there are more elegant ways, but I generally use an ORM, so naked queries on the command line are not my strong suit.

I’m not in front of my Dev machine at the minute, so I don’t have MySQL installed. Therefore, I have no idea if that query is correct or not. I’m sure it requires modification to work, but its kind of a general idea of what I think you’re looking for.

Yes thanks guy ithink it works!

1 Like