$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.
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.
$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.
$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.
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
‘a’ and ‘A’ are separate characters so you will probably need to utilise strtoupper() function to convert all to upper for analysis.
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
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.
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.