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>
<?
}