CODEWITHSUNDEEP
phpmysqlcodeigniter
Inishua Nakamoto
Inishua Nakamoto

Reputation: 11

How can I ignore special characters in CodeIgniter URL Segment for query

I've tried this a number of ways but I have a client being rather particular on this subject and I am not sure how to make this happen exactly as he wants.

We have a controller called Articles.

To find an article we are using the path /articles/year/month/date/article-title-as-is-in-database-but-highphenated

The controller takes only that last portion of the data, replaces hyphens with spaces, and looks in the articles table for article_title that matches the string.

Now he has some articles with colons and certain other special chars in them.

SO my question is, if we have "This: is my article name" in the database, his preferred link would be "/this-is-my-article-name/".. well, the query returns no results because we've replaced the colon with nothing.. how do we fetch the resulting article?!

I tried something akin to: 

$this->db->select('(SELECT * FROM articles WHERE REPLACE(REPLACE(REPLACE(articles.article_title, "-", ""), ":", ""),"!", "") LIKE "%'.$string.'%")', FALSE);
$query = $this->db->get();
return $query->row_array();

However that did not work- it simply resulted in this:

Error Number: 1241

Operand should contain 1 column(s)

SELECT (SELECT * FROM articles WHERE REPLACE(REPLACE(REPLACE(articles.article_title, "-", ""), ":", ""), "!", "") LIKE "%Computer Build of the Month : April 2015%")

Filename: C:\mstsage\system\database\DB_driver.php

Line Number: 331

Thank you to whatever genius helps me resolve this!

Upvotes: 1

Views: 951

Answers (3)

Francisco de Castro
Francisco de Castro

Reputation: 769

This error was resulted because you are using the wrong method to execute your query. In order to run a queries you use the function $this->db->query, see docs for more info.

In order to fix the error you got change your code to:

$sql = '(SELECT * FROM articles WHERE REPLACE(REPLACE(REPLACE(articles.article_title, "-", ""), ":", ""),"!", "") LIKE "%'.$string.'%")';
$query = $this->db->query($sql);
return $query->row_array();

Extra: Codeigniter has a query builder class to facilitate queries.

Upvotes: 2

axiac
axiac

Reputation: 72266

Take a closer look at the error message reported by MySQL server. It contains the SQL keyword SELECT followed by your entire query.

This happens because the $this->db object is a connection to the database but also a query builder. It's select() method requires as arguments only the expressions to put in the SELECT clause of the query it builds. Use its where() method to specify the WHERE conditions and so on.

If you want to write your own SQL query (and not use the query builder) then you can use the query() method. It just sends the query to the server, without changing it:

$query = $this->db->query('SELECT * FROM articles WHERE REPLACE(REPLACE(REPLACE(articles.article_title, "-", ""), ":", ""),"!", "") LIKE "%'.$string.'%")', FALSE);

Read the documentation page "Generating Query Results" to learn how to use the object returned by $this->db->query().

Upvotes: 0

Lkbhai Lr
Lkbhai Lr

Reputation: 264

If your article name is stored in db as "This: is my article name" and if you use the path as /articles/year/month/date/this--is-my-article-name. So your controller converts the highphenated article name to "this is my article name". Now your are in a point you compare "this is my article name" got from the controller and "This: is my article name" in the db.

Unfortunately mysql doesnot offer any regular expression replace function, you can create a custom one like the below

https://itsolutionstuff.com/post/how-to-remove-special-characters-by-mysql-custom-functionexample.html

DELIMITER $$

DROP FUNCTION IF EXISTS `removeSpacialChar` $$
CREATE DEFINER=`degree`@`%` FUNCTION `removeSpacialChar`(`in_str` varchar(4096)) RETURNS varchar(4096) CHARSET utf8
BEGIN

      DECLARE out_str VARCHAR(4096) DEFAULT ''; 

      DECLARE c VARCHAR(4096) DEFAULT ''; 

      DECLARE pointer INT DEFAULT 1; 



      IF ISNULL(in_str) THEN

            RETURN NULL; 

      ELSE

            WHILE pointer <= LENGTH(in_str) DO 



                  SET c = MID(in_str, pointer, 1); 



                  IF (ASCII(c) >= 48 AND ASCII(c) <= 57) OR (ASCII(c) >= 65 AND ASCII(c) <= 90) OR (ASCII(c) >= 97 AND ASCII(c) <= 122) THEN

                      SET out_str = CONCAT(out_str, c); 

                  ELSE

                      SET out_str = CONCAT(out_str, ' ');   

                  END IF; 



                  SET pointer = pointer + 1; 

            END WHILE; 

      END IF; 



      RETURN out_str;

END $$

DELIMITER ;





SELECT * FROM articles WHERE removeSpacialChar(articles.article_title)="this is my article name";

Upvotes: 0

Related Questions