How to execute a mySQL subquery in php?

[achana]

PROBLEM: php returns null even though the SQL executes correctly in mySQL

As an example, I have here 2 tables and I want the student's latest grades from XS_Course:

•  Student (SID, Name) pk is SID
•  XS_Course (SID, CID, Date, Grade) pk is (SID+CID) and Date is in Datetime format

I constructed a SQL statement like this (the real SQL works well in mySQL):

 

SELECT s.Name, sc.CID, sc.Grade
FROM Student s RIGHT JOIN XS_Course sc ON s.SID=sc.SID
WHERE sc.CID LIKE 'IT%' AND sc.Date>= ("select date(max(sc2.Date)) from XS_Course sc2;")
ORDER BY s.Name;

If I take out the subquery, PHP returns a result set, so it is the subquery that is truipping up php...

 

Then I the wrapped the SQL in PHP, like this:

<?php
$con = mysqli_connect("localhost", "username", "password", "dbname");
/* check connection */
if (!$con) {
    die("Connection failed: ".mysqli_connect_error());
}

$sql = "SELECT s.Name, sc.CID, sc.Grade
FROM Student s RIGHT JOIN XS_Course sc ON s.SID=sc.SID
WHERE sc.CID LIKE 'IT%' AND sc.Date>= ("select date(max(sc2.Date)) from XS_Course sc2;")";

$result = mysqli_query($con, $sql);

/* instantiate an array to hold the JSON name-value pairs */
$response = array();

