Search my database (not working)

Revolutsio · January 11, 2020

Could somebody help me with a little problem I am having, I havew spent the last two weeks trying different ways to search my database without success.

What I want to do is have an input field that I can search only one column of my database. (it is a game collection) for example I want to search the game 'Medal of Honor' i type this in the search field and click submit button and it should find all the entries with that name in the database.

I have tried a few online tutorials and nothing works.

here is some code that i tried.

<?php

$host = "localhost";
$user = "root";
$password ="";
$database = "csv_db";

$id = "";
$game = "";
$year = "";
$platform = "";

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// connect to mysql database
try{
    $connect = mysqli_connect($host, $user, $password, $database);
} catch (mysqli_sql_exception $ex) {
    echo 'Error';
}

// get values from the form
function getPosts()
{
    $posts = array();
    $posts[0] = $_POST['id'];
    $posts[1] = $_POST['game'];
    $posts[2] = $_POST['year'];
    $posts[3] = $_POST['platform'];
    return $posts;
}

// Search

if(isset($_POST['search']))
{
    $data = getPosts();
    
    $search_Query = "SELECT * FROM games WHERE id = $data[0]";
    
    $search_Result = mysqli_query($connect, $search_Query);
    
    if($search_Result)
    {
        if(mysqli_num_rows($search_Result))
        {
            while($row = mysqli_fetch_array($search_Result))
            {
                $id = $row['ID'];
                $game = $row['GAME'];
                $year = $row['YR'];
                $platform = $row['PLATFORM'];
            }
        }else{
            echo 'No Data For This Id';
        }
    }else{
        echo 'Result Error';
    }
}


// Insert
if(isset($_POST['insert']))
{
    $data = getPosts();
    $insert_Query = "INSERT INTO `games`(`Game`, `YR`, `PLATFORM`) VALUES ('$data[1]','$data[2]',$data[3])";
    try{
        $insert_Result = mysqli_query($connect, $insert_Query);
        
        if($insert_Result)
        {
            if(mysqli_affected_rows($connect) > 0)
            {
                echo 'Data Inserted';
            }else{
                echo 'Data Not Inserted';
            }
        }
    } catch (Exception $ex) {
        echo 'Error Insert '.$ex->getMessage();
    }
}

// Delete
if(isset($_POST['delete']))
{
    $data = getPosts();
    $delete_Query = "DELETE FROM `games` WHERE `ID` = $data[0]";
    try{
        $delete_Result = mysqli_query($connect, $delete_Query);
        
        if($delete_Result)
        {
            if(mysqli_affected_rows($connect) > 0)
            {
                echo 'Data Deleted';
            }else{
                echo 'Data Not Deleted';
            }
        }
    } catch (Exception $ex) {
        echo 'Error Delete '.$ex->getMessage();
    }
}

// Edit
if(isset($_POST['update']))
{
    $data = getPosts();
    $update_Query = "UPDATE `games` SET `GAMES`='$data[1]',`YR`='$data[2]',`PLATFORM`=$data[3] WHERE `ID` = $data[0]";
    try{
        $update_Result = mysqli_query($connect, $update_Query);
        
        if($update_Result)
        {
            if(mysqli_affected_rows($connect) > 0)
            {
                echo 'Data Updated';
            }else{
                echo 'Data Not Updated';
            }
        }
    } catch (Exception $ex) {
        echo 'Error Update '.$ex->getMessage();
    }
}



?>


<!DOCTYPE Html>
<html>
    <head>
        <title>PHP INSERT UPDATE DELETE SEARCH</title>
    </head>
    <body>
        <form action="php_insert_update_delete_search.php" method="post">
            <input type="number" name="id" placeholder="ID" value="<?php echo $id;?>"><br><br>
            <input type="text" name="game" placeholder="Game" value="<?php echo $game;?>"><br><br>
            <input type="text" name="year" placeholder="Year" value="<?php echo $year;?>"><br><br>
            <input type="number" name="platform" placeholder="Platform" value="<?php echo $platform;?>"><br><br>
            <div>
                <!-- Input For Add Values To Database-->
                <input type="submit" name="insert" value="Add">
                
                <!-- Input For Edit Values -->
                <input type="submit" name="update" value="Update">
                
                <!-- Input For Clear Values -->
                <input type="submit" name="delete" value="Delete">
                
                <!-- Input For Find Values With The given ID -->
                <input type="submit" name="search" value="Find">
            </div>
        </form>
    </body>
</html>

and here is the error i get.

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 in H:\xampp\htdocs\php_insert_update_delete_search.php:41 Stack trace: #0 H:\xampp\htdocs\php_insert_update_delete_search.php(41): mysqli_query(Object(mysqli), 'SELECT * FROM g...') #1 {main} thrown in H:\xampp\htdocs\php_insert_update_delete_search.php on line 41

can anybody help me do I search engine for my database

ginerjm · January 11, 2020

You are using an array for your inputs. Why? Why not simple variable names for each input field.

You say that you want to search for the name of a game. Is that stored in a field called "id"? Seems more like it belongs in your "game" field. Plus - you should enclose that in quotes since it is a string value. And you may need braces around the variable name as well.

Barand · January 11, 2020

Have you checked the content of $data[0]?

If it is blank, your query becomes "SELECT * FROM games WHERE id = ", and that would give you that syntax error.

edit:

P.S. You should be using a prepared statement when dealing with POST data

