Skip to content
Advertisement

display multiple results from a sql query

so I am trying to display multiple results from a database when a query is searched, the query is passed from a search box on another page.

I have it displaying one result, but that is all it will display. I need it to display all the results that are relevant to the search query.

the php code is below

<meta charset="UTF-8">
<?php
    $mysqli = new mysqli('localhost', 'scott', 'tiger','courses');
    if ($mysqli->connect_errno) 
    {
        die('Database connection failed');
    }
    //$m->set_charset('utf8');
    $search_sql = "
    SELECT title, summary, id
    FROM course
    WHERE title LIKE '%".$_POST['searchBar']."%'";
    $result = $mysqli->query($search_sql) or die($mysqli->error);
    $search_result = $result->fetch_assoc();
?>
<!doctype html>
<head>
    <meta charset="utf-8">
    <h1>Search Results</h1>
</head>
<body>
    <h3><?= $search_result['title'] ?></h1>
    <p><?= $search_result['summary'] ?></p>
</body>

and the code for the search bar

<!doctype html>
<html>
<Head>
    <meta charset = "utf-8">
    <title>Search</title>
</head>
<body>
    <h2>Search</h2>
    <form name="search" method="post" action="SearchResultsPage.php">
        <input name="searchBar" type="text" size="40" maxlength="60" />
        <input type="submit" name="Submitsearch" value="Search" />
    </form>
    </body>

Does anyone have any suggestions?

Thanks in advance;

Advertisement

Answer

You will need to place it in a while loop to show multiple results, the fetch function you’re using will only retrieve one row, if you place it in a loop you can keep fetching until there is nothing to fetch:

//$m->set_charset('utf8');
$search_sql = "
SELECT title, summary, id
FROM course
WHERE title LIKE '%".$_POST['searchBar']."%'";
$result = $mysqli->query($search_sql) or die($mysqli->error);
?>
<!doctype html>
<head>
    <meta charset="utf-8">
    <h1>Search Results</h1>
</head>
<body>
    <?PHP while($search_result = $result->fetch_assoc()) { ?>
    <h1><?= $search_result['title'] ?></h1>
    <p><?= $search_result['summary'] ?></p>
    <?PHP } ?>
</body>

P.S. your code is vulnerable to SQL injection, you should read about prepared statements. More Info on that

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement