SQL works, but value is not returned...

Beginners - Learning PHP
#1

Hi,

New to php so I am still at the experimental stage, and I have a problem whose solution is probably right in front of me. I have a simple SQL query that I feed a first and last name to, and it should return the sum of points for that individual. The name is fed to the query, and the query runs correctly when I run it manually, so I figure that that much is right. But I cannot get a sum of points to return to my php script.

Can anyone point me in the right direction?

[php]

<?php include('session.php'); echo $fName." ".$lName; // calculate sum value of points for individual BY SPECIFIC NAME $query = "SELECT SUM(Points) as Points FROM tx_records WHERE First_Name='$fName' AND Last_Name='$lName'"; $result = mysqli_query($connection, $query); $row = mysqli_fetch_assoc($result); $points = $row["Points"]; echo 'points = '.$points ?>

[/php]

#2

Prepared statements first of all
.

Second, use print_r to get what is coming back.

#3

Thanks for the response. I’m REALLY new to the concept of prepared statements although I recognize why they’re needed, and I was trying to get an understanding of php and something that works before I dove into them. But I guess there’s no time like the present…

So I modified the block to read:

[php]<?php
include(‘session.php’);

try{
	//$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$stmt = $connection->prepare("SELECT SUM(Points) as Points FROM tx_records WHERE First_Name=? AND Last_Name=?");
	$stmt->bind_param("ss", $fName, $lName);
	$stmt->execute();
	echo "SQL ran successfully.";
	}
catch(PDOException $e)
	{
		echo "Error: " . $e->getMessage();
	}

$row = mysqli_fetch_assoc($stmt);
$points = $row['Points'];
print_r ($points);
$stmt->close();

?>[/php]

I had to REM the setAttribute() line because it threw an error of: “Call to undefined method mysqli::setAttribute() in E:\bulldog\php\student_profile.php:5 Stack trace: #0 {main} thrown in E:\bulldog\php\student_profile.php on line 5”, which I do not understand. And I realize that the omission of this line of code would probably cause problems with the ‘catch(PDOexception $e)…’ at line 12, but I figured if I see an error relating to that it would only confirm my query did not run. Which would be something at least.

When I placed the '//" in that line, it appeared to work because I got a message of ‘SQL ran successfully’, which admittedly could be a false positive.

At that point, the code appears to run, but with an error of 'Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, object given in E:\bulldog\php\student_profile.php on line 16".

Interestingly, when I google to find info on prepared statements, everything I find is abot INSERT queries, which I can use elsewhere, but not terribly helpful in this instance.

Any ideas?

TIA

#4

Just found a site that discussed returning values…

Modified my code to read:
[php]<?php
include(‘session.php’);

try{
	//$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$stmt = $connection->prepare("SELECT SUM(Points) as Points FROM tx_records WHERE First_Name=? AND Last_Name=?");
	$stmt->bind_param("ss", $fName, $lName);
	$stmt->execute();
	echo "SQL ran successfully.";
	}
catch(PDOException $e)
	{
		echo "Error: " . $e->getMessage();
	}

$res = $stmt->get_result();
$row = $res->fetch_assoc();
$points = $row['Points'];
print_r ($points);
$stmt->close();

?>[/php]

It now throws NO errors. But it returns no value, either.

#5

You are mixing mysqli and PDO statements. You cannot do this. The statements must all be from the same extension. You should use the PDO extension. It is much simpler and more consistent then the mysqli extension. The setAttribute() method is from the PDO extension.

Next, forget about catching most exceptions in your code. The only time this is needed is if your code needs to deal with the error, such as inserting/updating duplicate data. If you remove the try/catch logic, php will catch the exception and use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information.

With the PDO extension, just supply the input data as an array when you call the ->execute(…) method. There is almost never a good reason to explicitly bind input values.

#6
#7

I got the foreach() block to work, but cannot get the prepare() segment to work. Maybe that’s a signal for what I’m doing wrong. Maybe not…

