search across many tables (long post)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

search across many tables (long post)

Post by shiznatix »

this really is not small but i could not think of another place to put this and i didnt really want to write a full tutorial for it, its just a script that will let you search across many different tables in a database and let you chose the different WHERE parts of the search. its quite good and flexible (its shown with using only radio buttons, text, and dates for input but i have put in many other things successfully) plus this is show with only having one SELECT thing from it (you may need more which is easily put into this script but i usually have 1 base table with like a user_id and thats all i really need in the end, is the people's user_id that matches the criteria). well sorry if this is a bad place to put this, if someone wants to write a tutorial based off this then be my guest. also feel free to edit and modify this as you please, just make sure you think of me when you do it :lol: .

Code: Select all

<?
    $operators = array
    (
        'a' => '=',
        'b' => '!=',
        'c' => '>',
        'd' => '<',
        'e' => 'LIKE',
    );//these are self explanitory

    //this is the base query, have the base table that you are querying fromand whatever is always going to be in the query here
    $query_base = '
        SELECT
            [qs]
        FROM
            mdl_users AS users
            [qf]
        WHERE
            [qw]
    ';

    $query_map = array
    (
        'qf' => array
        (
            '1' => ' 
                LEFT JOIN
                    mdl_profile AS profile
                ON
                    profile.fk_user_id = users.user_id 
                ',
	        '2' => '
                LEFT JOIN
                    mdl_centerfold AS centerfold
                ON
                    centerfold.fk_user_id = users.user_id
                ',
        ),//add as many as needed, usually 1 per table or group of tables

	    'qw' => array
        (
            '1' => array
            (
                'title' =>      'First Name',
                'field_name' => 'profile.first_name',
                'input_type' => 'text',
                'operators' =>  'a,b,e',
                'fk_qf_id' =>   1,

            ),
            '2' => array
            (
                'title' =>      'Last Name',
                'field_name' => 'profile.last_name',
                'input_type' => 'text',
                'operators' =>  'a,b,e',
                'fk_qf_id' =>   1,
            ),
            '3' => array
            (
                'title' =>      'Birthday',
                'field_name' => 'centerfold.birthday',
                'input_type' => 'date',
                'operators' =>  'a,b,c,d',
                'fk_qf_id' =>   2,
            ),
        ),//these are your conditions, like last name = smith or whatever. add as many as you want and change the input type and the operators to whatever would make sence, make sure the fk_qf_id is correct and so is the field name (field name is table_alias.field_name)
        
        'qs' => array
        (
            '1' => array
            (
                'title' =>      'User Id',
                'field_name' => 'users.user_id',
                'fk_qf_id' =>   1,

            ),
        )//you can add a lot more to the qs part, the form does not have the select part in it but can easily be added by copying the select box from the query where part and changing the appropriate fields, i just have never had to query for more than 1 field in the end
    );

    //set your query maps here
    (false !== isset($_POST['form']['hidden']['query_select']) ? $select_map = $_POST['form']['hidden']['query_select'] : '');

    if (false !== isset($_POST['form']['data']['query_where']))
    {
        $where_map = $_POST['form']['data']['query_where'];
    }
    if (false !== isset($_POST['form']['data']['where_add']))
    {
        if ($_POST['form']['data']['where_add']['qw_id'] != '0')//i added this so the self submitting where form will work properly
        {
            $where_map[]['qw_id'] = $_POST['form']['data']['where_add'];
        }
    }

    //this is for if the user checked the delete checkbox for that certain field and deletes it from the list of stuff
    if (isset($_POST['delete']))
    {
        foreach ($where_map as $key => $value)
        {
            foreach($_POST['delete'] as $del_key => $del_val)
            {
                if ($where_map[$key]['qw_id'] == $del_val)
                {
                    unset($where_map[$key]);
                }
            }
        }
    }

    //if they want to finally do the search
    if (isset($_POST['form']['action']['do_query']))
    {
        $qw = $_POST['form']['data']['query_where'];

        $qs = $_POST['form']['hidden']['query_select'];

        foreach ($qs as $key => $value)
        {
            // query select
            $qs_map[] = $query_map['qs'][$value]['field_name'];
            
            // query FROM
            $qf_map[$query_map['qs'][$value]['fk_qf_id']] = $query_map['qf'][$query_map['qs'][$value]['fk_qf_id']];
        }

        $count = count($qw);
        $counter = 0;

        foreach ($qw as $key => $value)
        {
            $counter++;

            // query WHERE
            if (isset($value['text']))
            {
                $ty = 'text';
            }
            else if (isset($value['radio']))
            {
                $ty = 'radio';
            }
            else if (isset($value['date']))
            {
                $value['date'] = $value['date']['year'].'-'.$value['date']['month'].'-'.$value['date']['day'];
                $ty = 'date';
            }
            
            if (false !== isset($value['and_or']))
            {
                $and_or = ($value['and_or'] == '2' ? 'OR' : 'AND');
            }
            else
            {
                $and_or = 'AND';
            }

            if ($counter == $count)
            {
                unset($and_or);
                $and_or = '';
            }

            $qw_map[] = $query_map['qw'][$value['qw_id']]['field_name'].' '.$operators[$value['operator_id']].' \''.$value[$ty].'\''.' '.$and_or.'';
            
            // query FROM
            $qf_map[$query_map['qw'][$value['qw_id']]['fk_qf_id']] = $query_map['qf'][$query_map['qw'][$value['qw_id']]['fk_qf_id']];
        }

        $query = str_replace('[qs]', implode(',', $qs_map), $query_base);//add in your select parts, only 1 unless you change the script a lil
        $query = str_replace('[qf]', implode(' ', $qf_map), $query);//add in your from parts, based off of your where selections
        $query = str_replace('[qw]', implode(' ', $qw_map), $query);//finally add in your where parts
        
        $do_query = mysql_query($query) or die(mysql_error());//do the query

        while ($info = mysql_fetch_assoc($do_query))
        {
            //display whatever here
        }
    }
    else //show the form if they did NOT click the do search submit button
    {

    ?>
 <TABLE cellspacing="0" cellpadding="2" border="0" width="490" class="outer_table">
 <form name="whatever" method="post" action="index2.php?u=gold&go=search">
 <input type="hidden" name="form[hidden][query_select][]" value="1"><!-- replace this with the select box style if you want multiple select parts -->
      <TR class="header_tr">
      	<TD class="header_td" width="">Search</TD>
      </TR>
      <TR class="inner_tr">
      	<TD class="inner_td">
		  
		   <TABLE cellspacing="0" border="0" class="inner_table" width="100%">
		   <TR class="inner_table_tr_row1">
		   	<TD colspan="3">
            Where:
            <select name="form[data][where_add]" onChange="this.form.submit();">
            <option value="0">-- select one --</option>
                    <?
                        foreach ($query_map['qw'] as $key => $value)
                        {
                            ?><option value="<?= $key ?>"><?= $value['title'] ?></option><?
                        }
                    ?>
                </select>
            </TD>
            <?
            if (isset($where_map) && count($where_map) != 0)
            {
            ?>
                <td>
                Delete
                </td>
                <td>
                AND
                </td>
                <td>
                OR
                </td>
            <?
            }
            ?>
		   </TR>
           <?
        if (false !== isset($where_map))
        {
            foreach ($where_map as $key => $value)
            {
                ?>
                    <tr>
                        <td>
                            <input type="hidden" name="form[data][query_where][<?= $key ?>][qw_id]" value="<?= $value['qw_id'] ?>">
                            <?= $query_map['qw'][$value['qw_id']]['title'] ?>
                        </td>
                        <td>
                            <select name="form[data][query_where][<?= $key ?>][operator_id]" style="width: 52px;">
                                <? 
                                    foreach ($operators as $op_key => $op_value)
                                    {
                                        if (false !== strpos($query_map['qw'][$value['qw_id']]['operators'], $op_key))
                                        {
                                            ?><option value="<?= $op_key ?>" <?= ((false !== isset($_POST['form']['data']['query_where'][$key]['operator_id']) && ($op_key == $_POST['form']['data']['query_where'][$key]['operator_id'])) ? 'SELECTED' : '') ?>><?= $op_value ?></option><?
                                        }
                                    }
                                ?>
                            </select>
                        </td>
                        <td nowrap>
                        <?
                        if ($query_map['qw'][$value['qw_id']]['input_type'] == 'text')
                        {
                        ?>
                            <input type="text" style="width: 200px" name="form[data][query_where][<?= $key ?>][text]" value="<?= ((false !== isset($_POST['form']['data']['query_where'][$key]['operator_id'])) ? $_POST['form']['data']['query_where'][$key]['text'] : '') ?>">
                        <?
                        }
                        else if ($query_map['qw'][$value['qw_id']]['input_type'] == 'radio')
                        {
                            //the yes and no can be replaced with anything such as Male or Female True or False, whatever
                        ?>
                            Yes
                            <input type="radio" name="form[data][query_where][<?= $key ?>][radio]" value="1"
                            <?
                            if (false !== isset($_POST['form']['data']['query_where'][$key]['radio']))
                            { 
                                echo ($_POST['form']['data']['query_where'][$key]['radio'] == '1' ? 'CHECKED' : '');
                            }
                            ?>>
                            No
                            <input type="radio" name="form[data][query_where][<?= $key ?>][radio]" value="0"
                            <?
                            if (false !== isset($_POST['form']['data']['query_where'][$key]['radio']))
                            {
                                echo ($_POST['form']['data']['query_where'][$key]['radio'] == '0' ? 'CHECKED' : '');
                            }
                            ?>>
                        <?
                        }
                        else if ($query_map['qw'][$value['qw_id']]['input_type'] == 'date')
                        {
                        ?>
                            <select name="form[data][query_where][<?= $key ?>][date][day]">
                                <?
                                for ($i=1; $i<=31; $i++)
                                {
                                    echo '<option value="'.$i.'">'.$i.'</option>';
                                }
                                ?>
                            </select>
                            <select name="form[data][query_where][<?= $key ?>][date][month]">
                                <option value="01" 
                                <?= ($_POST['form']['data']['query_where'][$key]['date']['month'] == '01' ? 'SELECTED' : '') ?>>
                                January</option>
                                <option value="02"
                                <?= ($_POST['form']['data']['query_where'][$key]['date']['month'] == '02' ? 'SELECTED' : '') ?>>
                                Febuary</option>
                                <option value="03"
                                <?= ($_POST['form']['data']['query_where'][$key]['date']['month'] == '03' ? 'SELECTED' : '') ?>>
                                March</option>
                                <option value="04"
                                <?= ($_POST['form']['data']['query_where'][$key]['date']['month'] == '04' ? 'SELECTED' : '') ?>>
                                April</option>
                                <option value="05"
                                <?= ($_POST['form']['data']['query_where'][$key]['date']['month'] == '05' ? 'SELECTED' : '') ?>>
                                May</option>
                                <option value="06"
                                <?= ($_POST['form']['data']['query_where'][$key]['date']['month'] == '06' ? 'SELECTED' : '') ?>>
                                June</option>
                                <option value="07"
                                <?= ($_POST['form']['data']['query_where'][$key]['date']['month'] == '07' ? 'SELECTED' : '') ?>>
                                July</option>
                                <option value="08"
                                <?= ($_POST['form']['data']['query_where'][$key]['date']['month'] == '08' ? 'SELECTED' : '') ?>>
                                August</option>
                                <option value="09"
                                <?= ($_POST['form']['data']['query_where'][$key]['date']['month'] == '09' ? 'SELECTED' : '') ?>>
                                September</option>
                                <option value="10"
                                <?= ($_POST['form']['data']['query_where'][$key]['date']['month'] == '10' ? 'SELECTED' : '') ?>>
                                October</option>
                                <option value="11"
                                <?= ($_POST['form']['data']['query_where'][$key]['date']['month'] == '11' ? 'SELECTED' : '') ?>>
                                November</option>
                                <option value="12"
                                <?= ($_POST['form']['data']['query_where'][$key]['date']['month'] == '12' ? 'SELECTED' : '') ?>>
                                December</option>
                            </select>

                            <select name="form[data][query_where][<?= $key ?>][date][year]">
                            <?
                                $year = date("Y");
                                $begin_year = $year-50;

                                for ($i=$begin_year; $i<$year; $i++)
                                {
                                    echo '<option value="'.$i.'"';
                                    echo ($_POST['form']['data']['query_where'][$key]['date']['year'] == $i ? 'SELECTED' : '');
                                    echo '>'.$i.'</option>';

                                    if ($_POST['form']['data']['query_where'][$key]['date']['month'] == $i)
                                    {
                                        $done = 'yes';
                                    }
                                }

                                echo '<option value="'.$year.'"';
                                echo (!isset($done) ? 'SELECTED>' : '>');
                                echo $year.'</option>';
                            ?>
                            </select>
                        <?
                        }
                        ?>
                        </td>
                        <td>
                        <input type="checkbox" name="delete[]" value="<?= $value['qw_id'] ?>">
                        </td>
                        <td>
                        <input type="radio" name="form[data][query_where][<?= $key ?>][and_or]" value="1"
                        <? if (false !== isset($value['and_or'])){ echo ($value['and_or'] != '2' ? 'CHECKED' : ''); } ?>
                        >
                        </td>
                        <td>
                        <input type="radio" name="form[data][query_where][<?= $key ?>][and_or]" value="2"
                        <? if (false !== isset($value['and_or'])){ echo ($value['and_or'] == '2' ? 'CHECKED' : ''); } ?>>                       
                        </td>
                    </tr>
                <?
            }
        }
        ?>
		   </TABLE>
		
		</TD>
      </TR>
      <TR class="footer_tr">
      	<TD class="footer_td" align="right">
        <input type="submit" name="form[action][update_fields]" value="Update">
        <input type="submit" name="form[action][do_query]" value="Search">
        </TD>
      </TR>
      </form>
      </TABLE>
      <?
    }
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Not had a chance to have a decent go at the code, but I'd just like to say I like your choice of database.. mdl_centerfold AS centerfold.. mmm..
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

haha thanks, i know i did put a touch of class in there
Post Reply