trouble selecting data from a database

Question

I just want to select and display data from a database. I think I am following all the tutorials etc. online but something is wrong. My code:

<?php

$user = 'root';
$pass = 'root';
$db='test';
$host ='localhost';
$name = 'ryan';


$mysqli = new mysqli($host, $user, $pass, $db);
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}
    else{
        echo 'Checkpoint 1 <br>';
    }

$sql = "SELECT * FROM user WHERE name LIKE ryan";
$result = $mysqli->query($sql);
$num_results = $result->num_rows;
echo'checkpoint 2';
$row = $result->fetch_assoc();
echo'checkpoint 3';
?>

I can get to 'checkpoint 2' but for some reason the fetch_assoc() function does not do anything. I am using Netbeans, and the fetch_assoc() does not even turn green as the num_rows function does. fetch_assoc does turn green if I remove both parentheses at the end.

Alex Andrei · Answer 1 · 2016-01-12T12:38:43.070

1

Your mysql query syntax is wrong.
LIKE takes a pattern, by not enclosing it in quotes, you basically provided a column name

Change your query like this

SELECT * FROM `user` WHERE `name` LIKE 'ryan';

Also add some error handling for the query

$result = $mysqli->query($sql) or die(mysqli->error);

edited Jan 12 '16 at 12:38

answered Jan 12 '16 at 08:13

Alex Andrei

7,315
3
28
42

this also worked, maybe i got lucky when i used mysql keywords w/o the back ticks. – ratrace123 Jan 12 '16 at 08:23
Points 1 and 2; they don't need to be escaped with backticks, as they are not reserved words, they're only keywords. There are no `(R)` next to those functions https://dev.mysql.com/doc/refman/5.5/en/keywords.html – Funk Forty Niner Jan 12 '16 at 12:32
I stand corrected, thanks, edited the answer. – Alex Andrei Jan 12 '16 at 12:39

score 0 · Accepted Answer · answered Jan 12 '16 at 08:11

0

Try this query:

$sql = "SELECT * FROM user WHERE name LIKE 'ryan'";

answered Jan 12 '16 at 08:11

RaisoMos

159
4
11

you sir are a genius, this worked perfect. Thank you so much. – ratrace123 Jan 12 '16 at 08:14
@ratrace123 You can also use this query for wildcard search `$sql = "SELECT * FROM user WHERE name LIKE '%ryan%'";` – Gunasegar Jan 12 '16 at 09:45

score 0 · Answer 3 · answered Jan 12 '16 at 08:12

0

You have to bind your like statement as

$param = "%ryan%";
$result = $mysqli->prepare("SELECT * FROM user WHERE name LIKE ?");
$result->bind_param("s", $param);
$result->execute();
$num_results = $result->num_rows;
$row = $result->fetch_assoc();

answered Jan 12 '16 at 08:12

Saty

22,443
7
33
51

is there a benefit in binding a prepared statement each time vs just asking straight as I have done without binding first? – ratrace123 Jan 12 '16 at 08:20
Bind statement is secure and prevent you form sql injection!! – Saty Jan 12 '16 at 08:48

score 0 · Answer 4 · answered Jan 12 '16 at 08:15

<?php

$user = 'root';
$pass = 'root';
$db='test';
$host ='localhost';
$name = 'ryan';


$mysqli = new mysqli($host, $user, $pass, $db);
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}
    else{
        echo 'Checkpoint 1 <br>';
    }

$sql = "SELECT * FROM user WHERE name LIKE 'ryan'";
$result = $mysqli->query($sql);
$num_results = $result->num_rows;
print_r($num_results);exit;
echo'checkpoint 2';
$row = $result->fetch_assoc();
echo'checkpoint 3';
?>

score 0 · Answer 5 · answered Jan 12 '16 at 08:17

You can use string input with LIKE statement by using the quotes and % sign.

Modified Code:

$sql = "SELECT * FROM user WHERE name LIKE '%ryan%'";
$result = $mysqli->query($sql);
$num_results = $result->num_rows;
$row = $result->fetch_assoc();

What i have changed?

Add single quotes inside the double quote for input string.
Add % sign at start and end for matching type both

trouble selecting data from a database

5 Answers5