- Php order results by name mysql
- How do I sort MySQL search results be relevance?
- How to order mysql search results by relevence of word priority [duplicate]
- Order displayed results by id in php
- MySQL and PHP Order Addresses by first letter, not numbers
- Предложение ORDER BY PHP MySQL
- Фильтрация записей в порядке возрастания или убывания
- Код РНР сортировки записей в алфавитном порядке
- Example
- Выборка данных стиле PDO (+ подготовленные операторы)
- Пример
Php order results by name mysql
Now I am trying to get the results to show up in ASC order, but when written, it sorts by the number in the address, so 100 Zebra Street comes before 200 Apple Way, which I expected. Provided below is an alternate solution to the problem in general which allows further customizations in terms of sorting the result : If you have data like this : Then, you can use the following query: This will result in: Solution 3: You can easy use this query: Question: Hi, I have a table with several columns containing fields like name, address, company etc.
How do I sort MySQL search results be relevance?
I’m using PDO to search a MySQL table for search results. My query looks like this
$sql = "SELECT * FROM users WHERE username LIKE :username OR name LIKE :name ORDER BY uniqueid DESC LIMIT 6"; $stmt = $conn->prepare($sql); $stmt->bindValue(':username', '%'.$query.'%'); $stmt->bindValue(':name', $query.'%'); What I’m trying to accomplish is that in my results array, the results that match like this:
Should appear before results that match like this:
Is there a way to sort results by such relevance without two queries, preferably in pure SQL?
You could order the results by the index of $query in username and name :
SELECT * FROM users WHERE username LIKE :username OR name LIKE :name ORDER BY uniqueid DESC, INSTR(:query, username), INSTR(:query, name) LIMIT 6 Note, however, that ordering by results of a function requires full table scan in MySQL.
Well, Eugene’s answer seems the most appropriate in this case. Provided below is an alternate solution to the problem in general which allows further customizations in terms of sorting the result :
If you have data like this :
Id Name 1 Aabbee 2 Aabriella 3 Aada 4 bahlelah 5 cailelah 6 daielah 7 gaisha 8 Aisha 9 Aishath 10 dalelh 11 Eleasha Then, you can use the following query:
select '1' as Relevance,ID, Name from MyTable where Name like 'el%' /*The results with the priority (or relevance) should be first */ union select '2' as Relevance,ID, Name from MyTable where Name like '%el%' and Name not like 'el%' /* The lower priority results go here*/ order by Relevance Relevance ID Name 1 11 Eleasha 2 2 Aabriella 2 4 bahlelah 2 5 cailelah 2 6 daielah 2 10 dalelh You can easy use this query:
$SQL = "SELECT * FROM users WHERE username LIKE :username OR name LIKE :name ORDER BY name LIKE :name DESC, uniqueid DESC LIMIT 6"; – Php — mySQL Order By String, Stack Overflow Public questions & answers; Stack Overflow for Teams Where developers & technologists share private knowledge with …
How to order mysql search results by relevence of word priority [duplicate]
Possible Duplicate:
PHP MySQL Search And Order By Relevancy
I have a table with several columns containing fields like name, address, company etc. Lets say someone search for «microsoft john». I want the results containing «microsoft» should appear first, then results containing john. vice versa if query is «john microsoft»
$searchitems=explode(" ", $trimmed); //print_r($searchitems); $so = $_GET['so']=='2'?"2":"1"; $clause = $so=='2'?"AND":"OR"; include("dbconnect.php"); // Build SQL Query $query = "select FirstName,LastName,course,Department,batch,City,companyjob,companylocation, companyposition,coursename,institutename,coursename2,institutename2,coursename3, institutename3 from alumni WHERE "; for($i=0;$i $query .=$queryappend; The problem is MYSQL is ordering the results by id. This makes it funny, because some higher valued results may be stuck deep in the stack. btw, phpmyadmin search has the same flaw.
SELECT FirstName, LastName, IF (FirstName LIKE '%Microsoft%' || LastName LIKE '%Microsoft%', 1, 0) AS One, IF (FirstName LIKE '%John%' || LastName LIKE '%John%', 1, 0) AS Two FROM alumni ORDER BY One DESC, Two DESC In your code, this will make the query pretty complicated. The advantage is, that items with both search term appear before items that match only a single search term.
An alternative is sorting the records into buckets while retrieving them using PHP. Assuming you have the search terms in an array $search (ordered by descending priority):
while ($record = mysql_fetch_array($result)) < $total = join(' ', $record); $found = false; foreach ($search as $term) < if (strpos($total, $term) !== false) < $buckets[$term][] = $record; $found = true; break; >> if (!$found) < $results[] = $record; >> foreach (array_reverse($search) as $term) < if (isset($buckets[$term])) < $result = array_merge($buckets[$term], $result); >> Now you have the results in array $results . Note that this demonstrates the algorithm, it it not tuned for performance.
I would think the Simplest way to solve it would be sorting the results by the levenstein distance.
$queryappend="ORDER BY length(firstname) - levenshtein(FirstName, '".$searchitems[$i]."') + length(lastname) - levenstein(LastName, '".$searchitems[$i]."') + length(City) - levenstein(City, '".$searchitems[$i]."') + . Although it might be a good idea to use a schema MORE SUITED to this kind of searching
Php — Sorting the results of a mysql query, Im having difficulties trying to figure out an elegant solution to sorting the results of a mysql query based on a delimited string. Ill explain in more …
Order displayed results by id in php
A webpage displays results from a table in a MySQL database and then order’s them using;
$quey1="select * FROM tbname ORDER BY id DESC"; «id» uses auto_increment. I have deleted some of the id’s.
MySQL orders results regardless of gaps in an auto-incremented column. Your query is correct, and should order results by id in descending order. It’s possible that any post-processing you may be doing with the data is messing with the order it is presented.
How to sort MYSQL fulltext search results by relevancy, 5 Answers. LIKE is not fulltext search. In Fulltext search, MATCH () AGAINST () returns a matching score that can be roughly approximated as …
MySQL and PHP Order Addresses by first letter, not numbers
Ok, so I’ve made a MySQL database for a real estate company, and have it functioning and displaying results. Now I am trying to get the results to show up in ASC order, but when written,
`$query="SELECT * FROM info ORDER BY Address ASC"; ` it sorts by the number in the address, so 100 Zebra Street comes before 200 Apple Way, which I expected. But my desired result is for the query to sort by the first letter in the street name.
Is there an easy way to achieve this, or should I just separate the fields so one column is the number, and the next column is the street name?
I suggest you separate the columns if possible. If you intend to work on parts of the address, then logically you should break it up.
Yes this can be achieved. The solution is outlined here:
Although it’s more practical & efficient to split it into multiple columns.
How to sort by name and surname from MySql using php?, You can ORDER BY multiple columns. Like this, SELECT * FROM members WHERE isHere = 0 ORDER BY surname ASC, name ASC; You can …
Предложение ORDER BY PHP MySQL
В этом уроке вы узнаете, как с помощью PHP сортировать и отображать данные из таблицы MySQL в порядке возрастания или убывания.
Фильтрация записей в порядке возрастания или убывания
Предложение ORDER BY используется для сортировки набора результатов в порядке возрастания или убывания.
Предложение ORDER BY по умолчанию сортирует записи в порядке возрастания. Чтобы отсортировать записи в порядке убывания, используйте ключевое слово DESC :
Код РНР сортировки записей в алфавитном порядке
Давайте сделаем SQL-запрос, используя предложение ORDER BY , после чего мы выполним этот запрос, передав его функции PHP mysqli_query() для получения упорядоченных данных. Рассмотрим следующую таблицу persons в базе данных demo:
Код РНР в следующем примере выбирает все строки из таблицы persons и сортирует результат в столбце first_name в алфавитном порядке возрастания:
Example
// Попытка выполнения запроса select с предложением order by $sql = "SELECT * FROM persons ORDER BY first_name"; if($result = mysqli_query($link, $sql)) < if(mysqli_num_rows($result) >0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = mysqli_fetch_array($result))< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Закрываем набор результатов mysqli_free_result($result); > else < echo "Не найдено ни одной записи, соответствующей вашему запросу."; >> else < echo "ОШИБКА: не удалось выполнить $sql. " . mysqli_error($link); >// Закрываем соединение mysqli_close($link); ?>connect_error); > // Попытка выполнения запроса select с предложением order by $sql = "SELECT * FROM persons ORDER BY first_name"; if($result = $mysqli->query($sql))< if($result->num_rows > 0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = $result->fetch_array())< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Free result set $result->free(); > else < echo "Не найдено ни одной записи, соответствующей вашему запросу."; >> else< echo "ОШИБКА: не удалось выполнить $sql. " . $mysqli->error; > // Закрываем соединение $mysqli->close(); ?>setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ОШИБКА: не удалось подключиться. " . $e->getMessage()); > // Попытка выполнения запроса select try< $sql = "SELECT * FROM persons ORDER BY first_name"; $result = $pdo->query($sql); if($result->rowCount() > 0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = $result->fetch())< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Free result set unset($result); > else < echo "Не найдено ни одной записи, соответствующей вашему запросу."; >> catch(PDOException $e)< die("ОШИБКА: не удалось выполнить $sql. " . $e->getMessage()); > // Закрываем соединение unset($pdo); ?>Результат выполнения скрипта будет выглядеть примерно так:
+----+------------+-----------+----------------------+ | id | first_name | last_name | email | +----+------------+-----------+----------------------+ | 3 | Clark | Kent | clarkkent@mail.com | | 5 | Harry | Potter | harrypotter@mail.com | | 2 | John | Rambo | johnrambo@mail.com | | 4 | John | Carter | johncarter@mail.com | | 1 | Peter | Parker | peterparker@mail.com | +----+------------+-----------+----------------------+
Объяснение кода из приведенного выше примера:
Сначала мы настраиваем SQL-запрос, который выбирает столбцы id, first_name, last_name и email из таблицы persons. Записи будут отсортированы по столбцу first_name. Следующая строка кода выполняет запрос и помещает полученные данные в переменную с именем $result.
Затем функция mysqli_num_rows() проверяет, было ли возвращено строк больше нуля.
Если возвращается более нуля строк, функция mysqli_fetch_array помещает все результаты в ассоциативный массив, который мы можем просмотреть. В цикле while() через результирующий набор выводятся данные из столбцов id, first_name, last_name и email.
Выборка данных стиле PDO (+ подготовленные операторы)
В следующем примере используются подготовленные операторы.
Он выбирает столбцы id, first_name, last_name и email из таблицы persons, где last_name=’Carter’, и отображает их в таблице HTML:
Пример
"; echo "Id Имя Фамилия E-mail " . parent::current(). " "; > function beginChildren() < echo ""; > function endChildren() < echo " " . "\n"; > > $serverName = "localhost"; $userName = "root"; $password = ""; $dbName = "demo"; try < $conn = new PDO("mysql:host=$serverName;dbname=$dbName", $userName, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $conn->prepare("SELECT id, first_name, last_name, email FROM persons ORDER BY last_name"); $stmt->execute(); // поместить результат в ассоциативный массив $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) < echo $v; >> catch(PDOException $e) < echo "Ошибка: " . $e->getMessage(); > $conn = null; echo ""; ?>Результат выполнения кода:
| Id | Имя | Фамилия | |
|---|---|---|---|
| 4 | John | Carter | johncarter@mail.com |
| 3 | Clark | Kent | clarkkent@mail.com |
| 1 | Peter | Parker | peterparker@mail.com |
| 5 | Harry | Potter | harrypotter@mail.com |
| 2 | John | Rambo | johnrambo@mail.com |
| 6 | Ron | Weasley | ronweasley@mail.com |