[php]<?php
include(‘session.php’);

//echo $fName . " " . $lName;
$stmt = $connection->prepare('SELECT First_Name, Last_Name, SUM(Points) FROM tx_records WHERE First_Name=? AND Last_Name=?');

$stmt->execute(array($fName, $lName));
$row = $stmt->fetchAll(PDO::FETCH_ASSOC);
$Points = $row['SUM(Points)'];
	
// THIS WORKS DO NOT CHANGE FOR NOW
//foreach($connection->query("SELECT First_Name,Last_Name, SUM(Points) FROM tx_records WHERE First_Name='Allen' AND Last_Name='Troxell'") as $row){
//	echo $row['First_Name'] . " " . $row['Last_Name'] . " " . $row['SUM(Points)'] . '<br>';
//	}

?>[/php]

With the prepare() block I get an error of

‘Warning: mysqli_stmt::execute() expects exactly 0 parameters, 1 given in E:\bulldog\php\student_profile.php on line 7’

followed by

‘Uncaught Error: Call to undefined method mysqli_stmt::fetchAll() in E:\bulldog\php\student_profile.php:8 Stack trace: #0 {main} thrown in E:\bulldog\php\student_profile.php on line 8’.

That’s not really a mysqli statement-why does it think it is? And so I’m guessing that I am not allowed to pass an array in the parentheses? Even though I have seen it used exactly as I have it as an example in multiple sites, including at php.net, where it shows:

[php]Example #3 Execute a prepared statement with an array of insert values (placeholders)

<?php /* Execute a prepared statement by passing an array of insert values */ $calories = 150; $colour = 'red'; $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < ? AND colour = ?'); $sth->execute(array($calories, $colour)); ?> [/php]

This is nearly identical to what I am attempting. And it does not work if I simply attempt to pass the variables individually, which I have also seen in examples.

I recall seeing the '$connection->query(…") method on line 12 is frowned upon for some reason, but it is the one that is working–without variables, which defeats its purpose. I have tried to modify that segment to use variables, but I have not been successful.

And I know that $fName and $lName are valid values because I see them echoed in my web page at the top (where I’ve remmed them in this display) correctly. They arrive via the session variable.

I have now looked at numerous forums and php sites and it’s all getting a bit jumbled. What am I missing? It must be something obvious…

#8

You already have the objects, you just need to iterate thru them, not query more.

[php]
$stmt = $connection->prepare(‘SELECT First_Name, Last_Name, SUM(Points) as Points FROM tx_records WHERE First_Name=? AND Last_Name=?’);

$stmt->execute([$fName, $lName]);
$row = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach($row as $r){
echo “{$r[‘First_Name’]} {$r[‘Last_Name’]} {$r[‘Points’]}
”;
}[/php]

#9

Your connection code is still using mysqli, not pdo, so, $connection is an instance of mysqli, which ‘works’ for the ->prepare() method call, but doesn’t work for the pdo style ->execute() method call. Make the connection using PDO. It would also help if you named the variable $pdo, rather than $connection, so that anyone looking at the code would know what type of connection it is/should be.

#10

:o

I missed that you hadn’t actually switched to PDO. Pick one or the other.

#11

phdr–You may be on to something there. That sounds like it makes sense.

I will go back and rewrite my “session.php” to reflect the correct PDO connection. I’m sure that’s going to take me at least a day or two to build and test out.

stonecipher–“muchas gracias” for your patience. I’m new to php, and I really had no idea that I was crossing wires and now I see that my code gave no hint that that might be the case, so your insight has helped greatly even without a successful closure. My apologies for crossing things up and confusing you.

Thank you!

#12

It is easy to change the connection string, provided you just put it in the one place. How far along in the project are you? If you have several queries, it is more time consuming to make the change.

Sponsor our Newsletter | Privacy Policy | Terms of Service