Solved

Pass HTML form values to a PHP array

Posted on 2013-11-01
29
648 Views
Last Modified: 2013-11-07
I am trying to pass filtering parameters from an HTML form to a PHP script which retrieves results from a mySQL database, then have the PHP write the results back to a specific table id.

I can display the full table results, but I am having trouble getting the form values from HTML to PHP.  The PHP can successfully build a WHERE clause with no, all or any filters, but I can't get the filter values into a PHP array.  How do I build the form values into an array so I only have to send one parameter to PHP so that PHP knows it has an array to work with?

Here's where I'm starting from:

HTML (partial)
    <script type="text/javascript">
        "use strict";
        var sZodiacFilters;

        function loadFilters() {
            "use strict";
            sZodiacFilters = "["
                + "'" + document.getElementById("txtKeySearch").value + "', "
                + "'" + document.getElementById("txtFirstnameSearch").value + "', "
                + "'" + document.getElementById("txtLastnameSearch").value + "', "
                + "'" + document.getElementById("txtColumnSearch").value + "', "
                + "'" + document.getElementById("txtDepartmentSearch").value + "'"
                +"]";
            //alert(sZodiacFilters);
            return sZodiacFilters;
        }
        
        function refreshData(sTargetId, sReloadOption) {
            "use strict";
            alert("sTargetId = " + sTargetId
                + "\n" + "sReloadOption = " + sReloadOption);
            if (sReloadOption == "reload") {
                alert("button was clicked");
                loadFilters();
                alert(sZodiacFilters);
            }
            $.ajax({
                url: "Get joined table cells (table only).php",
                data: {filtersArray: sZodiacFilters},
                success: function(result){
                    $("#table2").html(result);
                }
            });
        }
    </script>

Open in new window


HTML target
            <div id="dataTable">
                <form id="search1">
                    <button id="btnFind" onclick="refreshData('table2', 'reload');">Find</button><br/>
                    
                    <table id="table1">
                        <tbody>
                            <tr>
                                <td class="nogrid"><input id="txtKeySearch" type="text" class="keyFilter"></td>
                                <td class="nogrid"><input id="txtFirstnameSearch" class="firstnameFilter" type="text"/></td>
                                <td class="nogrid"><input id="txtLastnameSearch" class="lastnameFilter" type="text"/></td>
                                <td class="nogrid"><input id="txtColumnSearch" class="signFilter" type="text"/></td>
                                <td class="nogrid"><input id="txtDepartmentSearch" class="departmentFilter" type="text"/></td>
                            </tr>
                        </tbody>
                    </table>

                </form>

                <table id='table2' class='standardgrid'>
                </table>
            </div>            

Open in new window


PHP code not working
PHP
/*--------------------------------------------------
fill some arrays
--------------------------------------------------*/
$saryFieldNames = array(
    "key",
    "firstname",
    "lastname",
    "sign",
    "deptdescription"
);
$saryFieldNames = one_index_array($saryFieldNames);

$temp = $_POST["filtersArray"];
trigger_error($temp);
//trigger_error($_POST["txtFirstnameSearch"]);
$saryFilters = array(
    $_POST["txtKeySearch"],
    $_POST["txtFirstnameSearch"],
    $_POST["txtLastnameSearch"],
    $_POST["txtColumnSearch"],
    $_POST["txtDepartmentSearch"]
);
$saryFilters = one_index_array($saryFilters);
trigger_error($saryFilters[2]);


    $stmt = $cn->prepare($strSQL);
    $stmt->execute();

Open in new window


PHP code working
/*--------------------------------------------------
now build the HTML
--------------------------------------------------*/
    $strSummary = "$rowcount records found";
    $strSummary .= "<br/>" . $strSQL;

    $strTableData = 
            "<thead>\n"
                . "<th class='keyColumn'>Key</th>\n"
                . "<th class='firstnameColumn'>First Name</th>\n"
                . "<th class='lastnameColumn'>Last Name</th>\n"
                . "<th class='signColumn'>Sign</th>\n"
                . "<th class='departmentColumn'>Dept Desc</th>\n"
            . "</thead>\n"
            . "<tbody>\n";

