multiple queries

**garrensilverwing** · 05-10-2009 08:44 PM

there is probably an easier way to do this but what I want to do is, when people search for multiple things it to display results that match everything that they put in this is the code I have so far, there is a lack of tutorials online so everything is just me trying to figure out for the whole day please if you know an easier way or if you can help me with this code it will be greatly appreciated. I did my best to keep the brackets lined up so please dont yell at me if something is misaligned, its all coded by hand and I don't have much experience...

here are the error messages I get when I try a search:

Warning: Wrong parameter count for array_count_values() in /home/frostbit/public_html/games/gameshome/search/search1.php on line 120

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/frostbit/public_html/games/gameshome/search/search1.php on line 117

Code:

<?php
require ('../../../../dbgames_connect.php');
$classmin[G]="600";
$classmin[F]="800";
$classmin[E]="1000";
$classmin[D]="1200";
$classmin[C]="1400";
$classmin[B]="1600";
$classmin[A]="1800";
$classmin[X]="2000";
$classmin[M]="2200";
$classmin[Z]="600";
$classmax[G]="799";
$classmax[F]="999";
$classmax[E]="1199";
$classmax[D]="1399";
$classmax[C]="1599";
$classmax[B]="1799";
$classmax[A]="1999";
$classmax[X]="2199";
$classmax[M]="3000";
$classmax[Z]="3000";
if (!$_POST['search'])
    {
?>
<html>
<form method="post" style="text-align: center;" action="search1.php">
    Search Games, for help and tips <a href="help.htm" target="_blank">click here</a>.<br>
    White:<br>
    First Name:
    <input name="wFirst" type="text" tabindex="1">&nbsp; Last Name:<input name="wLast" type="text" tabindex="2"> <br>
    Rating:
    <input name="wRating" type="text" tabindex="3" style="height: 22px" size="4">&nbsp; 
    OR:
    <select name="wClass" tabindex="4" style="height: 22px">
    <option value="Z" selected="">Select Class</option>
    <option>G</option>
    <option>F</option>
    <option>E</option>
    <option>D</option>
    <option>C</option>
    <option>B</option>
    <option>A</option>
    <option value="X">Expert</option>
    <option value="M">Master+</option>
    </select><br>
    <br>
    Black:<br>
    First Name:
    <input name="bFirst" type="text" tabindex="5">&nbsp; Last Name:
    <input name="bLast" type="text" tabindex="6">
        <br>
    Rating:
    <input name="bRating" type="text" tabindex="7" size="4" style="width: 32px">&nbsp; OR:&nbsp;
    <select name="bClass" tabindex="8">
    <option value="Z" selected="">Select Class</option>
    <option>G</option>
    <option>F</option>
    <option>E</option>
    <option>D</option>
    <option>C</option>
    <option>B</option>
    <option>A</option>
    <option value="X">Expert</option>
    <option value="M">Master+</option>
    </select><br>
    <br>
    Game Info:<br>
    Eco:
    <input name="eco" type="text" tabindex="9" size="3"> (<a target="_blank" href="ecocodes.htm">help</a>)<br>
    Year:
    <input name="year" type="text" tabindex="10" size="4">
    <select name="result" tabindex="11">
    <option>Select Result</option>
    <option>Win (not a draw)</option>
    <option>1-0</option>
    <option>0-1</option>
    <option>1/2-1/2</option>
    </select>
<br>
    <input name="search" type="submit" value="Search" tabindex="12"></form>
</html>
<?php
    }
