CODEWITHSUNDEEP
phpmysqlsql
alen
alen

Reputation: 276

Substring in php or in sql query

I have a question. My friend told me PHP is better option but I'm not sure, I think sql might be faster. So basically I'm trying to get 50% of my db record and I use mysql function substr to do that. Of course I can do the same thing in PHP after data fetching, using this function right bellow.

sql

"select *,substr(text,1,(CHAR_LENGTH(text)*0.5)) as text FROM stories"

php

$data=$sql->fetchAll(PDO::FETCH_ASSOC);
$length=count($data);
for($i=0;$i<$length;$i++){
    $data[$i]['text']=substr($data[$i]['text'],0,strlen($data[$i]['text'])*0.5);}

What should I use and why? thank you

Upvotes: 1

Views: 997

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

This can be rather complicated, but your case is simple. The best query for you to use is:

select left(text, char_length(text)*0.5) as text
from stories

Notice that I removed the *. You should not return columns that you are not using. This is a no-brainer: Why pass more data back from the database than you are going to use? That just takes more time and uses resources inefficiently.

I also replaced substr() with left(). It just seems more in the spirit of what you are doing.

As to whether you should do the string manipulation in the database or in PHP, it depends on the operation. In this case, the operation is simple and easily expressed in MySQL. And, reducing the amount of data has the bonus of improving communication.

There are other cases where the string manipulation functionality of PHP is much better than MySQL. So, there are cases where it is better to do the formatting at the application level.

Upvotes: 2

Abhay
Abhay

Reputation: 3349

As much as your database handling stronger your code will be reduced

As per my opinion go with SQL queries that gives you required results.

  1. It reduces the load of communicate large records with code.
  2. List item speed will little faster

Upvotes: 0

Related Questions