/* In the loop, put the "name" and data into array's name-value pair */
while($row=mysqli_fetch_array($result)){
    array_push($response, array("studentname"=>$row[0], "coursecode" =>$row[1], "recentgrade"=>$row[2]);}

/* encode the data inside a JSON-array */
echo json_encode(array("db_response"=>$response));

/* close connection */
mysqli_close($con);
?>

PROBLEM: PHP returns nothing when I run it in browser.

Would it be better if I execute 2 SQL stmts in php, the first to get the max(Date) into a php-variable and them use the variable to filter the result set in WHERE clause, like this:

$maxdate = "SELECT max(Date) FROM XS_Course;";
$sql = "SELECT s.Name, sc.CID, sc.Grade
FROM Student s RIGHT JOIN XS_Course sc ON s.SID=sc.SID
WHERE sc.CID LIKE 'IT%' AND sc.Date>= $maddate
ORDER BY s.Name;";

Didn't work either, tried a few variations...

 

Some help please
 

[requinix]

I constructed a SQL statement like this (the real SQL works well in mySQL)

I hope it's noticeably different from that because what you posted will run but certainly will not work well.

$sql = "SELECT s.Name, sc.CID, sc.Grade
FROM Student s RIGHT JOIN XS_Course sc ON s.SID=sc.SID
WHERE sc.CID LIKE 'IT%' AND sc.Date>= ("select date(max(sc2.Date)) from XS_Course sc2;")";

Please familiarize yourself with how strings work in PHP.

 

PROBLEM: PHP returns nothing when I run it in browser.

Same problem as the other one. Fix the other and this one will go away.

[Barand]

Just because a query returns result doesn't mean it worked. You need to check the results against the input to verify their accuracy.

 

Even when you have sorted your php string syntax and SQL syntax problems, so you actually have a subquery, it will work only if all students latest date is the same date.

[achana]

Yes, the SQL is really bad even though it works and thx for the tip on string and literals.

 

QUESTION: would PHP handle 2 sql statements better than a concaternated one? I don't mind two or more trips to the server as it is a thick server in the same PHP script, like this :

 

 

<?php
$con = mysqli_connect("localhost", "username", "password", "dbname");
/* check connection */
if (!$con) {
    die("Connection failed: ".mysqli_connect_error());
}
 
$testdate = "SELECT max(Date) FROM XS_Course;";

$sql = "SELECT s.Name, sc.CID, sc.Grade
FROM Student s RIGHT JOIN XS_Course sc ON s.SID=sc.SID
WHERE sc.CID LIKE 'IT%' AND sc.Date>= $testdate
ORDER BY s.Name;";
 
...
/* close connection */
mysqli_close($con);
?>

[achana]

Just because a query returns result doesn't mean it worked. You need to check the results against the input to verify their accuracy.

 

Even when you have sorted your php string syntax and SQL syntax problems, so you actually have a subquery, it will work only if all students latest date is the same date.

 

True! Very good advice indeed.

 

Do not ever use a subquery like that in a production environment.

[requinix]

QUESTION: would PHP handle 2 sql statements better than a concaternated one? I don't mind two or more trips to the server as it is a thick server in the same PHP script, like this :

PHP doesn't care.

 

Either way you're executing the same two queries, so unless you need $testdate somewhere else then you might as well do it as a subquery.

 

And your two-query approach wouldn't work either. You still don't understand strings - at least not strings in MySQL.

[achana]

Why wouldn't the 2-way approach work? Pls point me in the right direction for PHP.   

 

Yes, it works after I removed the unfortunate double quotes but do not ever use a subquery in the where filter like that in production code! It was just something I hammered up as a vehicle to highlight my php problem.

 

2 unrelated questions:

• what is the typical IDE for php, eclipse?
• book for php-newbie?

[Barand]

Why wouldn't the 2-way approach work?

A 2-way approach would work, just not yours.

 

All you are doing is defining a string and expecting it magically to produce a result. You need to execute the query then get the date from the results.

 

Plus, if your students have different dates, then it isn't going to work correctly for all of them as you only get one max date. If they do have different dates then the 2-way approach will not work.

[achana]

Oh no, I did not expect it to automagically produce a result

 

This is the most recent incarnation and should work if I understand the php documentation right...

 

$mysql_testdate = "select date(max(Test_Date)) from XS_Course;"  // I think I need to know exactly what php returns here...
$testdate=strtotime($mysql_testdate);  // documentation says this will produce a UNIX date time
$date = date( 'Y-m-d H:i:s', $testdate );  // doco says this will format the unix datetime
echo $date

 

I need to see the hexadecimal hack, this isn't right;

hang on... and the cheshire cat smiles

Edited December 1, 2017 by achana

[requinix]

Oh dear. Alright, no more beating about the bush.

 

Your original code:

$sql = "SELECT s.Name, sc.CID, sc.Grade
FROM Student s RIGHT JOIN XS_Course sc ON s.SID=sc.SID
WHERE sc.CID LIKE 'IT%' AND sc.Date>= ("select date(max(sc2.Date)) from XS_Course sc2;")";

Do you see how the highlighting makes most of the query green but the subquery is not? Green highlighting represents a string and the whole query should be a string. It is not. Why? Because of the quoting. If you wanted a " inside a "-quoted string then you would have to escape it. So that brings us to

$sql = "SELECT s.Name, sc.CID, sc.Grade
FROM Student s RIGHT JOIN XS_Course sc ON s.SID=sc.SID
WHERE sc.CID LIKE 'IT%' AND sc.Date>= (\"select date(max(sc2.Date)) from XS_Course sc2;\")";

Still won't work because all you're doing is comparing sc.Date to the string "select...". It needs to be a subquery, not a string. The quotes should not even be there in the first place. The stray semicolon needs to go too.

$sql = "SELECT s.Name, sc.CID, sc.Grade
FROM Student s RIGHT JOIN XS_Course sc ON s.SID=sc.SID
WHERE sc.CID LIKE 'IT%' AND sc.Date>= (select date(max(sc2.Date)) from XS_Course sc2)";

Your two-query approach:

$sql = "SELECT s.Name, sc.CID, sc.Grade
FROM Student s RIGHT JOIN XS_Course sc ON s.SID=sc.SID
WHERE sc.CID LIKE 'IT%' AND sc.Date>= $testdate
ORDER BY s.Name;";

Let's say $testdate is the date 2017-12-01. That means the query will be

$sql = "SELECT s.Name, sc.CID, sc.Grade
FROM Student s RIGHT JOIN XS_Course sc ON s.SID=sc.SID
WHERE sc.CID LIKE 'IT%' AND sc.Date>= 2017-12-01
ORDER BY s.Name;";

Does that look right? If it does then you aren't thinking about it. Dates in MySQL need to be strings. This time, coincidentally, you do need quotes. Without the quotes you're just writing a couple numbers with subtraction:

WHERE sc.CID LIKE 'IT%' AND sc.Date>= 2017-12-01

WHERE sc.CID LIKE 'IT%' AND sc.Date>= 2004

I think maybe you were getting the quotes mixed up with the subquery?

 

Now your latest attempt:

$mysql_testdate = "select date(max(Test_Date)) from XS_Course;"  // I think I need to know exactly what php returns here...
$testdate=strtotime($mysql_testdate);  // documentation says this will produce a UNIX date time
$date = date( 'Y-m-d H:i:s', $testdate );  // doco says this will format the unix datetime
echo $date;

What is the value of $mysql_testdate? What does the code say it will be? It says it will be the string value "select date(max(Test_Date)) from XS_Course;". Trying to run that through strtotime() will definitely not work.

 

Now let's say you fix it to execute the query and you get $mysql_testdate as the result 2017-12-01. As in

$mysql_testdate = "2017-12-01";

You run it through strtotime(), then through date(), and you get back "2017-12-01 00:00:00". Great. But why? You went through a lot of work to get nothing out of it. So don't do that.

 

 

Anyways, there is no typical IDE for PHP. Netbeans, Eclipse, and PhpStorm are all popular. There are others.

As for books, I have no idea. Books are out of date so quickly. If you want to read one, find something written for PHP 7 but not 7.2. So 7.0 or better 7.1. If they mention versions. 7.2 just came out so there's no way any books for it will be totally accurate.

[Barand]

I see you changed $testdate to $mysqli_testdate.

 

More powerful magic I suppose.

 

And why would you want to take a datetime field (yyyy-mm-dd hh:ii:ss format), convert it to a unix integer timestamp then reconvert from that back to a date in the same format that you started with? Therein lies madness.

[achana]

Thx for putting so much effort into this, as you can see, I removed the double quotes soon as I spotted them.

Understanding what some of the php functions return is the trick.

Any one of this will do:

if (!$con) {
    die("Connection failed: ".mysqli_connect_error());
}
$row1=mysqli_fetch_assoc(mysqli_query($con,'SELECT date(max(Date)) as testdate FROM XS_Course '));
echo $row1['testdate']."<br>";

$row2=mysqli_fetch_array(mysqli_query($con,'SELECT max(Date) as testdate FROM XS_Course'));
echo $row2['testdate']."<br>";
echo $row2[0]."<br>";

$sql1 = "SELECT max(Date) as testdate FROM XS_Course;";
$result1 = mysqli_query($con, $sql1);
$row3 = mysqli_fetch_object($result1);
echo $row3->testdate."<br>" ;

$sql2 = "SELECT max(Date) as testdate FROM XS_Course;";
$result2 = mysqli_query($con, $sql2);
$row4 = mysqli_fetch_row($result2);
echo $row4[0];

/* close connection */
mysqli_close($con);

Edited December 2, 2017 by achana

[achana]

I see you changed $testdate to $mysqli_testdate.

 

More powerful magic I suppose.

 

And why would you want to take a datetime field (yyyy-mm-dd hh:ii:ss format), convert it to a unix integer timestamp then reconvert from that back to a date in the same format that you started with? Therein lies madness.

 

You are funny