//method 1
    while($row = $stmt->fetch(PDO::FETCH_OBJ) ) {
        $strTableData .= "<tr>\n"
            . "<td class='keyColumn'>$row->schluss</td>\n"
            . "<td class='firstnameColumn'>$row->firstname</td>\n"
            . "<td class='lastnameColumn'>$row->lastname</td>\n"
            . "<td class='signColumn'>$row->sign</td>\n"
            . "<td class='departmentColumn'>$row->deptdescription</td>\n"
        . "</tr>\n";
    }  // end while

    $strTableData .=
            "</tbody>\n"
            . "<tfoot>\n"
                . "<tr>\n"
                    . "<td class='rowcount' colspan='5' align='left'>$strSummary</td>\n"
                . "</tr>\n"
            . "</tfoot>\n";

    echo $strTableData;

Open in new window

0
Comment
Question by:VieleFragen
  • 13
  • 13
  • 2
  • +1
29 Comments
 
LVL 12

Expert Comment

by:adrian_brooks
ID: 39618342
Typically, when one wants form values from an html page, you would grab the post array like this;

    $var = $_POST;

And then just take each value from the post array like this;

    $key = $var['key'];

Then just use the local variables however you see fit.

Hope that helps.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39618734
Your HTML elements need to have a name attribute so the PHP script can access the POST data:

<input id="txtKeySearch" name="txtKeySearch" type="text" class="keyFilter">

Without a name, they won't get passed through POST

Having said that, your PHP is not having the data POSTed to it. You seem to calling your script through AJAX, which by default is a GET request. To POST your data to your script, you need something like this:

$.ajax({
   url: "Get joined table cells (table only).php",
   data: $('#search1').serialize(),
   type: 'POST',
   success: function(result){
      $("#table2").html(result);
   }
});

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39618995
It's probably going to be easier to get this right if you start without the JavaScript and just make sure the data is getting from the HTML form to the PHP script.  See the online man page here that describes the basic process.
http://php.net/manual/en/tutorial.forms.php
0
 
LVL 1

Author Comment

by:VieleFragen
ID: 39619305
Adrian/Chris,

I verified that the form's values are being serialized with
alert($("#"+sFormId).serialize());

Open in new window

, but I can't see the values once they get to my PHP script.

When I check my log after running the PHP script which includes the following code, the results are blank (not errors), but the form values are not printed.
$saryFilters = $_POST;
trigger_error($_POST["txtKeySearch"]);
trigger_error($saryFilters["txtKeySearch"]);

$saryFilters = array(
    $saryFilters["txtKeySearch"],
    $saryFilters["txtFirstnameSearch"],
    $saryFilters["txtLastnameSearch"],
    $saryFilters["txtColumnSearch"],
    $saryFilters["txtDepartmentSearch"]
);
$saryFilters = one_index_array($saryFilters);
trigger_error("$saryFilters[2] = " . $saryFilters[2]);

Open in new window


Ray,

Thanks for the link.  I have gotten PHP to work before, but only passing over single values, not arrays.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39619373
OK. Can't quite figure out why all the trigger_errors and can't see why you're trying to assign the $_POST variable to another variable. Also, what's the purpose of one_index_array()

When you POST a form to a script, you never pass over single values - you always pass over an array. That array is called $_POST and the keys are the names of your form inputs.

Can you explain what the code you've just posted is supposed to do - it looks like you're just moving the POST array around into other variables, but I can't figure out why.

Your PHP script can access the $_POST array directly. To view what's being passed into your script, change it to this:

<?php
var_dump($_POST);
?>

Open in new window

0
 
LVL 1

Author Comment

