Compare result of MySQL statement to string in PHP

[mattchamp97]

Hi All,

I'm working on PHP scripts to interact with a web hosted MySQL DB for an Android Application.

Simply what I am trying to do is in the PHP script is run a SELECT statement which will return the value of a column, UserType, and compare the result of this to a string, which will then execute code depending on it's value. This user type can only be either 'student' or 'lecturer'. Any help with this would be much appreciated. 

<?php

    require "init.php";
	
	$user = $_GET["userID"];
	
	#Selects column account type where the idNum equals $user which is passed from my app.
	$sql1 = "select accountType from user_info where idNum = '$user'";
	$result1 = mysqli_query($con,$sql1);
	$row1 = mysqli_fetch_assoc($result1);
	
	#This is where I am stuck. Simply, I am trying to run the code in the loop where the result of $sql1 equals 'Student'. The else will run if it is not 		#student and therefore is 'Lecturer'. I'm also not sure if my code inside the IF is fully correct either as it's not running that far.

	if($row1['accountType'] == 'Student')
	{
		$sql2 = "select courseCode from user_info where idNum = '$user'";
		$result2 = mysqli_query($con,$sql2);
		$row2 = mysqli_fetch_assoc($result2);
		
		$sql3 = "select * from module_details where classListCourseCode = '".$row2['courseCode']."'";
		$result3 = mysqli_query($con,$sql3);
		$response = array();
		
		while($row = mysqli_fetch_array($result3))
		{
			array_push($response,array("moduleID"=>$row[0],"lecturerID"=>$row[1],"moduleName"=>$row[2],"classListCourseCode"=>$row[3]));
		}
		
		echo json_encode(array("server_response"=>$response));
	}

Thanks in advance.

[ginerjm]

I"m confused.  Do you want to find the records with a specific id num or the ones that are students?

And - a table with named "user_info" with a column named "userid" kind of implies that there is only one row that matches.  Hmmm.....

[Barand]

Too many queries. Don't run queries inside loops - use JOINs. For example

<?php
const HOST     = 'localhost';                                                            
const USERNAME = '????';                                                                 
const PASSWORD = '????';                                                                 
const DATABASE = '????');                                                                
                                                                                         
function pdoConnect($dbname=DATABASE)                                                    
{                                                                                        
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);  
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                        
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);                   
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);                                
    return $db;                                                                          
}                                                                                        

$db = pdoConnect();

$user = 1;

