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.

DCM
  • 63
  • 9
  • 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 ` – Jay Blanchard Feb 12 '16 at 21:11
  • 3
    [Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). – Jay Blanchard Feb 12 '16 at 21:12
  • You're running the query, but not fetching the data from it with something like `fetch()` – Jay Blanchard Feb 12 '16 at 21:13
  • 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. – Cayde 6 Feb 12 '16 at 21:14
  • Error checking will tell you if you're actually connecting to the database @user2547075 – Jay Blanchard Feb 12 '16 at 21:18
  • 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... – Cayde 6 Feb 12 '16 at 21:22
  • I have never seen an instance where you couldn't employ error checking in the code. If there are hosts out there that disallow error checking they should be out of business @user2547075 – Jay Blanchard Feb 12 '16 at 21:23
  • @JayBlanchard I agree. I have seen it though. Not really a big deal... When I wrote my answer, I hadn't seen yours yet. If I had, I wouldn't have said anything. – Cayde 6 Feb 12 '16 at 21:34
  • 1
    You are using quotes for table and column names,switch to backticks.Also where is db_query defined? – Mihai Feb 13 '16 at 00:42
  • @Mihai You're right. My apologies for the omission. I'm not sure how it got left out. I've added it now. – DCM Feb 13 '16 at 01:18

2 Answers2

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.

Michael Wagner
  • 1,018
  • 8
  • 20
  • Thank you. Will this select all rows where the `root` table has a substring of $y rather than the entirety of $y? – DCM Feb 13 '16 at 02:27
  • 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. – Michael Wagner Feb 13 '16 at 09:34
  • 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! – DCM Feb 13 '16 at 16:13
  • That was my error, in PHP you concatinate strings using a . and not a + like in many other languages (e.g. java) – Michael Wagner Feb 13 '16 at 16:17
0

Try this SQL Query Like this

SELECT `conjugation` from normal_verbs where `root` like '%$y%'
Vadivel S
  • 660
  • 8
  • 15
  • 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? – DCM Feb 13 '16 at 04:34
  • try this http://stackoverflow.com/questions/29266134/weird-error-in-phpmyadmin-it-is-installed-successfully-but-still-not-working problem soved – Vadivel S Feb 13 '16 at 04:51