mysql Archives - Page 2 of 5

Full Text Search In MySQL ,a better way for MySQL Text search

One of the most common issue with MySQL is searching for a matching string from a huge database,suppose we need to search for ‘MySQL Tutorial’ it will only search the occurrence of ‘MySQL Tutorial’ neither ‘MySQL’ or ‘Tutorial’ also in MySQL compared to INT value search Char/Text is time consuming to solve this issue, we have Full Text Search .Full Text Search is a technique used by search engine to find the results in a database.we can use Full Text search in search Query.First you must create a full-text index on the table before you run full-text queries on a table.

Creating a full-text index inside a Table


ALTER TABLE TABLE NAME ADD FULLTEXT (FIELD1, FIELD2,FIELD3, etc)

Example

ALTER TABLE wptt_posts ADD FULLTEXT (`post_title`, `post_content`)

Now we created Full Text Index in wptt_posts table,next we will discuss how to use Full Text Search in MySQL

MySQL Query Using Full Text Search

SELECT * FROM TABLE NAME WHERE MATCH (FILED1FILED2,FILED3) AGAINST ('SEARCH WORD' IN NATURAL LANGUAGE MODE)

Example

SELECT * FROM wptt_posts WHERE MATCH (post_title,post_content) AGAINST ('Mysql Tutorials' IN NATURAL LANGUAGE MODE)

We can use the match case based on the Score

SELECT MATCH (post_title,post_content) AGAINST ('Mysql Tutorials') as score, post_title FROM wptt_posts WHERE MATCH (post_title,post_content) AGAINST ('Mysql Tutorials' IN NATURAL LANGUAGE MODE)





Where the score value represent the occurrence of search term in the filed

We can skip words (exclude words) from the result

For e.g “-Tutorials” means the search result should not contain Tutorials

SELECT * FROM wptt_posts WHERE MATCH (post_title,post_content) AGAINST ('+Mysql -Tutorials' IN BOOLEAN MODE)

update query in mysql with example

MySQL is an open-source relational database management system,actually the name MySQL come from the combinations of words “My“(name of co-founder Michael Widenius’s daughter) and “SQL“(Structured Query Language).Since MySQL is free and open-source it is the most popular and huge used RDBMS. In this section we are going to check out how to write a update query in MySQL.Let me explain the syntax for update query in mysql with example

Syntax

"UPDATE `TABLE_NAME` SET `COLUMN_NAME` = `VALUE' WHERE  [condition];

Example

"UPDATE student  SET mobile ='9999999999' WHERE id='9'";

update query in mysql

php mysql query where description like any of the word in the given string?

Almost all website having a search field on the top it will search in the entire database and display related data in the user interface.so when user type a group of word almost all case user will not get accurate report,because the MySQL query using the LIKE “%$query%/”,so in this tutorial we are planning to elaborate how to search word by word in the database for given string.


#search Form

<form action="search.php" method="post">
<input type="text" name="q" value="">

</form>

#search.php
$q=$_POST['q'];

$searchQ=explode(' ',$q );
$searchquery="";
foreach($searchQ as $key=>$value)
{
if($searchquery!='')
{
$searchquery.="description LIKE %".$value."%"
}
else
{
$searchquery.=" OR description LIKE %".$value."%"
}
}
$query="SELECT * FROM leads WHERE ".$searchquery;
you can use this query to fetch the data

Mysql query to fetch current month data

Sometimes we need to show only current month data in the application ,so for easy handling the data we should use a Mysql query to fetch current month data.Let we check out how to do that using one MySQL query

See below the Mysql query to fetch current month data


SELECT *
FROM leads
WHERE MONTH(date) = MONTH(CURRENT_DATE())
AND YEAR(date) = YEAR(CURRENT_DATE())
// leads= Table Name
// date = date field name

Here we are fetching current month data using the feature MONTH(CURRENT_DATE()) and MONTH(date)

MONTH(CURRENT_DATE()) will result 8 (since current month is Aug)

Next feature we are using in Mysql query to fetch current month data is YEAR(date) and YEAR(CURRENT_DATE())

YEAR(CURRENT_DATE()) will result 2019 (since today is Aug 24 2019)

so actual Mysql query to fetch current month data will work like this

SELECT *
FROM leads
WHERE MONTH(date) = 9
AND YEAR(date) = 2019

so it will fetch all result form database table leads that having date field value is in august month