Page 1 of 1

adding search box to page

Posted: Mon Apr 07, 2008 3:57 pm
by sbs
~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


I am getting a database error here. Any suggestions?

Code: Select all

<?php
// index.php    3/27/07
// Main Menu for Clients
 
$page = 'active';
include 'include/init.php';
include 'include/pager.class.php';
 
if ($_GET['show'] == 'past') {
    $filter .= " where t.status = 'closed'";
    $page = 'past';
    $title = 'Past Tickets';
}
else {
    $filter .= " where t.status != 'closed'";
    $page = 'active';
    $title = 'Active Tickets';
}
include 'include/header.php';
 
$pager = new Pager();
$pager->link = 'tickets.php' . ($_GET['show'] == 'past' ? '?show=past&' : '?');
$pager->total = ($_GET['show'] == 'past' ? $past_tickets : $active_tickets);
$pager->main_sql =  "(select t.time_pending as tp, t.ref, t.client_id, t.originator_client_id, t.problem, concat(y.name, ' #', ref) as info,".
        " concat_ws(' ', c.fname, c.lname) as client_name, concat_ws(' ', co.fname, co.lname) as originator_name,".
        " s.name as status_str, date_format(time_opened, '%l:%i%p %b %e, %Y') as time_opened,".
        " date_format(time_pending, '%l:%i%p %b %e, %Y') as time_pending from ticket t".
        " left join client c on c.client_id = t.client_id".
        " left join client co on co.client_id = t.originator_client_id".
        " left join tech on tech.tech_id = t.tech_id".
        " left join ticket_type y on t.type_id = y.type_id".
        " left join ticket_status s on s.status = t.status " .
        $filter . " and t.client_id = '" . $_SESSION['ticket_client_id'] .
        "') union (select t.time_pending as tp, t.ref, t.client_id, t.originator_client_id, t.problem, concat(y.name, ' #', ref) as info,".
        " concat_ws(' ', c.fname, c.lname) as client_name, concat_ws(' ', co.fname, co.lname) as originator_name,".
        " s.name as status_str, date_format(time_opened, '%l:%i%p %b %e, %Y') as time_opened,".
        " date_format(time_pending, '%l:%i%p %b %e, %Y') as time_pending from ticket t".
        " left join client c on c.client_id = t.client_id".
        " left join client co on co.client_id = t.originator_client_id".
        " left join tech on tech.tech_id = t.tech_id".
        " left join ticket_type y on t.type_id = y.type_id".
        " left join ticket_status s on s.status = t.status " .
        $filter . " and t.originator_client_id = '" . $_SESSION['ticket_client_id'] .
        "') order by tp desc";
//echo $pager->main_sql;
$results = $pager->run();
$res = mysql_query($sql) or die(mysql_error());
 
?>
 
<h2><?= $title; ?></h2>
 
<?=
$sql = "SELECT * FROM t WHERE ref LIKE '$string' OR problem LIKE '%'.$string.'%' ORDER BY ref";
$query = mysql_query($sql) or die(mysql_error());
$row_sql = mysql_fetch_assoc($query);
$total = mysql_num_rows($query);
 
if($total>0) {
    while ($row_sql = mysql_fetch_assoc($query)) {//echo out the results
    echo '';?><a href="ticket.php?id=<?= $row_sql['ref'];?>" style="position: absolute; right: 10px" >More &raquo;</a>
        </h3>
        <div class="ticket-header">
        <b>Date Opened:</b>&nbsp; <?= $row_sql['time_opened']; ?>,
        &nbsp;&nbsp;&nbsp;
        <b>Last Reply:</b>&nbsp; <?= $row_sql['time_pending']; ?>,
        &nbsp;&nbsp;&nbsp;
        <b>Status:</b>&nbsp; <?= $row_sql['status_str']; ?>
        </div>
        <div class="inner">
        <?= str_shorten($row_sql['problem'], 100); ?>
                </div>
            </div>'';
 
<?
}
} else
    {
    echo "No results to display";
}
?>
 
<table class="form" action="html_form_action.asp" method="get" cellspacing="0">
    <tr>
        <th width="100"></th>
        <td><input type="text" class="input" name="string" id="string" value="<?= $string; ?>" style="width: 200px" />
        &nbsp;&nbsp;<input class="btn" type="submit" value="Search" />
        </td>
    </tr>
</table>
 
<div id="main-content">
    <p style="padding: 5px 10px; border-bottom: 1px solid #eee">Below are are your <?= $count; ?> <b><?= $title; ?></b>:</p>
<?echo "test";?>
<? $pager->print_links(); ?>
 
