Acting on variables from a MySQL Query

[acarter360]

I am having a problem with a PHP7 routine to act on the value of a Search Results array returned by a MySQL query.

 

An example of the query returns the following when I do a var_dump($row):

["entry_id"]=> string(5) "20352" ["element_id"]=> string(1) "1" ["value"]=> string(6) "Robert"

 

There are 14 records per form entry. One record/row has the First Name. Next record has the Last Name. Third record has email address. And so on.

 

[entry_id] just stores the unique form number

[element_id] is the variable (1 = first name, 2 = last name, etc.)

[value] is the person's answer to the form question

 

I need to read each $row of the query and store each of the values in these 14 records into 14 variables which I can then send in an email.

 

If I echo the $row, it works fine using this code:

    while($row = mysqli_fetch_assoc($result)) {
        echo "entryid: " . $row["entry_id"]. " - Element ID: " . $row["element_id"]. " - Value: " . $row["value"]. "<br>";
 

 

But when I try to store the variables, using the code below, nothing works. I get a blank white screen trying this code and echoing the variable such as $varFirstName returns a blank screen too.

 

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

        // First Name
        if ( $row['element_id'] == "1" ) {
            $varFirstName="$row["value"]";
        }        
        
        // Last Name
        if ( $row['element_id'] == "2" ) {
            $varLastName="$row["value"]";
        }        
        
        // Email
        if ( $row['element_id'] == "3" ) {
            $varEmail="$row["value"]";
        }        

 

             ... etc
    }       
       
I hope this is enough information to explain my problem. Thank you in advance.

 

[mac_gyver]

the blank page is due to php syntax errors. to get php to help you find these type of errors, you must have php's error_reporting set to E_ALL and display_errors set to ON, in the php.ini on your development system. you cannot put these settings into your code and have them report/display php syntax errors in the same file because your code never runs to cause the settings to take effect.

 

next, when all you are doing is mapping input values to output values, don't write out conditional logic for each possible value. just map the inputs to outputs using either a database table (and write a JOIN query) or a php array (and get the name/label using the element_id to index into the array.) doing this will eliminate all the conditional logic, which will also eliminate the current syntax errors (you have unnecessary quotes around the "$row["..."]" variables and have double-quotes around the associative index names.)

 

also, there no good reason to create individual variables from this data. either build the email message inside the while(){} loop or if you do have a need to create variables, build an array of data, with the array associative index name being the element name/label.

Edited March 13, 2018 by mac_gyver

[ginerjm]

And on another note - why would you have a query that gives you the "first name" in one row and the "last name" in a 2nd row?

 

BTW - this:

$varFirstName="$row["value"]";

is messed up.

 

How about:

$varFirstName= $row["value"];

 

PS - if you are dead set on using local vars to reference your query results you should read up on the 'list' construct in the php manual. Check this out: http://us3.php.net/manual/en/function.list.php

Edited March 13, 2018 by ginerjm

[Barand]

And on another note - why would you have a query that gives you the "first name" in one row and the "last name" in a 2nd row?

 

 

ginerjm - Try googling "Entity Attribute Value (EAV) data model". Very popular with CMS packages.

[ginerjm]

So how did you know that this user was involved in a CMS?

[requinix]

Because we recognize the database design pattern.

 

acarter360, what is the structure of the table you're querying from? How about the structure of the table containing the "questions", or whatever those element_id values map to? A row or two of example data from each would also be nice for what I have in mind.

[acarter360]

First of all, many, many thanks to @mac_gyver and @ginerjm for taking the time to reply/help me and give me resources I can reference. I will be looking at the references to other materials due to your feedback. I really appreciate it. I also appreciate this forum.

 

Second, this is from a Forms plugin for WordPress (nice call). The Form # is created in one table:

 

 

and the Form Entries in a second one:

 

Sorry I cannot post more information but it contains personal information. The data values for the Form Entries are as follow:

element_id = 1        First Name
element_id = 2        City
element_id = 3        Email
element_id = 4        Phone
element_id = 5        Employment Status
element_id = 6        Rate of Pay
element_id = 7        Monthly Salary
element_id = 8        Time on Job
element_id = 9        Filed bankruptcy?
element_id = 10        Down payment amount
element_id = 11        Referred by
element_id = 12        Last Name
element_id = 13        Open loans?
element_id = 14        Agree to privacy policy
       

Entry_id field in the second table maps to the id field in the first table.

The form programs I have used before would put all 14 fields in one table and store the values there. This one creates 14 records.

[Psycho]

Might I suggest putting the data into a more logical structure to use in your code?

 

$elements = array(
    1 => 'first_name',
    2 => 'city',
    3 => 'email',
    4 => 'phone',
    // etc . . .
)
 
$records = array();
while($row = mysqli_fetch_assoc($result))
{
    $records[$row["entry_id"]][$elements[$row["entry_id"]]] = $row["value"];
}

 

The data would then be in a format like this

 

array (
 
  43 => array
  (
    'first_name' => 'Dob',
    'city' => 'Denver',
    'email' => 'bob@yahoo.com',
    // etc
  ),
  82 => array
  (
    'first_name' => 'Alan',
    'city' => 'Los Angeles',
    'email' => 'alan@gmail.com',
    // etc
  )
 
)

[gizmola]

Riffing on Psycho's suggestion here is another way of doing the transform, using a function and array_map:

 

function remap($e) {
    $elements = array(
        1 => 'first_name',
        2 => 'city',
        3 => 'email',
        4 => 'phone'
    );
    $key = isset($elements[$e['element_id']]) ? $elements[$e['element_id']]: 'unknown'.$e['element_id'];
    return array($key => $e['value']);
}

// Assumes your data is in $form_entries as described

$output = array();
foreach(array_map('remap', $form_entries) as $value) {
    $output[key($value)] = current($value);
};

var_dump($output);

You should get output like this:

 

array(4) {
  ["first_name"]=>
  string(4) "Fred"
  ["city"]=>
  string(7) "Phoenix"
  ["email"]=>
  string(13) "fred@test.com"
  ["unknown99"]=>
  string(23) "Something else entirely"
}

In my case I assume that the original database results are for one entry_id only, and entry_id can be safely ignored.

 

You also need to make sure that the $elements mapping array is accurate and up to date. It only needs mappings for the actual keys you need to use in your email and other keys will be named unknown?? where ?? is the original numeric 'element_id';