[RESOLVED] Passing form value to PHP/mySQL WHERE clause

[InfoBot]

After spending a few hours trying every syntax in my books and a few from on-line, I still can't get a form to pass a field value to the WHERE clause in a mySQL query that is part of a PHP script.

First, to debug everything else in the script, I hardcode a known value (DOE, JOHN) in the PHP/mySQL WHERE clause.
WHERE a.name = "DOE, JOHN"

The submit button uses the form action to call the PHP script and returns the values I want (DOE, JOHN, 2001, DOE, JOHN, 2002, etc.) formated with "while ($line = mysql_fetch_array($result, MYSQL_ASSOC))" etc.

With the problem narrowed down to passing the value, I enter DOE, JOHN in the form but I get either an empty result or a parsing error, depending on what I tried. But nothing I have tried as a value definition in the form has worked in any combination with declarations in the php script to put the form value in the WHERE clause and return results:

value="name"

or

value=<?php echo $_POST['name']; ?>

and in the where clause:

$name = 'name';
where a.username="$name"

or

$name = $_POST['name'];
where a.username="$name"

or

$name = $_POST['name'];
where a.username="{$name}"

or

$name = "name";
where a.username="{$name}"

or

where a.username={$_POST['name']}

or any of umpteen dozen other variations from the book or from wild guesses.

I also tried initializing the value, but to no avail:

$name = $_POST['name'];

This can't be as hard as I'm making it. I have run other scripts that update databases from form fields, and tried syntax from those scripts to no avail. Anybody have any advice?

[crazychris]

Check your form method is POST, not GET or blank.

Also, check your variables using $_REQUEST which contains GET and POST values

print "<pre>";
print_r($_REQUEST);
print "</pre>";

[InfoBot]

Very good. print_r($_REQUEST['$NAME']); prints the text from the form field, verifying that I have created a variable. Now if I can only figure out the exact syntax to use at

Where a.name = " ((WHAT GOES HERE???)) "

[kitchin]

Can you run a simplified version and post the source code here?

I usually use...

WHERE $field='$value'

[InfoBot]

I've been through dozens of "Name" "Value" and "WHERE" combinations, but basically, it's this, which works if I replace WHERE t.name = "$OWNNAME"' with WHERE t.name = "DOE, JOHN"';
;
:

<?php 

if (isset($_POST['submit'])) {

{

require_once ('db_connect.php');

$query = 'SELECT t.this, t.name, y.that, y.data
FROM table1 as t
JOIN table2 as y on t.id=y.tid

WHERE t.name = "$OWNNAME" ';

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";

mysql_free_result($result);
}}

?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

<fieldset ><legend>Search data:</legend>
	<table align="center" cellpadding="15"><tr><td width=300>
	<p ><b>Owner's name:</b><input type="text" name="$OWNNAME" size="25" maxlength="35" value="<?php echo $_REQUEST['$OWNNAME'] ?>" /></p> 

</td></tr></table>
</fieldset>

<div align="center"><input type="submit"
name="submit" value="Search"> </div></form>

<?php

print_r($_REQUEST['$OWNNAME']); 

?>

[niroshan]

Hi,
Its really help us that if you could post us the full query you have used. But try this it should work.

where a.username=".$_POST['name'];

Regards,
Niroshan

[InfoBot]

niroshan wrote:

where a.username=".$_POST['name'];

No, that didn't work. Nor did versions of WHERE a.username=".$_POST['name']. " ';

[niroshan]

can you plz post the table structures so i can run this in my local machine and get back to you with the answers..

Regards,
Niroshan

[InfoBot]

This should import:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL auto_increment,
  `THIS` int(9) unsigned default NULL,
  `NAME` varchar(30) default NULL,
  PRIMARY KEY  (`id`),
  KEY `this` (`this`),
  KEY `name` (`name`)

INSERT INTO `table1` VALUES (1, 123456, 'DOE, JOHN');

CREATE TABLE `table2` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `tid` int(10) unsigned default NULL,
  `data` int(10) unsigned default NULL,
  `year` year(4) default NULL,
  PRIMARY KEY  (`id`),
  KEY `tid` (`tid`),
  KEY `data` (`data`),
  KEY `year` (`year`)

