Error Query PHP after migrate to mysql database

Question

I have execute query using PHP which previously executed on mssql server database . Now with the same table and data. I using mysql database to execute my query. But error happen. Any suggestion for my query below in order to can execute using mysql database :

$year = mysql_query("SELECT * FROM education_year ORDER BY id DESC");

if (isset($_GET['year'])){
    $educationyear= mysql_fetch_array(mysql_query("SELECT * FROM educationyear WHERE year='{$_GET['year']}'"));
}else {$educationyear = mysql_fetch_array($year);}

$kode['KODE'] = mysql_fetch_array(mysql_query("SELECT KODE FROM educationyear WHERE year='$educationyear'"));

$result = mysql_query("SELECT * FROM Province");
while($row = mysql_fetch_array($result))
{
    $xd = mysql_fetch_array(mysql_query("SELECT COUNT (*) AS total FROM child WHERE id_province='{$row['province_code']}' AND education='A' 
          AND educationyear='{$educationyear['KODE']}'"));
}

Error message like below :

Notice: Array to string conversion in C:\xampp\htdocs\xy\demo.php on line 19 

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\xy\demo.php on line 20 .

Its line when execute $xd query.

Possible duplicate of [mysqli\_fetch\_array()/mysqli\_fetch\_assoc()/mysqli\_fetch\_row() expects parameter 1 to be resource or mysqli\_result, boolean given](http://stackoverflow.com/questions/2973202/mysqli-fetch-array-mysqli-fetch-assoc-mysqli-fetch-row-expects-parameter-1) — Marc B, Jul 18 '16 at 15:36
You write code as if nothing could ever possibly fail. Very very very very bad attitude to have. Never **EVER** assume success. always assume failure, check for failure, and treat success as a pleasant surprise. You are also vulnerable to [sql injection attacks](http://bobby-tables.com). — Marc B, Jul 18 '16 at 15:37
It would be helpful if you told us which is line 19. We can't tell where the file begins. — BeetleJuice, Jul 18 '16 at 15:55
can you show us the result of var_dump("SELECT COUNT (*) AS total FROM child WHERE id_province='{$row['province_code']}' AND education='A' AND educationyear='{$educationyear['KODE']}'") — Luca Jung, Jul 18 '16 at 16:46
@user I tried to address the issues I found in your code but you never responded. If you found my answer helpful, please select it (and upvote if you wish). It takes time to research, test and write up these answers. Not very cool to ask & run. — BeetleJuice, Jul 23 '16 at 02:26

BeetleJuice · Answer 1 · 2016-07-18T15:57:32.410

There are a few problems with your code

1st: When you use an array within double-quoted string, do not quote the array key. Change

"...WHERE year='{$_GET['year']}..."
"...WHERE id_province='{$row['province_code']}'..."

To:

"...WHERE year='{$_GET[year]}..."
"...WHERE id_province='{$row[province_code]}'..."

2nd: The design pattern below is not good:

mysql_fetch_array(mysql_query("SELECT...")

You're taking the result of mysql_query and feeding it directly to mysql_fetch_array. This works as long as the query succeeds and returns a resource. If the query fails, it will return FALSE and mysql_fetch_array will trigger the error you see:

mysql_fetch_array() expects parameter 1 to be resource, boolean given

Instead, make sure there is no error before proceeding

$result = mysql_query("SELECT...")
if($result===false){ 
    //Query failed get error from mysql_error($link). 
    //$link is the result of mysql_connect
}
else{
    //now it's safe to fetch results
    $record = mysql_fetch_array($result);
}

3rd: do not use mysql_ functions. They have been abandoned for years and have been removed from the most recent version of PHP. Switch to MySQLi or PDO

4th: learn about prepared statements. You're using user supplied input directly in your query ($_GET['year']) and this makes you vulnerable to SQL injection.

Checking `===false` is overkill. `$result` will be logically true if it succeeds, and otherwise you've got an error. Also, `mysql_real_escape_string` is not optional. It **must** be used here. — tadman, Jul 18 '16 at 17:21
I've been trying to build the habit of strict checking in general. If I'm escaping a boolean, why not check for it. Anyway I don't see the downside. I agree that escaping or using prepared statements (my 4th point) is a must. — BeetleJuice, Jul 18 '16 at 17:32
*escaping* a boolean is a typo: *expecting* a boolean is right. — BeetleJuice, Jul 19 '16 at 02:43

Error Query PHP after migrate to mysql database

1 Answers1