CODEWITHSUNDEEP
phpmysql
user7254469
user7254469

Reputation:

Return only first 500 characters of text

I am having an issue where I cannot select the first 500 characters of each post. (Posts are stored in a MySQL database). The code I have right now is: 

$link = mysqli_connect( 'localhost', 'jfairbanks_dbuser', 'password' );
mysqli_select_db( $link, 'jfairbanks_database' );
$results = mysqli_query( $link, "SELECT post FROM Users where verified = 1 LIMIT 0 , 10" ); //this displayes all the posts that have been verified
while( $record = mysqli_fetch_assoc( $results ) ) {
 $post = $record['post'];
 print $post;
}
  mysqli_free_result( $results );
mysqli_close( $link );
?>

That code selects all characters from all posts that are verified and that's good, but I need to restrict the length of the post that is displayed. (I want only the first 500 characters to be seen).

I tried using LEFT(post, 500) among other things but that did not work.

Upvotes: 0

Views: 673

Answers (2)

Nick
Nick

Reputation: 7451

As I mentioned in the comments, you need to provide an alias for your field after using the LEFT() function in MySQL.

Your query would look like this: SELECT LEFT(post,500) AS post FROM Users ...

By aliasing it with post you can now access it in the associative array, as you were doing before:

$post = $record['post'];

Without an alias in the sql query, the array does not have a key of post and throws the exception that you received.

Upvotes: 0

e4c5
e4c5

Reputation: 53774

What you need is the LEFT function of mysql 

SELECT LEFT(post,500) AS post FROM Users where verified = 1 LIMIT 0 , 10

I didn't even know PHP had a left function until your comment? Or perhaps it was something you wrote? The usual way to take a substring in PHP is to use the substr function. But taking the substring in mysql is more efficient because there is less data been transferred.

Also note the addition of AS post to alias the generated column to post so that it can be retrieved in your PHP code.

Upvotes: 1

Related Questions