Facing issues regarding SELECT Query

PHP
1

I am new to PHP and SQL I can insert the data without any issues into the database using the INSERT query. The SELECT query doesn’t seem to work properly in code. I am able to get a response when I run it in phpMyAdmin. Also the SELECT query works properly on xampp localhost server. It doesn’t work on domain server.

<?php

$status = "On";
$maxid = 1;

include_once 'db.php';
  $query	 = "SELECT  MAX(id) AS max_page  FROM HA_ESP";   // This runs fine 
  $result = mysqli_query($db, $query);
  if (mysqli_num_rows($result) > 0) {
  echo "Exists";                                       // Received in response   
  while($row = mysqli_fetch_assoc($result)) {
  	$maxid = $row["max_page"];                                     
  	echo $maxid;
  }

echo "Out of MaxId";                                    // Received in response
$query = "SELECT * FROM HA_ESP WHERE id = $maxid";     // Does not work as intended
$result = mysqli_query($db, $query);
while($row = $result->fetch_assoc()) {
echo $row["status"];                                    // Not received in response
}
echo "Out of status query";                 // Not received in response
}
else
{
	echo "Nothing in database";
}

?>
2

then you would need to check if the query was successful. a database doesn’t stop working without an error.

Also note that $maxid is a conditional variable, i.e. if the first query does not return a result, it is undefined.

PS. I’ll never get why people use while() for a single result set.

1 Like
3

I can’t see why your code isn’t working, I don’t use mysqli myself as I prefer PDO, but if all you want is the status of the row where id has the highest value, you could try

select status from ha_esp order by id limit 1

which if I’m not mistaken should give you that one single value.

In your code, what happens when you echo $query for your second query? Regardless of whether the query here will get around it, it would still be interesting to know why it’s failing. Is it putting the correct value in for $maxid?

ETA: If you want to run one query after another, don’t you have to call mysqli_free_result before the second one? I see it in the samples on the documentation page but it’s not entirely clear whether it’s mandatory.

1 Like
4

Thank You. In the response I get maxid. I don’t get any responses after `$query = “SELECT * FROM HA_ESP WHERE id = $maxid”. I guess there is an error in that statement. There are no problems when I run this on xampp local host.

5

Avoid using variables in your queries, mysqli provides functions for Prepared Statements against SQL Injection. You should also use apropriate methods like mysqli_error if you encounter any errors in the behavior of your statements, this way you do not have to ‘guess’ anything. Echo’ing out the statement and running it in a tool like PHPMyAdmin would be a first step.

6

What I meant was

echo "Out of MaxId";                                    // Received in response
$query = "SELECT * FROM HA_ESP WHERE id = $maxid";     // Does not work as intended How do you know?
echo $query; // ** WHAT DOES THIS SHOW? **
$result = mysqli_query($db, $query);
var_dump($result); // ** WHAT DOES THIS SHOW? **

But after you run that line, you don’t output any progress echoes until after two quite significant lines, so you cannot assume that the error is in the line you indicate.

7

Thanks for the response. I didn’t know mysqli_free_result should be written before a second query. Maxid has the correct value. I will use mysqli_free_result and let you know what happens.

8

I don’t know for sure, but it seems to be shown on the doc page. Because I don’t use mysqli for anything I can’t say what actual reality is. Worth a go though. Strange that one server might need it and another not, though there is a suggestion that the database driver might influence things.

9

As mentioned, using prepared statements is the better option. It’s been a super long time since I’ve used procedural mysqli_, but I don’t think the problem is PHP specific. I am speculating that it could be as max_page because I’ve looked through few websites and don’t see as ___ in any examples.

10

I thought that was just a means of renaming the column as it’s calculated and so doesn’t have a name of its own, fairly standard MySQL?

11

I get correct response when I use postman or simply hit the URL in the browser. I’m really sorry. I should have checked it before. I guess there is a problem with the way my IOT module esp8266 is parsing the data.

12

I’m not really sure what you’re talking about here, is this some other code you didn’t show that might be affecting things? It’s hard to see how it would have an effect on the code you showed above.

13

There is a code running in my IOT module which sends requests to the server at a regular interval. I have used Arduino IDE to develop the code. If you want to look at it, I can share. There is no problem when I connect my module to xampp localhost server. I get partial response when I connect to the domain server. I am certain about the source of the problem.

14

It would be interesting, but I wouldn’t be able to help solve the issue. I’ve long wanted an excuse to play with something like the Arduino, but haven’t yet thought of one.

15

That’s okay. I have been working with esp8266, which is basically an SOC having WiFi functionality and also has a built-in TCP/IP stack. So it can be connected to the internet and be used as an IOT device. Arduino IDE provides coding support. I got stumbled upon getting response from the server. Not sure what the source of problem is. I guess it is something to do with the arduino code. I did create confusion over here. My apologies for that. Thanks for all the support!!

16

Thanks for that. I will make sure I use echo’s and methods like mysqli_error while I code. I speculate a problem elsewhere in another code not pertaining to the php script since I get the correct response when I send request from postman or browser.

17

Thanks for the response. I believe the problem doesn’t lie in the php script at all since I get correct response when I send request from browser itself. I send requests from my iot device esp8266. I guess there is a problem with the code running on my esp.

18

I just tested your code and although it may work, it’s not safe to use IMO. You should really look to using prepared statements. Also, since you said that it works locally, but not on a live server, do you know the PHP version you are using on the live version? One last thing, make sure that you have replicated your local database to your live database and make sure that they have some what or exactly the same data so you can debug what is wrong.

1 Like
20

The version information of phpMyAdmin in local server and live server are 4.7.4 and 4.0.10.5 respectively. The php version in my local server is 7.1.0. I’m not able to find out the php version running on live server.
I used a different library in my arduino to send request and handle responses. There are no problems now.

21

use the PHP_VERSION constant. works even if phpinfo() is disabled.

cf. https://secure.php.net/manual/en/reserved.constants.php

1 Like