by:VieleFragen
ID: 39620492
Chris,

I'm sorry, but the hole I'm digging just keeps getting deeper.  Now I'm getting the message: "The requested URL /zodiac/post was not found on this server."  I don't understand why the action word "post" is being used instead of my actual URL.

This is my updated Javascript:
    <script type="text/javascript">
        function refreshData(sFormId, sTargetId) {
            "use strict";
            alert("sFormId = " + sFormId
                + "\n" + "sTargetId = " + sTargetId);
            var asPostVariable = $("#"+sFormId).serialize();
            alert("asPostVariable = \n" + asPostVariable);
            $.ajax({
                type: "POST", 
                url: "Get joined table cells (table only).php",
                data: asPostVariable,
                success: function(result){
                    $("#"+sTargetId).html(result);
                }
            });
        }  // end function refreshData()
    </script>

Open in new window


This is the HTML code to call the function:
                <form id="search1" action="post">
                    <button id="btnFind" onclick="refreshData('search1', 'table2');">Find</button><br/>
...
                <div id="table2">
                </div>
                
                <table id='table2a' class='standardgrid'>
                </table>

Open in new window


And here is the simplified code in my gutted PHP script:
<?php
    echo($_POST["txtKeySearch"]);
?>

Open in new window


The Key search field in my form is the letter "abcd", so I am expecting to see that string directly under the input form.  It seems like the Ajax call is failing.

Thanks, Alan
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39620495
It's probably going to be easier to get this right if you start without the JavaScript and just make sure the data is getting from the HTML form to the PHP script.  See the online man page here that describes the basic process.
http://php.net/manual/en/tutorial.forms.php

Once you know that part is working, we can help you with the JavaScript.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39620514
Can't see anything in your script that would cause that, but I have to agree with Ray here. Let's strip it right back and build your application up step-by-step. Not only will it help you learn about all the various components, it will make sure each part is working properly before moving on - this is the normal way of coding, so it's worth doing.

Start off with just 2 files - an HTML page containing your form, and a PHP script that you can POST to. This is what you need to get started:

HTML File - form.php
<!DOCTYPE html>
<html lang="en">
	<head>
		<meta charset="utf-8">
		<title>Form Test</title>
		<script src="//ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
		<script type="text/javascript">
		$(document).ready(function() {
			
		});
		</script>
	</head>

	<body>

	<form id="search1" method="POST" action="process.php">
		<button id="btnFind">Find</button><br>
		<table id="table1">
		<tbody>
			<tr>
				<td><input name="txtKeySearch" type="text"></td>
				<td><input name="txtFirstnameSearch" type="text"></td>
				<td><input name="txtLastnameSearch" type="text"></td>
				<td><input name="txtColumnSearch" type="text"></td>
				<td><input name="txtDepartmentSearch" type="text"></td>
			</tr>
		</tbody>
		</table>
	</form>
	
	<div id="table2"></div>

	</body>

</html>

Open in new window

PHP File - process.php
<?php
var_dump($_POST);
?>

Open in new window

Create those 2 files, fire up the form.php, fill in the form and click the button - you should see your posted data appear on it's own page. Once you know that's working we can move on to the next step
0
 
LVL 1

Author Comment

by:VieleFragen
ID: 39620518
So far, so good.  The result is:

array(5) { ["txtKeySearch"]=> string(4) "abcd" ["txtFirstnameSearch"]=> string(0) "" ["txtLastnameSearch"]=> string(0) "" ["txtColumnSearch"]=> string(0) "" ["txtDepartmentSearch"]=> string(0) "" }
0
 
LVL 42

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 500 total points
ID: 39620529
Perfect. So now we know your form is working and posting data to the PHP script. It shows that you filled in one of the fields (txtKeySearch) and left the rest blank.

By adding some jQuery we can send the data through an AJAX request, instead of the normal POST method.  Your jQuery will look like this:

