CODEWITHSUNDEEP
phpsqlsearch
Matt Murphy
Matt Murphy

Reputation: 265

Selecting multiple data from a database through PHP

I have a search form that is able to retrieve the username of a user, however I can't figure out how to get it to return more than that, I want it to display the first names and last names too.

Below is the code at the minute that works, but when I try and add in more variables, for example if ($stmt = $connection->prepare ("SELECT Username FROM users WHERE Username LIKE ?")) then it doesn't return anything at all and asks to insert a search query.

I have also tried if ($stmt = $connection->prepare ("SELECT Username FROM users WHERE Username LIKE %?%")) and LIKE "%?%")), but no results.

search.php

<?php 
include 'connection.php';
if(isset($_POST['searchsubmit']))
{
    include 'searchform.php';
        $name=$_POST['name'];
         if ($stmt = $connection->prepare ("SELECT Username FROM users WHERE Username LIKE ?")) 
        {
            $stmt->bind_param('s', $name);
            $stmt->execute();
            $stmt->bind_result($personresult);
            $stmt->fetch();
            ?>
            <center>
                <BR>
                <h1>Search Results are as follows:</h1>
                <h2>USERNAMES</h2>
                <BR>
            <?php

            print_r($personresult);
               ?>
            </center>
            <?php
        } 
        else
        { 
            echo  "<p>Please enter a search query</p>"; 
        } 
    }
 else 
    {
     echo "NOT SET!";
     }

Upvotes: 1

Views: 892

Answers (4)

Professor Abronsius
Professor Abronsius

Reputation: 33813

<?php 

    include 'connection.php';
    if( isset( $_POST['searchsubmit'] ) ) {

        include 'searchform.php';
        $name=$_POST['name'];

        if ( $stmt = $connection->prepare ("SELECT `Username`,`firstname`,`lastname` FROM `users` WHERE `Username` LIKE ?") ) {
            /* not 100% sure about whether this is required here like this or not but usually a like expression uses '%' as a wildcard */

            $var='%'.$name'.%';
            $stmt->bind_param('s', $var );

            $res=$stmt->execute();
            /* 3 columns selected in query, 3 columns bound in results */
            $stmt->bind_result( $personresult, $firstname, $lastname );

            if( $res ){

                $stmt->fetch();

                echo "
                    <center>
                        <BR>
                        <h1>Search Results are as follows:</h1>
                        <h2>USERNAMES</h2><!-- 3 columns/variables -->
                        {$personresult},{$firstname},{$lastname}
                        <BR>
                    </center>";
            }

        } else { 
            echo  "<p>Please enter a search query</p>"; 
        }
    } else {
        echo "NOT SET!";
    }

    $stmt->close();
    $connection->close();
?>

Upvotes: 0

RomanPerekhrest
RomanPerekhrest

Reputation: 92854

If the database server cannot successfully prepare the statement, PDO::prepare() returns FALSE or emits PDOException (depending on error handling)

add this line in connection.php right after creating connection object:

$connection->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

At least, you can trace possible errors

Upvotes: 0

Zak
Zak

Reputation: 7515

You are only calling Username .. You need to be calling *

SELECT * FROM users WHERE Username LIKE ?

This is my personal script I use:

<?php

$dbservername = "localhost";
$dbusername = "db_user";
$dbpassword = "pass";
$dbname = "db";
// Create connection
$conn = new mysqli($dbservername, $dbusername, $dbpassword, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if (!empty($_POST["username"])) {
$username = $_POST["username"];
}
if (!empty($_POST["password"])) {
$password = $_POST["password"];
}

    $sql = "SELECT * FROM users";
    $result = $conn->query($sql);
   if ($result->num_rows > 0) {

     while ($row = $result->fetch_assoc()) {
     echo $row["Username"] . " " . $row["Firstname"] . " " .  $row["Lastname"] . "<br>";
if ($row["Username"] == $username && $row["Password"] == $password) {
            echo "success";
            // do more stuff here like set session etc
        } else {
            $echo "incorrect username and/or password";
        }
     }
    }
?>

Upvotes: 1

marian0
marian0

Reputation: 654

Are you initializing the statement object with mysqli_stmt_init?

See mysqli_stmt_init and mysqli-stmt.prepare

Upvotes: 0

Related Questions