In the DB table, apartments area is stored in the square line according to the following pattern: * "common-living-kitchen". So it looks like 32-28-6 .

Now the question is: how to form a SQL query, if you want to display only the total area of ​​at least the desired value? I understand that the task is not simple, but the database is already filled with ads, and only this functionality will have to be added to the search system without disrupting the work of the rest of the project code.

Up to a point, I used an erroneous request (but this is not an option):

 SELECT * FROM apartments WHERE square LIKE '30-%'

You can use PHP tools, but here in the request itself, the division into pages through LIMIT is already in progress. Thus, if, after receiving the search results, we select the “necessary” by area of ​​the apartment, then we will have 10 entries on one page, and less on the other page or nothing at all.

Guys, thank you very much for the prompt decision of a difficult question for me! Especially grateful to users of cache and Akina - your solution works successfully!

  • Perhaps it is worth solving this task not by means of SQL but by means of PHP? - Viktorov
  • trim the substring, translate it into a number, compare it with the required one - etki
  • And what is your SQL? string functions are needed here, and they are different in different DBMSs. - Mike
  • For MySql SELECT * FROM apartments a WHERE (0 + SUBSTRING_INDEX(square, '-' , 1)) >= 30 - cache
  • That's right. And he has a MySQL or a clone - nowhere else is LIMIT and the lightweight interface from PHP doesn't seem to be lit up. - Akina

2 answers 2

 SELECT * FROM apartments WHERE 0+SUBSTRING_INDEX(square,'-',1) >= 30;
  • And how from the point of view of performance it is better to do type conversion? implicitly through addition or explicitly cast to the number WHERE CAST(SUBSTRING_INDEX(square, '-' , 1) AS SIGNED) >= 30 ? - cache
  • I think that in both cases there will be no cast as such, MySQL has a weak typing after all. Yes, an explicit CAST does not have an overhead in the form of an additional explicit addition - but again, this can hardly be caught in benchmarks ... By the way, why is it unsigned? And think again - and, perhaps, it is more correct to compare as strings? WHERE SUBSTRING_INDEX (square, '-', 1)> = '30' ? if the first number is 200 - should it be included in the output or not? - Akina

Perhaps it makes sense to use the presence of a hyphen. All three values ​​of numbers are highlighted as follows.

1. Left hyphen

2. hyphen left and right

3. hyphen on the right

In this case, retrieve option 3.

  $value = 30; $max = 100; for (int i = $value; i < $max; i++) { $result = mysql_query('SELECT * FROM apartments WHERE square LIKE '.$value.'- ',$db) or die('error! Записей не найдено!'); //проверяем, есть ли результат, что-то делаем с результатом, собираем в массив к примеру }

as another option

  $result = mysql_query('SELECT * FROM apartments WHERE square LIKE '%-%-%',$db) or die('error! Записей не найдено!'); while ($rows=mysql_fetch_row($result)){ $arraySubString[] = split("-", $rows[n], 3);//n - требуемый столбец if($arraySubString[0] >= $value ){ //что-то делаем с $rows, собираем или выводим, к примеру $arraySubString[]= array();//обнуляем массив }
  • one
    The author also writes that he needs to get not thirty meter apartments, but apartments from thirty meters and more - etki
  • @Etki Thanks for the clarification, a few tweaks. In the case of criticism, I am ready to remove this decision. - TimurVI
  • Yes, it is not necessary to delete (here, on request, a person who needs an exact match can easily come from Google), but in the current version the number of requests grows as O (n), or you can do with simple O (1). All magic in general boils down to three actions - to isolate the first element (just a substring or regexp), turn it into a number (castes), compare it with the provided one. If you sign this algorithm, it will be cool. - etki