//make sure the DOM is ready before binding the event.
$(document).ready(function() {
	
	//lets handle the form submit
	$('#search1').submit(function(e){
		//stop the normal 'submit' function
		e.preventDefault();
	
		//get the URL from the form that we're wanting to submit
		var urlToSubmitTo = $(this).attr('action');
		//get the form data into a suitable format for making the AJAX request
		var formData = $(this).serialize();
		
		//Now for the ajax request
		$.ajax({
			url: urlToSubmitTo,
			data: formData,
			type: 'post'
		}).done(function(response) {
			//the AJAX request is done so lets put the response into the document
			$('#table2').html(response);
		});
	});
});

Open in new window

I've commented it heavily so it should make sense. Now load up the form.php file, fill in the form, and click on submit. If all goes well, you should see the POST data appear under your Form
0
 
LVL 1

Author Comment

by:VieleFragen
ID: 39620586
Yes, that's good, too: my text string displayed below the form in the table2 div.

array(5) { ["txtKeySearch"]=> string(4) "efgh" ["txtFirstnameSearch"]=> string(0) "" ["txtLastnameSearch"]=> string(0) "" ["txtColumnSearch"]=> string(0) "" ["txtDepartmentSearch"]=> string(0) "" }
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39620614
Excellent - we now know the HTML form is working, and that your jQuery is POSTing the form data to the script. Now we need to work on the PHP to deal with that data. Below is the PHP you'll need to query your database, and build the TABLE HTML to be sent back to your jQuery script. I've documented the code again, so have a careful read through it. A couple of Gotchas to be aware of. I've used the HEREDOC syntax to create the querystring and the table rows. For this to work, you need to make sure the end tag (EOT;) stays on it's own line, and doesn't have any spaces before it.

The query is very simple at this point, only using the txtKeySearch value from your Form. Stick with this to make sure it works - you can always add to your query once this works.

I've stripped out all the class info to keep the HTML clean. You can worry about styling it later. You'll also notice that the table row data is built within the PHP database loop, and then the HTML for the table is built later.

You'll need to put in your own database name, username, password and make sure the query matches your table. If there's anything you don't understand, just ask :)

<?php 
try{
	//connect to the database
	$host = "localhost";
	$database = "yourDB";
	$username = "yourUsername";
	$password = "yourPassword";
	
	$dbh = new PDO("mysql:host=$host;dbname=$database", $username, $password);
	$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
	
	//create the SQL String, using Named Parameters (:schluss)
	$sqlString = <<<EOT
	SELECT schluss, firstname, lastname, sign, deptdescription
	FROM yourTable
	WHERE schluss = :schluss
EOT;

	//prepare the query
	$stmt = $dbh->prepare($sqlString);

	//prepare the data for the query. The array needs to contain the
	//named parameter from the query, plus the data to pass in.
	$data = array(
		'schluss' => $_POST['txtKeySearch']
	);

	//execute the query
	$stmt->execute($data);
	
	//get the number of rows
	$numOfRows = $stmt->rowCount();
	
	//loop through the returned rows to create the HTML for the table rows
	$rowData = "";
	while($row = $stmt->fetch(PDO::FETCH_OBJ)):
		
	$rowData .= <<<EOT
	<tr>
		<td>$row->schluss</td>
		<td>$row->firstname</td>
		<td>$row->lastname</td>
		<td>$row->sign</td>
		<td>$row->deptdescription</td>
	</tr>
EOT;

	endwhile;

} catch(PDOException $e) {
	echo $e->getMessage();
	exit;
}
?>

<table>
<thead>
	<tr>
		<th>Key</th>
		<th>First Name</th>
		<th>Last Name</th>
		<th>Sign</th>
		<th>Dept Desc</th>
	</tr>
</thead>

<tbody>
<?php echo $rowData; ?>
</tbody>

<tfoot>
	<tr>
		<td colspan='5'>Records Found: <?php echo $numOfRows ?></td>
	</tr>