<?
while ($t = mysql_fetch_assoc($results)) {
?>
    <div class="ticket" onmouseover="this.className='ticket-on'" onmouseout="this.className='ticket'" onclick="window.location='ticket.php?id=<?= $t['ref']; ?>'">
        <h3 style="position: relative"><?= $t['info']; ?>
<?
if ($t['client_id'] != $_SESSION['ticket_client_id'])
    echo ' (Requested by: ' . $t['client_name'] . ')';
else if ($t['client_id'] != $t['originator_client_id'])
    echo ' (On behalf of: ' . $t['originator_name'] . ')';
?>
        <a href="ticket.php?id=<?= $t['ref']; ?>" style="position: absolute; right: 10px">More &raquo;</a>
        </h3>
        <div class="ticket-header">
        <b>Date Opened:</b>&nbsp; <?= $t['time_opened']; ?>,
        &nbsp;&nbsp;&nbsp;
        <b>Last Reply:</b>&nbsp; <?= $t['time_pending']; ?>,
        &nbsp;&nbsp;&nbsp;
        <b>Status:</b>&nbsp; <?= $t['status_str']; ?>
        </div>
        <div class="inner">
        <?= str_shorten($t['problem'], 100); ?>
        </div>
    </div>
<?
}
?>
    <p style="padding: 5px 10px; border-bottom: 1px solid #eee"></p>
<? $pager->print_links(); ?>
</div>
<?
include 'include/footer.php';
?>

~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.

Re: adding search box to page

Posted: Mon Apr 07, 2008 4:44 pm
by andym01480
From which query and what is mysql_error() saying?

Re: adding search box to page

Posted: Mon Apr 07, 2008 4:55 pm
by sbs
SELECT * FROM t WHERE ref = '' OR problem LIKE '%'..'%' ORDER BY ref DESCYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '..'%' ORDER BY ref DESC' at line 1

This is what the page says below "Past tickets".

The problem appears to be in this sentence: SELECT * FROM t WHERE ref LIKE '' OR problem LIKE '%'..'%' ORDER BY ref

Re: adding search box to page

Posted: Tue Apr 08, 2008 5:01 am
by andym01480
That's a relief - didn't like the look of the other query!

Code: Select all

$sql = "SELECT * FROM t WHERE ref LIKE '$string' OR problem LIKE '%'.$string.'%' ORDER BY ref";
The error is '%'.$string.'%' - you have enclosed the whole string in double quotes, so don't need a concatenation dot and you don't need all those single quotes in the middle either.

Code: Select all

$sql = "SELECT * FROM t WHERE ref LIKE '$string' OR problem LIKE '%{$string}%' ORDER BY ref";
or

Code: Select all

$sql = "SELECT * FROM t WHERE ref LIKE '$string' OR problem LIKE '%.{$string}.%' ORDER BY ref";
if those dots weren't for concatenation!

You may not even need the {} try it both ways...

Re: adding search box to page

Posted: Tue Apr 08, 2008 3:52 pm
by sbs
Now I'm getting many other errors. Any suggestions? I know the search form should be a form and not a table. Anything else you see wrong?

Re: adding search box to page

Posted: Wed Apr 09, 2008 2:13 pm
by sbs
Here is the code. Sorry, I can't figure out how to format it.
Two lines are commented out so that there is not an overflow of data that crashes the server. This is the output: SELECT * FROM ticket WHERE ref = '' OR problem LIKE '%%' ORDER BY ref DESCNo results to display

There is a search box that appears but it doesn't produce any search results. The tickets to be searched appear below the search box.

<?php
// index.php 3/27/07
// Main Menu for Clients

$page = 'active';
include 'include/init.php';
include 'include/pager.class.php';

if ($_GET['show'] == 'past') {
$filter .= " where t.status = 'closed'";
$page = 'past';
$title = 'Past Tickets';
}
else {
$filter .= " where t.status != 'closed'";
$page = 'active';
$title = 'Active Tickets';
}
include 'include/header.php';

