5

I’m trying to write a PHP script with MySQLi to query a database.

I’d like it if the user-input could be checked against the database and then return a result from the column ‘conjugation’ if the string in the column ‘root’ of the table ‘normal_verbs’ is in the input.

So if the user input is something like "foobar", and the root-column has "foo", I'd like it to see 'foo' in 'foobar' and return that value of 'conjugation' in that row.

I can’t seem to get the query to work like I want it to. The one I'm using below is basically just a placeholder. I don't entirely understand why it doesn't work.

What I’m trying, is this :

        function db_connect() {

            static $connection;

            if(!isset($connection)) {
                $connection = mysqli_connect('localhost','user','password','Verb_Bank');
            }

            if($connection === false) {
                return mysqli_connect_error(); 
            }
            return $connection;
        }

        function db_query($query) {
            $connection = db_connect();
            $result = mysqli_query($connection,$query);

            return $result;
        } 

        function db_quote($value) {
                $connection = db_connect();
                return "'" . mysqli_real_escape_string($connection,$value) . "'";
            }    
$m= db_query("SELECT `conjugation` from normal_verbs where `root` in (" . $y . ")");
    if($m === false) {
        // Handle failure - log the error, notify administrator, etc.
    } else {
        // Fetch all the rows in an array
        $rows = array();
        while ($row = mysqli_fetch_assoc($m)) {
            $rows[] = $row;
        }
    }
    print_r ($rows);

It’s not giving me any errors, so I think it’s connecting to the database.

EDIT2: I was wrong. I was missing something obvious due to misunderstanding MySQLi and have edited the code accordingly. So the above code does work in that it connects to the database and returns a result, but I'm still stumped on a viable SQL statement to do what I want it to do.

10
  • 2
    If those curly quotes are in there you're getting syntax errors, they're in the logs. Add error reporting to the top of your file(s) right after your opening <?php tag error_reporting(E_ALL); ini_set('display_errors', 1); Commented Feb 12, 2016 at 21:11
  • 3
    Your script is at risk for SQL Injection Attacks. Learn about prepared statements for MySQLi. Commented Feb 12, 2016 at 21:12
  • 1
    Have you tried running the query without the where? I would suggest doing SELECT 'conjugation' FROM 'normal_verbs' LIMIT 1. Assuming your database has data in it, that will tell you if you are connecting to your actual database. Commented Feb 12, 2016 at 21:14
  • 1
    @JayBlanchard Yes, but in some cases you can't do that based on hosting set up. Granted, a programmer should have access to that, but still... Commented Feb 12, 2016 at 21:22
  • 1
    You are using quotes for table and column names,switch to backticks.Also where is db_query defined? Commented Feb 13, 2016 at 0:42

2 Answers 2

4

Please try this:

SELECT 'conjugation' FROM 'normal_verbs' WHERE " . $y . " LIKE CONCAT('%',root,'%')

It selects all rows where root contains $y anywhere.

In addition, your code is vulnerable to SQL injections. Please look here for more information.

Sign up to request clarification or add additional context in comments.

4 Comments

Thank you. Will this select all rows where the root table has a substring of $y rather than the entirety of $y?
Yes, it selects all rows where root is contained in $y. If you want only one result, you can sort them by some parameter and limit them to one.
Okay, so this more or less worked for me, except that I had to change the "+$y+" to (" . $y . "). Using "+$y+" wasn't doing anything at all. I'd like to know why, but I'm just thrilled that it worked. Thank you!
That was my error, in PHP you concatinate strings using a . and not a + like in many other languages (e.g. java)
0

Try this SQL Query Like this

SELECT `conjugation` from normal_verbs where `root` like '%$y%'

2 Comments

That's giving me a weird error telling me that the variable in my print_r statement is now undefined. I put it in as $m= db_query("SELECT conjugation from normal_verbs where root like '%$y%'"); Have I missed some punctuation somewhere or something?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.