PHP FORM to QUERY MySQL

zeroge · #1 (**permalink**) 01-02-10, 07:24

Hello

I searched this forum for a similar case but couldn't find any conclusion to my question. I also searched online but hang still in the air. I have two html pages,

ONE - (search.php) containing a form that would allow me to choose from a drop-down list a column and then a text field where I try to match a value in this column by sending the SQL to my DB.

TWO - (results.php) upon the search.php sending the sql to my DB, the DB will then return ANY result and display in in a table on the results.php.

Connection is ok but any search would ALWAYS return a "NOTHING FOUND". Following are the two codes I use trying to achieve what I explained above.

search.php

Quote:

<form method="post" action="results.php" target="_blank">
<div align="center">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td bordercolor="#000000">
<p align="center">
<select name="metode" size="1">
<option value="first_name">First Name</option>
<option value="last_name">Last Name</option>
<option value="nickname">Nickname</option>
<option value="dob">DOB</option>
</select>
<input type="text" name="search" size="25"> <br>
Search database: <input type="submit" value="Go!!" name="Go"></p>
</td>
</tr>
</table>
</div>
</form>

results.php

Quote:

<table border="1" cellpadding="5" cellspacing="0" bordercolor="#000000">
<tr>
<td width="120"><b>First Name</b></td>
<td width="120"><b>Last Name</b></td>
<td width="70"><b>Nickname</b></td>
<td width="100"><b>DOB</b></td>
</tr>
<tr>
<td>
<?php
$hostname = "host"; // DBserver.
$username = "User"; // DBusername
$password = "My PW"; // DBpassword
$usertable = "my table"; // TBLname
$dbName = "my DB"; // DBname

$conn = mysql_connect("host","User","My PW") or die ('Error connecting to mysql');
$dbname = 'my DB';
mysql_select_db($dbname);
?>
<?php
//error message (not found message)begins
$XX = "No Record Found, to search again please close this window";
//query details table begins
$query = mysql_query("SELECT * FROM clients WHERE $metode LIKE '%$search%' LIMIT 0, 50");
while ($row = @mysql_fetch_array($query))
{
$variable1=$row["first_name"];
$variable2=$row["last_name"];
$variable3=$row["nickname"];
$variable4=$row["dob"];
//table layout for results

print ("<tr>");
print ("<td>$variable1</td>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("<td>$variable4</td>");
print ("</tr>");
}
//below this is the function for no record!!
if (!$variable1)
{
print ("$XX");
}
//end
?>
</table>

Thanks a lot

sco08y · #2 (**permalink**) 01-02-10, 13:06

Well, your HTML is screwed up so it might be hiding the results.

Code:

$query = mysql_query("SELECT * FROM clients WHERE $metode LIKE '%$search%' LIMIT 0, 50");

Try changing that to this:

Code:

$q_str = "SELECT * FROM clients WHERE $metode LIKE '%$search%' LIMIT 0, 50";
print ($q_str);
$query = mysql_query($q_str);

Then you can see what query is actually being sent to MySQL.

I don't see where you defined $metode, so that's probably causing a problem. I'm no PHP guy, but I think you'd want to look it up with $_POST["metode"].

Further, you'll need to take steps to prevent SQL injection. $metode is easy, verify that it's equal to one of the proper values. $search needs to have double quotes and search characters escaped.

zeroge · #3 (**permalink**) 01-02-10, 14:12

Hi sco08y

I tried the SQL already (in phpMyAdmin) and the query seems to be OK, it returns results. I also created a PHP code in there and MySQL gives me the exact same query string output.

I searched online for some explanation and came across a neat script, it works fine the only thing I changed is the SQL I send. Instead of just returning the value I search for, let's say "first name" the query would also return other values like email, DOB, etc. I tried the SQL and it works fine I just cant get the output into an array so it shows on the search page?

Would you mind if I post the script here? It may as well be interesting for the others. It's short and very clean.

CHEERS

zeroge · #4 (**permalink**) 01-02-10, 14:16

OH, sorry - I forgot

it's defined on the "search.php ... <select name="metode" size="1">

zeroge · #5 (**permalink**) 01-02-10, 16:34

No need for further replies to this post ... I solved the problem meanwhile.

Thanks a lot

Philip Montero · #6 (**permalink**) 01-23-10, 11:21

mysql php form is like day month year, the three fields are stored in single dob field.
In the dob field cannot be stored mysql database.
Further, my mysql database dob field under the datatype is 'date' and mentioned not null.
But, i cannot stored dob field. when arise the problem the error message is "Could not execute mysql query!Incorrect date value: '1947-August-15' for column 'dob' at row 1"
What i do? Please give me a solution.

______________
Download Windows 7

Internet Services

Web Development

Digital Marketing

Consumer Tech