Two Seperate Search Results, Same Table/Page

[wikit]

what I'm on the hunt for is a php script using mysql or mysqli that searches for results. This part is the easy part the hard part is this. I'd like it to search 1 database to to yeild 2 sets of results with 1 search.

It would use a 2 cell (side by side) table to show the results.

basically I have a table that holds data abdout items a player in the game have and wish to have. the search would show in 1 column the names of everyone who input that they HAVE the item and the other would list the names of those who have input a WISH to have the item....

if I'm being confusing please just ask questions :) I'm more then willing to answer them (after i have some sleep ofc XD it's late) any help is appreciated, any coding or examples of coding even more appreciated... Thanks in advance for your time

[cereal]

Try with the flow control function CASE:

• http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html#operator_case

I don't know your table structure, so here's an example:

• http://sqlfiddle.com/#!2/a9e87/3

Bye!

[wikit]

wow @_@ that went way over my head.
in a simple term my database has records like

name item1 item2 item3 item4 item5 item6 wish1 wish2 wish3 wish4 wish5

I want the search to look at all of it and gimme a table like

  has (looks at all has items)|     need (looks at all wishes)
  name1                       |            name3
  name2                       |            name5
  name4                       |
  name6                       |

is that makes any sense at all >.< I was kinda hoping there was a script that would do it in simple ways

[DJBirdi]

I'm a little confused by the table example you gave above. How would that work for different items and wishes since you say "looks at ALL items and ALL wishes"?

When I think about what you're looking for, I'm thinking more along these lines, with separate tables for each item and wish:

ITEM 1 | WISH 1

name 1 | name 3
name 2 | name 5
name 4 | name 6

ITEM 2 | WISH 2

name 1 | name 2
name 3 | name 5
name 4 | name 6

...and so on

Correct me if I completely messed that up, but tables like that would be easy to build with queries such as:

// For items:
SELECT name FROM players WHERE item1 IS NOT NULL;

// For wishes:
SELECT name FROM players WHERE wish1 IS NOT NULL;

// This ofcourse is assuming you have empty items and wishes set to NULL

[wikit]

I could be wording things weird so please forgive me if I am I want it to be a single search, and it to post both results at the same time. as for 'null' I dont know how to do an update that leaves the fields null so 'empty' fields are item="" in the update query.

as an example people wish for a red shirt. I want het search to look for all names that have 'red shirt' in either items or wishes, and post a list for each side by side. But a list for each item all at once, just whats searched for.

[DJBirdi]

Ahh I see! Show us your schema. As cereal mentioned, we can't give you accurate help with the query without knowing your table structure.

[wikit]

The table for this one (yes I know it's LONG sry) looks like this cause it holds everything about their char everything from their level and class to what gear they have for their two main classes and and the gear they want to have for their character

CREATE TABLE `gearowned` (
  `CharName` varchar(200) NOT NULL DEFAULT '',
  `MainClass` text,
  `MainLevel` text,
  `DualClass` text,
  `DualLevel` text,
  `Mainuppertype` text,
  `Mainlowertype` text,
  `Mainheadtype` text,
  `Mainhandtype` text,
  `Mainfoottype` text,
  `Mainshldtype` text,
  `Mainwep1type` text,
  `Mainwep2type` text,
  `Dualuppertype` text,
  `Duallowertype` text,
  `Dualheadtype` text,
  `Dualhandtype` text,
  `Dualfoottype` text,
  `Dualshldtype` text,
  `Dualwep1type` text,
  `Dualwep2type` text,
  `MainEar1Type` text,
  `DualEar1Type` text,
  `MainEar2Type` text,
  `DualEar2Type` text,
  `MainEar3Type` text,
  `DualEar3Type` text,
  `MainNeck1Type` text,
  `DualNeck1Type` text,
  `MainNeck2Type` text,
  `DualNeck2Type` text,
  `MainRing1Type` text,
  `DualRing1Type` text,
  `MainRing2Type` text,
  `DualRing2Type` text,
  `MainRing3Type` text,
  `DualRing3Type` text,
  `talisman` text,
  `shirttyp` text,
  `belttype` text,
  `Wishuppertype` text,
  `Wishlowertype` text,
  `Wishheadtype` text,
  `Wishhandtype` text,
  `Wishfoottype` text,
  `Wishshldtype` text,
  `Wishwep1type` text,
  `WishEar1Type` text,
  `WishEar2Type` text,
  `WishNeck1Type` text,
  `WishRing1Type` text,
  `WishRing2Type` text,
  `WishExtra1` text,
  `WishExtra2` text,
  `WishExtra3` text,
  `WishExtra4` text,
  `WishExtra5` text,
  `WishExtra6` text,
  `Wishtalisman` text,
  `Wishshirttyp` text,
  `Wishbelttype` text,
  PRIMARY KEY (`CharName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Thats the table (I'm still a noob and learning so if more info needed please lemme know.

The search code I am CURRENTLY using which provides me with 1 set or results looks like this....

            <form  method="post" action="search_has.php?go"  id="searchform"> 
                <font face="Verdana"> 
                <select size="1" name="name">
                <option selected disabled>Select One...</option>
<?php
include('../../lib/connections/connect-mysql.php');

$sql3="SELECT * FROM armor WHERE wishlist='Yes' Order by grade asc";
$results = mysqli_query($dbcon,$sql3) or die();

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

      echo '<option value="'.$row['upper'].'">'.htmlspecialchars($row['upper']).'</option>'; 
      echo '<option value="'.$row['lower'].'">'.htmlspecialchars($row['lower']).'</option>'; 
      echo '<option value="'.$row['head'].'">'.htmlspecialchars($row['head']).'</option>'; 
      echo '<option value="'.$row['hands'].'">'.htmlspecialchars($row['hands']).'</option>'; 
      echo '<option value="'.$row['feet'].'">'.htmlspecialchars($row['feet']).'</option>'; 
      echo '<option value="'.$row['shield'].'">'.htmlspecialchars($row['shield']).'</option>'; 
  }                         
?>
</select><font size="2"> </font> 
                <input  type="submit" name="submit" value="Search"><font size="2">
                </font></font> 
            </form> 

<table width="40%" style="border-collapse: collapse" border="1" cellspacing="0" cellpadding="0" bordercolor="#7D9BAF">
        <tr>
            <th width="50%" bgcolor="#F2F6F8"><font face="Verdana" size="2">Players Who Have the Item</font></th>
        </tr>
        <tr><td>
<?php
  if(isset($_POST['submit'])){
  if(isset($_GET['go'])){
  if(preg_match("/^[  a-zA-Z]+/", $_POST['name']))
{
  $name=$_POST['name'];

//connect  to the database
  include('../../lib/connections/update-connect.php');

//-query  the database table
  $sql="SELECT * FROM gearowned WHERE  Mainuppertype LIKE '%" . $name ."%' OR Mainlowertype LIKE '%" . $name ."%' OR Mainheadtype LIKE '%" . $name ."%' OR Mainhandtype LIKE '%" . $name ."%' OR Mainfoottype LIKE '%" . $name ."%' OR Mainshldtype LIKE '%" . $name ."%' OR  Mainwep1type LIKE '%" . $name ."%' OR Mainwep2type LIKE '%" . $name ."%' OR   Dualuppertype LIKE '%" . $name ."%' OR Duallowertype LIKE '%" . $name ."%' OR Dualheadtype LIKE '%" . $name ."%' OR Dualhandtype LIKE '%" . $name ."%' OR Dualfoottype LIKE '%" . $name ."%' OR Dualshldtype LIKE '%" . $name ."%' OR   Dualwep1type LIKE '%" . $name ."%' OR Dualwep2type LIKE '%" . $name ."%' OR   MainEar1Type LIKE '%" . $name ."%' OR DualEar1Type LIKE '%" . $name ."%' OR MainEar2Type LIKE '%" . $name ."%' OR DualEar2Type LIKE '%" . $name ."%' OR MainEar3Type LIKE '%" . $name ."%' OR DualEar3Type LIKE '%" . $name ."%' OR   MainNeck1Type LIKE '%" . $name ."%' OR DualNeck1Type LIKE '%" . $name ."%' OR MainNeck2Type LIKE '%" . $name ."%' OR DualNeck2Type LIKE '%" . $name ."%' OR MainRing1Type LIKE '%" . $name ."%' OR DualRing1Type LIKE '%" . $name ."%' OR MainRing2Type LIKE '%" . $name ."%' OR DualRing2Type LIKE '%" . $name ."%' OR MainRing3Type LIKE '%" . $name ."%' OR DualRing3Type LIKE '%" . $name ."%' OR shirttyp LIKE '%" . $name ."%' OR belttype LIKE '%" . $name ."%'";

//-run  the query against the mysql query function
  $result=mysql_query($sql);

//-create  while loop and loop through result set
  while($row=mysql_fetch_array($result)){
          $CharName=$row['CharName'];
          $Mainuppertype=$row['Mainuppertype'];
          $Mainlowertype=$row['Mainlowertype'];
          $Mainheadtype=$row['Mainheadtype'];
          $Mainhandtype=$row['Mainhandtype'];
          $Mainfoottype=$row['Mainfoottype'];
          $Mainshldtype=$row['Mainshldtype'];
          $Mainwep1type=$row['Mainwep1type'];
          $Mainwep2type=$row['Mainwep2type'];
          $Dualuppertype=$row['Dualuppertype'];
          $Duallowertype=$row['Duallowertype'];
          $Dualheadtype=$row['Dualheadtype'];
          $Dualhandtype=$row['Dualhandtype'];
          $Dualfoottype=$row['Dualfoottype'];
          $Dualshldtype=$row['Dualshldtype'];
          $Dualwep1type=$row['Dualwep1type'];
          $Dualwep2type=$row['Dualwep2type'];
          $MainEar1Type=$row['MainEar1Type'];
          $DualEar1Type=$row['DualEar1Type'];
          $MainEar2Type=$row['MainEar2Type'];
          $DualEar2Type=$row['DualEar2Type'];
          $MainEar3Type=$row['MainEar3Type'];
          $DualEar3Type=$row['DualEar3Type'];
          $MainNeck1Type=$row['MainNeck1Type'];
          $DualNeck1Type=$row['DualNeck1Type'];
          $MainNeck2Type=$row['MainNeck2Type'];
          $DualNeck2Type=$row['DualNeck2Type'];
          $MainRing1Type=$row['MainRing1Type'];
          $DualRing1Type=$row['DualRing1Type'];
          $MainRing2Type=$row['MainRing2Type'];
          $DualRing2Type=$row['DualRing2Type'];
          $MainRing3Type=$row['MainRing3Type'];
          $DualRing3Type=$row['DualRing3Type'];
          $shirttyp=$row['shirttyp'];
          $belttype=$row['belttype'];

//-display the result of the array
  echo "<ul>\n";
  echo "<li>" . "<font face='verdana' size='2'>"   .$CharName .  "</font></li>\n";
  echo "</ul>";
  }
  }
  else{
  echo  "<p>Please enter a search query</p>";
  }
  }
  }
?></td></tr>
</table>

I'm using a seperate search atm for those who WISH the item doing a very similar concept except the search queries the Wish table columns instead of the Main or Dual columns

[DJBirdi]

Oh my! So after looking at your schema and your search query, my conclusion about what you're looking for is a way to search your entire table for a specific value (in your case an item user searches for to check which CharName has it and which CharName wishes for it). Something like a SELECT CharName FROM gearowned WHERE * LIKE "$name". Unfortunately, nothing like that exists.

Take a look at cereal's fiddle. See how he distributes player data from item data with relational ID's between them? That would be a better approach to this not only for organizational purposes, but when you have a ton of players, your search queries will take forever in the table you have set up.

I'm guessing you're not looking to redo your entire DB structure however, so I'm sorry to say there's no easy way of doing this. The simplest approach really is the way you've done it already with SQL's OR statement to search each column. Other approaches would be to use SHOW COLUMNS and create a looping query with all column names, or to do a SQLDump of the entire DB and search through that.

[wikit]

I'm not sure I need to actually change the query it works for the most part as is. all I'm trying to do is make a singe search call on both queries (if thats even possible) and post the results to both simultaniously into a 2 cell table. If I'm unable to do this then i'll leave it as is ^^; was just trying to see if I can make it possible to see both lists at one time

[cereal]

So, no matter what you choose from the first form (created from the armor table), you search all the columns of the gearowned table? And then you want to match them together to build a list of character names, correct?

You could use the IN() clause, basically something like this:

SELECT CASE WHEN 'term_to_find' IN(Mainupper, Mainlower, Mainhead) THEN CharName END as HAVE, CASE WHEN 'term_to_find' IN(Wishupper, Wishlower, Wishhead) THEN CharName END as WISH FROM gearowned;

Live example: http://sqlfiddle.com/#!2/02dcd/1

What I'm not sure is why you're using LIKE, are you going to search similar text also? If yes, can you create a fiddle as my example with some inserts? That would help us to understand better your intentions.

By the way, to set null to a column just send it in the insert or update query:

<?php

# $db
require './connections/mysqli.php';
$msg = NULL;

$stmt = mysqli_prepare($db, "INSERT INTO can_be_null SET msg = ?");
mysqli_stmt_bind_param($stmt, 's', $msg);
mysqli_stmt_execute($stmt);

In the database you will see:

> select * from can_be_null;
+----+-------+
| id | msg   |
+----+-------+
|  1 | NULL  |
|  2 | hello |
+----+-------+
2 rows in set (0.00 sec)

[wikit]

reason I'm using LIKE is that the 'wishes' possible via the drop menu do not have the added bonuses available in game (example the drop menu allows you to chose 'Eternal Breastplate' but there are 3 different ones.. there are Eternal Breastplate, Eternal Preastplate Defense Type and Eternal Breastplate Attack Type. The drop menu only has 'Eternal Breastplate' and would search for all 3 cause Eternal breasplate is the base type)

http://sqlfiddle.com/#!2/406aec/3

theres an actual database snippet. I'm didnt fix the entirety of the sql but you get at least the basics of what the search would be

[cereal]

Ok, I'm not sure this is the best approach but it can be done creating two variables in MySQL (@have and @wish) that will stack the player names into separated lists, this is done through group_concat() in two subqueries.

Then, by using the FIND_IN_SET() function in MySQL, we build the list with the two columns HAVE and WISH.

The example queries:

> SET @name = '%Breastplate%', @have = NULL, @wish = NULL;
Query OK, 0 rows affected (0.00 sec)

> SELECT CASE WHEN FIND_IN_SET(CharName, @have) > 0 THEN CharName END AS HAVE, CASE WHEN FIND_IN_SET(CharName, @wish) > 0 THEN CharName END AS WISH FROM gearowned, (SELECT @have := group_concat(CharName) FROM gearowned WHERE Mainuppertype LIKE @name OR Mainlowertype LIKE @name OR Mainheadtype LIKE @name OR Mainhandtype LIKE @name OR Mainfoottype LIKE @name OR Mainshldtype LIKE @name) as subhave, (SELECT @wish := group_concat(CharName) FROM gearowned WHERE Wishuppertype LIKE @name OR Wishlowertype LIKE @name OR Wishheadtype LIKE @name OR Wishhandtype LIKE @name OR Wishfoottype LIKE @name OR Wishshldtype LIKE @name) as subwish HAVING (HAVE is not null OR WISH is not null);
+-------+-------+
| HAVE  | WISH  |
+-------+-------+
| Loot  | NULL  |
| user1 | NULL  |
| NULL  | Wikit |
+-------+-------+
3 rows in set (0.00 sec)

Here's an example with PDO:

<?php

    # $db
    require './connections/pdo.php';
    $name = "%Breastplate%"; # "%{$_POST['name']}%"

    $stmt1 = $db->prepare("SET @name = ?, @have = NULL, @wish = NULL");
    $stmt1->execute(array($name));

    $stmt2 = $db->query("SELECT CASE WHEN FIND_IN_SET(CharName, @have) > 0 THEN CharName END AS HAVE, CASE WHEN FIND_IN_SET(CharName, @wish) > 0 THEN CharName END AS WISH FROM gearowned, (SELECT @have := group_concat(CharName) FROM gearowned WHERE Mainuppertype LIKE @name OR Mainlowertype LIKE @name OR Mainheadtype LIKE @name OR Mainhandtype LIKE @name OR Mainfoottype LIKE @name OR Mainshldtype LIKE @name) as subhave, (SELECT @wish := group_concat(CharName) FROM gearowned WHERE Wishuppertype LIKE @name OR Wishlowertype LIKE @name OR Wishheadtype LIKE @name OR Wishhandtype LIKE @name OR Wishfoottype LIKE @name OR Wishshldtype LIKE @name) as subwish HAVING (HAVE is not null OR WISH is not null)");

    $rows = $stmt2->fetchAll(PDO::FETCH_ASSOC);

    $result = array();
    foreach($rows as $row)
    {
        if(isset($row['HAVE'])) $result['have'][] = "<li>".$row['HAVE']."</li>";
        if(isset($row['WISH'])) $result['wish'][] = "<li>".$row['WISH']."</li>";
    }

?>
<!DOCTYPE html>
<html>
<head>
    <title>Play</title>
    <style type="text/css">

        table
        {
            width:200px;
        }

        th
        {
            text-align:left;
        }

        td
        {
            vertical-align:top;
            text-align:left;
        }

        td ul
        {
            list-style:none;
            text-indent:0;
            padding:0;
            margin:0;
        }
    </style>
</head>
<body>
    <table>
        <thead>
            <tr>
                <th>HAVE</th>
                <th>WISH</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>
                    <ul><?php echo implode("\n", $result['have']); ?></ul>
                </td>
                <td>
                    <ul><?php echo implode("\n", $result['wish']); ?></ul>
                </td>
            </tr>
        </tbody>
    </table>
</body>
</html>

The fiddles:

• with subqueries: http://sqlfiddle.com/#!2/bdc397/13
• with separated queries: http://sqlfiddle.com/#!2/bdc397/11

Just be aware of the group_concat() size limit, which by default is 1024 bytes:

• http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_group_concat_max_len

Note: these examples are not searching all the columns in your table. You should test it a bit as I'm not sure about the performance when using a few thousand of rows. I would probably try to change the database structure to create something similar to the example in my first post, but I don't know enough about your application.. so good luck! :)

[wikit]

I'm very sorry I'm going to give up on this. It's way beyond me and I cant seem to wrap my head around it. Thankyou very much for your time in trying to help me <3