</tfoot>
</table>

Open in new window

0
 
LVL 1

Author Comment

by:VieleFragen
ID: 39620658
Now I've run into a snag.  Here's a section of your code, and the results after entering what should be a valid key for the first field:

	//prepare the data for the query. The array needs to contain the
	//named parameter from the query, plus the data to pass in.
	$data = array(
		'schluss' => $_POST['txtKeySearch']
	);

Open in new window


Result:
array(4) { ["txtKeySearch"]=> string(7) "DoeJohn" ["txtFirstnameSearch"]=> string(0) "" ["txtLastnameSearch"]=> string(0) "" ["txtColumnSearch"]=> string(0) "" }

Notice: Undefined index: txtKeySearch in C:\wamp\www\zodiac\form3.php on line 76
Key       First Name       Last Name       Sign
Records Found: 0

For the moment, I've removed department description from the search fields, SQL and table rows and table head because it comes from a joined table.  It looks like the connection is being made to the database, but the record isn't being returned.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39620669
OK. If you've used the code I've posted then I'm not sure why your getting an error on line 76. My entire code only has 76 lines - and the POST key is accessed on line 26! The code I've posted above should be your entire process.php script (your error indicates that it's form3.php)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:VieleFragen
ID: 39620679
Sorry, I misunderstood and pasted your code into a copy of form.php instead of a new copy of process.php.  I'm now getting a proper table with column headings, 1 row of data and a table footer with a record count of 1.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39620687
Perfect. You're well on your way. You now know that your HTML form is working. You know that your jQuery is passing the form data to your PHP script, and that your PHP script is receiving and handling the POSTed data successfully. Hopefully, you understand WHY it's all working and you can adapt it to your specific requirements.

The next step is to adapt your query to make use of all the POSTed data. You'll need to edit the Query String and also the $data array that's passed in (to match the named parameters).
0
 
LVL 1

Author Comment

by:VieleFragen
ID: 39620713
I am studying what you did differently that I did, so far.

1) You used the jQuery submit method, which I haven't run into yet.
2) You moved the urlToSubmitTo from the $.ajax call to the form's action attribute and specified the form's method attribute.
3) You used .done instead of .success in the $.ajax call.  Since this is a slightly different syntax than mine, I don't understand the subtle difference.
4) You built the entire HTML table after the PHP in the process file instead of the form file.  Because of #3, whatever wasn't included in the main PHP portion of the process file is returned as HTML, instead of echoing a string variable, which worked in my original problem HTML before adding the selection form.  This was completely unexpected.

I need to stop for tonight, but I will resume by adding the rest of the POST fields to the process form.  I want to try getting back a single row based on two search fields, in a case where only one search field would return multiple rows.

Thank you very much for all of your time so far; I can't tell you how glad I am to have your help.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39621151
Just to confirm what you just said.

1. It makes sense to attach the event to the form submit() because that's what you want to handle. You could have wired it up to the click event, but that would have been slightly more complicated.

2. Keeping the URL in the form action means that your form will submit, even if Javascript is disabled. It's good-practice to keep things where they're supposed to be - the form action belongs in the Form

3. You could have easily used the success: parameter, but I just prefer the done() method

4. When you make an AJAX call, whatever is output by the script will be sent back - it doesn't matter whether you use echo out strings or just create the HTML directly - it all gets returned (the same would be true if you used the success: parameter). Creating the table the way I did seemed like a cleaner way to code it - there's less string concatenation, which often gets messy. When you need to edit the outputted table, it's easier to do because essentially all the HTML is in one neat place.

With most things in PHP and jQuery, there are usually several different ways to achieve what you need. This is just how I'd do it :)
0
 
LVL 1

Author Comment

by:VieleFragen
ID: 39623236
There's only so much I can learn from reading examples on the Internet; it really helps talking to someone with practical experience.

