CODEWITHSUNDEEP
phphtmlmysqlsorting
Aaron F
Aaron F

Reputation: 47

PHP/MySQL: how to display the words before and after the searched item

In my database, I have these words: AA, AB, AC, AD, AE, AF, AG

I search for “AD” as:

<form method="post" action="AF9.php">
        <input type="submit" name="submit" value="search">
        <input type="text" name="search" />
</form>

I want to sort all the words in the database alphabetically. Then only display the searched value (i.e., AD) and the word before and the word after. Something like this:

Here is what I have written so far (your help is greatly appreciated):

<?php
include('includes/db_AF.php'); //includes the db credentials
$connection = @new mysqli(HOSTNAME, MYSQLUSER, MYSQLPASS, MYSQLDB);
if ($connection->connect_error) {
  die('Connect Error: ' . $connection->connect_error);
} else {

  $search=$_POST["search"];

  $query="SELECT word FROM wordtable2
  WHERE word like '%' 
  ORDER BY word ASC";   // I am sorting all the words in the database                       

  $result_obj = '';
  $result_obj = $connection->query($query);         

  while($result = $result_obj->fetch_array(MYSQLI_ASSOC)) { 
    $items[] = $result;
  }                             

  foreach ($items as $item) {
    echo $item['word']; //Here I need to echo the results, but don't know how to
  }
}
?>

Upvotes: 1

Views: 210

Answers (2)

Kenny
Kenny

Reputation: 1553

There may be a more elegant way to do this in SQL, but the easiest way I can think of actually involves two queries.

using your example...

lets say your database has the following entries

AA, AB, AC, AD, AE, AF

Following your example, you have the value AD and you want to retreive AC and AE.

First, do a query like this...

SELECT word FROM wordtable2
    WHERE word > 'AD'
    ORDER BY word ASC
    LIMIT 1;

Then do a query like this...

SELECT word FROM wordtable2
    WHERE word < 'AD'
    ORDER BY word DESC
    LIMIT 1;

Upvotes: 2

user1780242
user1780242

Reputation: 551

I think you want to say something like:

echo $items[$item];

If used in the context of your foreach() loop, this will display each word in your $items array.

Upvotes: 0

Related Questions