Data from form showing up, but not sure why query can't match it...

Jim R · January 9, 2018

I'm getting information from a form, which is posting to a_players_reviews (APR) directly. It would be one row at a time. It's also suppose to check the rows in a_players (AP), and if doesn't match nameFirst, nameLast and grade then it Inserts a new a Row.

I know by viewing the lines of log input after $data array, the proper data is there. However, the logs below are showing no errors and two NULL results. I've tested it with a player who is already in AP, so it should produce one Row.

I can't tell if it's not getting information from AP, or if it's just not matching the information from the form.

    $data = array(
        'f_school' => $form->getValue('quform_1_7'),
        'f_grade' => $form->getValue('quform_1_5'),
        'f_nameFirst' => $form->getValue('quform_1_3'),
        'f_nameLast' => $form->getValue('quform_1_4')
    );
 
 			Quform::log($data['f_school']);
 			Quform::log($data['f_grade']);
 			Quform::log($data['f_nameFirst']);
 			Quform::log($data['f_nameLast']);

include (ABSPATH ."resources/connection.php");

 
			$query = "SELECT nameFirst,nameLast,grade
						FROM a_players
						WHERE concat(nameFirst,nameLast,grade) = concat ('" .$data['f_nameFirst']. "', '" .$data['f_nameLast']."', '".$data['f_grade']. "')";
			
			$results = mysql_query($query);
			$num_rows = mysql_num_rows($results);
		
		
			Quform::log(mysql_error());
			Quform::log(mysql_num_rows());
			
			Quform::log($results['nameFirst']);
		
		//	while($check = mysql_fetch_assoc($results)) {			
  
					if (($num_rows) == 0) {
			 			"INSERT INTO a_players (school, grade, nameFirst, nameLast)
						VALUES ('".$data['f_school']."', '".$data['f_grade']. "', '" .$data['f_nameFirst']. "', '" .$data['f_nameLast']."')";
					}			

		return $result;

ginerjm · January 9, 2018

Question.

Is "abc,def,ghi" the same as "abcdefghi"?

Jim R · January 9, 2018

No, but keep in mind, it's showing a NULL value in the log for $results['nameFirst']); off the query, but I am getting the proper values off the form.

Edited January 9, 2018 by Jim R

Jim R · January 9, 2018

Current query looks like this now:

$nameFirst = $data['f_nameFirst'];

$nameLast = $data['f_nameLast'];

$grade = $data['f_grade'];

$query = "SELECT nameFirst,nameLast,grade FROM a_players WHERE concat(nameFirst,nameLast,grade) = ".$nameFirst.$nameLast.$grade."";

Error: No current column AaronHenry18

And I'm still getting

Quform::log(mysql_num_rows());

Quform::log($results['nameFirst']);

Both as NULL.

Edited January 9, 2018 by Jim R

gizmola · January 9, 2018

Can we start with the basics of querying?

Why are you trying to compare concatenated values? Did you want your queries to be as slow and error prone as possible?

Your query should have a where clause like this:

    $query = "SELECT nameFirst, nameLast, grade
    FROM a_players
    WHERE nameFirst = '{$data['f_nameFirst']}' AND nameLast = '{$data['f_nameLast']}' AND grade = '{$data['f_grade']}'";

The '{}' is php blocking that allows you to use the properly formatted array key strings without confusing PHP's parsing and interpolation.

This would not work:

WHERE nameFirst = '$data['f_nameFirst']'

This does:

WHERE nameFirst = '{$data['f_nameFirst']}'

I also have to provide the perfunctory admonition that this code is both obsolete (using mysql_) and unsafe, and you should use bound parameters, but you can add that in later once you fix the basic queries and get predictable results.

Jim R · January 10, 2018

I had that structure at first, but it wasn't working. I likely didn't have it just like that, but I just copied it and pasted it over the query I had. It's still showing NULL values for $results['nameFirst']);

