SQL query working on SQL but not through PHP

Question

I have searched this site and the rest of the internet for a bit but I am having a hard time figuring this out.

//Connecting to database using $con and then getting percentage of items that have pass
$query = mysqli_query($con, 'SELECT 100 * 
SUM(exterioralignment = pass)/COUNT(exterioralignment) FROM door1');
//passing the resource to an array 
$row=mysqli_fetch_assoc($query);
//printing the array to check if everything worked well
echo $row;

When I enter the below into MySQL it gives me the required result but the above code does not print anything

SELECT 100 * SUM(exterioralignment = pass)/COUNT(exterioralignment) FROM door1

The error I get in the logs is mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\ but my understanding is that mysqli_query is a result and not a boolean. As you can tell I am inexperienced in this so please try to be detailed with your answers.

Thank you

EDIT: I used $a="PASS"; $a=mysqli_real_escape_string($con, $a); $query = mysqli_query($con, 'SELECT 100 * SUM(exterioralignment ("$a"))/COUNT(exterioralignment) AS total FROM door1');

But the returned answer is 0 instead of 80 as it is supposed to be. If I copy paste the query to Mysql and put "pass" instead of $a it gives me 80 so I think something is going wrong when I am escaping the $a string

That did not make a difference. The error is in the mysqli_fetch. Also I run that query on MySQL and it run properly. Is there a reason I am missing why it would be false here? My only change is taking the 'pass' and making it pass so it does not give me an error from PHP — user3159515, Jan 04 '14 at 05:47
Check `while (($row = mysqli_fetch_assoc($result)) !== false){ print_r($row); }` — Nouphal.M, Jan 04 '14 at 05:49
I cannot ask the mysqli_error for the same reason the fetch is not working. It says that $query is boolean and not a resource — user3159515, Jan 04 '14 at 05:50
The manual says `mysqli_error($con);`, not `$query`. Also stop using mysqli; switch to PDO. — mario, Jan 04 '14 at 05:52
possible duplicate of [mysql\_fetch\_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select) — mario, Jan 04 '14 at 05:54
okay...it may be that exterioralignment=pass needs to be exterioralignment='pass' but the '' makes PHP give me an error. How do I work around that? I need to get all the exterioralignment rows that have the string pass — user3159515, Jan 04 '14 at 05:57
I will look into that. Again I am still in the early stages of learning so you will see rookie mistakes. Hopefully google links me to something about that. Thank you for the help — user3159515, Jan 04 '14 at 06:01
@user3159515 Yes, exterioralignment=pass is causing problem. I worked on example and posted it. — RaviRokkam, Jan 04 '14 at 07:13
The object thing does not do anything. As I wrote in my edit, I escaped the string but the result through PHP is not matching the result through MySQL so something is going wrong there — user3159515, Jan 04 '14 at 18:03

RaviRokkam · Answer 1 · 2014-01-04T07:34:53.013

0

// I have used mysql in my example. (I will later switch to mysqli / PDO)

$query = "SELECT 100 * SUM(exterioralignment = 'pass') / COUNT(exterioralignment) AS total FROM door1";
//echo $query;
$result = mysql_query($query);
$row=mysql_fetch_assoc($result);
print_r($row);

Result

Array ( [total] => 16 )

edited Jan 04 '14 at 07:34

answered Jan 04 '14 at 07:06

RaviRokkam

789
9
16

score 0 · Answer 2 · answered Jan 04 '14 at 07:59

0

Try this:

    $query = mysqli_query($con, 'SELECT 100 * 
    SUM(exterioralignment = pass)/COUNT(exterioralignment) as total FROM door1');
    if($row=mysqli_fetch_object($query)){ 
       echo $row->total;
    }

answered Jan 04 '14 at 07:59

Awlad Liton

9,366
2
27
53

score 0 · Answer 3 · answered Jan 04 '14 at 18:21

0

Ok so I figured it out with your help and some help from the rest of this site.

I had to use mysqli_real_escape_string to escape the string and then use single quotes around $a in the query. I hope this helps someone in the future

answered Jan 04 '14 at 18:21

user3159515

9
2

score -1 · Answer 4 · edited Jan 04 '14 at 06:42

-1

Please try Code as below

$query = mysqli_query('SELECT SUM(exterioralignment = pass)/COUNT(exterioralignment) FROM door1 LIMIT 100 ',$con);
//passing the resource to an array 
if($row=mysqli_fetch_assoc($query)){
    //printing the array to check if everything worked well
    echo $row;
}

edited Jan 04 '14 at 06:42

zamnuts

9,492
3
39
46

answered Jan 04 '14 at 05:54

Gopal Joshi

2,350
22
49

You are using mysql and mysqli both. – RaviRokkam Jan 04 '14 at 06:10
You did not solve the problem with the "pass". I solved it using $a="pass"; $a=mysqli_real_escape_string($con, $city); – user3159515 Jan 04 '14 at 17:43

SQL query working on SQL but not through PHP

4 Answers4