trying to search a mysql db using php code searching html form

diamondlifestyle · October 7, 2010

I'm trying to do something that I thought was very simple about 2 weeks ago :-(

I want to put a form on my site and link it to a database so when a user types a surname into the form they can search the db and the page will only display the surnames that match the search criteria. I got the db set up using phpmyadmin in about 2 minutes flat, but keep getting error messages when I write the php. I'm currently working with the below script, which keeps giving me the error 'unexpected T_string' on line 176

I've searched every forum and help site I can find, and the mysql and php manuals are just mind-boggling. I'm sure I'm making some really amateur mistake, but I'd really appreciate help with this!

thanks all!

<html>

<head>

<title>SEARCH RECORDS</title>

</head>

<body>

</FORM>

</body>

</html>

<?

$user_name = "*****";

$password = "*****";

$database = "*****";

$server = "localhost";

$db_handle = mysql_connect($server, $user_name, $password);

$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {

$SQL = "SELECT * FROM *****" WHERE surname=$_POST['surname'];

$result = mysql_query($SQL);

while ($db_field = mysql_fetch_assoc($result)) {

print $db_field['Grave'] . "<BR>";

print $db_field['Surname'] . "<BR>";

print $db_field['Forenames'] . "<BR>";

print $db_field['Death_Date'] . "<BR>";

print $db_field['Birth_Year'] . "<BR>";

}

mysql_close($db_handle);

}

else {

print "Database NOT Found ";

mysql_close($db_handle);

}

?>

phpeter · October 7, 2010

Not sure if this'll fix it completely, but I think

$SQL = "SELECT * FROM *****" WHERE surname=$_POST['surname'];

The quotation mark after ***** should be moved to right before the semicolon.

diamondlifestyle · October 7, 2010

omg that returned:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING on line 176

I don't think I was as close to nailing this as I originally thought! (and it really doesn't like line 176!)

Pikachu2000 · October 7, 2010

The $_POST array element should be escaped, enclosed in single quotes, and concatenated to the string.

$SQL = "SELECT * FROM ***** WHERE surname = '" . mysql_real_escape_string($_POST['surname']) . "'";

diamondlifestyle · October 7, 2010

thank you so much for your help so far, that looks a lot better now, I've got my page up with the search box in it, but when I type in and press submit I get a HTTP 404 error (page not found/broken). Does this mean I've logged in wrong or something?

jcbones · October 7, 2010

Replace your opening form element with this:

<form name ="Search" method ="POST" action = "">

You were sending it to "test3" but that is an invalid file as there is no file extension on it. Setting the action to an empty string, returns the form to itself.

PS. Your query will only return a surname if it is typed exactly as it appears in the database. If you want it to return surnames that start with the characters typed, use:

$SQL = "SELECT * FROM ***** WHERE surname LIKE '" . mysql_real_escape_string($_POST['surname']) . "%'";

diamondlifestyle · October 7, 2010

omg I'm sorry this is so frustrating, now I've got the page, with the form, I type in the name and click 'search' and it refreshes the same page without displaying the results of the search. you're all being very helpful and I really appreciate it...fingers crossed one more tweak might fix it??

BlueSkyIS · October 7, 2010

please post your latest code.

diamondlifestyle · October 8, 2010

the latest code is:

<html>

<head>

<title>SEARCH RECORDS</title>

</head>

<body>

</FORM>

</body>

</html>

<?

$user_name = "*****";

$password = "*****";

$database = "*****";

$server = "localhost";

$db_handle = mysql_connect($server, $user_name, $password);

$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {

$SQL = "SELECT * FROM ***** WHERE surname LIKE '" . mysql_real_escape_string($_POST['surname']) . "%'";

$result = mysql_query($SQL);

while ($db_field = mysql_fetch_assoc($result)) {

print $db_field['Grave'] . "<BR>";

print $db_field['Surname'] . "<BR>";

print $db_field['Forenames'] . "<BR>";

print $db_field['Death_Date'] . "<BR>";

print $db_field['Birth_Year'] . "<BR>";

}

mysql_close($db_handle);

}

else {

print "Database NOT Found ";

mysql_close($db_handle);

}

?>

thank you all for your help with this!!

Pikachu2000 · October 8, 2010

Edit the query execution code to report any errors, and see what happens.

$result = mysql_query($SQL) or die ('<br>Query string: ' . $SQL . '<br>Produced error: ' . mysql_error() . '<br>');

diamondlifestyle · October 8, 2010

right, that's given me:

Parse error: syntax error, unexpected T_WHILE on line177

which has thrown me completely; I thought the 'while' clause was necessary to tabulate the results?

I was wondering if it's because the html is above the php, when the 'submit' button is pressed and it sends the data back to the same page, is it hitting the html before the php and just resetting the form? or will that not make any difference?

thanks again for everyone's help!

Pikachu2000 · October 8, 2010

I just noticed something. You have the value= attribute of the surname field specified as "surname". Remove the value="surname" from the <input tag completely.

For the parse error, did you paste the or die() statement in, or type it? There's probably a missed quote, or something like that.

Paste your current code in here, please.

diamondlifestyle · October 8, 2010

Hi, I pasted the script in

Pikachu2000 · October 8, 2010

Post your code as it currently stands . . .

diamondlifestyle · October 8, 2010

the code currently stands at:

<html>

<head>

<title>SEARCH RECORDS</title>

</head>

<body>

</FORM>

</body>

</html>

<?

$user_name = "*****";

$password = "*****";

$database = "*****";

$server = "localhost";

$db_handle = mysql_connect($server, $user_name, $password);

