SQL - Order Results by Relevance

Question

I am currently learning MySQL, and I am getting the hang of it, but I am having a few difficulties.

This is my table structure - https://i.stack.imgur.com/24P9b.jpg

This is my code -

$host = 'localhost';
$username = 'user';
$password = 'pass';
$database = 'db';

$url = $_GET['url'];
$title = $_GET['title'];
$description = $_GET['description'];

mysql_connect($host, $username, $password);
@mysql_select_db($database) or die('DB ERROR');

$query = "SELECT * FROM search";
$result = mysql_query($query);
$number = mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $number) {

$db_url = mysql_result($result, $i, "url");
$db_title = mysql_result($result, $i, "title");
$db_description = mysql_result($result, $i, "description");

echo $i.'-'.$db_url.'-'.$db_title.'-'.$db_description.'<br />';
$i++;
}

?>

Note that the $query is not complete. How do I, for example, order that by relevance to, for example $search_query?

Thank you in advance, and please note that I just started MySQL,

As you are learning, [Please, don't use `mysql_*` functions in new code](http://stackoverflow.com/q/12859942). They are no longer maintained and the deprecation process has begun, see the [red box](http://php.net/mysql-connect). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli); [this article](http://php.net/mysqlinfo.api.choosing) will help you decide which. If you choose PDO, [here is a good tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). — vascowhite, Mar 24 '13 at 08:25
I heard about that, but `mysql_` seems to be a lot easier for me. After I learn it, I'll switch to `mysqli_`. — user2153768, Mar 24 '13 at 08:26
For a very basic search engine, you may want to get your feet wet with the [`LIKE`](http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html) operator. — Aiias, Mar 24 '13 at 08:26
PDO is very simple, much more so than the MySQL functions, give it a go you won't regret it and you won't be learning something that you'll need to relearn and can be dangerous to your users. — vascowhite, Mar 24 '13 at 08:28

score 1 · Accepted Answer · answered Mar 24 '13 at 08:28

1

for example:

Filter:

SELECT * FROM search WHERE title = 'Vlad'

SELECT * FROM search WHERE description LIKE 'Vlad%'

Order:

SELECT * FROM search ORDER BY title DESC

SELECT * FROM search ORDER BY title ASC

answered Mar 24 '13 at 08:28

Mikatsu

530
2
4
15

About to try it out. Thank you. – user2153768 Mar 24 '13 at 08:30
Here u will find all the basics u need for your task i am sure http://www.w3schools.com/sql/default.asp – Mikatsu Mar 24 '13 at 08:32
WHOA awesome man. I used stored info in files and now in switching to SQL. Thanks a lot ;) – user2153768 Mar 24 '13 at 08:34
Oh, btw, the percentage signs can be like this too, right, `'%Vlad%'`? – user2153768 Mar 24 '13 at 08:35
1

yes it can be like this would mean u will find for example BigVladGoesSmall too! – Mikatsu Mar 24 '13 at 08:42

SQL - Order Results by Relevance

1 Answers1