Php7 query mystery

PHP
1

I’ve been using php5 for years and had been holding off on upgrading to php7 as I knew it would be A LOT of work to convert all my code and I just didn’t have the time for it. Last week, however, I had some PC problems that forced me to upgrade my entire system and thus I’m now forced to work with php7. And so the nightmare I had anticipated begins…

So here’s one specific problem that has me banging my head against the wall.

This was my old php5 code:

$restit = mysql_db_query($base, "SELECT Title_col FROM tbl_Titles WHERE FilmID = '$id' AND Country_col = '$cnt' AND TitleType_col = '$type' ");
	while($row = mysql_fetch_object($restit))
		$ftit = $row->Title_col;
	mysql_free_result($restit);

I converted that to this:

mysqli_select_db($db, $base);
$restit = mysqli_query($db, "SELECT Title_col FROM tbl_Titles WHERE FilmID = '$id' AND Country_col = '$cnt' AND TitleType_col = '$type' ");
	while($row = mysqli_fetch_object($restit))
		$ftit = $row->Title_col;
	mysqli_free_result($restit);

But it’s not working! $ftit is showing empty when it shouldn’t be.

What puzzles me most is that I have very similar code in another file that works fine. The only difference I can see between the two is in the query itself, which makes little sense since they both use standard SQL syntax.

So puzzle me that.

Anyone can help me understand why the above doesn’t work?

2

Where are the variables, that you are putting directly into the sql query statement, coming from and does your code have logic in it to validate them before using them?

3

Well, let’s put it this way… even if I define the variables just before the query, it still doesn’t work, ie:

$base = 'cine';
$id = 20905;
$cnt = 'US';
$type = 'sort';

So I guess that leaves $db. Hmm.

The way my files are structured (which worked with php5, but maybe this broke with php7) is this:

design.php = holds all the layout data and common code, so this is where $db is set (see below for the code).
functions.php = holds all the functions I wrote myself, this is where the broken code is located (within a function).
film.php = main file where said broken function is called.

So when I load film.php it starts with an include that calls design.php, then runs until I call that function. The function itself seems to work properly (I’ve tested it with echo statements at various points within, the query above is where it appears to break).

And here’s how I set $db in design.php:

$db = mysqli_connect("localhost", "myusername", "mypassword");
4

Well, i’d certainly recommend you learn prepared statements. But lets deal with your current problem.

What does mysqli_error($db); tell you?

5

I added this right after my query:

if(!$restit)
	echo "Error description: " . mysqli_error($db);

Result: nothing. It stays blank after printing "Error description: "…

6

So, the posted code is inside of a function?

If so, you have a variable scope problem, but you first need to set php’s error_reporting to E_ALL and display_errors to ON, preferably in the php.ini on your system, so that php will help you with these fundamental problems.

7

I believe that every PHP function returns a result and I would first check that the returned value is the correct type according to the online manual:

<?PHP // following catches most errors
declare(strict_types=1);
error_reporting(-1);
ini_set('display_errors','1');


$tmp = mysqli_select_db($db, $base);

// check following return type and value against  online manual
var_dump($tmp);
die('<br>above type and results must be correct before proceeding');
8

In this case we are going to need more than a code snippet that you think is the problem. I would suggest you put your application on a Github repo so we can review it as a whole. An SQL dump of the DB schema would also be helpful so we can run it ourselves.

9

Top of the script:

ini-set('display-errors',1);
error_reporting(-1);

Refresh, verify there are no PHP errors.

10

Even with errors enabled, mysqli_error($db) still comes back blank.

However, your scope comment made me realize that I did introduce the $db variable into my function so I added “global $db” and that made part of the problem disappear. Now it apparently no longer considers $restit empty since it doesn’t print "Error description: " anymore, but $ftit itself still came back empty :o

So that made me wonder about my data. I went looking into that specific entry and found that it did not match the parameters, which is why it was coming out empty! Duh. So I changed the ID# to a field that actually had data to return… and it worked.

So it was just the scope thing after all. That’s a bit of a relief.

Thank you everyone! You guys were great :wink:

11

Thats great you figured it out except you fixed it “wrong”. DO NOT use global in your function. Pass the parameter to it like so…

function myfunc($db)
{
//stuff
}
12