$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {

$SQL = "SELECT * FROM ***** WHERE surname = '" . mysql_real_escape_string($_POST['surname']) . "$'";

$result = mysql_query($SQL) or die ('<br>Query string: ' . $SQL . '<br>Produced error: ' . mysql_error() . '<br>');

while ($db_field = mysql_fetch_assoc($result)) {

print $db_field['Grave'] . "<BR>";

print $db_field['Surname'] . "<BR>";

print $db_field['Forenames'] . "<BR>";

print $db_field['Death_Date'] . "<BR>";

print $db_field['Birth_Year'] . "<BR>";

}

mysql_close($db_handle);

}

else {

print "Database NOT Found ";

mysql_close($db_handle);

}

?>

which is producing the error 'unexpected T_while on line 190' (however before I added the 'die' phrase it looked right except when the 'submit' button was pressed it refreshed the form, rather than showing the results of the search)

diamondlifestyle · October 8, 2010

ooh just spotted an error on the die phrase (amended in the script above so that I don't have to repost the whole thing again!) now I've got the page with the search box, but it's still refreshing the screen rather than showing results when I press the submit button.

Pikachu2000 · October 8, 2010

Side note: change your <? short open tag to full syntax <?php. Not the cause of this issue, but not good form nonetheless.

I believe the $ at the end your query string should be changed to a percent sign for the wildcard character.

EDIT: Didn't see your post saying you found a typo . . .

Since it indicates the problem is on line 190, and there aren't nearly that many lines of code, there has to be a missing brace or semicolon or something in the preceding script. The code above, on its own, has no parse errors.

diamondlifestyle · October 8, 2010

it's not displaying any parse errors now, I've got the page looking exactly as I want it to, except when I submit a query in the form it refreshes the page rather than loads the results of the search. I've tried swapping the code over so the php script is on top and the html table is on the bottom (just in case!) but it hasn't altered anything :-(

Pikachu2000 · October 8, 2010

That form submits to itself, right? I don't think the query is returning any results from the database. Add an echo to send the query string to the screen, then paste the query into phpMyAdmin and see if any results are returned.

After the result = line, add:

echo $SQL;

chmpdog · October 8, 2010

Add an echo to send the query string to the screen, then paste the query into phpMyAdmin and see if any results are returned.

This could be the most helpful piece of knowledge when working w/ databases

diamondlifestyle · October 8, 2010

I've added the echo, but it's repeating the same error, except now above the table are the words

SELECT * FROM ***** WHERE surname='%'

Is this possibly an error with the LIKE function? when I write in the script

$SQL = "SELECT * FROM ***** WHERE surname LIKE '" . mysql_real_escape_string($_POST['surname']) . "%'";

I get an parse error message about using LIKE with % (I'm using version 5.0.91) but when I omit the like and just use

$SQL = "SELECT * FROM ***** WHERE surname '" . mysql_real_escape_string($_POST['surname']) . "%'";

I get the page showing properly but it reloads on itself when I submit a query in the form.

diamondlifestyle · October 8, 2010

sorry, didn't get what you were saying then, I've tested the string in phpmyadmin using the example 'smith' (as I have a couple in the DB), that's coming back as a good script but no results. When I add LIKE to the equation I get a compatability error using LIKE and %, so I'm assuming this is the problem?

Pikachu2000 · October 8, 2010

Two things: The wildcard % operator can't be used with =

and for whatever reason, $_POST['surname'] doesn't appear to have a value.

Let me make a change or two, and add some conditionals to the script. Give me a few minutes.

Pikachu2000 · October 8, 2010

Try this. It has some additional output for debugging so we can get an idea of what's going on.

<html>
<head>
<title>SEARCH RECORDS</title>
</head>
<body>
<?php
if( isset($_POST['Search']) ) {
echo '////////////////// DEBUGGING CODE ////////////////////////////////////////////';
echo '<pre>';
print_r($_POST);
echo '</pre>';
echo '/////////////////////// END DEBUGGING /////////////////////////////////////////';
}
?>
<FORM NAME ="Search" METHOD ="POST" ACTION = "test2.php">
<INPUT TYPE = "TEXT" NAME = "surname">
<INPUT TYPE = "Submit" Name = "Search" VALUE = "Search">
</FORM>

<?php
if( isset($_POST['Search']) ) {
$user_name = "*****";
$password = "*****";
$database = "*****";
$server = "localhost";
$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);
if ($db_found) {
	$SQL = "SELECT * FROM ***** WHERE surname LIKE '" . mysql_real_escape_string($_POST['surname']) . "%'";
	$result = mysql_query($SQL) or die ('<br>Query string: ' . $SQL . '<br>Produced error: ' . mysql_error() . '<br>');
                echo $SQL; // debugging output
	while ($db_field = mysql_fetch_assoc($result)) {
		print $db_field['Grave'] . "<BR>";
		print $db_field['Surname'] . "<BR>";
		print $db_field['Forenames'] . "<BR>";
		print $db_field['Death_Date'] . "<BR>";
		print $db_field['Birth_Year'] . "<BR>";
	}
	mysql_close($db_handle);
} else {
	print "Database NOT Found ";
	mysql_close($db_handle);
}
} else {
echo '<br>(Form not yet submitted.)<br>';  // debugging output
}
?>
</body>
</html>

diamondlifestyle · October 8, 2010

Thank you for all your help, I've put it in and it's showing the text

(Form not submitted.)

underneath the form, when I enter a name and hit submit it's still refreshing the page without displaying results.

Sign In

trying to search a mysql db using php code searching html form

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information