Edited January 11, 2020 by Barand

mac_gyver · January 11, 2020

the most immediate problem is you are not using the correct $_POST field and table column in the WHERE clause in the SELECT query (edit: i realized while writing this that the SELECT query you have shown is part of the update process, to retrieve a specific row of data to populate the form fields with, not part of a name search.) if you are entering the name (or partial name) of a game to search for, wouldn't you be searching the table column holding the game's name? why are you trying to match the id column?

next, don't write code like this. there's a bunch of problems that have resulted in a wall of code that's both insecure and has created a 'cannot see the forest for the trees' problem (which is perhaps why you are using the wrong field/column in the WHERE clause.)

a laundry list of issues -

don't create a bunch of discrete variables for each different form you write code for. this is just a waste of typing time. instead, operated on the form data as a set, by keeping the data as an array, and operating on the elements in the array.
by using exceptions for database statement errors, any connection, query, prepare, and execute error will transfer control to the exception handler. therefore, any discrete error handling logic in your code won't ever be executed and should be removed.
related to #2, the only exception handling try/catch block you should have in your code are for those errors that are recoverable, that your code can do something about, such as dealing with the inserting/updating of duplicate or out of range user submitted data. all other database statement errors are non-recoverable and there's no good reason for your code to catch these exceptions. just let php catch and handle these exceptions.
by using exceptions for errors and letting php catch them, php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed or logged the same as php errors.)
'function getPosts()' - don't do this. you want code to be easy to write and debug and be readable by everyone. by intentionally using numerical indexes, you have made more work while writing this code and made more work for anyone trying to read/maintain the code. also, once you write and test the code for a function, you should not find yourself regularly editing the code. functions should not contain application specific code that you must change each time you do something new.
external data can be anything and cannot be trusted. you must validate all external data before using it.
you should use a prepared query when supplying external/unknown data to the sql query statement.
items #6 and #7 will help avoid the current error, because you would not attempt to run a query if an expected input is empty and a numerical input that can be empty/null won't produce a query error.
a name or partial name search can match more than one row (the same game name for more than one platform.) you would loop over the result from such a query and display as many rows of data that the query matched. to update/delete the data for a specific row, you would produce an edit link and a delete form for each row that is displayed. the edit link would contain the id of the row. when you click the edit link, the code would query for an fetch the data matching that row and populate the form field values. the current SELECT query seems to be for this part of the process, not for a name search. you need one more SELECT query.

Edited January 11, 2020 by mac_gyver

Revolutsio · January 11, 2020

44 minutes ago, mac_gyver said:

the most immediate problem is you are not using the correct $_POST field and table column in the WHERE clause in the SELECT query (edit: i realized while writing this that the SELECT query you have shown is part of the update process, to retrieve a specific row of data to populate the form fields with, not part of a name search.) if you are entering the name (or partial name) of a game to search for, wouldn't you be searching the table column holding the game's name? why are you trying to match the id column?

next, don't write code like this. there's a bunch of problems that have resulted in a wall of code that's both insecure and has created a 'cannot see the forest for the trees' problem (which is perhaps why you are using the wrong field/column in the WHERE clause.)

a laundry list of issues -

don't create a bunch of discrete variables for each different form you write code for. this is just a waste of typing time. instead, operated on the form data as a set, by keeping the data as an array, and operating on the elements in the array.

by using exceptions for database statement errors, any connection, query, prepare, and execute error will transfer control to the exception handler. therefore, any discrete error handling logic in your code won't ever be executed and should be removed.

related to #2, the only exception handling try/catch block you should have in your code are for those errors that are recoverable, that your code can do something about, such as dealing with the inserting/updating of duplicate or out of range user submitted data. all other database statement errors are non-recoverable and there's no good reason for your code to catch these exceptions. just let php catch and handle these exceptions.

by using exceptions for errors and letting php catch them, php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed or logged the same as php errors.)

'function getPosts()' - don't do this. you want code to be easy to write and debug and be readable by everyone. by intentionally using numerical indexes, you have made more work while writing this code and made more work for anyone trying to read/maintain the code. also, once you write and test the code for a function, you should not find yourself regularly editing the code. functions should not contain application specific code that you must change each time you do something new.

external data can be anything and cannot be trusted. you must validate all external data before using it.

you should use a prepared query when supplying external/unknown data to the sql query statement.

items #6 and #7 will help avoid the current error, because you would not attempt to run a query if an expected input is empty and a numerical input that can be empty/null won't produce a query error.

a name or partial name search can match more than one row (the same game name for more than one platform.) you would loop over the result from such a query and display as many rows of data that the query matched. to update/delete the data for a specific row, you would produce an edit link and a delete form for each row that is displayed. the edit link would contain the id of the row. when you click the edit link, the code would query for an fetch the data matching that row and populate the form field values. the current SELECT query seems to be for this part of the process, not for a name search. you need one more SELECT query.

Thank you for your reply this is not my code I found this on youtube I put this as an example of what i tried to do to get a search field on my screen

Edited January 11, 2020 by Revolutsio
MORE INFORMATION

Revolutsio · January 11, 2020

Thank you all the replys.

What should i do to make a search, edit and insert form to my database

ginerjm · January 12, 2020

Quote

insert form to my database

What does that line mean?

Sign In

Search my database (not working)

Recommended Posts

Revolutsio

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Revolutsio

Link to comment

Share on other sites

Revolutsio

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information