I tried adding the rest of the fields to the POST array, but I get the error:
"SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"

My only code change was to process4.php (I'm numbering the steps sequentially so I can look back at the last successful attempt)
	$data = array(
		'schluss' => $_POST['txtKeySearch'],
		'firstname' => $_POST['txtFirstnameSearch']
	);

Open in new window

0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39623244
OK. The number and name of elements you add to your $data array MUST match your SQL String. The array code you've just posted will work if your query has 2 parameters named :schluss and :firstname:

$sqlString = <<<EOT
SELECT schluss, firstname, lastname, sign, deptdescription
FROM yourTable
WHERE schluss = :schluss
AND firstname = :firstname
EOT;

Open in new window

The error you're getting tells me that your query isn't expecting those 2 named parameters. It's either expecting more or less.
0
 
LVL 1

Author Comment

by:VieleFragen
ID: 39623335
AHA!  The fields in the WHERE clause must agree in position and number with the POST variable.  Got it.  I am now able to use either ANDs or ORs in the WHERE clause and get the results I expect.

Can you explain why $stmt->execute($data); needs the ($data) parameter when I have already done my matching in the SQL and have already processed $stmt = $dbh->prepare($sqlString); ?

I can finally see the light at the end of the tunnel, but I'm not quite there yet.  I still need to restore my join to get the final Department field back in play, and I need to change the SQL from WHERE schluss = :schluss back to WHERE schluss = ?, which (I've read) prevents SQL injection and is good programming form.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39623834
OK. The named parameters in the query (those bits starting with a :colon) act as temporary place holders. You then prepare() the query ready for use later on. When you execute the query, it needs to passed the information to replace those temporary placeholders. This information can be passed in 2 different ways - you can either bind the parameters to variables, or you can pass in an array of name/value pairs. We created an array called $data to hold these values (it cold just have easily been called anything else) and passed that to the query. The keys in the array must match the named parameters from the query - only in name and number, not necessarily position.

You don't need to replace :schuss with ? - both act as parameters and prevent SQL injection. The first method uses named parameters and the second uses placeholders. They both do exactly the same job - I just happen to prefer named parameters - I find it easier to read.
0
 
LVL 1

Author Comment

by:VieleFragen
ID: 39629166
I have managed to integrate your suggestions into my original code, and it works splendidly!  I understand everything we did (not yet thoroughly, but that will come with more experience).  I found a couple of mistakes that I introduced when expanding from a straight query to one with form parameters.

1) The name of the search field has to agree between the calling form and the $_POST array (one was off).

2) The PHP execute statement won't use parameters unless they're specified, i.e. $stmt->execute($astrFilterValues); vs. $stmt->execute();

Thank you very much for all of your time and patience.

The only side question I have remaining is about SQL injection.  I want create a structure that can be re-used for a number of forms, and only the tables and fields need to be changed.  The following code works, but does building the Where clause this way expose me to SQL injection?  Or do I need to manually do this with the variables approach you showed me (schluss=:schluss)?  
$dbtable = "$dbname.membersxml a
    INNER JOIN $dbname.department_ids b
    ON a.deptidlink = b.deptid";


/*--------------------------------------------------
fill some arrays
--------------------------------------------------*/
$astrFieldNames = array(
    "a.schluss",
    "a.firstname",
    "a.lastname",
    "a.sign",
    "b.deptdescription"
);
$astrFieldNames = one_index_array($astrFieldNames);

	//prepare the data for the query. The array needs to contain the
	//named parameter from the query, plus the data to pass in.
$astrFilterValues = array(
    "schluss" => $_POST["txtKeySearch"],
    "firstname" => $_POST["txtFirstnameSearch"],
    "lastname" => $_POST["txtLastnameSearch"],
    "sign" => $_POST["txtSignSearch"],
    "deptdescription" => $_POST["txtDepartmentSearch"]
);