$pager = new Pager();
$pager->link = 'tickets.php' . ($_GET['show'] == 'past' ? '?show=past&' : '?');
$pager->total = ($_GET['show'] == 'past' ? $past_tickets : $active_tickets);
$pager->main_sql = "(select t.time_pending as tp, t.ref, t.client_id, t.originator_client_id, t.problem, concat(y.name, ' #', ref) as info,".
" concat_ws(' ', c.fname, c.lname) as client_name, concat_ws(' ', co.fname, co.lname) as originator_name,".
" s.name as status_str, date_format(time_opened, '%l:%i%p %b %e, %Y') as time_opened,".
" date_format(time_pending, '%l:%i%p %b %e, %Y') as time_pending from ticket t".
" left join client c on c.client_id = t.client_id".
" left join client co on co.client_id = t.originator_client_id".
" left join tech on tech.tech_id = t.tech_id".
" left join ticket_type y on t.type_id = y.type_id".
" left join ticket_status s on s.status = t.status " .
$filter . " and t.client_id = '" . $_SESSION['ticket_client_id'] .
"') union (select t.time_pending as tp, t.ref, t.client_id, t.originator_client_id, t.problem, concat(y.name, ' #', ref) as info,".
" concat_ws(' ', c.fname, c.lname) as client_name, concat_ws(' ', co.fname, co.lname) as originator_name,".
" s.name as status_str, date_format(time_opened, '%l:%i%p %b %e, %Y') as time_opened,".
" date_format(time_pending, '%l:%i%p %b %e, %Y') as time_pending from ticket t".
" left join client c on c.client_id = t.client_id".
" left join client co on co.client_id = t.originator_client_id".
" left join tech on tech.tech_id = t.tech_id".
" left join ticket_type y on t.type_id = y.type_id".
" left join ticket_status s on s.status = t.status " .
$filter . " and t.originator_client_id = '" . $_SESSION['ticket_client_id'] .
"') order by tp desc";
//echo $pager->main_sql;
$results = $pager->run();
$res = mysql_query($sql) or die(mysql_error());

?>

<h2><?= $title; ?></h2>

<?=
$sql = "SELECT * FROM ticket WHERE ref = '$string' OR problem LIKE '%".$string."%' ORDER BY ref DESC";
$query = mysql_query($sql) or die(mysql_error());
//$row_sql = mysql_fetch_assoc($query);
//$total = mysql_num_rows($query);

if($total>0) {
while ($row_sql = mysql_fetch_assoc($query)) {//echo out the results
echo '';?><a href="ticket.php?id=<?= $row_sql['ref'];?>" style="position: absolute; right: 10px" >More &raquo;</a>
</h3>
<div class="ticket-header">
<b>Date Opened:</b>&nbsp; <?= $row_sql['time_opened']; ?>,
&nbsp;&nbsp;&nbsp;
<b>Last Reply:</b>&nbsp; <?= $row_sql['time_pending']; ?>,
&nbsp;&nbsp;&nbsp;
<b>Status:</b>&nbsp; <?= $row_sql['status_str']; ?>
</div>
<div class="inner">
<?= str_shorten($row_sql['problem'], 100); ?>
</div>
</div>'';

<?
}
} else
{
echo "No results to display";
}
?>

<table class="form" action="html_form_action.asp" method="get" cellspacing="0">
<tr>
<th width="100"></th>
<td><input type="text" class="input" name="string" id="string" value="<?= $string; ?>" style="width: 200px" />
&nbsp;&nbsp;<input class="btn" type="submit" value="Search" />
</td>
</tr>
</table>

<div id="main-content">
<p style="padding: 5px 10px; border-bottom: 1px solid #eee">Below are are your <?= $count; ?> <b><?= $title; ?></b>:</p>
<?echo "test";?>
<? $pager->print_links(); ?>

<?
while ($t = mysql_fetch_assoc($results)) {
?>
<div class="ticket" onmouseover="this.className='ticket-on'" onmouseout="this.className='ticket'" onclick="window.location='ticket.php?id=<?= $t['ref']; ?>'">
<h3 style="position: relative"><?= $t['info']; ?>
<?
if ($t['client_id'] != $_SESSION['ticket_client_id'])
echo ' (Requested by: ' . $t['client_name'] . ')';
else if ($t['client_id'] != $t['originator_client_id'])
echo ' (On behalf of: ' . $t['originator_name'] . ')';
?>
<a href="ticket.php?id=<?= $t['ref']; ?>" style="position: absolute; right: 10px">More &raquo;</a>
</h3>
<div class="ticket-header">
<b>Date Opened:</b>&nbsp; <?= $t['time_opened']; ?>,
&nbsp;&nbsp;&nbsp;
<b>Last Reply:</b>&nbsp; <?= $t['time_pending']; ?>,
&nbsp;&nbsp;&nbsp;
<b>Status:</b>&nbsp; <?= $t['status_str']; ?>
</div>
<div class="inner">
<?= str_shorten($t['problem'], 100); ?>
</div>
</div>
<?
}
?>
<p style="padding: 5px 10px; border-bottom: 1px solid #eee"></p>
<? $pager->print_links(); ?>
</div>
<?
include 'include/footer.php';
?>