Page 1 of 1

Php Mysql Search Query help

Posted: Thu Jul 21, 2005 1:32 pm
by niceguy20
Form to Submit Data

Code: Select all

<html>
<style type=&quote;text/css&quote;>
<!--
h1 {
background-color:orange;
color:#0033CC;
font-size:30px;
font-family: Verdana, Arial, Helvetica, sans-serif;
text-align:center;
} 

.small_txt,
{
font-weight: bold;
font-size:12px;
font-family: Verdana, Arial, Helvetica, sans-serif;


}
body {
background-image: url(../../../../Documents%20and%20Settings/Owner/My%20Documents/My%20Pictures/images.jpg);
</style>
<body>
<h1> Oilers Search Page</h1>
<form action=&quote;oilers-results.php&quote; method=&quote;POST&quote;>
<table border=&quote;2&quote; cellpadding=&quote;0&quote; cellspacing=&quote;0&quote; align=&quote;center&quote; bordercolor=&quote;#FFA500&quote;>

<tr><td width=&quote;125&quote;><font class=small_txt><B>Enter your First Name:</B></font></td><td><input type=&quote;text&quote; class=small_txt name=&quote;first&quote; /></td></tr>
<tr><td width=&quote;125&quote;><font class=small_txt><B>Enter your Last Name:</B></font></td><td> <input type=&quote;text&quote; class=small_txt name=&quote;last&quote;/></td></tr>

<tr><td width=&quote;125&quote;><font class=small_txt><B>Enter Jersey No#: </B></font></td><td width=&quote;57&quote;><input type=&quote;text&quote; maxlength=&quote;2&quote; name=&quote;number&quote; size=&quote;2&quote; class=small_txt /></td></tr>
<tr><td width=&quote;125&quote;><font class=small_txt><B>Enter Assists: </B></font></td><td><input type=&quote;text&quote; maxlength=&quote;2&quote; name=&quote;assists&quote; size=&quote;2&quote; class=small_txt /></td></tr>
<tr><td width=&quote;125&quote;><font class=small_txt><B>Enter Goals: </B></font></td><td><input type=&quote;text&quote; maxlength=&quote;2&quote; name=&quote;goals&quote; size=&quote;2&quote; class=small_txt /></td></tr>
<tr><td width=&quote;125&quote;><font class=small_txt><B>Enter Points: </B></font></td><td><input type=&quote;text&quote; maxlength=&quote;2&quote; name=&quote;points&quote; size=&quote;2&quote; class=small_txt /></td></tr>

<tr><td width=&quote;125&quote;text&quote; maxlength=&quote;2&quote; name=&quote;number&quote; size=&quote;2&quote; class=small_txt /></td></tr>
</table>

<P> <center>
<input type=submit value=&quote;Submit Info&quote; class=small_txt> </center>
</form>
</body>
</html>

Results Page
--------------

Code: Select all

?php
$first=$_REQUEST['first'];
$last=$_REQUEST['last'];
$number=$_REQUEST['number'];
$goals=$_REQUEST['goals'];
$assists=$_REQUEST['assists'];
$points="";


$db=mysql_connect("localhost","root") or die ("Cant connect");
mysql_select_db("oilers",$db);
$results = mysql_query("SELECT * FROM players WHERE first LIKE '%$first%' AND last LIKE '%$last%' and goals=".$goals." " ,$db ) or die (mysqlerror());

while ($row = mysql_fetch_array($results)) 
{ 
$variable1=$row["first"];
$variable2=$row["last"]; 
$variable3=$row["goals"]; 
$variable4=$row["points"];
//table layout for results 

print ("First:$variable1 <BR>");
print ("Last:$variable2 <BR>"); 
print ("Goals:$variable3 <BR>"); 
print ("Points:$variable4 <BR><BR><BR>"); 


}


?>
Basically, what I am trying to do is Search by firstname,lastname,points of a hockey player.

My issue is my query statement, I know there is something wrong with it.

Code: Select all

$results = mysql_query("SELECT * FROM players WHERE first LIKE '%$first%' AND last LIKE '%$last%' and goals=".$goals." " ,$db ) or die (mysqlerror());
What I am trying to do is trying to find all the players by goals,lastname,firstname.

Right now the errors are when I included the option of searching via goals which is an int, it wont work when i try to search it with another textbox like firstname.


Currently here is what is being outputtted.


Firstname : Wayne
Won't show naything


If I try putting points,
It will output the points


What I am trying to do is have it so a person can search any textbox, ie via their firstname,lastname or how many points they have scored.


Thanks

JCART | Please use

Code: Select all

tags when posting php code. Review [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Thu Jul 21, 2005 3:44 pm
by ol4pr0

Code: Select all

$results = mysql_query("SELECT * FROM players WHERE first LIKE '%".$first."%' AND last LIKE '%".$last."%' and goals='".$goals."' " ,$db ) or die (mysqlerror());

Posted: Thu Jul 21, 2005 8:06 pm
by timvw

Code: Select all

$columns = array("first" => "string", "last" => "string", "goals" => "integer");

$where = "";
foreach($columns as $name => $type)
{
  if (!empty($_POST[$name]))
  {
    switch ($type)
    {
      case "string":
        $where .= "`$name`='" . mysql_real_escape_string($_POST["name"]) . "' AND ";
        break;
      case "integer":
        $where .= "`$name`=" . mysql_real_escape_string($_POST["name"]) . "' AND ");
        break;
      default:
        trigger_error('Unknown type', E_USER_ERROR);
    }
  }  
}

if ($where != "")
{
  rtrim($where, " AND ";
}

if ($where != "")
{
  $sql .= "WHERE " . $where;
}

Didn't work

Posted: Tue Jul 26, 2005 7:45 pm
by niceguy20
Anyone else able to help me out?

Bump.

Posted: Tue Jul 26, 2005 8:58 pm
by Burrito
tim's solution looked fine to me:

try this old-school hack-job way:

Code: Select all

$where = "";

if($_POST['first'] != "")
   $where .= "first = '".$_POST['first']."'";
if($_POST['last'] != "")
   $where .= ($where != "" ? " and" : "")." last = '".$_POST['last']."'";
if($_POST['goals'] != "")
   $where .= ($where != "" ? " and"  : "")." goals = ".$_POST['goals'];

if($where != "")
   $where = "where ".$where;

mysql_query("select * from players $where");