My 'Foreach' SQL query build is not working

[m2244]

So, I am trying to build a SQL query dynamically from user input. The mysql_real_escape_string seems to be the problem. When I reun this I get the 'No fields entered' echo message. Can someone help me understand what's wrong here?

$FName = $_POST['fname'];
$LName = $_POST['lname'];
$Email = $_POST['email'];

$Organization = $_POST['organization'];
$Supervisor = $_POST['supervisor'];
$SuperEmail = $_POST['superemail'];
//$Class = $_POST['courseID'];
//$PGrade = $_POST['gs'];
//$OccSeries = $_POST['occseries'];
//$MilGrade = $_POST['milgrade'];
//$MilSpec = $_POST['milspec'];
$MajCom = $_POST['majcom'];

//$LName = "FILLER";
echo "Here you are: " . $LName . "<br />" . $FName . "<br />";



//$tsql = "select ID,CourseID,lastname,firstname,email,paygrade,organization,supervisor,superemail,milgrade,milspec,majcom from Registrations";

// List of possible form fields. (The "name" attributes of the <input> elements.)
$expectedKeys = array($LName, $FName, $Email, $Organization, $Supervisor, $SuperEmail, $MajCom);

// A list to be populated with the "key=value" pairs you want in your WHERE clause.
$fields = array();

// Loop through the keys and add the field to the list if needed.
foreach ($expectedKeys as $key)
{
    if (!empty($_POST[$key]))
    {
        $fields[] = sprintf("`%s` = '%s'", $key, mysql_real_escape_string($_POST[$key]));
    }
}

// Make sure there were actually some fields you can use.
if (count($fields) > 0)
{
    // Construct the WHERE clause by gluing the fields together with a " AND " separator.
    $whereClause = "WHERE " . implode(" AND ", $fields);

    // And then create the SQL query itself.
    $sql = "select ID,CourseID,lastname,firstname,email,paygrade,organization,supervisor,superemail,milgrade,milspec,majcom from Registrations " . $whereClause;
	
	echo $sql;
}
else
{
    echo "No fields entered!";
}

[Andy-H]

You have created your $expectedKeys array incorrectly, you use:

 

array($FName, $LName); // etc

 

However, these variables are set to $_POST['fname'], $_POST['lname'] etc. This will result in an empty string, or the data posted, if the form has been submitted, so later in your script you are essentially calling:

 

if ( !empty($_POST['']) )

 

or

 

if ( !empty($_POST['some_random_name']) )

 

So you need to change your expected keys array to contain the relevant strings.

Edited January 3, 2014 by Andy-H

[Ch0cu3r]

The $expectedKeys array should contain the $_POST arrays keys (form field names) you are wanting to check.

 

This is how you should $expectedKeys setup the array

$expectedKeys = array('fname', 'lname', 'email', 'organization', 'supervisor', 'superemail', 'majcom');

The next problem you have is your database fields are named differently to your form fields. The code you are using to dynamically generating your SQL queries uses the keys listed in the $expectedKeys array as the actual database fields.

[m2244]

You have created your $expectedKeys array incorrectly, you use:

 

array($FName, $LName); // etc

 

So you need to change your expected keys array to contain the relevant strings.

 

I am not very good with PHP. I tried to cast as a string but I can't seem to get it.

[hansford]

You don't need to explicitly cast variables to new data types in PHP as it's done automatically.

Here is a link to the page in the manual: http://www.php.net/manual/en/language.types.type-juggling.php

[hansford]

If you're trying to get the name value pairs here is a way to accomplish that.

// A list to be populated with the "key=value" pairs you want in your WHERE clause.
$fields = array();

// Loop through the keys and add the field to the list if needed.
foreach ($_POST as $name => $value)
{
    if (!empty($value))
    {
        $fields[] = sprintf("'%s' = '%s'", $name, $value);
    }
}