/*--------------------------------------------------
build the SQL Select clause
--------------------------------------------------*/
$strSQL = "SELECT";
foreach ($astrFieldNames as $val)  {
    if ($strSQL != "SELECT") {
        $strSQL .= ",";
    }  // check whether any field names have already been added
    $strSQL .= " " . $val;
}  // end foreach
$strSQL .= " FROM $dbtable";


/*--------------------------------------------------
build the SQL Where clause
--------------------------------------------------*/
$strWhere = "";
$iintLoop = 0;
foreach ($astrFilterValues as $val) {
    $iintLoop ++;
    if ($val != null) {
        if($strWhere <> '') {
            $strWhere .= " AND";
        } else {
            $strWhere = " WHERE";
        }  // check for $strWhere <> ''
        $strWhere .= " " . $astrFieldNames[$iintLoop] . " = '" . $val . "'";
        //$strWhere .= " " . $astrFieldNames[$iintLoop] . " = " . "?" . "";
    }  // check for $val != null
}  // end foreach $_POST


/*--------------------------------------------------
build the SQL Order By clause
--------------------------------------------------*/
    $strOrderBy = "ORDER BY b.deptdescription, a.lastname, a.firstname";


/*--------------------------------------------------
assemble the query components
--------------------------------------------------*/
    if ($strWhere <> "") {
        $strSQL .= $strWhere;
    }
    $strSQL .= " " . $strOrderBy;
    

Open in new window

0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39629801
Firstly, I certainly wouldn't do it like that, and secondly, your code is unlikely to provide the Query that you need. You'll end up with a WHERE clause that looks like this:

WHERE a.schluss = 'schluss' AND
a.firstname = 'firstname' AND
a.lastname = 'lastname' AND
a.sign = 'sign' AND
b.deptdescription = 'deptdescription'

Clearly wrong!

You're trying to write 75 lines of code to produce a query that would normally only take 10 lines. Don't see what you're hoping to achieve here!

While what you have won't necessarily open you up for SQL injection, by making it more complicated that it needs to be, it will open you up to other problems, and if you get it wrong it could be a security risk. Keep it simple, and stick to the proper way of doing things.
0
 
LVL 1

Author Comment

by:VieleFragen
ID: 39630330
I had hoped to avoid some typing when using this mechanism for several forms and tables, but more importantly I am trying to use good programming style.  Once a form/script combination is working, I don't need to worry about how much typing I did to get the script ready.  Is this better/clearer?

/*--------------------------------------------------
fill some arrays
--------------------------------------------------*/
	//prepare the data for the query. The array needs to contain the
	//named parameter from the query, plus the data to pass in.
$astrFilterValues = array(
    "schluss" => $_POST["txtKeySearch"],
    "firstname" => $_POST["txtFirstnameSearch"],
    "lastname" => $_POST["txtLastnameSearch"],
    "sign" => $_POST["txtSignSearch"],
    "deptdescription" => $_POST["txtDepartmentSearch"]
);

/*--------------------------------------------------
build the SQL Select clause
--------------------------------------------------*/
$strSQL = "SELECT a.schluss,
        a.firstname,
        a.lastname,
        a.sign,
        b.deptdescription
    FROM $dbname.membersxml a
    INNER JOIN $dbname.department_ids b
    ON a.deptidlink = b.deptid";

/*--------------------------------------------------
build the SQL Where clause
--------------------------------------------------*/
$strWhere = "";

if ($astrFilterValues["schluss"] != null) {
    if($strWhere <> "") {
        $strWhere .= " AND ";
    } else {
        $strWhere = " WHERE ";
    }  // check for $strWhere <> ''
    $strWhere .= "schluss = '" . $astrFilterValues["schluss"] . "'";
}

if ($astrFilterValues["firstname"] != null) {
    if($strWhere <> "") {
        $strWhere .= " AND ";
    } else {
        $strWhere = " WHERE ";
    }  // check for $strWhere <> ''
    $strWhere .= "firstname = '" . $astrFilterValues["firstname"] . "'";
}

