Unable to retrieve row from MySQL using PHP

Question

I am still new to PHP and I am trying to retrieve data from MySQL using PHP. I have a list of id stored in a csv file (first column of the csv). So I am trying to retrieve a data based on that id.

//MySQL Connection
$con=mysqli_connect($hostname,$username,$password,$dbName);

//CSV File
$file_handle = fopen($fileName, "r");

$sql = "SELECT count(*) AS total FROM user WHERE user_id = ";

$i = 0;
while ($i < 50) {

    //Retrieving user_id from csv file
    $file_line = fgetcsv($file_handle, 1024);
    $query = $sql . $file_line[0];

    //Retrieving data from mySQL
    $result = mysqli_query($con,$query);   

    //Tested with single row but still giving me an error
    //$row = $result->fetch_assoc();
    $row = mysqli_fetch_array($result);
    echo $row;

    $i++;
}

 //close file
 fclose($file_handle);

 //close the connection
mysqli_close($con);

?>

The error message I received:

 Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\.. on line 59

So, I am not sure what I did wrong. Any help would be great.

UPDATE:

Thanks to the help of everyone here I was able to figure out what was wrong. Here's an updated code of what I did.

$i = 0;
while ($i < 50) {

    //Retrieving user_id from csv file
    $file_line = fgetcsv($file_handle, 1024);
    $user_id = $file_line[0];

    $sql = "SELECT count(*) AS total FROM user WHERE user_id = $user_id ";

    //Retrieving data from mySQL
    $result = mysqli_query($con,$sql);   

    //Tested with single row but still giving me an error
    $row = mysqli_fetch_array($result);
    echo $row[0]; //Since this is an array I forgot to retrieve the first index of the array.

    $i++;
}

errors says there's a problem with the query. try `$result = mysqli_query($con, $query) or die(mysqli_error($con));` to see the detailed error — Fabricator, Jun 17 '14 at 02:00
Your query is either failing, or returning 0 rows. As a simple way to debug, change `$result = mysqli_query($con,$query); ` to `$result = mysqli_query($con,$query) or die(mysqli_error($con);` — Sean, Jun 17 '14 at 02:01
@dcclassics I am trying to get the user_id from csv. $query = $sql . $file_line[0]; — Cryssie, Jun 17 '14 at 02:05

score 0 · Answer 1 · answered Jun 17 '14 at 02:01

0

Almost right, you just have to change:

$row = mysqli_fetch_array($result);

To:

$row = $result->fetch_array(MYSQLI_NUM);

Or just do:

$row = mysqli_fetch_array($result, MYSQLI_NUM);

You had to specify your result type.

answered Jun 17 '14 at 02:01

Shahar

1,681
2
12
18

From the manual -> [`mysqli_fetch_array ( mysqli_result $result [, int $resulttype = MYSQLI_BOTH ] )`->`resulttype This **optional** parameter...`](http://www.php.net//manual/en/mysqli-result.fetch-array.php) – Sean Jun 17 '14 at 02:08
@Sean Yeah, of course it's optional, otherwise there would be a syntax error. However, if you don't want to get a Boolean, you have to specify. – Shahar Jun 17 '14 at 02:53

score 0 · Accepted Answer · edited Nov 27 '14 at 11:37

0

I think you forgot to add some variables here

$sql = "SELECT count(*) AS total FROM user WHERE user_id = ";

usually it is like this

$sql = "SELECT count(*) AS total FROM user WHERE user_id = " $userId = $file_line[0] "";

For more information please see this. It is all about WHERE clause, which I think will really help you.

edited Nov 27 '14 at 11:37

answered Jun 17 '14 at 02:04

Cary Bondoc

2,770
4
35
57

OP is setting the `user_id` in the loop using `$query = $sql . $file_line[0];` – Sean Jun 17 '14 at 02:05
I initially thought the same thing but it's concatenated with the string `$file_line[0]`. It's not conventional but I don't think it would throw any errors. – dcclassics Jun 17 '14 at 02:05
2

Typically in PHP, I would just do `$userId = $file_line[0]`, then add the second line for @CaryBondoc's answer. – dcclassics Jun 17 '14 at 02:07
Yeah. dcclassics has a point on that. – Cary Bondoc Jun 17 '14 at 02:23
2

Thank to you guys I was able to figure out what went wrong. It seems concatenation doesn't work so well and using $user_id = $file_line[0] helps. – Cryssie Jun 17 '14 at 02:26

Unable to retrieve row from MySQL using PHP

2 Answers2