INSERT INTO `table2` VALUES (234534, 1, 10000252, '1996');
INSERT INTO `table2` VALUES (768678, 1, 10000253, '1997');
INSERT INTO `table2` VALUES (678868, 1, 10000254, '1998');
INSERT INTO `table2` VALUES (345543, 1, 10000255, '1999');
INSERT INTO `table2` VALUES (234564, 1, 10000256, '2000');
INSERT INTO `table2` VALUES (753160, 1, 10000257, '2001');
INSERT INTO `table2` VALUES (345454, 1, 10000258, '2002');
INSERT INTO `table2` VALUES (345455, 1, 10000259, '2003');
INSERT INTO `table2` VALUES (345876, 1, 10000260, '2004');
INSERT INTO `table2` VALUES (456625, 1, 10000261, '2005');

(I changed one id number in table2 so it wouldn't duplicate. Probably doesn't matter. They're all made up data.)

[niroshan]

try this,

if(isset($_POST['submit']))
{
$sql="SELECT t.THIS, t.NAME, y.data FROM table1 AS t, table2 AS y WHERE t.id = y.tid AND t.name = '".$_POST['name']."'";
$row=mysql_query($sql) or die("error in the query");


while($result=mysql_fetch_array($row))
{
	print $result['NAME']."<br/>";
}
}
?>


<form name="aa" method="post" action="builder.php">
<select name="name">
<?php
	$sql="SELECT NAME FROM table1";
	$row=mysql_query($sql) or die("error in the query");
	while($result=mysql_fetch_array($row))
	{
		print "<option value=\"".$result['NAME']."\">".$result['NAME']."</option>";
	}					


?>
</select>
<input type="submit" name="submit" value="submit">
</form>

[InfoBot]

Sorry, I'm not sure what that is supposed to do. There is no text field in the form you suggested, so I don't know how it is supposed to pass the text as a variable.

I am creating a form with a text field. Submit is supposed to pass the contents of the text field to WHERE t.name = "(TEXT FIELD CONTENTS)"

If you are suggesting a pull-down select, that won't work -- this data set has nearly 100,000 rows.

[WinterDragon]

hrm

$search = trim($_POST["name"]);

$query = "SELECT t.this, t.name, y.that, y.data
FROM table1 as t
JOIN table2 as y on t.id=y.tid

WHERE t.name = '$search'";

try this?

[InfoBot]

HOWEVER, I did benefit from your efforts. I continued using the script structure as I had styled it, but I paid closer attention to your syntax in the SELECT statement, specifically, placement of single-quotes and double-quotes in relation to the WHERE variable.

In the version I posted above, I had started the query line as:

$query = [COLOR=DarkRed]'[/COLOR][COLOR=DarkGreen]SELECT.... [/COLOR] 
[I]and ended as [/I] 
WHERE t.name = [COLOR=DarkRed][B]'[/B][/COLOR][COLOR=DarkOrange][B]"[/B][/COLOR][COLOR=Navy].$_POST['OWNNAME']. [/COLOR][COLOR=DarkOrange][B]"[/B][/COLOR] [COLOR=DarkRed][B]'[/B][/COLOR] [COLOR=DarkOrange][B]"[/B][/COLOR];

Following your application of double-quotes first, I used:

$query = [COLOR=DarkRed]"[/COLOR][COLOR=DarkGreen]SELECT.... [/COLOR] 
[I]which ended as [/I] 
WHERE t.name = [COLOR=DarkRed][B]"[/B][/COLOR] [COLOR=DarkOrange][B]'[/B][/COLOR][COLOR=Navy].$_POST['OWNNAME'].[/COLOR][COLOR=DarkOrange][B]'[/B][/COLOR] [COLOR=DarkRed][B]"[/B][/COLOR] [COLOR=DarkOrange][B]'[/B][/COLOR];

That worked. Thanks. I see some other differences in syntax in the way you approached it too, so reading your code also helped me learn some different approaches.

[niroshan]

Ur welcome.. Thats what this community for. Good luck 🙂

Regards,
Niroshan

[InfoBot]

Winter, your code worked too. The real problem was the use of parenthesis, but the trim function is useful to clean up form contents, which I better coded as like "value%"

[WinterDragon]

lol 🙂 Glad it worked.