Query Statement Help

[ttsprez]

I'm having an issue with a query statement pulling info: 

This link carries customers to a "document gateway" where they are required to enter a reference code.

https://pdforms.losttitleconnection.net/TX Title App Gate.php

Use this Reference Code for test purpose:

QKEX49MKDE

On submit the PHP code is to take the reference code from the Post function of the gateway form and convert it to a variable $refcode

This variable is then used in the query statement to pull 'name' and 'data' from the form_data table and use it to replace form field names with data in an html template. Then it is to present the new form in a pdf format to the browser for download or printing. I'm using the dompdf library to do this, but for now, am only using a short query test code to ensure I'm pulling the correct data by having the code display results.  

Currently, on submit, the browser is presenting the following:

SELECT name,data FROM thetitl1_livesite754.form_data WHERE form_id IN(SELECT id FROM thetitl1_livesite754.forms WHERE reference_code = '')

The following is the code as it appears in my PHP file. Help to get this figured out would be greatly appreciated.   

	<?php
	//create a connection to MySQL
require 'dogs.php';
	//query for reference_code from 'forms' table
$refcode = filter_input(INPUT_POST,'reference_code');
	$mysqli = new mysqli($server, $username, $password, $dbase);
	$query = "SELECT name,data FROM thetitl1_livesite754.form_data WHERE form_id IN(SELECT id FROM thetitl1_livesite754.forms WHERE reference_code = '" . $refcode . "')";
die($query);
	$results = $mysqli->query($query);
	$new_form = file_get_contents("TX_Title_App.html");
	//Replaces form_fields in html temp with "data"
if ($results) {
//below is the line referenced in error message above     
while ($row = mysqli_fetch_fields($results)) {
        echo $row["name"] . ":" . $row["data"] . "<br>";
       $new_form = str_replace($row["name"], $row["data"], $new_form);
 }
}
?>
	

[mac_gyver]

your form is using method='get'. your php code is trying to use post data. if your code had checked the value in $refcode before using it, you would have already known this (it should be a null since the input variable is not set.)

[ttsprez]

@Mac_gyver 

Thanks for pointing that out.  I'm new to this and learning as I go... but I change the method="get" to method="post" and I'm still getting the same message in the browser. 

[mac_gyver]

and did you reload the page in your browser so that the change would take effect? i just tried your form page and it displayed the value that i entered in the form in the sql query statement.

next, you should NOT put external/unknown data directly into an sql query statement. you should use a prepared query, with a place-holder in the sql query, then supply the actual data when the query gets executed.

[ttsprez]

yes, and it gives me the same statement with the $refcode. Okay so, I forgot I have had the "die($query);", but just removed it and now all it gives me a repetitive " : " with no name or data info as the code is requesting. 

[ttsprez]

yes, and it gives me the same statement with the $refcode. Okay so, I forgot I have had the "die($query);", but just removed it and now all it gives me a repetitive " : " with no name or data info as the code is requesting. 

as to the second part of your statement pertaining to 'prepared statements' I will have to research that and implement it.  If it makes this query function in a more proper manner than it would seem that it needs to be done. 

[mac_gyver]

mysqli_fetch_fields() doesn't do what you think. it fetches information about the fields. it doesn't fetch data and you would have been getting php undefined index errors from your code to alert you to the problem. you need to ALWAYS have php's error_reporting set to E_ALL and when learning, developing, and debugging code,  have display_errors set to ON and when on a live/public server have display_errors set to OFF and log_errors set to ON.

you would want to use mysqli_fetch_assoc() to fetch the data.

[maxxd]

While you're at this point, and especially if you're going to be exploring prepared statements as mac_gyver (correctly) suggested, look at switching to PDO. It's much easier to use than mysqli_*, and it's not tied to a single SQL language so portability is easier.