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

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

#1

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.
Thanks.


#3

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);
	$stmt->execute();
	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.


#4

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


#5

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.

NOTE:
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.


#6

Yes thanks guy ithink it works!