php- cant get result from mysql (mysqli)

Question

I'm attempting to set up an API for an app project.

I've got a mysql table called 'users', which I've added a row to.

using the code:

// Create connection
$mysqli = new mysqli("localhost","user", "pass", "db");

// Check connection
if($mysqli->connect_errno){

    $result = "Failed to connect to MySQL: " . mysqli_connect_error();
    print_r( json_encode($result) );    
    return false;

}

$row = $mysqli->query("SELECT * FROM users");
print_r( json_encode($row) );

I get an empty result, how come? (connection doesn't throw an error)

to be exact i get:

{"current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null}

EDIT:

got the answer to ym original question, thanks!

so now using the code:

$row = $mysqli->query("SELECT * FROM users WHERE email = '".$email."'");
$result = $row->fetch_array();
print_r( json_encode($result) );

I get the result:

{"0":"test","username":"test","1":"[email protected]","email":"[email protected]","2":"test","password":"test","3":"2013-10-18 22:22:53","date_registered":"2013-10-18 22:22:53","4":"1","id":"1"}

where what i want is something like:

{"username":"test","password":"test","email":"[email protected]", ...etc }

how do i get that?

If you've used mysql_* previously.. Could you obtain results from running mysql_query()? I don't think so. fetch an array from the query — Daryl Gill
– Daryl Gill, Commented Oct 18, 2013 at 21:50
Edited my answer. You need to use mysqli_fetch_assoc() to avoid duplicating the results with their numeric indexes. — Francisco Presencia
– Francisco Presencia, Commented Oct 18, 2013 at 22:02

gloomy.penguin · Accepted Answer · 2013-10-18 22:08:54Z

2

Try this:

$result = $mysqli->query("SELECT * FROM users");
$row = $result->fetch_array(MYSQLI_ASSOC);
print json_encode($row);   // json_encode returns a string...

Try this for your associative array:

while($row = $result->fetch_array(MYSQLI_ASSOC))
{
   $rows[] = $row;
}
print json_encode($rows);

or you can try... $rows = $result->fetch_all(MYSQLI_ASSOC);

edited Oct 18, 2013 at 22:08

answered Oct 18, 2013 at 21:56

gloomy.penguin

5,9206 gold badges37 silver badges59 bronze badges

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

Comments

Francisco Presencia · Accepted Answer · 2013-10-18 22:02:42Z

1

mysqli_query() will return a mysqli_result, you need to fetch (as an array in this case) your rows before doing anything with them. Added a line:

// Create connection
$mysqli = new mysqli("localhost","user", "pass", "db");

// Check connection
if($mysqli->connect_errno){

    $result = "Failed to connect to MySQL: " . mysqli_connect_error();
    print_r( json_encode($result) );    
    return false;

}

// Get a mysql_result
$row = $mysqli->query("SELECT * FROM users");

// Get it into an array without numeric indexes
$result = $row->fetch_assoc();

// Display the row
print_r( json_encode($result) );

edited Oct 18, 2013 at 22:02

answered Oct 18, 2013 at 21:57

Francisco Presencia

8,8757 gold badges50 silver badges96 bronze badges

Collectives™ on Stack Overflow

php- cant get result from mysql (mysqli)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related