Jump to content


Photo

Complex query


  • Please log in to reply
8 replies to this topic

#1 Haden

Haden
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 07 February 2006 - 08:06 PM

Hi all, I was wondering if someone could help me figure out how to query when sometimes the person making the request won't be choosing all the variables? In my search form, the person making the search has the option to search for certain variables or not, my problem is, that I do not what to do with my query if they so choose not to, because if they don't it nulls my search.

Here is the search form...
<form method="POST" action="eqresults.php">
<table width="100%" cellspacing="1" cellpadding="0" border="0">
  <tr>
    <td>EQ or Weapon</td>
    <td>
<select name="eq_or_weap">
    <option value="1">EQ</option>
    <option value="2">Weapon</option>
</select>
    </td>
  </tr>
  <tr>
    <td>Wear Location</td>
    <td>
<select name="wear_location">
  <option value=""></option>
  <option value="1">Light</option>
  <option value="2">Finger</option>
  <option value="3">Neck</option>
  <option value="4">Body</option>
  <option value="5">Head</option>
  <option value="6">Legs</option>
  <option value="7">Feet</option>
  <option value="8">Hands</option>
  <option value="9">Arms</option>
  <option value="10">About Body</option>
  <option value="11">Waist</option>
  <option value="12">Wrist</option>
  <option value="13">Ears</option>
  <option value="14">Eyes</option>
  <option value="15">Back</option>
  <option value="16">Face</option>
  <option value="17">Ankle</option>
  <option value="18">Lance</option>
</select>
    </td>
  </tr>
  <tr>
    <td>Weapon Type</td>
    <td>
<select name="weap_type">
  <option value=""></option>
  <option value="1">Bludgeon</option>
  <option value="2">Long Blade</option>
</select>
    </td>
  </tr>
  <tr>
    <td>EQ or Weapon Level</td>
    <td><input type="text" name="level" value="" size="2"></td>
  </tr>
  <tr>
    <td valign="top">Alignment</td>
    <td>
<input type="checkbox" name="allalignments" value="1"> All Alignments<br>
<input type="checkbox" name="good" value="1"> Good<br>
<input type="checkbox" name="neutral" value="1"> Neutral<br>
<input type="checkbox" name="evil" value="1"> Evil
  </tr>
  <tr>
      <td colspan="2"><hr></td>
  </tr>
  <tr>
    <td valign="top">Race</td>
    <td>
<input type="checkbox" name="allraces" value="1"> All Barbarian Races<br>
<input type="checkbox" name="dwarf" value="1"> Dwarf<br>
<input type="checkbox" name="halfelf" value="1"> Half-Elf<br>
<input type="checkbox" name="halfogre" value="1"> Half-Ogre<br>
<input type="checkbox" name="halforc" value="1"> Half-Orc<br>
<input type="checkbox" name="halftroll" value="1"> Half-Troll<br>
<input type="checkbox" name="human" value="1"> Human
    </td>
  </tr>
  <tr>
    <td colspan="2"><font color="#FF0000">Place the minimal amount of AC or AFF_AC in the appropriate box if you are looking for EQ or Weapon with a minimal amount of AC or AFF_AC.</font></td>
  </tr>
  <tr>
    <td>AC</td>
    <td><input type="text" name="ac" size="4"></td>
  </tr>
  <tr>
    <td>AFF_AC</td>
    <td><input type="text" name="aff_ac" size="4"></td>
  </tr>
  <tr>
    <td colspan="2"><font color="#FF0000">Place a checkmark in the coinciding box if you are looking for that type of bonus for the EQ or Weapon.</font></td>
  </tr>
  <tr>
    <td>HR</td>
    <td><input type="checkbox" name="hr"></td>
  </tr>
  <tr>
    <td>DR</td>
    <td><input type="checkbox" name="dr"></td>
  </tr>
  <tr>
    <td>HP</td>
    <td><input type="checkbox" name="hp"></td>
  </tr>
  <tr>
    <td>MV</td>
    <td><input type="checkbox" name="mv"></td>
  </tr>
  <tr>
    <td>STR</td>
    <td><input type="checkbox" name="str"></td>
  </tr>
  <tr>
    <td>INT</td>
    <td><input type="checkbox" name="int"></td>
  </tr>
  <tr>
    <td>WIS</td>
    <td><input type="checkbox" name="wis"></td>
  </tr>
  <tr>
    <td>DEX</td>
    <td><input type="checkbox" name="dex"></td>
  </tr>
  <tr>
    <td>CON</td>
    <td><input type="checkbox" name="con"></td>
  </tr>
  <tr>
    <td>CHA</td>
    <td><input type="checkbox" name="cha"></td>
  </tr>
  <tr>
    <td>LCK</td>
    <td><input type="checkbox" name="lck"></td>
  </tr>
  <tr>
    <td colspan="2"> </td>
  </tr>
  <tr>
    <td colspan="2"><center><input type="submit" name="searcheq" value="Submit"></center></td>
  </tr>