if ($astrFilterValues["lastname"] != null) {
    if($strWhere <> "") {
        $strWhere .= " AND ";
    } else {
        $strWhere = " WHERE ";
    }  // check for $strWhere <> ''
    $strWhere .= "lastname = '" . $astrFilterValues["lastname"] . "'";
}

if ($astrFilterValues["sign"] != null) {
    if($strWhere <> "") {
        $strWhere .= " AND ";
    } else {
        $strWhere = " WHERE ";
    }  // check for $strWhere <> ''
    $strWhere .= "sign = '" . $astrFilterValues["sign"] . "'";
}

if ($astrFilterValues["deptdescription"] != null) {
    if($strWhere <> "") {
        $strWhere .= " AND ";
    } else {
        $strWhere = " WHERE ";
    }  // check for $strWhere <> ''
    $strWhere .= "deptdescription = '" . $astrFilterValues["deptdescription"] . "'";
}

/*--------------------------------------------------
add the SQL Order By clause
--------------------------------------------------*/
    $strOrderBy = "ORDER BY b.deptdescription, a.lastname, a.firstname";

/*--------------------------------------------------
assemble the query components
--------------------------------------------------*/
    if ($strWhere <> "") {
        $strSQL .= $strWhere;
    }
    $strSQL .= " " . $strOrderBy;

Open in new window

0
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39630636
Nope. You're definitely leaving yourself open to SQL injection - you're building your WHERE clause with the direct values from the POST array, which is the very definition of a SQL injection vulnerability. You need to be using Named Parameters

If you want to build the WHERE clause based on only the form fields that are filled in, you could do it like this:

//create the SQL SELECT clause
$sqlString = "SELECT schluss, firstname, lastname, sign FROM yourTable";

//build the DATA array
$data = array(
	"schluss" => $_POST["txtKeySearch"],
	"firstname" => $_POST["txtFirstnameSearch"],
	"lastname" => $_POST["txtLastnameSearch"],
	"sign" => $_POST["txtSignSearch"],
	"deptdescription" => $_POST["txtDepartmentSearch"]
);

//build the WHERE clause
$whereFields = array();
foreach ($data as $fieldname => $value):
	if ($value):
		$whereFields[] = sprintf('%s = :%s', $fieldname, $fieldname);
	else:
		unset($data[$fieldname]);
	endif;
endforeach;

$whereClause = (count($whereFields)) ? " WHERE " . implode(' AND ', $whereFields) : "";
$orderBy = " ORDER BY lastname, firstname";
$sqlString .= $whereClause . $orderBy;
	
var_dump($sqlString);
var_dump($data);

Open in new window

Try running the code like this before connecting it to your database. That way you can visualize the QueryString and Data array and make sure it's correct.
0
 
LVL 1

Author Comment

by:VieleFragen
ID: 39631183
That's exactly what I was looking for!  There are 3 new PHP functions in there (I'm still a beginner with PHP), but I looked them up and I understand what they do.  But I don't think I would have come up with that solution in a month of Sundays.

I'm ready to close out this question now.  Once again, thank you for your time and patience.  Your help has been invaluable.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39631206
Nice one - that's the best way to learn - see a function that you don't understand and go and check it out on PHP.net - if you still don't understand, ask here - we're always happy to help.

Glad it's working for you, but more importantly, hope you learnt something new :)
0
 
LVL 1

Author Closing Comment

by:VieleFragen
ID: 39631284
This was the most rewarding experience I have had at Experts Exchange.  The complex solution was built step-by-step with well-commented code, and best practices were explained with patience.  Great job!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

OverviewThis article demonstrates a simple search form using AJAX. The purpose of the article is to demonstrate how to use the same code to render a page and javascript (JQuery) and AJAX to make subsequent calls to refine the results. The princip…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now