This query gives no error and no result either

[ajoo]

Hi all !,

 

I am stuck on the following piece of code which does not give an error nor does it give a result. ( i.e. it gives 0 num_rows which should be > 1). 

 

If, however, I execute the query in phpmyadmin by simply substituting the values of $pp,$ll and $room_no in the query it gives the correct result. 

 

 

Please can someone tell me what I may be doing wrong here.  Thanks !

	$fcon = mysqli_connect($db_host,$db_user,$db_pass,$db_database) or die('Unable to establish a DB connection');

				$pp = "(ms.level = 'Beginner' || ms.level = 'Intermediate')";	  
				$ll = 'ms.diff <= 7';
				$room_no = 4;

				$query = "SELECT md.Member_reg_id, md.fname, md.lname, md.email, md.cell, 
						ms.level, ms.diff, ms.score,  
						r.ID_Status 
						FROM register as r
						JOIN member_detail as md ON r.ID = md.Member_reg_id  
						JOIN memstatus as ms On r.ID = ms.ID  
						WHERE r.CENTERCODE = ? AND r.ID_Status ='A' AND ? AND ?
						ORDER by level, diff, score DESC";
		
				$stmt=$fcon->prepare($query);
				$stmt->bind_param('iss',$room_no,$pp,$ll);
				if(!$stmt->execute()) die('Failed to execute the query'.$fcon->error);
				else
				{
					echo "Executed";
					$stmt->bind_result($Member_reg_id,$fname,$lname,$email,$cell,$level,$diff                                               ,$score,$ID_Status);
					$numrows = $stmt->num_rows;
					$stmt->store_result();
//					echo $numrows;
					while($stmt->fetch())
					{
						echo "<br>".$fname.' '.$lname;
						echo "<br>".$level;
						echo "<br>".$diff;
						echo "<br>".$score;
						echo "<br>".$cell;
						echo "<br>".$email;
					}
				}

Edited January 3, 2015 by ajoo

[Barand]

You can't bind column names, only values

$pp1 = 'Beginner';      
$pp2 = 'Intermediate';      
$ll = 7;
$room_no = 4;

$query = "SELECT md.Member_reg_id, md.fname, md.lname, md.email, md.cell, 
        ms.level, ms.diff, ms.score,  
        r.ID_Status 
        FROM register as r
        JOIN member_detail as md ON r.ID = md.Member_reg_id  
        JOIN memstatus as ms On r.ID = ms.ID  
        WHERE r.CENTERCODE = ? 
            AND r.ID_Status ='A' 
            AND ms.level IN (?,?) 
            AND ms.diff <= ?
        ORDER by level, diff, score DESC";

$stmt=$fcon->prepare($query);
$stmt->bind_param('issi',$room_no,$pp1,$pp2,$ll);

[ajoo]

Hi Guru Barand, 

 

Thanks loads for that. The problem is that $pp and $ll in the original query were handling the Query to deal with different conditions where the strings created different conditionals. 

 

for example :

 

$pp = "(ms.level = 'Beginner' || ms.level = 'Intermediate')";

$pp = "ms.level = '$dd_level'";   // where dd_level can be one of the three values 'Beginner', 'Intermediate' or 'Expert'

 

Similarly the value of Diff can be <7 or it can be any one of the values from 1 to 7.

so $ll = "ms.Diff <= 7";

or $ll = "ms.Diff = 3";

 

Thus the same query was able to handle so many situations. How can I achieve something like that here using prepared statements. 

 

Thanks very much.

[kicken]

How can I achieve something like that here using prepared statements.

You still build the SQL text dynamically, but bind whatever user-input needs to be used where necessary.

 

EG:

$params=array();
if ($someCondition){
    $pp = "(ms.level = 'Beginner' || ms.level = 'Intermediate')";
}
else {
    $pp = "ms.level = ?";
    $params[] = $dd_level;
}

$sql = "
SELECT ...
FROM ...
WHERE
   $pp
";

//bind whatever is in $params

[ajoo]

Hi Kicken. Thanks for the clarification. 

 

However, after some testing, I do feel that if there are a number of conditions that the query must handle it would result in a rather awkward solution. Is there by any chance a better way to handle such queries?

 

Thanks again !

[mac_gyver]

how many of the actual data values ('Beginner', 'Intermediate', 7, 3, ...) that you have shown are directly from external user supplied data?

 

if the values being used and the different conditions ('<=' vs '=') in the query are all produced by your program logic, with none of the actual values coming directly from external user supplied data, there's no need to use a prepared query for them.

 

perhaps if you show how you are coming up with the values in $pp, $ll, and $room_no now, someone can help.

[ajoo]

Hi Mac_gyver, 

 

Well the values come from two drop down menus where the user selects these values. The Level dropdown has three values as of now:-

1. All

2  Beginner

3. Intermediate.  and will have one more value Expert eventually.

 

2. Diff is another drop down and has values from