It should've returned a Row with the information I put in there.

[10-Jan-2018 04:34:41 UTC] string(9) "Ben Davis"

[10-Jan-2018 04:34:41 UTC] string(2) "18"

[10-Jan-2018 04:34:41 UTC] string(5) "Aaron"

[10-Jan-2018 04:34:41 UTC] string(5) "Henry"

[10-Jan-2018 04:34:41 UTC] string(0) ""

[10-Jan-2018 04:34:41 UTC] NULL

[10-Jan-2018 04:34:41 UTC] NULL

The first NULL should be 1

The second NULL should be Aaron

dodgeitorelse3 · January 10, 2018

so your query returned all of those results from 1 query? I'm lost. Ben Davis looks like first and last name and then second result looks like his grade. Third result is just first name? 4th result is last name? 5th result is no grade? first null should have been 1 and second null should have been Aaron? how does that work? wouldn't all results be formatted the same? instead of all jumbled up? or am I missing something?

Edited January 10, 2018 by dodgeitorelse3

gizmola · January 10, 2018

What is the current code that you have?

In the code you posted above, you commented out the part that would fetch results. You also attempted to read a variable prior to fetching which would not work:

Quform::log($results['nameFirst']);
		
		//	while($check = mysql_fetch_assoc($results)) {

This is wrong on several levels:

The result of mysql_query is a result set handle, so you can't try and access data from that.