</table>
</form>

Here is my database structure...
CREATE TABLE `eq` (
  `id` int(11) NOT NULL auto_increment,
  `low_level` tinyint(2) NOT NULL default '0',
  `item_name` varchar(100) NOT NULL default '',
  `area` varchar(100) NOT NULL default '',
  `mob` varchar(100) NOT NULL default '',
  `eq_or_weap` tinyint(1) NOT NULL default '0',
  `wear_location` tinyint(2) NOT NULL default '0',
  `weap_type` tinyint(1) NOT NULL default '0',
  `good` tinyint(1) NOT NULL default '0',
  `neutral` tinyint(1) NOT NULL default '0',
  `evil` tinyint(1) NOT NULL default '0',
  `dwarf` tinyint(1) NOT NULL default '0',
  `halfelf` tinyint(1) NOT NULL default '0',
  `halfogre` tinyint(1) NOT NULL default '0',
  `halforc` tinyint(1) NOT NULL default '0',
  `halftroll` tinyint(1) NOT NULL default '0',
  `human` tinyint(1) NOT NULL default '0',
  `ac` tinyint(6) NOT NULL default '0',
  `aff_ac` tinyint(6) NOT NULL default '0',
  `hr` tinyint(4) NOT NULL default '0',
  `dr` tinyint(4) NOT NULL default '0',
  `hp` tinyint(4) NOT NULL default '0',
  `mv` tinyint(4) NOT NULL default '0',
  `str` tinyint(2) NOT NULL default '0',
  `int` tinyint(2) NOT NULL default '0',
  `wis` tinyint(2) NOT NULL default '0',
  `dex` tinyint(2) NOT NULL default '0',
  `con` tinyint(2) NOT NULL default '0',
  `cha` tinyint(2) NOT NULL default '0',
  `lck` tinyint(2) NOT NULL default '0',
  `notes` longtext NOT NULL,
  `poster` varchar(100) NOT NULL default '',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3;


#2 Haden

Haden
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 08 February 2006 - 06:45 AM

A guy on the game was kind enough to show me a starting of some code that looks like it might work...

<?php
global $HTTP_POST_VARS;
$x = $HTTP_POST_VARS;
$sql = "SELECT whatever FROM wherever WHERE eq_or_weap={$x[eq_or_weap]}";
if (!empty($x[wear_location])  $sql .= " AND wear_location={$x[wear_location]}
";
if (!empty($x[weap_type]) $sql .= " AND weap_type={$x[weap_type]}";
if (!empty($x[level]) $sql .= " AND level={$x[level]}";
if (!empty($x[allalignments]) $sql .= " AND (good=1 and evil=1 and neutral=1)";
?>


#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 08 February 2006 - 08:06 AM

This is the technique I use

[a href=\"http://www.phpfreaks.com/forums/index.php?s=&showtopic=50772&view=findpost&p=195970\" target=\"_blank\"]http://www.phpfreaks.com/forums/index.php?...ndpost&p=195970[/a]
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 Haden

Haden
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 11 February 2006 - 01:25 AM

Here is my code that I integrated with yours... but I still get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home2/barbpcom/public_html/testeqresults.php on line 75

<?php

if(isset($_POST[searcheq]))    {

include_once "../cgi-bin/connection.php";

    $eq_or_weap=$_POST[eq_or_weap];
    $wear_location=$_POST[wear_location];
    $weap_type=$_POST[weap_type];

if($eq_or_weap == '1')    {
    $searchtype = 'wear_location';
    $searchvariable = $wear_location;
                        }
if($eq_or_weap == '2')    {
    $searchtype = 'weap_type';
    $searchvariable = $weap_type;
                        }
if(empty($_POST[ac])){$ac = '0';}else{$ac = $_POST[ac];}
if(empty($_POST[aff_ac])){$aff_ac = '0';}else{$aff_ac = $_POST[aff_ac];}

$where = array();

if (!empty($_POST['hr']) ) {
              $hr = $_POST['hr'];
              $where[] = "(hr = '$hr')";
}

if (!empty($_POST['dr']) ) {
              $dr = $_POST['dr'];
              $where[] = "(dr = '$dr')";
}
if (!empty($_POST['hp']) ) {
              $hp = $_POST['hp'];
              $where[] = "(hp = '$hp')";
}
if (!empty($_POST['mv']) ) {
              $mv = $_POST['mv'];
              $where[] = "(mv = '$mv')";
}
if (!empty($_POST['str']) ) {
              $str = $_POST['str'];
              $where[] = "(str = '$str')";
}
if (!empty($_POST['ini']) ) {
              $ini = $_POST['ini'];
              $where[] = "(ini = '$ini')";
}
if (!empty($_POST['wis']) ) {
              $wis = $_POST['wis'];
              $where[] = "(wis = '$wis')";
}
if (!empty($_POST['dex']) ) {
              $dex = $_POST['dex'];
              $where[] = "(dex = '$dex')";
}
if (!empty($_POST['con']) ) {
              $con = $_POST['con'];
              $where[] = "(con = '$con')";
}
if (!empty($_POST['cha']) ) {
              $cha = $_POST['cha'];
              $where[] = "(cha = '$cha')";
}
if (!empty($_POST['lck']) ) {
              $lck = $_POST['lck'];
              $where[] = "(lck = '$lck')";
}

if (count($where) > 0) $whereclause = " WHERE " . join (' AND ', $where);

$query = mysql_query("SELECT * FROM eq WHERE eq_or_weap = '$eq_or_weap' && $searchtype = '$searchvariable' && low_level <= '$_POST[level]' && $_POST[alignment] = '1' && $_POST[race] = '1' && ac >= $ac && aff_ac >= $aff_ac" . $whereclause);

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

    $id=$row[id];
    $low_level=$row[low_level];
    $item_name=$row[item_name];
    $area=$row[area];
    $mob=$row[mob];
    $eq_or_weap=$row[eq_or_weap];
    $wear_location=$row[wear_location];
    $weap_type=$row[weap_type];
    $good=$row[good];
    $neutral=$row[neutral];
    $evil=$row[evil];
    $dwarf=$row[dwarf];
    $halfelf=$row[halfelf];
    $halfogre=$row[halfogre];
    $halforc=$row[halforc];
    $halftroll=$row[halftroll];
    $human=$row[human];
    $ac=$row[ac];
    $aff_ac=$row[aff_ac];
    $hr=$row[hr];
    $dr=$row[dr];
    $hp=$row[hp];
    $mv=$row[mv];
    $str=$row[str];
    $ini=$row[ini];
    $wis=$row[wis];
    $dex=$row[dex];
    $con=$row[con];
    $cha=$row[cha];
    $luck=$row[luck];
    $notes=$row[notes];
    $poster=$row[poster];

if($wear_location == 1){$wear_location = 'Light';}
if($wear_location == 2){$wear_location = 'Finger';}
if($wear_location == 3){$wear_location = 'Neck';}
if($wear_location == 4){$wear_location = 'On Body';}
if($wear_location == 5){$wear_location = 'Head';}
if($wear_location == 6){$wear_location = 'Legs';}
if($wear_location == 7){$wear_location = 'Feet';}
if($wear_location == 8){$wear_location = 'Hands';}
if($wear_location == 9){$wear_location = 'Arms';}
if($wear_location == 10){$wear_location = 'About Body';}
if($wear_location == 11){$wear_location = 'Waist';}
if($wear_location == 12){$wear_location = 'Wrist';}
if($wear_location == 13){$wear_location = 'Ears';}
if($wear_location == 14){$wear_location = 'Eyes';}
if($wear_location == 15){$wear_location = 'Back';}
if($wear_location == 16){$wear_location = 'Face';}
if($wear_location == 17){$wear_location = 'Ankle';}

if($weap_type == 1){$weap_type = 'Bludgeon';}
if($weap_type == 2){$weap_type = 'Long Blade';}
if($weap_type == 3){$weap_type = 'Lance';}

echo '<center><table width=400 cellspacing=1 cellpadding=0 border=1>';
echo '<tr><td width=150>EQ ID</td><td>', $id, '</td></tr>';
if(isset($low_level)){echo '<tr><td width=150>Lowest Level Found</td><td>', $low_level, '</td></tr>';}
if(isset($item_name)){echo '<tr><td width=150>Item</td><td>', $item_name, '</td></tr>';}
if(isset($area)){echo '<tr><td width=150>Area</td><td>', $area, '</td></tr>';}
if(isset($mob)){echo '<tr><td width=150>Mob</td><td>', $mob, '</td></tr>';}
if($wear_location != ''){echo '<tr><td width=150>Wear Location</td><td>', $wear_location, '</td></tr>';}
if($weap_type != 0){echo '<tr><td width=150>Weapon Type</td><td>', $weap_type, '</td></tr>';}

if($good == 0 AND $neutral == 0 AND $evil == 0){echo '<tr><td width=150>Alignments</td><td>Unknown</td></tr>';}
if($good > 0 AND $neutral > 0 AND $evil > 0){
echo '<tr><td width=150>Alignments</td><td>All</td></tr>';}else{
if($good > 0){echo '<tr><td width=150>Alignment</td><td>Good</td></tr>';}
if($neutral > 0){echo '<tr><td width=150>Alignment</td><td>Neutral</td></tr>';}
if($evil > 0){echo '<tr><td width=150>Alignment</td><td>Evil</td></tr>';}
                                                                    }

if($dwarf == 0 AND $halfelf == 0 AND $halfogre == 0 AND $halforc == 0 AND $halftroll == 0 AND $human == 0){echo '<tr><td width=150>Races</td><td>Unknown</td></tr>';}
if($dwarf > 0 AND $halfelf > 0 AND $halfogre > 0 AND $halforc > 0 AND $halftroll > 0 AND $human > 0){
echo '<tr><td width=150>Races</td><td>All</td></tr>';}else{
if($dwarf > 0){echo '<tr><td width=150>Race</td><td>Dwarf</td></tr>';}
if($halfelf > 0){echo '<tr><td width=150>Race</td><td>Half-Elf</td></tr>';}
if($halfogre > 0){echo '<tr><td width=150>Race</td><td>Half-Ogre</td></tr>';}
if($halforc > 0){echo '<tr><td width=150>Race</td><td>Half-Orc</td></tr>';}
if($halftroll > 0){echo '<tr><td width=150>Race</td><td>Half-Troll</td></tr>';}
if($human > 0){echo '<tr><td width=150>Race</td><td>Human</td></tr>';}
                                                                        }

if($ac != 0){echo '<tr><td width=150>AC</td><td>', $ac, '</td></tr>';}
if($aff_ac != 0){echo '<tr><td width=150>AFF_AC</td><td>', $aff_ac, '</td></tr>';}
if($hr != 0){echo '<tr><td width=150>HR</td><td>', $hr, '</td></tr>';}
if($dr != 0){echo '<tr><td width=150>DR</td><td>', $dr, '</td></tr>';}
if($hp != 0){echo '<tr><td width=150>HP</td><td>', $hp, '</td></tr>';}
if($mv != 0){echo '<tr><td width=150>MV</td><td>', $mv, '</td></tr>';}
if($str != 0){echo '<tr><td width=150>STR</td><td>', $str, '</td></tr>';}
if($ini != 0){echo '<tr><td width=150>INT</td><td>', $ini, '</td></tr>';}
if($wis != 0){echo '<tr><td width=150>WIS</td><td>', $wis, '</td></tr>';}
if($dex != 0){echo '<tr><td width=150>DEX</td><td>', $dex, '</td></tr>';}
if($con != 0){echo '<tr><td width=150>CON</td><td>', $con, '</td></tr>';}
if($cha != 0){echo '<tr><td width=150>CHA</td><td>', $cha, '</td></tr>';}
if($lck != 0){echo '<tr><td width=150>LCK</td><td>', $lck, '</td></tr>';}
if($notes != ''){echo '<tr><td width=150>Notes</td><td>', $notes, '</td></tr>';}
if(isset($poster)){echo '<tr><td width=150>Poster</td><td>', $poster, '</td></tr>';}
echo'</table></center>';
                            }

print("<br><br><center><a href=eqandstats.php>Search Again</a></center>");
}
?>


#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 11 February 2006 - 09:18 AM

Add some error checking code

$sql = "SELECT * FROM eq WHERE eq_or_weap = '$eq_or_weap' && $searchtype = '$searchvariable' && low_level <= '$_POST[level]' && $_POST[alignment] = '1' && $_POST[race] = '1' && ac >= $ac && aff_ac >= $aff_ac" . $whereclause;

$query = mysql_query ($sql ) or die ($sql . '<br>' . mysql_error());

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 Haden

Haden
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 08 March 2006 - 06:53 PM

I get this error:
Parse error: parse error, unexpected T_VARIABLE in /home2/barbpcom/public_html/eqresults.php on line 44
which is this line: $whereclause = "";

Here is my code
if(isset($_POST[searcheq]))    {
if($_POST[eq_or_weap] == '1' && empty($_POST[wear_location])){header("Location: eqandstats.php?missing=wearlocation");}
if($_POST[eq_or_weap] == '2' && empty($_POST[weap_type])){header("Location: eqandstats.php?missing=weapontype");}
include_once "../cgi-bin/connection.php";

    $eq_or_weap=$_POST[eq_or_weap];
    $wear_location=$_POST[wear_location];
    $weap_type=$_POST[weap_type];

if($eq_or_weap == '1')    {
    $searchtype = 'wear_location';
    $searchvariable = $wear_location;
                        }
if($eq_or_weap == '2')    {
    $searchtype = 'weap_type';
    $searchvariable = $weap_type;
                        }
if(empty($_POST[level])){$level = '70';}else{$level = $_POST[level];}

$where = array()
$whereclause = "";

if (!empty($_POST['ac']) ) {
              $ac = $_POST['ac'];
              $where[] = "(ac >= '$ac')";
}

if (!empty($_POST['aff_ac']) ) {
              $aff_ac = $_POST['aff_ac'];
              $where[] = "(aff_ac >= '$aff_ac')";
}

if (!empty($_POST['avg_dmg']) ) {
              $avg_dmg = $_POST['avg_dmg'];
              $where[] = "(avg_dmg >= '$avg_dmg')";
}

if (!empty($_POST['hr']) ) {
              $where[] = "(hr > '0')";
}

if (!empty($_POST['dr']) ) {
              $where[] = "(dr > '0')";
}

if (!empty($_POST['hp']) ) {
              $where[] = "(hp > '0')";
}

if (!empty($_POST['mv']) ) {
              $where[] = "(mv > '0')";
}

if (!empty($_POST['str']) ) {
              $where[] = "(str > '0')";
}

if (!empty($_POST['ini']) ) {
              $where[] = "(ini > '0')";
}

if (!empty($_POST['wis']) ) {
              $where[] = "(wis > '0')";
}

if (!empty($_POST['dex']) ) {
              $where[] = "(dex > '0')";
}

if (!empty($_POST['con']) ) {
              $where[] = "(con > '0')";
}

if (!empty($_POST['cha']) ) {
              $where[] = "(cha > '0')";
}

if (!empty($_POST['lck']) ) {
              $where[] = "(lck > '0')";
}

if (count($where) > 0) $whereclause = " WHERE eq_or_weap = '$eq_or_weap' && $searchtype = '$searchvariable' && low_level <= '$level' && $_POST[alignment] = '1' && $_POST[race] = '1' " . join (' AND ', $where);

$sql = "SELECT * FROM eq  " . $whereclause;
$query = mysql_query($sql) or die ($sql . '<br>' . mysql_error());
$num_rows = mysql_num_rows($query);
if($num_row == '0'){'<center><H3>Your search did not produce any results.</H3></center>';}
echo '<center><H3>Your search produced ', $num_rows, ' results.</H3></center>';
while($row = mysql_fetch_array($query))    {


#7 henry

henry
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 March 2006 - 07:27 PM

[!--quoteo(post=352934:date=Mar 8 2006, 11:53 AM:name=Haden)--][div class=\'quotetop\']QUOTE(Haden @ Mar 8 2006, 11:53 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]I get this error:
Parse error: parse error, unexpected T_VARIABLE in /home2/barbpcom/public_html/eqresults.php on line 44
[/quote]
That error often means you've done something careless like drop a semicolon on the previous line.

$where = array()
$whereclause = "";


#8 Haden

Haden
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 08 March 2006 - 08:16 PM

Thank you... works like a charm!

#9 keeB

keeB
  • Staff Alumni
  • Advanced Member
  • 1,078 posts
  • LocationCalifornia

Posted 08 March 2006 - 08:43 PM

[!--quoteo(post=352958:date=Mar 8 2006, 08:16 PM:name=Haden)--][div class=\'quotetop\']QUOTE(Haden @ Mar 8 2006, 08:16 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Thank you... works like a charm!
[/quote]

One problem with this method, if you havent realized yet, is that people can use SQL Insertion techniques on your fields very easily this way..

Designing something better structured is more complex, but security is really important. Just something to take note if you will have a lot of people visiting your site.

Come visit my site to see my latest projects
http://nick.stinemates.org/wordpress/





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users