$res = $con->prepare("SELECT m.moduleId
                           , m.lecturerId
                           , m.moduleName
                           , m.classListCourseCode
                           , u.accountType
                      FROM user_info u
                           JOIN
                           module_details m ON u.courseCode = m.classListCourseCode
                           WHERE u.idNum = ?    
                     ");
$res->execute( [$user] );
$response = [];
foreach ($res as $r) {
    $acc = array_pop($r);
    if ($acc=='student') {
        $response[] = $r;
    }
}
echo json_encode(array("server_response"=>$response));                  
?>  

 

[Phi11W]

14 hours ago, mattchamp97 said:

This user type can only be either 'student' or 'lecturer'.
. . . 
if( $row1[ 'accountType' ] == 'Student' )

String Comparsons in PHP are case sensitive. 

 

14 hours ago, mattchamp97 said:

$sql1 = "select accountType from user_info where idNum = '$user'";

Is idNum a number or not?  If it is, then the value passed in $user should not appear in quotes.  Doing so forces MySQL to perform an implicit Type Conversion, which can cause some nasty side-effects. 

Better still, use a prepared statement to pass the parameter value; that will protect you better from SQL Injection attacks. 

 

14 hours ago, mattchamp97 said:

$sql2 = "select courseCode from user_info where idNum = '$user'";
$sql3 = "select * from module_details where classListCourseCode = '".$row2['courseCode']."'";

No need to do this in two successive queries.  Let your Database do the Joining: 

select u.courseCode
, m.field1
, m.field2
, m.field3
, ... 
from user_info u 
inner join module_details m 
      on   m.classListCourseCode = u.courseCode 
where u.idNum = ?

Never use "select *" in Production code. 

Regards, 
   Phill  W.

 

[mattchamp97]

14 hours ago, ginerjm said:

I"m confused.  Do you want to find the records with a specific id num or the ones that are students?

And - a table with named "user_info" with a column named "userid" kind of implies that there is only one row that matches.  Hmmm.....

What I'm trying to achieve is find the account type of the user who's ID is passed to the PHP script and stored as $user. $user is a string variable as in my instance it can contain letters as well as numbers. 

So I'm looking to find the record where the idNum equals that passed to the script, and then see what it has stored under accountType, which will either be "Student" or "Lecturer". 

[mattchamp97]

51 minutes ago, Phi11W said:

String Comparsons in PHP are case sensitive. 

 

Is idNum a number or not?  If it is, then the value passed in $user should not appear in quotes.  Doing so forces MySQL to perform an implicit Type Conversion, which can cause some nasty side-effects. 

Better still, use a prepared statement to pass the parameter value; that will protect you better from SQL Injection attacks. 

 

No need to do this in two successive queries.  Let your Database do the Joining: 

select u.courseCode
, m.field1
, m.field2
, m.field3
, ... 
from user_info u 
inner join module_details m 
      on   m.classListCourseCode = u.courseCode 
where u.idNum = ?

Never use "select *" in Production code. 

Regards, 
   Phill  W.

 

Hi Phill,

idNum is a string as in my case, ID numbers may contain characters as well as numbers. This is also not production code, just a personal project I'm working on.

[ginerjm]

So have you got your answer yet from Philw's post?

[mattchamp97]

Hi guys,

My knowledge of PHP is very basic. The only piece of functionality I need is to see if the result of following SELECT equals a string "Student", and to then run the code inside my loop.

	$user = $_GET["userID"];
	
	$sql1 = "select accountType from user_info where idNum = '$user'";
	$result1 = mysqli_query($con,$sql1);
	$row1 = mysqli_fetch_assoc($result1);
	#$usertype = $row1['accountType'];
	
	if($row1['accountType'] == 'Student')
	{
		#run code here

I don't understand joins to a huge degree I just want to simply compare the string to the result of my SQL statement and run the code in my if.

[ginerjm]

What people are telling you is to NOT write your code like this and to learn how it SHOULD be done.

Ignoring that - what is the problem now?    YOu can't find the right value?  Are you allowing for case being incorrect?  Or have you researched how to make a proper compar to allow for different cases?

Research, research, research - something a programmer has to do.

[mattchamp97]

14 minutes ago, ginerjm said:

What people are telling you is to NOT write your code like this and to learn how it SHOULD be done.

Ignoring that - what is the problem now?    YOu can't find the right value?  Are you allowing for case being incorrect?  Or have you researched how to make a proper compar to allow for different cases?

Research, research, research - something a programmer has to do.

I understand what people mean but I just don't understand PHP very well and can't progress with my work without this bit of code.

My problem is that I need to fetch the account type, which can only be Student or Lecturer. I then want to run different code to return modules relevant to the account type based on this.

I can't find anywhere online how to compare the result of a SELECT to a string, this is ultimately where I'm getting stuck.

[ginerjm]

Again - point out to us which line is failing you or explain what you are doing and not understanding.  25 words or less please.  That will make more sense.

BTW - did you understand what I told you last?

[mattchamp97]

2 minutes ago, ginerjm said:

Again - point out to us which line is failing you or explain what you are doing and not understanding.  25 words or less please.  That will make more sense.

BTW - did you understand what I told you last?

	$user = $_GET["userID"];
	
	$sql1 = "select accountType from user_info where idNum = '$user'";
	$result1 = mysqli_query($con,$sql1);
	$row1 = mysqli_fetch_assoc($result1);
	#$usertype = $row1['accountType'];
	
	if($row1['accountType'] == 'Student')
	{
		#run code here

The line that is failing me is my if statement. I don't understand how to store the result of my sql query and compare it to a string.

 

And yes I do understand what you meant regarding good practice with my coding, I'll be implementing the join as suggested inside of my loop once I figure out how to identify the account type.

[ginerjm]

What do you mean by "store the result....."   Why do you need to store it anywhere?  You already have it!  And what compare are you trying to do that you are not already doing in this if?

What I told you was not about good practice.  I how you have to be aware of what you are trying to compare as far as the case goes.  

Edited March 31, 2020 by ginerjm

[mattchamp97]

30 minutes ago, ginerjm said:

What do you mean by "store the result....."   Why do you need to store it anywhere?  You already have it!  And what compare are you trying to do that you are not already doing in this if?

What I told you was not about good practice.  I how you have to be aware of what you are trying to compare as far as the case goes.  

I want to compare the result (Which will be either Student OR Lecturer) to the String "Student". Through error checking I have discovered that the line that will not run is the following.

if($row1['accountType'] == 'Student')
{

Where am I going wrong here?

 

I have also tried the following with no luck.

$sql1 = "select accountType from user_info where idNum = '$user'";
$result1 = mysqli_query($con,$sql1);
$row1 = mysqli_fetch_row($result1);
	
if($row1[0] == 'Student')
{

 

[ginerjm]

Don't you think you should share that error that you found by error checking?

[ginerjm]

Perhaps this line will solve your problem - even tho you haven't told us of the problem

if(strtoupper($row1['accountType']) == 'STUDENT')
   echo " result is student";
else
   echo "results is NOT student";

Of course you might want to add some code to check that the query actually returned some row(s).....  Have you read up on how to do that?

[mattchamp97]

16 minutes ago, ginerjm said:

Perhaps this line will solve your problem - even tho you haven't told us of the problem

if(strtoupper($row1['accountType']) == 'STUDENT')
   echo " result is student";
else
   echo "results is NOT student";

Of course you might want to add some code to check that the query actually returned some row(s).....  Have you read up on how to do that?

I'm not sure how to error check like this as my PHP scripts are sending responses to my app via JSON objects.

I didn't get an error code in my error checking, but by process of elimination I was able to identify that the line of code identified earlier is what is not working for me.

if(strtoupper($row1['accountType']) == 'STUDENT')

I tried this line for my if statement but unfortunately it does not work.

[ginerjm]

Define "does not work".

[ginerjm]

Here's another suggestion.  You say the if statement does not work.  Let's prove that it is actually getting executed by placing an echo line just before it to show that your script is getting to that spot

echo "About to test the query value: ".$row1['accountType'] . "<br>";
if(strtoupper($row1['accountType']) == 'STUDENT')
   echo " result is student";
else
   echo "results is NOT student";

This will ABSOLUTELY HAVE TO SHOW YOU SOMETHING unless you are not doing any output to the web browser.

BTW - do you have php error checking enabled?

[Phi11W]

17 hours ago, mattchamp97 said:

$row1 = mysqli_fetch_assoc($result1);
if($row1['accountType'] == 'Student')
   . . . 

The line that is failing me is my if statement. I don't understand how to store the result of my sql query and compare it to a string.

$row1 is an associative (keyed) array containing the data returned by your SQL query.  That's a good enough "store" for this context.

If the "if" is misbehaving, then you need to find out what values it's [not] working with.  This is the fundamental core of "debugging" your application - getting in amongst the code and seeing what's going on. 

The var_dump() and print_r() functions are your friends here.

$row1 = mysqli_fetch_assoc($result1);
var_dump( $row1 ); 
if($row1['accountType'] == 'Student')
. . . 

Regards, 
   Phill  W.

 

[ginerjm]

Or use the code sample I posted previously..  if you are still with us.