This would have a chance of working:

    while($row = mysql_fetch_assoc($results)) {
        Quform::log($row['nameFirst']);	
        ....

In summary:

- mysql_query returns a result set handle (if it worked)

- with the results set handle ($results in your code), you can then attempt to fetch a row from the result set.

- You need to fetch each row individually, if you want to get them all.

Jim R · January 10, 2018

so your query returned all of those results from 1 query? I'm lost. Ben Davis looks like first and last name and then second result looks like his grade. Third result is just first name? 4th result is last name? 5th result is no grade? first null should have been 1 and second null should have been Aaron? how does that work? wouldn't all results be formatted the same? instead of all jumbled up? or am I missing something?

The first four results are from the form. The fifth would show any errors. The final two should show query results.

Jim R · January 11, 2018

What is the current code that you have?

In the code you posted above, you commented out the part that would fetch results. You also attempted to read a variable prior to fetching which would not work:
Quform::log($results['nameFirst']);
		
		//	while($check = mysql_fetch_assoc($results)) {	
This is wrong on several levels:

The result of mysql_query is a result set handle, so you can't try and access data from that.

This would have a chance of working:
    while($row = mysql_fetch_assoc($results)) {
        Quform::log($row['nameFirst']);	
        ....
In summary:

- mysql_query returns a result set handle (if it worked)

- with the results set handle ($results in your code), you can then attempt to fetch a row from the result set.

- You need to fetch each row individually, if you want to get them all.

Added the WHILE statement and moved it up a bit. Here is my current code:

 $data = array(
        'f_school' => $form->getValue('quform_1_7'),
        'f_grade' => $form->getValue('quform_1_5'),
        'f_nameFirst' => $form->getValue('quform_1_3'),
        'f_nameLast' => $form->getValue('quform_1_4')
    );
 
 			Quform::log($data['f_school']);
 			Quform::log($data['f_grade']);
 			Quform::log($data['f_nameFirst']);
 			Quform::log($data['f_nameLast']);
 			
 		$nameFirst = $data['f_nameFirst'];
 		$nameLast = $data['f_nameLast'];
 		$grade = $data['f_grade'];
 		

include (ABSPATH ."resources/connection.php");

 
			$query = "SELECT nameFirst, nameLast, grade
    				FROM a_players
    				WHERE nameFirst = '{$data['f_nameFirst']}' AND nameLast = '{$data['f_nameLast']}' AND grade = '{$data['f_grade']}'";
			
			$results = mysql_query($query);

			while($check = mysql_fetch_assoc($results)) {			
  
				$num_rows = mysql_num_rows($check);
					
		
				Quform::log(mysql_error());
				Quform::log(mysql_num_rows());
			
				Quform::log($check['nameFirst']);
		
		
						if (($num_rows) == 0) {
							"INSERT INTO a_players (school, grade, nameFirst, nameLast)
							VALUES ('".$data['f_school']."', '".$data['f_grade']. "', '" .$data['f_nameFirst']. "', '" .$data['f_nameLast']."')";
						}			
			}
			
		return $result;

Number of Rows returned: Still NULL

$check['nameFirst'] : Aaron (so that worked)

Jim R · January 11, 2018

Keep in mind, my need is I'm just trying to get information from a form and inset it into a secondary data table if it doesn't already exist. The only data I plan to use from the query is whether or not it found a Row which matched information from the form. If it did, it stops. If it didn't, it Inserts a row of information passed through from the form.

Edited January 11, 2018 by Jim R

Barand · January 11, 2018

You should be checking how many rows were returned in $results.

$check is an array of the field values from the first record (or false if there was no record).

Basically you need to think about what you are doing, and RTFM so you know what the functions do.

Jim R · January 11, 2018

You should be checking how many rows were returned in $results.

$check is an array of the field values from the first record (or false if there was no record).

Basically you need to think about what you are doing, and RTFM so you know what the functions do.

If you note the original code, that's what I had. It was returning NULL.

As for the RTFM, I have RTFM as I have needed to learn it. I can't always wrap my head around it, so I come here. :-*

Edited January 11, 2018 by Jim R

dodgeitorelse3 · January 11, 2018

Is return $result supposed to be $result or $results?

Jim R · January 11, 2018

Is return $result supposed to be $result or $results?

$result is passed from the form via add_action. $results is what I have set up for my query to check if a Row exists. Everything between add_action line and return $result; is code by me.

gizmola · January 12, 2018

You are progressing to almost working code, but again you are using a key function incorrectly.

Here is your issue:

while($check = mysql_fetch_assoc($results)) {			
  
				$num_rows = mysql_num_rows($check);

Now if you think about this for a second, $check in this code is a row from the result set. That is what you are asking for with:

$check = mysql_fetch_assoc($results))

It makes no sense at all to call mysql_num_rows() and pass it the array of row data you just fetched. It also doesn't make sense for this to be inside your fetching loop, and it wasn't in your previous code.

That is why you are getting null.

If you don't want or need that you don't need to loop and if you aren't going to do anything with the individual rows you are fetching, there is no reason to be looping and fetching rows of data when you plan to throw that data away.

Based on what you just stated, this is probably what you are looking for:

$data = array(
       'f_school' => $form->getValue('quform_1_7'),
       'f_grade' => $form->getValue('quform_1_5'),
       'f_nameFirst' => $form->getValue('quform_1_3'),
       'f_nameLast' => $form->getValue('quform_1_4')
);

Quform::log($data['f_school']);
Quform::log($data['f_grade']);
Quform::log($data['f_nameFirst']);
Quform::log($data['f_nameLast']);
     
$nameFirst = $data['f_nameFirst'];
$nameLast = $data['f_nameLast'];
$grade = $data['f_grade'];

include (ABSPATH ."resources/connection.php");

$query = "SELECT nameFirst, nameLast, grade
          FROM a_players
          WHERE nameFirst = '{$data['f_nameFirst']}' 
          AND nameLast = '{$data['f_nameLast']}' 
          AND grade = '{$data['f_grade']}'";
     
$results = mysql_query($query);

if (!$results) {
    Quform::log('SQL Error: ' . mysql_error($results));
    return 'SQL Error';
} else {
    // Was a row found?
    $num_rows = mysql_num_rows($results);
    Quform::log("Number Rows: $num_rows");
    if ($num_rows == 0) {
        $query = "INSERT INTO a_players (school, grade, nameFirst, nameLast)
        VALUES 
        ('{$data['f_school']}', 
        '{$data['f_grade']}', 
        '{$data['f_nameFirst']}', 
        '{$data['f_nameLast']}')";
        $results = mysql_query($query);
        // Add check for error here, logging in case Insert fails
    }			
}

Please review this code carefully, and keep in mind there might be typos or other errors, as I'm compiling this stuff in my head.

Another logic issue your code has:

-What happens when a user has 2 assignments, and they got an 'A' in both? Your system won't allow a new grade row to be inserted.

Jim R · January 12, 2018

@gizmola that's definitely closer, as at least it's finally posting to the AP table. The issue may have been I didn't set up the Insert Into properly. However, now the problem is it's posting to AP regardless if the row already exists or not.

Going by what you wrote, if !$results, it should Insert a new row. If $results, it should just stop.

Jim R · January 12, 2018

I tried this...

if (!$results) {
			$query = "INSERT INTO a_players (school, grade, nameFirst, nameLast)
			VALUES 
			('{$data['f_school']}', 
			'{$data['f_grade']}', 
			'{$data['f_nameFirst']}', 
			'{$data['f_nameLast']}')";
			
        $results = mysql_query($query);
				
	}

...but it didn't Insert anything regardless if the row already existed or not.

Jim R · January 12, 2018

I tested this a little by printing into the log evidence of what row(s) it finds, if any. I just ended with the test. I commented out the insertion part.

The test worked...

$query = "SELECT nameFirst, nameLast, grade
		FROM a_players
		WHERE nameFirst = '{$data['f_nameFirst']}' 
		AND nameLast = '{$data['f_nameLast']}' 
		AND grade = '{$data['f_grade']}'";

$results = mysql_query($query);

while($check = mysql_fetch_assoc($results)) {

	Quform::log($check['nameFirst']);

}

When I typed in a player's name, it showed up in the log. When I typed in my name, nothing even was printed. Not even a NULL. So we know that part of the code works. It's just not able, so far, to tell the rest of it whether or not to Insert a row.

Jim R · January 12, 2018

OK...I got it to work. I have no clue how it's much different than any of the versions I had before. Earlier it wasn't picking up whether or not it was finding Rows. It just kept saying NULL. I look at what I have now, which works, and it makes sense, but I can't really decipher how it was different. Now it even shows 0 vs. NULL.

I may have the queries wrong and functions right, then spent too much time messing with the functions. Anyway, below is the final code.

(I removed the log input lines I had in previous versions.)

    $data = array(
        'f_school' => $form->getValue('quform_1_7'),
        'f_grade' => $form->getValue('quform_1_5'),
        'f_nameFirst' => $form->getValue('quform_1_3'),
        'f_nameLast' => $form->getValue('quform_1_4')
    );
 			
 		$nameFirst = $data['f_nameFirst'];
 		$nameLast = $data['f_nameLast'];
 		$grade = $data['f_grade'];
 		

include (ABSPATH ."resources/connection.php");

 
$query = "SELECT nameFirst, nameLast, grade
		FROM a_players
		WHERE nameFirst = '{$data['f_nameFirst']}' 
		AND nameLast = '{$data['f_nameLast']}' 
		AND grade = '{$data['f_grade']}'";

$results = mysql_query($query);

$num_rows = mysql_num_rows($results);

		if (($num_rows) == 0) {
			$query = "INSERT INTO a_players (grouping, school, grade, nameFirst, nameLast)
			VALUES 
			('4',
			'{$data['f_school']}', 
			'{$data['f_grade']}', 
			'{$data['f_nameFirst']}', 
			'{$data['f_nameLast']}')";
			
        $results = mysql_query($query);
	
	}

Jim R · January 12, 2018

I appreciate everyone's time in helping.

Sign In

Data from form showing up, but not sure why query can't match it...

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information