asked on

How would I craft a PDO SELECT based on an IF Statement?

I want to shape a SELECT statement before I "prepare" it.

Here's what I've got so far:

public function term_select() {
		
		global $conn;
		
		$qualifier=$_GET['qualifier'];
		
		$sql="select from terms";
		
		if($qualifier=="PDO")
		{
			$sql.=" where discipline=:qualifier";
		}
		else
		{
			$sql.=" order by discipline";	
		}
		
		if($qualifier=="PDO")
		{
			$query=$conn->prepare($sql);
			$query->bindParam(':qualifier', $qualifier, PDO::PARAM_STR);
			$query->execute();
		}
		else
		{
			$query=$conn->prepare($sql);
			$query->execute();
		}
		
		$result_array=array();

		while($row=$query->fetch_array())
			{
			$result_array[]=$row;
			}
		
	return $result_array;
		
	}

Open in new window

The "qualifier" in this instance is PDO, so I'm good right up till:

$query->execute();

At that point, I'm getting an error which reads:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from terms where discipline='PDO'' at line 1' in C:\wamp64\www\adm\term_class_pdo.php on line 276

Line 276 is:

$query=$conn->prepare($sql);
$query->bindParam(':qualifier', $qualifier, PDO::PARAM_STR);
$query->execute();

What am I missing and / or what do I need to do differently? I realize I could eliminate the need to "build" the $sql and do it like this:

if($qualifier=="PDO")
{
$query=$conn->prepare("select * from terms where discipline=:qualifier");
$query=bindParam(':qualifier', $qualifier, PDO::PARAM_STR);
$query->execute();
}

Open in new window

...but if I wanted to structure the SELECT based on the IF statement, how could I do it?

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bruce Gust

ASKER

Thanks for weighing in, Ray! I did look up cyclomatic complexity and I understand. Thing is, what you're looking at is a stripped down version of a problem where there's a hoss of a SELECT and 25 different scenarios all broken up with IF statements. I didn't write the code and it's an antiquated MySQL configuration. I was hoping to avoid having to write the SELECT 25 different times.

Ray Paseur

Also, you can use PDOStatement::fetchAll() and you won't need the while() iterator at the end of the function.

Ray Paseur

Well, there is certainly nothing wrong with building a query dynamically. Maybe if we get closer to the actual set of issues we can show you a better way forward.

I think the SELECT query is missing the names of the columns you want to select?

Bruce Gust

ASKER

Ray, I got it!

I went back and incorporated a function I found online that gives you a chance to see what the PDO dynamic is looking at. It's nowhere near as "direct" as simply echo-ing the sql, but it gets the job done.

It was with that I was able to determine I was missing my "*" in my SELECT. So, instead of "SELECT * from blah-blah," I had "SELECT from blah-blah." It's amazing how something that simple can escape your notice when you're expecting something more complicated to be the issue.

Thank you!

Ray Paseur

Yeah, I've found myself looking right at the cause of the error and never seeing the tree because it was in the forest! It doesn't help that MySQL messages are notoriously opaque. Anyway, glad you've gotten over the hump.

Bruce Gust

ASKER

As an aside, here's the function that I used to "see" what's going on. It requires some doing, but it helps reveal things that might otherwise go in-noticed...

class TermAdmin {
	//the function that displays what's going on

	public static function debug_sql($sql, $function_name, $sql_string, $sql_data) {
	ob_start();
	$sql->debugDumpParams();
	$r = ob_get_contents();
	ob_end_clean();
	//end of debug, start of script that prints sql

	 $indexed=$sql_data==array_values($sql_data);
        foreach($sql_data as $k=>$v) {
            if(is_string($v)) $v="'$v'";
            if($indexed) $sql_string=preg_replace('/\?/',$v,$sql_string,1);
            else $sql_string=str_replace(":$k",$v,$sql_string);
        }
		
	$michelle= '<pre style="white-space: pre-wrap; word-wrap: break-word;"><div style="font-family:Arial; size=10pt; display:inline-block; width:800px; height:auto; padding:10px;border:1px solid #cccccc; border-radius:10pt;"><div style="background-color:#000000; color:#ffffff; width:780px; padding:10px; display:inline-block; border-top-left-radius:10pt; border-top-right-radius:10pt;">System Debug...</div><br><br><b>function name:'.$function_name.'</b><br><br>'.htmlspecialchars($r).'<br><br><div style="background-color:blue; color:#ffffff; width:780px; padding:10px; display:inline-block;">SQL Statement...</div><br><br>'.$sql_string.'<br><br></div></pre>';

	echo $michelle;
	}

// an example of some sql that's not behaving

public function term_select() {
		
		global $conn;
		
		if(isset($_GET['qualifier']))
		{
			$qualifier=$_GET['qualifier'];
		}
		else
		{
			$qualifier="";
		}
		
		$sql="select * from terms";
		
		if($qualifier=="PDO")
		{
			$sql.=" where discipline=:qualifier";
		}
		else
		{
			$sql.=" order by discipline";	
		}
		
		if($qualifier=="PDO")
		{
			$query=$conn->prepare($sql);
			$query->bindParam(':qualifier', $qualifier, PDO::PARAM_STR);
			
                         //here's the code you have to add to "see" what's goin on

			[b]$sql_string=$sql;
			$sql_data=array('qualifier'=>$qualifier);
			$this->debug_sql($query, "select registration", $sql_string, $sql_data);
[/b]			
			$query->execute();
		}
		else
		{
			$query=$conn->prepare($sql);
			$query->execute();
		}
		
		$result_array=array();

		while($row=$query->fetch(PDO::FETCH_ASSOC))
			{
			$result_array[]=$row;
			}
		
	return $result_array;
		
	}

Open in new window

And here's the "window" that appears that displays the text / errors...
Capture.PNG

Ray Paseur

That looks helpful. I think everyone, myself included, has found that PDO queries are a little obscure since you cannot see the query and the data in the same display. Most of us wind up with an abstraction layer that gives us access to the kind of information you have there.