($db would have had no bearing on it at all, given that if $db wern’t defined in the function’s scope already, it would have thrown PHP errors left right and center. $db is already in the global space, by deduction.)

13

A little off-topic, but worth pointing out at this stage:-
Now you have that problem out of the way, a suggestion.
If you are going to refactor your old code, how about doing right, instead of doing it twice?
I seriously recommend looking at PDO over mysqli, it’s more advanced, but easier to use.
Also I would insist that you look into using prepared statments where you have variables as values in your queries.
It may be tempting to make the quick-fix to “get it working”, but in the long term, this really should be done to bring your code right up to date and not fall so far behind again.

14

Can you expand just a little on the best approach to get started with PDO.

From memory I had problems with having to choose between using PDO classes and PDO functions or am I getting confused with Mysqli functions and PDO does not have functions, only methods?

15

PDO is just a class. MySQLi is the one that has functions and/or OO.

1 Like
16

PDO is a big wrapper/abstraction layer, to avoid having to write database-engine-specific code for every database (though it doesn’t quite work that way.)

$pdo->query(); vs

if($db == 'mysql') {
  $res = mysqli_query(....)
} elseif ($db == 'sqls') {
 $res = sqlsrv_query(...)
} elseif 
.....
....
...

As far as getting started with PDO, i’d say its the same as getting started with your specific db engine’s functions. Start with the connection, make a query.
As far as getting started with prepared statements, which is a separate thing, essentially it’s a question of thinking like you’re breaking up your code, similar to MVC modelling or even function definitions or such; that the statement provides the structure, and waits to be given specific data to fill it with, in the form of bindings, either ahead of time, or JIT bindings during the execute function.

17

I will look into all that. My main problem though is that I’m not a coder and a lot of the stuff I see I have a hard time understanding. So I often give up in frustration and just stick with stuff that feels easier to me, even if it’s not perfect.

BTW, here’s something else that puzzles me in php7… in my old code, I sometimes have a bunch of queries in a row, like this for instance:

mysql_db_query("$base","UPDATE tbl_Users SET Name_col = '$name' WHERE UserID = '$uid' ");
mysql_db_query("$base","UPDATE tbl_Users SET Phone_col = '$phone' WHERE UserID = '$uid' ");
mysql_db_query("$base","UPDATE tbl_Users SET Address_col = '$address' WHERE UserID = '$uid' ");
mysql_db_query("$base","UPDATE tbl_Users SET Joined_col = '$joined' WHERE UserID = '$uid' ");

Converting that results in this:

mysqli_select_db($db, $base);
mysqli_query($db, "UPDATE tbl_Users SET Name_col = '$name' WHERE UserID = '$uid' ");
mysqli_select_db($db, $base);
mysqli_query($db, "UPDATE tbl_Users SET Phone_col = '$phone' WHERE UserID = '$uid' ");
mysqli_select_db($db, $base);
mysqli_query($db, "UPDATE tbl_Users SET Address_col = '$address' WHERE UserID = '$uid' ");
mysqli_select_db($db, $base);
mysqli_query($db, "UPDATE tbl_Users SET Joined_col = '$joined' WHERE UserID = '$uid' ");

I fail to see how this is an improvement? It adds more lines and more repetitions. One file I’ve been working on has already near doubled in size and I’m not even done with it yet.

There has to be a better way, to avoid all the repetitiveness?

18

By the look of it this could all be done in a single query.
There is no need to do a separate query for every field you want to update.
The code was ineficient to start with.

In PDO it would look something like:-

$sql = $db->prepare("UPDATE tbl_Users SET Name_col = ?, Phone_col = ?, Address_col = ?, Joined_col = ? WHERE UserID = ?");
$sql->execute([$name, $phone, $address, $joined, $uid]);

With the bonus of being safer from SQL injection.

19

Interesting. I’ll definitely have to look into prepared statements. I can see how that one works, so should be able to adopt it.

Thanks!

20

I really don’t know why the mysqli_select_db() function is used there at all, let alone why it is repeated for every query.
It’s a while since I used mysqli but I have no recolection of ever using that function.
Since all the queries deal with the same database, there is no need to repeat it, even if it is even needed at all. Not that you need all the queries anyway, as you have seen.
You would only need that function if dealing with different databases.