PHP script comparing answers to a key

I think I'm going about this the wrong way

[Chiaroscuro]

First: I have been searching the internet, PHP website, w3schools, the MySQL website for a few weeks, but I have no background in scripting so I may be doing this completely backwards. Thank you for any help - Todd

Second the project:
Web quiz of 10 videos and a yes/no question. Answers are put into a MySQL database. After much hard work, this actually works. Now I was trying to write a PHP script to compare the input in the database to the answer key in another database (no reason it's a separate database, just could figure out how to compare cells repeatedly within the same table). Below is my abbreviated code:

Notes: only a few answers were filled in excelUSdb, the ELSE IF statement I just added in should have been true for almost all number_wrong but nothing was changed. Putting in echos, results from strings printed out as expected outside of the "IF" statements, but all IF statements came back true (most should have been false). The a_ptx strings printed out correctly.

<?php
$link=mysqli_connect('localhost','root','password');

if (!$link) {
echo ("<P>Unable to connect to the " . "database server at this time.</P>");
exit();
}

if (!@mysqli_select_db($link,"excelUSdb_answer_key")) {
echo( "<P>Unable to locate the US answers " .
"database at this time.</P>" );
exit();
}
$answers=mysqli_query($link,"SELECT * FROM answers");

$a_row=mysqli_fetch_array($answers);
$a_ptx1=$a_row["ptx1"];
$a_ptx2=$a_row["ptx2"];
$a_ptx3=$a_row["ptx3"];

?>

<?php
if (!@mysqli_select_db($link,"excelUSdb")) {
echo( "<P>Unable to locate the US " .
"database at this time.</P>" );
exit();
}
$result=mysqli_query($link,"SELECT * FROM PGYName");

WHILE ($row=mysqli_fetch_array($result)) {
$l_name=$row["LastName"];
$_ptx1=$row["PTX1"];
IF ($_ptx1=$a_ptx1){
$r_number_c=mysqli_query($link,
"SELECT number_correct FROM PGYName WHERE LastName = $l_name");
mysqli_query($link,
"UPDATE answers SET number_correct='$r_number_c + 1'
WHERE LastName = $l_name");
}
ELSE IF ($_ptx1="x") {
mysqli_query($link,
"UPDATE answers SET number_wrong=99 WHERE LastName = $l_name");
}
ELSE {
$r_number_w=mysqli_query($link,
"SELECT number_wrong FROM PGYName WHERE LastName = $l_name");
mysqli_query($link,
"UPDATE answers SET number_wrong='$r_number_w +1'
WHERE LastName = $l_name");
}
$_ptx2=$row["PTX2"];
IF ($_ptx2=$a_ptx2){
$r_number_c=mysqli_query($link,
"SELECT number_correct FROM PGYName WHERE LastName = $l_name");
mysqli_query($link,
"UPDATE answers SET number_correct='$r_number_c + 1'
WHERE LastName = $l_name");
}
ELSE {
$r_number_w=mysqli_query($link,
"SELECT number_wrong FROM PGYName WHERE LastName = $l_name");
mysqli_query($link,
"UPDATE answers SET number_wrong='$r_number_w +1'
WHERE LastName = $l_name");
}
}
Echo ("it's done");?>

[cameraman]

Welcome to WebmasterWorld, Chiaroscuro!

In php, to compare you use a double-equal sign:
if($a == 1)

A single equal sign means "set the value of the left side to the evaluation of the right side" - even in an if.
So
if($a = 1)

well set the variable to 1.

[Chiaroscuro]

Thank you so much. Don't know why I didn't find that anywhere. Of course that brings up the issue that I just noted:

first off I was trying to update the wrong table, corrected that. Then PHP didn't like [column]=$_string+1, then I tried to add it above the query $new_string=$_string+1... [column]=$new_string. That didn't work. I echoed the $_string and got this error:
PHP Catchable fatal error: Object of class mysqli_result could not be converted to string in C:\Inetpub\wwwroot\MyWeb\check_answers_test.php on line 42 (the line with the echo).

Took out the echo, and now the code worked?

Any problem with me redefining those variables over and over like that without clearing them?

while ($row=mysqli_fetch_array($result)) {
$l_name=$row["LastName"];
$_ptx1=$row["PTX1"];
IF ($_ptx1==$a_ptx1){
$r_number_c=mysqli_query($link, "SELECT number_correct FROM PGYName WHERE LastName = '$l_name'")
OR DIE("SELECT Error: ".mysqli_error($link));
$c='$r_number_c'+1;
mysqli_query($link,"UPDATE pgyname SET number_correct='$c' WHERE LastName = '$l_name'")
OR DIE("UPDATE Error: ".mysqli_error($link));
}
ELSE IF ($_ptx1=="x") {
ECHO (" ERROR ".$a_ptx1);
mysqli_query($link, "UPDATE pgyname SET number_wrong=99 WHERE LastName = $l_name");
}
ELSE {
$r_number_w=mysqli_query($link, "SELECT number_wrong FROM PGYName WHERE LastName = $l_name");
$w='$r_number_w'+1;
mysqli_query($link, "UPDATE pgyname SET number_wrong='$w'+1 WHERE LastName = $l_name");
}
$_ptx2=$row["PTX2"];
IF ($_ptx2==$a_ptx2){
$r_number_c=mysqli_query($link, "SELECT number_correct FROM PGYName WHERE LastName = '$l_name'")
OR DIE("SELECT Error: ".mysqli_error($link));
$c='$r_number_c'+1;
mysqli_query($link,
"UPDATE pgyname SET number_correct='$c'+1 WHERE LastName = $l_name");
}
ELSE {
$r_number_w=mysqli_query($link, "SELECT number_wrong FROM PGYName WHERE LastName = $l_name");
$w='$r_number_w'+1;
mysqli_query($link, "UPDATE pgyname SET number_wrong='$w'+1 WHERE LastName = $l_name");
}
}

[cameraman]

There's nothing wrong with reusing variables as long as you don't confuse yourself - I reuse them ad nauseum.
If the variable that you're reusing is a resource, best practice is to free the resource (how depends on what sort it is) before you reuse it for something else.

The error is telling you that you're trying to print an object. I'm not seeing in your code where $a_ptx1 becomes an object, but in this line here:
$r_number_c=mysqli_query($link, "SELECT number_correct FROM PGYName WHERE LastName = '$l_name'")

$r_number_c is a result object, not the value of number_correct from the query. I've never used mysqli, but looking at the docs it appears to me that you have to use a fetch method.

You may find that using the mysql_ functions is a bit easier than the mysqli ones. You still use a connect function (mysql_connect) and get a linkID (that's an example of a resource, by the way), and you still use a select db function (mysql_select_db). Then to do the above,
$query = mysql_query("SELECT number_correct FROM PGYName WHERE LastName = '$l_name'",$linkID);
$row = mysql_fetch_assoc($query);
$r_number_c = $row['number_correct'];
mysql_free_result($query); // once you've retrieved all the rows you need you free this resource.

And if you only have one connection open to the database, you don't even need to supply $linkid on the mysql_query line.
Generally if I am only retrieving one or two fields, I use mysql_fetch_row instead - it saves a tiny bit on server load and more importantly saves typing:
$row = mysql_fetch_row($query);
$r_number_c = $row[0];

One more thing, when you're doing addition on a numeric:
$c='$r_number_c'+1;

you don't need those quotes.
$c=$r_number_c+1;

Unless this is some sort of fallout from the mysqli stuff that I didn't see in the docs (I didn't study them at length) - and this is once you've got $r_number_c really being a number.

[Chiaroscuro]

Thanks, that post clarified the query results vs fetch results for me after I went back and read that stuff again. I modified the code to as follows (changing the names to make sense to me). The mysqli_free_result didn't work though. Gonna work on that later. The mysql vs mysqli seems more or less the same (and I'm too tired to contemplate changing all the code at this point). mysqli though, throws an error if $link is not in query ('expect 2 parameters, 1 given' - or something similar).

Repeat code (which didn't work) below:

while ($row=mysqli_fetch_array($result)) {

$l_name=$row["LastName"];
$_ptx1=$row["PTX1"];

IF ($_ptx1==$a_ptx1){

$r_number_c=mysqli_query($link,
"SELECT number_correct FROM PGYName WHERE LastName = '$l_name'")
OR DIE("SELECT Error: ".mysqli_error($link));

$r_c=mysqli_fetch_array($r_number_c);

$c=$r_c+1;

mysqli_query($link,
"UPDATE pgyname SET number_correct='$c' WHERE LastName = '$l_name'")
OR DIE("UPDATE Error: ".mysqli_error($link));

mysqli_free_result($r_number_c);
}

ELSE IF ($_ptx1=="x") {
ECHO (" ERROR ".$a_ptx1);
}

ELSE {$r_number_w=mysqli_query($link,
"SELECT number_wrong FROM PGYName WHERE LastName = $l_name");

$r_w=mysqli_fetch_array($r_number_w);

$w=$r_w+1;

mysqli_query($link,
"UPDATE pgyname SET number_wrong='$w'+1 WHERE LastName = $l_name");

mysqli_free_result($r_number_w);

}
}

[cameraman]

I understand what you're saying on the mysql vs mysqli - greek vs french, does it really make much difference...
Yes, as the first parameter, the link id would be required since the second parameter is required - optional parameters have to follow required ones.

Ok, here:
$r_c=mysqli_fetch_array($r_number_c);

$c=$r_c+1;

You're still one step out. $r_c is now an array, not a number, so you can't add one to it quite yet. mysqli_fetch_array is returning the query data to you as both:
$r_c[0]
and
$r_c['number_correct']

So in the next line, you would (pick one, doesn't matter):
$c = $r_c[0]+1;

[Chiaroscuro]

Okay, I'm back looking at it after working several long days.... I went over the command mysqli_free_result and the repeating $string thing.... I'm not sure I can use it/need to change my code(see below):

//simplified code
$result=mysqli_query($link,"SELECT * FROM PGYName");

while ($row=mysqli_fetch_array($result)) {
$l_name=$row["LastName"];
$_ptx1=$row["PTX1"];
IF ($_ptx1==$a_ptx1){
$r_number_c=mysqli_query($link, "SELECT stuff FROM table WHERE lname=$l_name");
$r_c=mysqli_fetch_array($r_number_c);
$c=$r_c[0]+1;
mysqli_query($link,"UPDATE table SET number='$c' WHERE lname=$l_name);
[mysqli_free_result($r_number_c);]
}
//<-- I don't think I can release $r_number_c, the documentation
//I read isn't clear if mysqli_free_result only looks at the
//last mysqli_query (in the code above that would be a TRUE or
//FALSE value from the UPDATE command). I get an error for the
//line in brackets above. I tried moving it up above the UPDATE
//without luck.

ELSE {
$r_number_w=mysqli_query($link, "SELECT other_stuff FROM table WHERE lname = $l_name");
$r_w=mysqli_fetch_array($r_number_w);
$w=$r_w[0]+1;
mysqli_query($link, "UPDATE table SET other_stuff='$w'+1 WHERE lname= $l_name");
mysqli_free_result($r_number_w);
}
$_ptx2=$row["PTX2"];
IF ($_ptx2==$a_ptx2){
more or less repeat code above

So what do people think... do I need to cascade the variables differently/query differently, or should this theorectically work and I'm just not approaching it correctly?

Thanks,
Todd

[cameraman]

Where you've got this:
$r_number_c=mysqli_query($link, "SELECT stuff FROM table WHERE lname=$l_name");

Is lname a varchar or text field? If so, you need to surround the variable with single quotes:
$r_number_c=mysqli_query($link, "SELECT stuff FROM table WHERE lname='$l_name'");

and in the update query as well. You don't need quotes around the numbers (I'm assuming $c and $w are numbers in the update queries).

If there's an error in the query (such as no quotes around literal text), $r_number_c would be FALSE which would explain why you can't free it further down.
You can also do this (for debugging, then remove it when it's in "production"):
if(( $r_number_c=mysqli_query($link, "SELECT stuff FROM table WHERE lname=$l_name")) === false)
echo mysqli_error();

To get mysql to tell you if/where an error is in the query statement.

free_result frees the resource that you pass to the function. You are correct, an update or insert query doesn't return a resource, but you're [correctly] freeing the resources from your select statements by specifying them in your calls to the function.

[Chiaroscuro]

I had already added the quotes (it's a varchar), and the script works without the free_result (it updates the database and correctly echoes stuff I've left out for simplicities sake). There actually was Error code in that doesn't show an error (may switch to mysql after all, mysqli requires error() to have something in there - i.e. mysqli_error($link) - these things are a little onerous). Below is the actual code with error stuff in - running this I got:
FREE RESULT Error: Number 1Error in my_thread_global_end(): 1 threads didn't exit

IF ($_ptx1==$a_ptx1){
$r_number_c=mysqli_query($link, "SELECT number_correct FROM PGYName WHERE LastName = '$l_name'")
OR DIE("SELECT Error: ".mysqli_error($link));
$r_c=mysqli_fetch_array($r_number_c)
OR DIE("FETCH Error: ".mysqli_error($r_number_c));
$c=$r_c[0]+1;
mysqli_free_result($r_number_c)
OR DIE("FREE RESULT Error: Number 1");
mysqli_query($link,"UPDATE pgyname SET number_correct='$c' WHERE LastName = '$l_name'")
OR DIE("UPDATE Error: ".mysqli_error($link));

[cameraman]

Well, after spending the afternoon getting the stupid mysqli extension to work on my php installation, I wrote these. The first is "procedural" style and the second is "object" style.

error_reporting(E_ALL);
$dbi = mysqli_connect('localhost','user','pass','dbname');
$qry = mysqli_query($dbi,"SELECT * FROM partmast LIMIT 10");

while($dat = mysqli_fetch_assoc($qry)) {
echo "<div>\n";
foreach($dat as $fld => $val)
echo "$fld = $val<br>\n";
echo "<br>\n";
echo "</div>\n";
}// EndWhile getting data
mysqli_free_result($qry) or die(mysqli_error($dbi));
mysqli_close($dbi);

error_reporting(E_ALL);
$dbi = new mysqli('localhost','user','pass','dbname');
$qry = $dbi->query("SELECT * FROM partmast LIMIT 10");

while($dat = $qry->fetch_assoc()) {
echo "<div>\n";
foreach($dat as $fld => $val)
echo "$fld = $val<br>\n";
echo "<br>\n";
echo "</div>\n";
}// EndWhile getting data
$qry->free();
$dbi->close();

Both work, so I'm at a loss as to why free_result isn't working for you. You might try $r_number_c->free() just for grins to see what happens. If it all works except for that you could just leave it out - it's not the end of the world if you don't free the resources, but it sure is bothersome that it's complaining.