Remove Last Word in SQL row if maxlength is reached? (PHP)

Question

A php script I have is extracting data from an XML file to a MySQL database.

It is set-up as followed:

$sql   = "INSERT IGNORE INTO `tablename` (columnname) VALUES ('text text text text text text $variablename text text $variablename2 text text')

$variablename and $variablename2 are both variables being extracted from the XML file, and have varying lengths.

So, I know, if I was just dealing with PHP I could use strlen or a variation of (see How to Truncate a string in PHP to the word closest to a certain number of characters? but 'columnname' in my sql database is not a variable.

And 'columnname' is what is limited to the characters. I set this in mySQL to VARCHAR MAX=106 -- and that works --

But when re-outputting this to my web host it takes the data from the SQL database which is stopped at the maxlength cut-off mid-word.

I want it so that if it does reach the maxlength, the last word is just removed.

Could this be done, perhaps when inputting into the SQL table?

Or even perhaps in the PHP file outputting back to my web host?, such as here:

$rs->data_seek(0);
while($res = $rs->fetch_assoc()) {
   $a_topic = array(
   "columnname" => $res["columnname"]

or maybe in the $params here?

  $params = array(
    'status' => $share['columnname'],

Big thanks!

Would imagine this is a dupe... but going to throw out there that basic string functions as used in the link you provided translate to pretty much any programming language as well as SQL. `str_replace()` -> `replace()`, `strpos()` -> `substring_index()`, etc... https://dev.mysql.com/doc/refman/5.0/en/string-functions.html — ficuscr, Jun 01 '15 at 22:58

score 0 · Accepted Answer · answered Jun 01 '15 at 23:10

0

function cut($string, $maxLength)
{
    $revertString = strrev($string);
    $spaceRevertedPos = strpos($revertString, ' ', strlen($string)-$maxLength);
    $revertedCutString = substr($revertString, $spaceRevertedPos);

    return strrev($revertedCutString);
}

DEMO

answered Jun 01 '15 at 23:10

deathpote

210
1
8

(I didn't checked the inputs, just make sure `$string` is a string, `$maxLength` an int and `strlen($string) > $maxLength`) – deathpote Jun 01 '15 at 23:13
Thanks for the reply. As I attempted to explain I would do something like this, but I am confused because I do not have a $string to deal with -- only a MySQL column. Are you saying to convert the column to a string, if possible? – bbruman Jun 01 '15 at 23:23
can't you cut before inserting into DB ? or else you can translate the same algorithm in SQL i think – deathpote Jun 01 '15 at 23:34
Ha, yeah, I guess I can. I was overcomplicating it, and searching for the wrong things. I got it by using a similiar method purely in php. I'll up your answer – bbruman Jun 02 '15 at 03:55

Remove Last Word in SQL row if maxlength is reached? (PHP)

1 Answers1