php mysql pdo

Robert Saylor
Robert Saylor used Ask the Experts™
on
I installed a fresh google VM and getting odd errors on a common mysql command I use. The query works fine from shell but from the browser I get 500 error.

[Thu Apr 06 20:30:37 2017] [error] [client 108.168.152.134] PHP Fatal error:  Call to a member function fetch_assoc() on a non-object in /var/www/html/myfile.php on line 21

Open in new window


The issue is most likely in my server setup. I installed php using yum.

<?php
error_reporting(E_ALL);

$linkID5 = new mysqli('hostname', 'user', 'password', 'database');

        $tbl_name = "query_table_" . $_REQUEST['qbID'];
        //print "$tbl_name<br>";

        if ($_REQUEST['confirmed_lead'] == "yes") {
                $ts = date("U");
                $sql = "UPDATE `$tbl_name` SET `confirmed_lead` = 'yes',`confirmed_timestamp` = '$ts' WHERE `xxLeadId` = '$_REQUEST[xxLeadID]'";
                $result = $linkID5->query($sql);
        } else {
                // get columns
                $sql = "SHOW Columns FROM `$tbl_name` WHERE `Field` NOT IN ('id','date','system_timestamp','system_reposted')";
                $result = $linkID5->query($sql);
                $sql_top = "";
                $sql_bot = "";
                $date = date("Y-m-d H:i:s");
                $system_timestamp = date("U");
                while ($row = $result->fetch_assoc()) {
                        $field = $row['Field'];
                        $sql_top .= "`$row[Field]`,";
                        $sql_bot .= "'".$_REQUEST[$field]."',";
                }
                $sql_top .= "`date`,`system_timestamp`,`system_reposted`";
                $sql_bot .= "'$date','$system_timestamp','No'";

                $sql = "INSERT INTO `$tbl_name` ($sql_top) VALUES ($sql_bot)";
                //print "SQL: $sql";
                $result = $linkID5->query($sql);
        }

?>

Open in new window


This same code works on other servers just fine.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Robert SaylorSenior Developer

Author

Commented:
just noticed when I installed php rpm using yum it installed 5.3.3 that is most likely the issue
Most Valuable Expert 2011
Top Expert 2016
Commented:
Certainly could be.  There is no PHP5 version that is still supported.  But my guess is that a query failed, and this looks like MySQLi, not PDO.  

Consider this statement:

$result = $linkID5->query($sql); // MAN PAGE: http://php.net/manual/en/mysqli.query.php

The MySQLi->query() method has a mixed set of return values.  You should test this value in the script and visualize the errors if $result === FALSE.  Examples showing how to do that are available in this article.
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Look for the MySQLi code snippet in the part titled Create and Execute a SELECT Query
Robert SaylorSenior Developer

Author

Commented:
Thanks Ray will try this in the morning. Been messing with it all afternoon.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Also try putting this debug statement after line 16
if ($linkID5->error) {
  die("Error: {$linkID5->error}<br/>") ;
}

Open in new window

To see if the database is reporting any errors.
Note: using die() is not recommended for production applications - this is purely for debug purposes.
Most Valuable Expert 2011
Top Expert 2016

Commented:
Instead of die(), a sturdier approach to visualizing errors is described here:
https://www.experts-exchange.com/articles/29115/PHP-Error-Handling-Never-Say-die-Again.html

This is the same strategy that is illustrated in the article about MySQLi.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Just a note on die(). There are two scenarios to consider here

1. Writing sturdy code that gracefully handles errors in a production environment
2. Debugging code

The use of die in my last post was for the purpose of item 2 above. A temporary insert to determine the state of the application as part of determining why the logic of the application is not working. This is different from the application failing due to an unforeseen / unhandled condition in a production system.

Once the state of the DB is known through the error property and the code logic amended, the debug statement is removed.

Just to clarify the difference between my last suggestion and the good suggestions in Ray's article.
Most Valuable Expert 2011
Top Expert 2016

Commented:
Once the state of the DB is known ... the debug statement is removed.
I actually never do that --  I never remove the error handler.  I have different approaches to displaying errors in deployed applications, but I always catch the errors/exceptions and always log the errors.

The reason for my strategy is that I often have clients who use shared hosting (I'm looking at you, GoDaddy) and these environments can and will have MySQL errors that arise from factors that are completely outside of the PHP scripts' control.  If you don't trap and identify the MySQL errors, you will have run-time failures with no evidence that points to the cause.  If you have an error_log file with the times, the queries, and the messages, you have a better chance of getting the hosting company to investigate and correct the conditions that lead to the error.  Example: Too many MySQL connections, or I/O error in the database server.  PHP can't do anything about those problems -- it can only report them to the application manager through the error_handler and the error_log.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I actually never do that --  I never remove the error handler.
It is not an error handler - it is a debug statement. If you have a proper dev environment with a debugger you would step through your code and test the value you are looking for in the debugging environment. As PHP (for the most part) does not provide us with such tools - we use the debug output to check values in the script so that we can make decisions on code logic. Sometimes these debug statements turn into error handlers and sometimes they are simply removed. I am referring to the latter case.

Again I want to distinguish between unhandled errors that can occur in the wild and situations where your code logic bombs or does not do what it is supposed to do and you need to find out why so you can fix the logic - this is part of the development cycle.

Here is an example - I needed to find a particular path WP was taking in a site that was not behaving. I put die(__FILE__) statements in various suspected files and hit the site - gave me the info I wanted and I removed the die() statements as they had served their purpose.

In this particular case the application is erroring out due to error in code - insert die() to get the query error - check that is or is not the problem and remove the die - temporary solution for the purpose of gathering information.

I guess it comes down to personal preference - I use this technique successfully all the time - but none of my production code has die() anywhere in it - for the reasons you cover in your article.
Robert SaylorSenior Developer

Author

Commented:
I am creating a new VM image with CentOS 7 instead of 6.x

The query I know works something with the VM setup is wrong.
Senior Developer
Commented:
Ray,

In a odd condition I set the environment vars to display more errors and found mysqli was getting a permission error.

I googled that and this was the solution:
from shell: setenforce 0

I then re-ran my query and boom it worked.

BTW, I am going to re-do my other VM's because I did not know CentOS 6 defaulted to php 5.3. I created a new CentOS 7 and installed php 5.6 from rpm.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Answer has no bearing on the question.

Recommend this question is deleted.
Most Valuable Expert 2011
Top Expert 2016

Commented:
The answer here shows how to do error visualization, which is what the author needed in this case.  I recommend this be the accepted answer, or at least the assisted answer.
https://www.experts-exchange.com/questions/29014567/php-mysql-pdo.html#a42082632
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Agree - that would have been my choice.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial