Looking for guidance on MySQL tables and columns issue

Looking for guidance on MySQL tables and columns issue
0

#1

This is an image of data that I need to use to fill an HTML template that will then be used to create a pdf doc using the docpdf lib. I am querying this in MySQL using the following statement:

 SELECT * FROM `thetitl1_livesite754`.`form_data` WHERE (CONVERT(`form_id` USING utf8) LIKE '29') ORDER BY `form_data`.`form_field_id` ASC

As you can see the ‘name’ column has values like VehVin6, VehVin etc.,… and the ‘data’ column has the actual info that I need to fill in the HTML template fields that correspond to the ‘name’ column data.

my question is…

Is it easier to make a PHP code that will make a MySQL connection and then fetch each ‘data’ value from this query using the 'form_field_id" and matching it for what the form field is in the HTML template that needs to be replaced?

Or is it better to create a table called finaldetails, create columns for each value in the ‘name’ column and then somehow assign the values in the ‘data’ column of this query to the associated column and then fetch that data?

Here is the project scenario, each customer is provided with a reference code when they submit a “finaldetails” form (losttitleconnection-dot-net/finaldetails). After processing payment for their service the will see the “instruct” page (http://losttitleconnection.net/bondinstruct) where they will be asked to input that reference_code. If you move to the very bottom of this page there is a list of yellow buttons, one of which has the words “Application For Texas Title” at the very bottom. This is the HTML template (https://pdforms.losttitleconnection.net/TX_Title_App.html) that I am working with and I want to push the MySQL data to using PHP code. I have a PHP code file created, but as you can tell it is in need of proper coding to complete this process successfully. This is a copy of the PHP code structure thus far that is connected to the link button. It is a work in progress of course


<?php 

var theForm = document.forms["finaldetails"];

var $refcode = reference_code.innerHTML;

require 'dogs.php';

$sql = "SELECT *  FROM `thetitl1_livesite754`.`forms` 
WHERE (CONVERT(`reference_code` USING utf8) LIKE \'$refcode\')";

//I realize there are other lines of code that need to go here to complete the fetch
//of proper info to fill the HTML file below. 


$new_form = file_get_contents(“/Tx Title App.html”);

while ($row = mysql_fetch_assoc($query)) {
    foreach($row as $key => $value) {
        $new_form = str_replace("__" . $key . "__", $value, $new_form);
     }
}

// reference the Dompdf namespace
use Dompdf\Dompdf;
use Dompdf\Options;
require_once ‘lib/dompdf/autoload.inc.php’;

// instantiate and use the dompdf class
$dompdf = new Dompdf();
$dompdf->loadHtml($new_form);

// (Optional) Setup the paper size and orientation
$dompdf->setPaper(‘A4’, ‘portrait’);

// Render the HTML as PDF
$dompdf->render();

// Output the generated PDF to Browser
$dompdf->stream();

// Save to file
$pdf_gen = $dompdf->output();
file_put_contents(“Tx Title App.pdf”, $pdf_gen);

// Display PDF in browser
echo $dompdf->output();


?>

I’ve been working to understand how I can take information that the database can produce in a query and find the best way to structure my request in PHP so that it accomplishes my customers need for a preprepared pdf form that will be downloaded, printed or saved.

As I have said, a reference_code is issued per customer that has a ‘form_id’ associated with it in a ‘forms’ table. My overall thought was to perform a query using the ‘reference_code’ from "forms’ table like such:


$sql = "SELECT *  FROM `thetitl1_livesite754`.`forms` WHERE (CONVERT(`reference_code` USING utf8) LIKE \'QKEX49MKDE\')";

Then create PHP code to gather the 'form_id" where the ‘reference_code’ matches.

Then the following statement to fetch the query for the image above:


$sql = "SELECT *  FROM `thetitl1_livesite754`.`form_data` WHERE (CONVERT(`form_id` USING utf8) LIKE \'29\') ORDER BY `form_field_id` ASC";

And then just get some help with creating code that will take the info in the ‘data’ column that would associate each value with a field name in the template so that the field names are replaced with the data.

So then, of course, the question arose. Would it be easier to create a table with the info and use the existing PHP code to fill the HTML template as such


while ($row = mysql_fetch_assoc($query)) {
    foreach($row as $key => $value) {
        $new_form = str_replace("__" . $key . "__", $value, $new_form);
     }
}

To me, the latter would seem best, but as I’ve said, I don’t know how to go about it or if it is even possible.

Guidance on this would be of great help. If you could please give me your perspective and an overview of what needs to be done. I can try to put it together and present it for further review