php file not echoing a query from a mysql table on remote server BUT works on local XAMMP mariaDB

Question

SUMMARY:

I am new to back-end development.
For my site, the user inputs a city location and date then clicks on the submit button for a selection of activites to do.
City GPS coordinates are supplied by geobytes.com JSON-P API's
The date is supplied by jQuery datepicker
The coordinates and date are extracted within an external javascript file then passed to a PHP file (AJAX) for querying several tables, and then the results are echoed back to the webpage.
On my local machine using XAMMP, everything works.
On my student web space, a query on a table that uses both GPS and date DOES NOT echo

BACKGROUND:

The PHP code is the same on both servers, except for connection
Queries of locations tables work
Queries of dates tables work
One table has fields containing both GPS and date. This query does NOT echo from the school server.
Local machine is MariaDB 10.1.38 // XAMMP v3.2.3 on windows 10)
School mysql server version is 5.5.54 (on unix)
PHP Version 7.3.3

// PHP code
// AJAX VALUES PASSED FROM JAVASCRIPT FILE`
$s = $_GET['s'];
$q = floatval($_GET['q']);
$r = floatval($_GET['r']);

// FOR MYSQL
$sqlStarParty = "SELECT sp_name, sp_location, sp_date_start, sp_nights, sp_link, sp_comment
FROM star_party 
WHERE 
sp_date_start <= DATE_ADD(STR_TO_DATE('$s', '%Y-%m-%d'), INTERVAL 14 DAY)
AND
sp_date_start >= DATE_SUB(STR_TO_DATE('$s', '%Y-%m-%d'), INTERVAL 14 DAY)
AND
sp_latitude < ($q + 2) AND sp_latitude > ($q - 2)
AND
sp_longitude < ($r + 2) AND sp_longitude > ($r - 2)";


// TO BE ECHOED TO WEBPAGE
$resultStarParty = mysqli_query($conn, $sqlStarParty);

if (mysqli_num_rows($resultStarParty) > 0) {

while($row = mysqli_fetch_assoc($resultStarParty)) {
    echo $row["sp_name"] . "<br/>";
    echo "Location : " . $row["sp_location"] . "<br/>";
    echo "Current year event start date: " . date_format(new DateTime($row["sp_date_start"]),'F, jS') . " for " . $row["sp_nights"] . " nights<br/>"; 
        echo "Details and map: " . "<a href='".$row["sp_link"]."' target='_blank'>Website</a><br/>";
        echo "<br></br>";
    }
}   // if no records match query - print 0 results
 else {
     echo "Sorry no star parties for this time and location";
}

EXPECTED RESULTS:

All queries dates/location/date + location should echo to webpage. ACTUAL RESULTS:
A user submits a location and date which lands on a star party event date range (+- 14 days)
No error messages show
No response from submit button OR no echo of star party table.
Any help would be much appreciated!

SOLUTION:

I was getting a 500 server error, which could be many things.
The MySQL DB was getting the correct js variable values.
Problem was rectified when saw that the two tables giving me grief were using NEW DATE_TIME.
Change PHP code to date_create instead. Star party results are showing and correct.
Thank you Paulo!

One way to debug this would be to use ChromePhp and log variables in the browser console, like the query. Another way would be to create a URL that passes those parameters manually (since they're all GET) and see what your query looks like on the server. If you don't get what you need, you probably have a query that doesn't work in that environment for some reason. — Paulo Hgo
– Paulo Hgo, Commented May 16, 2019 at 5:06
Thanks, I am trying to figure out how to get ChromePhp.php include going but no luck. On chrome, I found a '500 Internal Server Error'. Waiting to hear back from webmaster. — greasyHamster
– greasyHamster, Commented May 22, 2019 at 18:44
Download ChromePhp and in your code, do an include 'ChromePhp.php'; and to display variables to the console, use ChromePhp::log($variable_name_here); — Paulo Hgo
– Paulo Hgo, Commented May 23, 2019 at 19:16
OK the 3 inputs from the javascript file (latitude, longitude, date) are entering the php file correctly. I don't understand much of what I'm seeing. Here is the link to my project. student.athabascau.ca/~coreyhe/homepage.html — greasyHamster
– greasyHamster, Commented May 24, 2019 at 19:48
What do you use to query directly into the school DB? MySql Workbench, MySql command line client? Echo your query ($sqlStarParty) after you've formed it. Copy it and run in a client pointing to your deployment and see what error message you get. — Paulo Hgo
– Paulo Hgo, Commented May 24, 2019 at 23:22

greasyHamster · Accepted Answer · 2019-06-15 21:51:14Z

0

I think I've solved it. Where date_format(new DateTime($row["op_date"]) occured, I replaced with date_format(date_create($row["op_date"]). Star Parties are showing correctly now. Hopefully this holds.

answered Jun 15, 2019 at 21:51

greasyHamster

12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

php file not echoing a query from a mysql table on remote server BUT works on local XAMMP mariaDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related