Calling some part only with $key

$key='a'; //$_GET['key'] = 'a';
$like="%$key%";

$sql="SELECT title, contents FROM myTable 
WHERE title like ? or contents like ? ORDER BY editDate DESC LIMIT 2";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$like, $like]); 
while ($searchL=$searchQ->fetch()) {
echo $searchL['title'] . '<br>' . $searchL['contents']. ' <hr>';
}

The code above works fine. It produces the quote below.

SELECT title, contents // before modification
SELECT title, left(contents, 6) as contents // after modification 

If I modify like the code above, it produces the result below

I like to make the result like the following.

The code below doesn’t work correctly, but I hope that it shows what I want.

 including$key(contents, 6) FROM myTable 

Is it possible?

I think what you are trying to do is grab the characters before and after a search character. In the word ‘happy’, you want to grab ‘ha’ and ‘py’ if your search term is ‘p’ right?

If so, I think perhaps you want to take a look at the function SUBSTRING_INDEX which can be used for this purpose.

Do know that “string” in this function can be your content field.

1 Like
$sql="SELECT title, SUBSTRING_INDEX(contents, $key, 3) FROM myTable 
WHERE title like ? or contents like ? ORDER BY id DESC LIMIT 2";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$like, $like]);  

I try to change the code like the above by using SUBSTRING_INDEX(str, delim, count) .
Sadly It produces Fatal error like the below.

How can I fix it?

Maybe you need quotes around your $key variable, to show that it’s a string literal and not a column name.

$sql="SELECT SUBSTRING_INDEX(contents, '$key', 3) as conKey FROM myTable 
WHERE title like ? or contents like ? ORDER BY id DESC LIMIT 2";
$searchQ=$dbc-> prepare ($sql);
$searchQ-&_gt;execute([$like, $like]);
while ($searchL=$searchQ->fetch()) {
echo $searchL['conKey']. ' <hr>';
}

I modified it like the above.
And its result is the following.

 SUBSTRING_INDEX(contents, '$key', 2) as conKey 

if I change the code like the above, it produces the below.

 SUBSTRING_INDEX(contents, '$key', 1) as conKey 

if I change the code like the above, it produces the below.

my target result is the following which has 6 characters including the $key a.

I don’t follow the logic, I can see no logic in what you want to display and how, all I can see is that you want to add a space after each occurrence, highlight each occurrence in bold. You state 6 characters bus A si a i is 8 chars, or 5 without the spaces or 6 chars including spaces but excluding $key a. But then the second example is 7 chars using same logic. I just can’t see what you want to do ?

highlight each occurence in bold is just a explanation for you that it is relate to $key.
adding space is not real space it’s just sitepoint make it space when I make it bold for your uderstanding that it is relate to the $key a
What I really want is the following.

I am sorry for make you confusion.

6 characters could be 7 characters like the following.

if the would-be code below make my target result below, it would be logical.

SUBSTRING_INDEX(contents, '$key', 6) as conKey 

but its real output is, sadly, the following.

if the would-be code below make my target result below, it would be logical.

SUBSTRING_INDEX(contents, '$key', 7) as conKey 

but its real output is, sadly, the following.

Well what you are trying to do makes no sense without further explanation but let me try and help just using some text variables. You need to be looking at both substr() and strpos() functions. strpos() returns the position of the first occurrence whilst substr() returns the actual substring relative to that point.

Check out this example-

<?php
$contents='Asia is a continent';
$like='A';
$position=strpos($contents,$like);
$result=substr($contents,$position, 7);
echo $result.'<br>';

$contents='This country is big in population';
$like='a';
$position=strpos($contents,$like);
$result=substr($contents,$position, 7);
echo $result.'<br>';
?>

result -

Asia is
ation

Now you have a couple of other considerations

  1. ‘a’ and ‘A’ are separate characters so you will probably need to utilise strtoupper() function to convert all to upper for analysis.
  2. you will need to check the length of the returned substring because if it is near the end of the string and you still want 7 characters you will need to adjust the start position accordingly, something like this -
<?php
$contents='This country is big in population';
$like='a';
$position=strpos($contents,$like);
$result=substr($contents,$position-2, 7);
echo $result.'<br>';
?>

result -

ulation

I cant give you exact code because I am still not sure of the exact purpose and therefore my code may not work with other scenarios, for example what if original string is less than 7 characters, what if the $like appears multiple times, say $like = ‘a’ and string = ‘aaaaa’ what happens then?

Hopefully I have given you enough to develop your ideas

Adding the below code, it might be solved.

if ( strlen($result < 7) {
substr($contents, -7);
}

The 1st one should be, I think, the basis.

It will be not called from myTable because there is no records which has such one in the column “contents”.

It can be done , I think, in PHP.
What I want to know is whether SQL can do it or not?

(If SQL dot it partly, not completely, it will be good and okay because I can do the rest part in PHP.)

If you want a purely SQL solution, you might be better off asking the question in the SQL section of the forum. Asking in the PHP section is likely to have people concentrating on solving it using PHP.

Yep I agree, I am OK on PHP basics but really only use SQL for basic CRUD, anything like this I would use PHP. If it’s gotta be SQL = SQL section

LEFT(contents, 6) LIKE ?

you’d have to do some fairly fancy maths to get what he described in his OP.

the target character to start at is Max(X+Min(-6+(Length-X),0),0), given X as a 0-indexed search of the string for the original target character.
Pull a substring 6 characters long (if possible) from target character.

1 Like

I am afraid I don’t understand the cod above and I don’t know how to apply it.
.