Hi I have created the following search script which works great. I need to be able to export the results of the search to a excel spreadsheet which I can do with normal pages but as this page brings up a lot of different results its not that straight forward.
Does anyone know how I would pass the sql query to export.php
Thanks
Does anyone know how I would pass the sql query to export.php
Thanks
Code:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Contacts Search</title>
<link rel="STYLESHEET" type="text/css" href="style.css">
</head>
<body leftmargin=15>
<?php
import_request_variables("gPC","");
$host = "localhost";
$dbuser = "username";
$dbpassword = "password";
#mailapp db settings
$sugardb = "`db`";
$sugartable = "table";
echo "<div class='title'>Search Contacts</div>" ;
if ($limit == "")
{
$limit=500;
}
?>
<hr>
<table width="80%" border="1" cellspacing="0" cellpadding="4" bordercolor="#C0C0C0" align="center">
<form action="" method="post">
<tr>
<td colspan='4'><strong>Filter results</strong></td>
</tr>
<tr>
<td width="15%" bgcolor='#F2F2F2'><strong>First Name </strong></td>
<td width="35%" bgcolor='#F2F2F2'><input type="text" name="fname" value="<?php echo $fname ?>">
</td>
<td width="15%" bgcolor='#F2F2F2'><strong>Last Name </strong></td>
<td width="35%" bgcolor='#F2F2F2'><input type="text" name="lname" value="<?php echo $lname ?>">
</td>
</tr>
<tr>
<td><strong>Email </strong></td>
<td><input type="text" name="email" value="<?php echo $email ?>">
</td>
<td><strong>Job Title</strong></td>
<td><input type="text" name="jobtitle" value="<?php echo $jobtitle ?>">
</td>
</tr>
<tr>
<td bgcolor='#F2F2F2'><strong>Company Name </strong></td>
<td bgcolor='#F2F2F2'>
<input type="text" name="oname" value="<?php echo $oname ?>">
</td>
<td bgcolor='#F2F2F2'><strong>Company Type</strong></td>
<td bgcolor='#F2F2F2'>
<select name="orgtype">
<?php
$handle_db1 = mysql_connect($host,$dbuser,$dbpassword);
mysql_select_db('table',$handle_db1);
### get all org types
$selected = "";
$sql3 = "select account_type from $sugardb.accounts group by account_type order by account_type";
$result3 = mysql_query( $sql3);
echo mysql_error();
while ($row3 = mysql_fetch_row($result3))
{
$accounttype = $row3[0];
if ($accounttype == $orgtype)
{
$selected = "SELECTED";
}
print "<option value='$accounttype' $selected>$accounttype</option>";
$selected = "";
}
?>
</select>
</td>
</tr>
<tr>
<td><strong>Address</strong> </td>
<td><input type="text" name="address" value="<?php echo $address ?>">
</td>
<td><strong>City </strong></td>
<td><input type="text" name="city" value="<?php echo $city ?>">
</td>
</tr>
<tr>
<td bgcolor='#F2F2F2'><strong>County </strong></td>
<td bgcolor='#F2F2F2'><input type="text" name="county" value="<?php echo $county ?>">
</td>
<td bgcolor='#F2F2F2'><strong>Membership Start Date</strong></td>
<td bgcolor='#F2F2F2'><input type="text" name="start_date" value="<?php echo $start_date ?>">
</td>
</tr>
<tr>
<td><strong>Primary Contact</strong></td>
<td><input type="checkbox" name="primary" value="on"
<?php
if ($primary != "")
{
echo " checked ";
}
?>
>
</td>
<td><strong>Paid Member</strong></td>
<td><input type="checkbox" name="paid_mem" value="on"
<?php
if ($paid_mem != "")
{
echo " checked ";
}
?>
>
</td>
</tr>
<tr>
<td bgcolor='#F2F2F2'><strong>Number of results </strong></td>
<td bgcolor='#F2F2F2'><input type="text" name="limit" value="500">
</td>
<td bgcolor='#F2F2F2'><strong>Group by Company</strong></td>
<td bgcolor='#F2F2F2'><input type="checkbox" name="group" value="on"
<?php
if ($group != "")
{
echo " checked ";
}
?>
>
</td>
</tr>
<tr>
<td colspan='4'><input type="submit" value="Search">
</td>
</tr>
</form>
</table>
<hr>
<table width="80%" border="1" cellspacing="0" cellpadding="4" bordercolor="#C0C0C0" align="center">
<tr>
<!-- <td> </td> -->
<td><strong>Name</strong></td>
<td><strong>Email</strong></td>
<td><strong>Company</strong></td>
</tr>
<?php
###List all current mails and allow editing,sending
if ($fname != ""){$fnamesql = "contacts.first_name like '%$fname%'";}
if ($lname != ""){$lnamesql = "and contacts.last_name like '%$lname%'";}
if ($email != ""){$emailsql = "and contacts.email1 like '%$email%'";}
if ($oname != ""){$onamesql = "and accounts.name like '%$oname%'";}
if ($address != ""){$addresssql = "and accounts.billing_address_street like '%$address%'";}
if ($city != ""){$citysql = "and accounts.billing_address_city like '%$city%'";}
if ($county != ""){$countysql = "and accounts.billing_address_state like '%$county%'";}
if ($jobtitle != ""){$jobtitlesql = "and contacts.title like '%$jobtitle%'";}
if ($orgtype != ""){$orgtypesql = "and accounts.account_type like '%$orgtype%'";}
if ($primary != ""){$primarysql = "and contacts_cstm.primarycontact_c = '1'";}
if ($paid_mem != ""){$paidsql = "and contacts_cstm.Paid_Member_c = '1'";}
if ($start_date != ""){$startsql = "and contacts_cstm.Membership_Start_Date_c like '%$start_date%'";}
if ($group != ""){$groupsql = "group by accounts.name";}
$sql = "select contacts.id, contacts.first_name, contacts.last_name, contacts.email1, accounts.name
from contacts
LEFT JOIN users ON contacts.assigned_user_id=users.id
LEFT JOIN accounts_contacts ON contacts.id=accounts_contacts.contact_id
LEFT JOIN accounts ON accounts_contacts.account_id=accounts.id
LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c
where $fnamesql $lnamesql $emailsql $onamesql $addresssql $citysql $countysql $jobtitlesql $orgtypesql $primarysql $paidsql $startsql and contacts.deleted = '0' $groupsql
order by accounts.name asc limit 0,$limit";
##now get rid of any occurence of "where and" in the sql, this will happen if firstname is not used in the search
$sql = str_replace(' ', '', $sql);
$sql = str_replace('whereand', 'where', $sql);
$sql = str_replace('where and', 'where', $sql);
$sql = str_replace('whereorder', 'where 1 order', $sql);
print "<!-- \n\n$sql\n\n -->";
$result = mysql_query( $sql,$handle_db1);
echo mysql_error();
while ($row = mysql_fetch_row($result))
{
$contactid = $row[0];
$name = "$row[1] $row[2]";
$resemail = $row[3];
$company = $row[4];
$ismember="";
$buttonname = "Add";
##get contact name
$sql2 = "select * from $sugardb.MAILAPP_groupxref where contact = '$contactid' and groupid = '$groupid'";
$result2 = mysql_query( $sql2);
echo mysql_error();
while ($row2 = mysql_fetch_row($result2))
{
$ismember="checked";
$buttonname = "Remove";
}
##get contact email
if ($ismember == "")
{
if ($colourflag == 0)
{
$bgcol = "#F2F2F2";
$colourflag = 1;
}
else
{
$bgcol = "#FFFFFF";
$colourflag = 0;
}
print"<tr><td bgcolor='$bgcol'><a name='item$resultcount'><a href='/index.php?module=Contacts&action=DetailView&record=$contactid' target='_top'>$name</a> </td><td bgcolor='$bgcol'>$resemail </td><td bgcolor='$bgcol'>$company </td>
<input type='hidden' name='groupid' value='$groupid'>
<input type='hidden' name='fname' value='$fname'>
<input type='hidden' name='lname' value='$lname'>
<input type='hidden' name='oname' value='$oname'>
<input type='hidden' name='email' value='$email'>
<input type='hidden' name='address' value='$address'>
<input type='hidden' name='city' value='$city'>
<input type='hidden' name='county' value='$county'>
<input type='hidden' name='groupname' value='$groupname'>
</tr></form>";
$resultcount++;
}
}
print "</table>$resultcount results<br><br><br><a href='export.php?query=$sql'>Export</a>";
?>
</table>
<br>
<br>
</body>
</html>