1. All and  

Numbers from 1 to 10. 

( Hence 11 values in the dropdown)

 

The user select these from these menus and the output displays the records accordingly.

 

By default both Level and Diff have a default value of All.

i.e. Level = Beginner OR Level =Intermediate

and 

Diff  <= 10

 

I hope this helps. If any one has a better idea on implementing such queries in a more elegant manner then please share it with me.

Thanks loads everyone.

[Barand]

If the user chooses the "All" option then you can leave that column and the condition value out of the query

[mac_gyver]

based on what you have posted, this really isn't a 'prepared query' problem, but a program logic and a design problem.

 

in addition to what has already been posted, your level choice (all, Beginner, Intermediate, Expert, anything else in the future...) needs to use numerical values in the program logic and in the database table (1 = beginner, 2 = intermediate, 3 = expert) , rather than keywords. you would then be able to use a value comparison <= in the query (leaving the term out of the query for the ALL choice.) if at this time you don't want to change your database to store numerical level values, you would use an array in your php code that takes the submitted number from the select menu to give you the corresponding term using keywords to put into the query.

[ajoo]

Hi All,

 

Thanks for all the inputs. I have been trying to use them all to find a fit all solution. I think, after a few trials, that the solution posted by Guru Kicken would work great if there is a way for the query to extract the values to be bound from an array. i.e.

 

Can the bound variables in the statement

$stmt->bind_param('iss',$room_no,$pp,$ll); 

be somehow replaced by an array of values like this  

$stmt->bind_param('iss',$param[]);

This was suggested by Guru Kicken. 

 

 

 

//bind whatever is in $params

 

So I request Guru Kicken or anyone to suggest a simple way to "bind whatever is in the params" to the query using an array.

 

However the length of the array would be varying depending upon the conditons involved.  That should solve this quite elegantly.

 

Thanks all ! 

[mac_gyver]

there is no simple way of doing this using mysqli prepared queries. you will need to dynamically call the bind_param() function using the call_user_func_array() function or by using the reflection class (there are examples of both of these methods in the user contributed notes for the mysqli bind_param() function in the php.net documentation.)

 

using PDO with prepared queries is actually easier. since each bind statement is called separately, you can just loop over the $param array and bind each value.

[ajoo]

Thanks Sir, 

 

I have just shifted over to mysqli prepared queries so it will be a while before I make the transition to PDO. I have already converted more than 60% of the code to use mysqli prepared statements. I will however keep that in mind as I have been advised by some other gurus too besides yourself.

 

I will now look up and try out the functions that you have just suggested above. Will revert. 

 

Thanks very much!

[ajoo]

Hi, all, Back again. 

 

I now tried as follows: 

				$pp = "(ms.level = ? || ms.level = ?)";	  
				$qq = 'ms.diff <= ?';
				
				$mm = 'Beginner';
				$nn = 'Intermediate';
				$ll = 7;
				$room_no = 4;
				
//                $bmm = &$mm;
//                $bnn = &$nn;
//                $bll = &$ll;
//                $broom_no = &$room_no;
// $bvalues = array($broom_no,$bmm,$bnn,$bll);

				$btypes = "issi";
				$types = &$btypes;
                                               
				$bvalues = array($room_no,$mm,$nn,$ll);
				$values = &$values;
				
				$params = array($types,$values);
				
				$query = "SELECT md.Member_reg_id, md.fname, md.lname, md.email, md.cell, 
						ms.level, ms.diff, ms.score,  
						r.ID_Status 
						FROM register as r
						JOIN member_detail as md ON r.ID = md.Member_reg_id  
						JOIN memstatus as ms On r.ID = ms.ID  
						WHERE r.room_no = ? AND r.ID_Status ='A' 
						AND $pp 
						AND $qq
						ORDER by level, diff, score DESC";
		
				$stmt=$fcon->prepare($query);
				call_user_func_array(array($stmt, 'bind_param'), $params);

and this gives me the following warning:

 

 

 

mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

 

I have tried the same with a changes as well but I am not able to get thru this. I have 4 bound parameters in the query and I have passed 4 values thru the array so I don;t know why I am getting this error.

 

Please can someone show me how to devise the params array correctly or what might be the error here. 

Thanks all !

Edited January 4, 2015 by ajoo

[ajoo]

Ok I managed to find a solution. With a slight modification. Here it is:-

				$btypes = array('issi');
				$bvalues = array($room_no,$bmm,$bnn,$bll);
				
				$params = array_merge($btypes, $bvalues);
				$refs = array();
				
				foreach($params as $key => $value)
				$refs[$key] = &$params[$key];

                                ...  
                                call_user_func_array(array($stmt, 'bind_param'), $refs);

There are actually 3 best answers to this Guru Barand's initially, then Guru Kicken's and finally MacGyvers which pointed me to call_user_func_array() for dynamically binding the variables.

 

 Thanks all !

Edited January 4, 2015 by ajoo