else {
    $wValue=$_POST['wClass'];
    $bValue=$_POST['bClass'];
    $wRatingmin=$classmin[$wValue];
    $wRatingmax=$classmax[$wValue];
    $bRatingmin=$classmin[$bValue];
    $bRatingmax=$classmax[$bValue];
    if ($_POST['wFirst'])
        {$arr[wFirst]=strtolower($_POST['wFirst']);}
    if ($_POST['wLast'])
        {$arr[wLast]=strtolower($_POST['wLast']);}
    if ($_POST['bFirst'])
        {$arr[bFirst]=strtolower($_POST['bFirst']);}
    if ($_POST['bLast'])
        {$arr[bLast]=strtolower($_POST['bLast']);}
    if ($_POST['eco'])
        {$arr[eco]=$_POST['eco'];}
    if ($_POST['year'])
        {$arr[year]=$_POST['year'];}
    if ($_POST['wRating'])
        {$arr[wRating]=$_POST['wRating'];}
    if ($_POST['bRating'])
        {$arr[bRating]=$_POST['bRating'];}
    $counter=1;
    foreach($arr as $key => $value)
        {
        $query = mysql_query("SELECT * FROM games
            WHERE $key LIKE '$value'") or die(mysql_error());
            $queries[$counter]=$query;
            $counter=$counter+1;
        }
    $counter=1;
    while($row = mysql_fetch_array($queries[$counter]))
        {
        $searches=count($arr);
        $valuecount=array_count_values($queries,$queries[$counter]);
        if ($valuecount==$searches)
            {
            echo "$counter. <a href=\"".$row['link']."\" target=\"RightSide\">".$row['wLast']." (".$row['wRating'].") - ".$row['bLast']." (".$row['bRating'].")</a><br />";
            }
        $counter=$counter+1;
        }
    if (count($queries)=="0")
        {
        echo "Sorry, your search found no results.";
        }
    }
?>

thanks in advance :D

**garrettroyce** · 05-10-2009 09:53 PM

You need to enclose the indexes of arrays with quotes, unless you're using constants: $array["a"] not $array[a]

You need to connect to mysql before sending a query:

mysql_connect('localhost', 'your_user_name', 'your_password');

**misson** · 05-11-2009 12:41 AM

In addition to what garretroyce points out, there are a few other things that need attention.

Originally Posted by garrensilverwing

there is probably an easier way to do this

You can turn those long sequences of similar expressions into foreach loops. For example, $classmin and $classmax can be initialize by:

PHP Code:


$min=600;
foreach (str_split('GFEDCBAXM') as $class) {
    $classmin[$class] = $min;
    $classmax[$class] = $min+199;
    $min+=200;
}
$classmin['Z']="600";
$classmax['Z']="3000";

Less repetitive typing, which means less chance of a typo.

Originally Posted by garrensilverwing

but what I want to do is, when people search for multiple things it to display results that match everything that they put in this is the code I have so far,

So you want to allow someone to find all games where (eg) white was D rated, black was C rated and the result was a draw? The SELECT statement for this search would be:

Code:

SELECT * FROM games WHERE wRating='D' AND bRating='C' AND result='1/2-1/2'

Is that the sort of thing you want to generate?

Originally Posted by garrensilverwing

here are the error messages I get when I try a search:

Warning: Wrong parameter count for array_count_values() in /home/frostbit/public_html/games/gameshome/search/search1.php on line 120
[...]

PHP Code:


   while($row = mysql_fetch_array($queries[$counter]))
        {
        $searches=count($arr);
        $valuecount=array_count_values($queries,$queries[$counter]);
        if ($valuecount==$searches)
            {
            echo "$counter. <a href=\"".$row['link']."\" target=\"RightSide\">".$row['wLast']." (".$row['wRating'].") - ".$row['bLast']." (".$row['bRating'].")</a><br />";
            }
        $counter=$counter+1;
        }

You're passing too many arguments to array_count_values(). Read the documentation to make sure that the function does what you think it does.

What is this loop supposed to do? It's rather a mess. For instance, $searches=count($arr); does the same thing every time through the loop; it's what's called "invariant".

Also, you never output a "<body>" element (or a DOCTYPE) and you don't output an "<html>" element if $_POST['search'] is false.

Most importantly, you don't sanitize the data you pass to MySQL, opening yourself to an SQL injection. Don't forget about mysql_real_escape_string and the filter functions. You'll want to pass all of the $_POST values through something like:

PHP Code:


function sanitize($value) {
    if (get_magic_quotes_gpc()) {  
        $string = stripslashes($string);  
    } 
    return mysql_real_escape_string($string); 
}

**garrensilverwing** · 05-11-2009 01:00 PM

Code:

<?php
require ('../../../../dbgames_connect.php');
$min=600;
foreach (str_split('GFEDCBAXM') as $class) {
    $classmin['$class'] = $min;
    $classmax['$class'] = $min+199;
    $min+=200;
}
$classmin['Z']="600";
$classmax['Z']="3000"; 
if (!$_POST['search'])
	{
?>
<html>
<form method="post" style="text-align: center;" action="search.php">
	Search Games, for help and tips <a href="help.htm" target="_blank">click here</a>.<br>
	White:<br>
	First Name:
	<input name="wFirst" type="text" tabindex="1">&nbsp; Last Name:<input name="wLast" type="text" tabindex="2"> <br>
	Rating:
	<input name="wRating" type="text" tabindex="3" style="height: 22px" size="4">&nbsp; 
	OR:
	<select name="wClass" tabindex="4" style="height: 22px">
	<option value="Z" selected="">Select Class</option>
	<option>G</option>
	<option>F</option>
	<option>E</option>
	<option>D</option>
	<option>C</option>
	<option>B</option>
	<option>A</option>
	<option value="X">Expert</option>
	<option value="M">Master+</option>
	</select><br>
	<br>
	Black:<br>
	First Name:
	<input name="bFirst" type="text" tabindex="5">&nbsp; Last Name:
	<input name="bLast" type="text" tabindex="6">
		<br>
	Rating:
	<input name="bRating" type="text" tabindex="7" size="4" style="width: 32px">&nbsp; OR:&nbsp;
	<select name="bClass" tabindex="8">
	<option value="Z" selected="">Select Class</option>
	<option>G</option>
	<option>F</option>
	<option>E</option>
	<option>D</option>
	<option>C</option>
	<option>B</option>
	<option>A</option>
	<option value="X">Expert</option>
	<option value="M">Master+</option>
	</select><br>
	<br>
	Game Info:<br>
	Eco:
	<input name="eco" type="text" tabindex="9" size="3"> (<a target="_blank" href="ecocodes.htm">help</a>)<br>
	Year:
	<input name="year" type="text" tabindex="10" size="4">
	<select name="result" tabindex="11">
	<option selected="" value="%">Select Result</option>
	<option>1-0</option>
	<option>0-1</option>
	<option>1/2-1/2</option>
	</select>
<br>
	<input name="search" type="submit" value="Search" tabindex="12"></form>
</html>
<?php
	}
else {    
    $wValue=$_POST['wClass'];
    $bValue=$_POST['bClass'];
    $wRatingmin=$classmin['$wValue'];
    $wRatingmax=$classmax['$wValue'];
    $bRatingmin=$classmin['$bValue'];
    $bRatingmax=$classmax['$bValue'];
    if ($_POST['wFirst'])
        {$wFirst=strtolower($_POST['wFirst']);}
    else
    	{$wFirst="%";}
    if ($_POST['wLast'])
        {$wLast=strtolower($_POST['wLast']);}
    else
    	{$wLast="%";}
    if ($_POST['bFirst'])
        {$bFirst=strtolower($_POST['bFirst']);}
    else
    	{$bFirst="%";}
    if ($_POST['bLast'])
        {$bLast=strtolower($_POST['bLast']);}
    else
    	{$bLast="%";}
    if ($_POST['eco'])
        {$eco=$_POST['eco'];}
    else
    	{$eco="%";}
    if ($_POST['year'])
        {$year=$_POST['year'];}
    else
    	{$year="%";}
    if ($_POST['wRating'])
        {$wRating=$_POST['wRating'];}
    else
    	{$wRating="%";}
    if ($_POST['bRating'])
        {$bRating=$_POST['bRating'];}
    else
    	{$bRating="%";}
    if ($_POST['result'])
        {$result=$_POST['result'];}
    $query = mysql_query("SELECT * FROM games WHERE wFirst LIKE '$wFirst' AND wLast LIKE '$wLast' AND wRating LIKE '$wRating' AND bFirst LIKE '$bFirst' AND bLast LIKE '$bLast' AND wRating LIKE '$wRating' AND bRating LIKE '$bRating' AND result LIKE '$result'") or die(mysql_error());
    while($row = mysql_fetch_array($query))
        {    
   		echo "<a href=\"".$row['link']."\" target=\"RightSide\">".$row['wLast']." (".$row['wRating'].") - ".$row['bLast']." (".$row['bRating'].") ".$row['year']."<br />";
       	echo $row['eco']." ".$row['result']."</a><br />";        	
        }
    }  
?>

awesome thanks for the help i got it mostly working...now what i want to do is check to make sure $row['wRating'] and $row['bRating'] are within the min/maxes set in the beginning but every time I try it with an if statement I return no results. thanks again for help :D

**OdieusG** · 05-11-2009 01:53 PM

Just to add in, I received this from someone that was trying to help me with a problem, it may come in handy for others also

Code:

http://www.astahost.com/info.php/Mysql-Multiple-Tables_t12815.html

**garrensilverwing** · 05-11-2009 05:05 PM

*warning long post with lots of code*
ok i got the search feature to work somewhat like i want it to and now I have it inset into the frames final product here http://www.brianwallchess.x10hosting.com/games/search but now I have a new problem which i believe stems from the javascript. When you search for a game and select it it loads it up nice in the frame beside it but you cannot play through the moves like you can @ http://www.brianwallchess.x10hosting.com/games i understand that this is going to be a complicated code thing so if someone is willing to help me out I will post the code

this is the game that loads if you search nothing and choose the first game:

Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<META name="GENERATOR" content="ChessBase HTML Publisher 3.0">
<STYLE TYPE='text/css'>
<!--
body {color: 000000; font-family:Arial; font-size:12pt;  font: normal}
p {color: 000000; font-family:Arial; font-size:12pt;  font: normal}
h1 {color: 000000}
h4 {color: 000000}
.e1b {font-family:Arial; font-size:12pt;  font-style: normal; text-decoration: none}
.e0 {color: 000000; font-family:Arial; font-size:12pt;  font-style: normal; text-decoration: none}
.e1 {color: #0000FF; font-family:Arial; font-size:12pt;  font-style: normal; text-decoration: none}
.e2 {color: #0000FF; font-family:Arial; font-size:12pt;  font-style: normal; text-decoration: none}
.e3 {color: #0000FF; font-family:Arial; font-size:12pt;  font-style: normal; text-decoration: none}
.e4 {color: #0000FF; font-family:Arial; font-size:12pt;  font-style: normal; text-decoration: none}
.game {font-family:Arial; font-size:12pt;  font-style: normal;}
//-->
</STYLE>
</HEAD>
<BODY bgcolor="#FFFFFF"link="#B70801" vlink="#637178" >
<script>
<!--
function g0(a,b) { parent.frames[0].g0(a,b); };

var n0 = new Array();
var b0 = -1;
var m0 = new Array(1);
var fenNota="rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR w KQkq - 0 1";
m0[0] = new Array(51,35,12,20,52,36,11,27,57,42,6,21,58,30,5,12,36,28,21,11,55,39,12,30,39,30,3,30,62,47,30,12,59,38,14,22,47,30,13,29,28,21,93,93,11,21,38,46,1,18,60,58,120,59,2,11,42,25,4,6,71,5,61,43,12,14,25,10,18,35,30,15,21,15,43,22,35,52,58,57,52,46,22,15,14,15,63,15,6,15,10,0,46,36,53,45,36,21);
m0[0].base = new Array(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,20,21,22,23,24,24,25,26,27,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,0);
m0[0].root =0;
m0[0].move =0;
//-->
</script>
<P><h3>(1) Joseph Fang (2463) - Brian Wall (2255) [C14]<BR>
 1995 New Hampshire Open (2), 07.06.1995<BR>
<I> [Wall, Brian, Fritz 9]</I><BR>
</h3></P>
<P><B>
<BR><BR>
<a href="javascript:g0(1,0)" id='0' CLASS='e0' name="zeroAnchor">1.d4</a>
 <a href="javascript:g0(2,0)" id='0' CLASS='e0'>e6</a>
 <a href="javascript:g0(3,0)" id='0' CLASS='e0'>2.e4</a>
 <a href="javascript:g0(4,0)" id='0' CLASS='e0'>d5</a>
 <a href="javascript:g0(5,0)" id='0' CLASS='e0'>3.Nc3</a>
 <a href="javascript:g0(6,0)" id='0' CLASS='e0'>Nf6</a>
 <a href="javascript:g0(7,0)" id='0' CLASS='e0'>4.Bg5</a>
 <a href="javascript:g0(8,0)" id='0' CLASS='e0'>Be7</a>
 <a href="javascript:g0(9,0)" id='0' CLASS='e0'>5.e5</a>
 <a href="javascript:g0(10,0)" id='0' CLASS='e0'>Nfd7</a>
 <a href="javascript:g0(11,0)" id='0' CLASS='e0'>6.h4</a>
 <a href="javascript:g0(12,0)" id='0' CLASS='e0'>Bxg5</a>
 <a href="javascript:g0(13,0)" id='0' CLASS='e0'>7.hxg5</a>
 <a href="javascript:g0(14,0)" id='0' CLASS='e0'>Qxg5</a>
 <a href="javascript:g0(15,0)" id='0' CLASS='e0'>8.Nh3</a>
 <a href="javascript:g0(16,0)" id='0' CLASS='e0'>Qe7</a>
 <a href="javascript:g0(17,0)" id='0' CLASS='e0'>9.Qg4</a>
 <a href="javascript:g0(18,0)" id='0' CLASS='e0'>g6</a>
 <a href="javascript:g0(19,0)" id='0' CLASS='e0'>10.Ng5</a>
 <a href="javascript:g0(20,0)" id='0' CLASS='e0'>f5</a>
 <a href="javascript:g0(21,0)" id='0' CLASS='e0'>11.exf6</a>
 <a href="javascript:g0(22,0)" id='0' CLASS='e0'>Nxf6</a>
 <a href="javascript:g0(23,0)" id='0' CLASS='e0'>12.Qg3</a>
 <a href="javascript:g0(24,0)" id='0' CLASS='e0'>Nc6</a>
 <a href="javascript:g0(25,0)" id='0' CLASS='e0'>13.0-0-0</a>
 <a href="javascript:g0(26,0)" id='0' CLASS='e0'>Bd7</a>
 <a href="javascript:g0(27,0)" id='0' CLASS='e0'>14.Nb5</a>
 <a href="javascript:g0(28,0)" id='0' CLASS='e0'>0-0</a>
 <a href="javascript:g0(29,0)" id='0' CLASS='e0'>15.Bd3</a>
 <a href="javascript:g0(30,0)" id='0' CLASS='e0'>Qg7</a>
 <a href="javascript:g0(31,0)" id='0' CLASS='e0'>16.Nxc7</a>
 <a href="javascript:g0(32,0)" id='0' CLASS='e0'>Nxd4</a>
 <a href="javascript:g0(33,0)" id='0' CLASS='e0'>17.Nxh7</a>
 <a href="javascript:g0(34,0)" id='0' CLASS='e0'>Nxh7</a>
 <a href="javascript:g0(35,0)" id='0' CLASS='e0'>18.Bxg6</a>
 <a href="javascript:g0(36,0)" id='0' CLASS='e0'>Ne2+</a>
 <a href="javascript:g0(37,0)" id='0' CLASS='e0'>19.Kb1</a>
 <a href="javascript:g0(38,0)" id='0' CLASS='e0'>Nxg3</a>
 <a href="javascript:g0(39,0)" id='0' CLASS='e0'>20.Bxh7+</a>
 <a href="javascript:g0(40,0)" id='0' CLASS='e0'>Qxh7</a>
 <a href="javascript:g0(41,0)" id='0' CLASS='e0'>21.Rxh7</a>
 <a href="javascript:g0(42,0)" id='0' CLASS='e0'>Kxh7</a>
 <a href="javascript:g0(43,0)" id='0' CLASS='e0'>22.Nxa8</a>
 <a href="javascript:g0(44,0)" id='0' CLASS='e0'>Ne4</a>
 <a href="javascript:g0(45,0)" id='0' CLASS='e0'>23.f3</a>
 <a href="javascript:g0(46,0)" id='0' CLASS='e0'>Nf6</a>
 </B>Joe Resigns<B> 0-1</B></P>
<BR><BR></CENTER>
<script>
<!--
if ( parent.loaded >= 1 )
{    parent.frames[0].Start( parent.frames[1].m0, parent.frames[1].n0, false );
}
 else parent.loaded++;
//-->
</script>
</BODY></HTML>

**garrensilverwing** · 05-11-2009 05:05 PM

This is the code for the javascript chessboard:

Code:

<HTML>
<HEAD>
<META name="GENERATOR" content="ChessBase HTML Publisher 3.0">
<SCRIPT type="text/javascript" language="JavaScript" src="gamesj0.js"></SCRIPT>
<SCRIPT type="text/javascript" language="JavaScript" src="gamesj0c.js"></SCRIPT>
<STYLE TYPE='text/css'>
<!--
body {color: #000000; font-family:Arial; font-size:12pt;  font: normal; text-align: center;}
p {color: #000000; font-family:Arial; font-size:12pt;  font: normal}
h1 {color: #000000;}
h4 {color: #000000;}
.e1b {font-family:Arial; font-size:12pt;  font-style: normal; text-decoration: none}
.e0 {color: #000000; font-family:Arial; font-size:12pt;  font-style: normal; text-decoration: none}
.e1 {color: #0000FF; font-family:Arial; font-size:12pt;  font-style: normal; text-decoration: none}
.e2 {color: #0000FF; font-family:Arial; font-size:12pt;  font-style: normal; text-decoration: none}
.e3 {color: #0000FF; font-family:Arial; font-size:12pt;  font-style: normal; text-decoration: none}
.e4 {color: #0000FF; font-family:Arial; font-size:12pt;  font-style: normal; text-decoration: none}
.game {font-family:Arial; font-size:12pt;  font-style: normal;}
//-->
</STYLE>
</HEAD>
<BODY bgcolor="#FFFFFF"link="#B70801" vlink="#637178"  onLoad=" Init('gif/'); ">
<TABLE border><TR><TD>
<IMG alt="chessboard" SRC="gif/brw.gif" name='basePic'><IMG alt="chessboard" SRC="gif/bnb.gif"><IMG alt="chessboard" SRC="gif/bbw.gif"><IMG alt="chessboard" SRC="gif/bqb.gif"><IMG alt="chessboard" SRC="gif/bkw.gif"><IMG alt="chessboard" SRC="gif/bbb.gif"><IMG alt="chessboard" SRC="gif/bnw.gif"><IMG alt="chessboard" SRC="gif/brb.gif"><BR><IMG alt="chessboard" SRC="gif/bpb.gif"><IMG alt="chessboard" SRC="gif/bpw.gif"><IMG alt="chessboard" SRC="gif/bpb.gif"><IMG alt="chessboard" SRC="gif/bpw.gif"><IMG alt="chessboard" SRC="gif/bpb.gif"><IMG alt="chessboard" SRC="gif/bpw.gif"><IMG alt="chessboard" SRC="gif/bpb.gif"><IMG alt="chessboard" SRC="gif/bpw.gif"><BR><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><BR><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><BR><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><BR><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><IMG alt="chessboard" SRC="gif/b.gif"><IMG alt="chessboard" SRC="gif/w.gif"><BR><IMG alt="chessboard" SRC="gif/wpw.gif"><IMG alt="chessboard" SRC="gif/wpb.gif"><IMG alt="chessboard" SRC="gif/wpw.gif"><IMG alt="chessboard" SRC="gif/wpb.gif"><IMG alt="chessboard" SRC="gif/wpw.gif"><IMG alt="chessboard" SRC="gif/wpb.gif"><IMG alt="chessboard" SRC="gif/wpw.gif"><IMG alt="chessboard" SRC="gif/wpb.gif"><BR><IMG alt="chessboard" SRC="gif/wrb.gif"><IMG alt="chessboard" SRC="gif/wnw.gif"><IMG alt="chessboard" SRC="gif/wbb.gif"><IMG alt="chessboard" SRC="gif/wqw.gif"><IMG alt="chessboard" SRC="gif/wkb.gif"><IMG alt="chessboard" SRC="gif/wbw.gif"><IMG alt="chessboard" SRC="gif/wnb.gif"><IMG alt="chessboard" SRC="gif/wrw.gif"></TD></TR></TABLE><BR>
<FORM NAME="panel0">
<P>
<input type=button value=" Start " onClick="currB=GoStart0();">
<input type=button value=" &lt;&lt; " onClick="MB_50();" onKeyPress="MB_50();" onDblClick="MB_50();">
<input type=button value=" &lt; " onClick="MB0();" onKeyPress="MB0();" onDblClick="MB0();">
<input type=button value=" &gt; " onClick="MF0(false);" onKeyPress="MF0(false);" onDblClick="MF0(false);">
<input type=button value=" &gt;&gt; " onClick="MF_50();" onKeyPress="MF_50();" onDblClick="MF_50();">
<input type=button value=" End " onClick="currB=GoEnd0();">
</P>
</form>
<DIV id="igames0"></DIV>
For more games try:<br>
    <a target="_parent" href="http://forums.x10hosting.com/games.htm">Games Home</a><br>
    <a target="_parent" href="http://forums.x10hosting.com/briansannotatedgames/games.htm">Brian's Annotated
    Games 1-40</a><br>
    <a target="_parent" href="http://forums.x10hosting.com/bwchess4180/games.htm">Brian's Annotated Games 41-80</a><br>
    <a target="_parent" href="http://forums.x10hosting.com/bwchess81100/games.htm">Brian's Annotated Games 81-100</a><BR>
<iframe src="search.php" style="width: 250px; height: 500px" frameborder="0">
</iframe>
</BODY>
</html>

and the two javascript documents it refers to can be seen here

http://brianwallchess.x10hosting.com...ch/gamesj0c.js
http://brianwallchess.x10hosting.com...rch/gamesj0.js

i understand that what i'm doing right now is extremely lazy but i don't really know the first thing about javascript and my eyes are falling out of my head from trying to get the search thing to work, thanks :D massive reputation and credits to everyone who tries to help :D

**misson** · 05-11-2009 09:01 PM

Originally Posted by garrensilverwing

now what i want to do is check to make sure $row['wRating'] and $row['bRating'] are within the min/maxes set in the beginning but every time I try it with an if statement I return no results. thanks again for help :D

That should be handled by the SELECT statement, not the PHP script.

Originally Posted by garrensilverwing

PHP Code:


$wRatingmin=$classmin['$wValue'];

'$wValue' is the literal string "$wValue", so the above line looks for '$wValue' in $classmin, which isn't set. Only put quotes around indices that are string literals, like $_POST['wClass']. In the above line, you'd want to use $classmin[$wValue], which will get the value of $wValue before looking in $classmin.

Originally Posted by garrensilverwing

PHP Code:


    if ($_POST['wFirst'])

        {$wFirst=strtolower($_POST['wFirst']);}

    else

        {$wFirst="%";}

All the "LIKE '%'" restraints this generates are inefficient, as they will cause MySQL to check the field even though the test will succeed. Remember what I said about repeated statements? You should have turned this section into a loop. That way, you can store restraints for only those fields specified in the form. You still need to sanitize the input so a malicious user doesn't delete all your data.

Originally Posted by garrensilverwing

PHP Code:


echo "<a href=\"".$row['link']."\" target=\"RightSide\">".$row['wLast']." (".$row['wRating'].") - ".$row['bLast']." (".$row['bRating'].") ".$row['year']."<br />";

           echo $row['eco']." ".$row['result']."</a><br />";

<br> elements have no semantic value. If you find yourself using many of them in production code, you're probably doing something wrong. In this instance your search results is a list of games, so why not use a list element, such as <ul>?

Study the following. You should be able to adapt it for your search form handler. One thing in particular that might need changing is that a player rating takes precedence over player class: if both are specified, only the rating is included in the search. I wrote this because .

PHP Code:


function sanitize($value) {

    if (get_magic_quotes_gpc()) {  

        $string = stripslashes($value);  

    } 

    return mysql_real_escape_string($value); 

}

function sanitize_lower($value) {

    return strtolower(sanitize($value)); 

}

function sanitize_rating($rating) {

    if (get_magic_quotes_gpc()) {  

        $string = stripslashes($rating);  

    } 

    return preg_replace('/[^A-FWX]+/g', '', strtoupper($rating));

}



function includeInSearch($name) {

    return isset($_REQUEST[$name]) && $_REQUEST[$name] !== '';

}



function addClassOrRatingRestraint($color) {

    global $classmin, $classmax, $restraints;

    $rating=False;

    if (includeInSearch($color.'Rating')) {

        $rating = sanitize_rating($_REQUEST[$color.'Rating']);

        if ($rating) {

            $restraints[] = "{$color}Rating='$rating'";

        }

    }

    if (!$rating && includeInSearch($color.'Class')) {

        $value=sanitize($_REQUEST[$color.'Class']);

        $restraints[] = "{$color}Rating>='{$classmin[$value]}'";

        $restraints[] = "{$color}Rating<='{$classmax[$value]}'";

    }

}





addClassOrRatingRestraint('w');

addClassOrRatingRestraint('b');

foreach (array('wFirst'=>'_lower', 'wLast' => '_lower', 'bFirst'=>'_lower', 'bLast'=>'_lower', 'eco'=>'', 'year'=>'') as $key => $sanitizer) {

    if (includeInSearch($_REQUEST[$key])) {

        $sanitizer = 'sanitize' . $sanitizer;

        $restraints[] = $key . "='" . $sanitizer($_REQUEST[$key]) ."'";

    }

}

if (count($restraints)) {

    $restraints = ' WHERE ' . implode(' AND ', $restraints);

} else {

    $restraints = '';

}

if ($results=mysql_query("SELECT * FROM games$restraints")) {

    print_head();

    while ($row = mysql_fetch_array($results)) {

        print_row($row);

    }

    print_foot();

}



function print_head() {

    echo '<ul class="gameList">';

}

function print_row($row, $columns) {

    echo "<li><a href=\"$row[link]\" target='RightSide'>$row[wLast] ($row[wRating]) - $row[bLast] ($row[bRating]) $row[year]<br/>$row[eco]  $row[result]</a></li>";            

}

function print_foot() {

    echo '</ul>';

}

The above is still a little messy. In general, you want to minimize code dependency so that a change in one line will require updating as few other lines as possible. This involves things like data access layers, which divorce the structure of information in the database from the structure of information as used by the script (aka the "model"). The print_* functions are a start at separating the model from what the user sees (the "view").

Another big improvement would be to abstract away restraint generation, so you could set the name, type and label for the various fields, pass that to the data access layer and get back the corresponding rows. I'm envisioning an interface like filter_input_array()'s.

The use of globals in addClassOrRatingRestraint() should be factored out in a clean implementation.

Lastly, take a look at the thread that OdieusG mentioned. When you have a single table, you open it to anomalies. When you list a player's name with each game, it's easy to mistype it for some of the games, making it impossible to find those games by searching.

Here's another scenario: imagine a player gets married and changes her last name. New games the player plays have the married name, but because you didn't update the database, the old games are under the old name. When someone searches for the player's games, they will get either the older games or the newer games, but not both. This is known as an "update anomaly".

Database design can be a tricky subject. Enough is involved that people specialize in database design and administration. "Introduction to Databases and Relational Data Model", by Maurer and Scherbakov, is a good text that covers RDB design and is available online.

**garrensilverwing** · 05-11-2009 09:30 PM

this is some complicated stuff haha

**OdieusG** · 05-11-2009 09:42 PM

Shouldn't $restraints be encapsulated? {$restraints} or just concatenate the variable onto the string?

On this line, I mean

Code:

if ($results=mysql_query("SELECT * FROM games$restraints")) {

Thread: multiple queries

LinkBack

Thread Tools

Display

multiple queries

Re: multiple queries

Re: multiple queries

Re: multiple queries

Re: multiple queries

Re: multiple queries

Re: multiple queries

Re: multiple queries

Re: multiple queries

Re: multiple queries

Similar Threads

Multiple Forum / Hosting Account Links

Date/Calendar functions and parameterized queries help needed.

Multiple cpanel accounts

Bid For Multiple Background Dynamic SIG

Posting Permissions