...

View Full Version : variables in sql query's



jedimastermopar
11-28-2006, 03:35 PM
ok so I have been trying to get my sql query to run searches. I have it working is I manualy enter in the field name to search under but when I have th value inut from a dropdown box it doesn't work. Any ideas? The non working line is commented out.



<form name="form" action="contactlookup.php" method="get">
Look for
<input name="q" type="text"/>
under
<label>
<select name="column">
<option value="company_0.CompanyName">Company Name</option>
<option value="contact_0.Name">Contact Name</option>
<option value="branch_0.Address">Address</option>
<option value="branch_0.City">City</option>
<option value="branch_0.PostalCode">Postal Code</option>
<option value="branch_0.Phone">Phone</option>
<option value="branch_0.Fax">Fax</option>
<option value="company_0.CompanyID">Company ID</option>
<option value="branch_0.BranchID">Branch ID</option>
<option value="contact_0.ContactID">Contact ID</option>
</select>
</label>
<input type="submit" name="Submit" value="Search" />
</form>

<?php
$var = @$_GET['q'] ;
$trimmed = trim($var);
$searched = @$_GET['column'] ;

@mysql_connect("x.x.x.x","user","supercoolpass");
@mysql_select_db("superdb") or die("Can not find database");

echo 'Currently showing all ' . $trimmed . ' matches under the ' . $searched . ' column.';

$rowsPerPage = 25;
$pageNum = 1;

if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

$offset = ($pageNum - 1) * $rowsPerPage;

$query = "SELECT company_0.CompanyID,
company_0.CompanyName,
branch_0.Address,
branch_0.City,
branch_0.PostalCode,
branch_0.Phone,
branch_0.Fax,
branch_0.BranchID,
contact_0.ContactID,
contact_0.Name
FROM soileng.branch branch_0,
soileng.company company_0,
soileng.contact contact_0
WHERE company_0.CompanyID = branch_.
ParentCompanyID AND
branch_0.BranchID = company_0.MainBranchID AND
contact_0.ContactID = branch_0.MainContactID AND
branch_0.BranchID = contact_0.ParentBranchID AND
(branch_0.City LIKE '%$trimmed%') ";
//('%$searched%' LIKE '%$trimmed%') ";

GJay
11-28-2006, 04:03 PM
the % signs act as a wildcard when doing comparisons, they belong on the right hand side but not the left.
you really need to use mysql_real_escape_string on all user-input

http://php.net/mysql-real-escape-string

jedimastermopar
11-28-2006, 05:18 PM
I got it to work, I put the % on both sides so it can find matches on either side of the search eg
Search string Town
will return Town of Oranges and Orange Town

Here is my final sql query that I got working


$query = "SELECT contact_0.Name,
contact_0.Email,
company_0.CompanyName,
branch_0.Address,
branch_0.City,
branch_0.Province,
branch_0.PostalCode,
branch_0.Phone,
contact_0.Cell,
branch_0.Fax,
company_0.CompanyID,
branch_0.BranchID,
contact_0.ContactID
FROM soileng.branch branch_0,
soileng.company company_0,
soileng.contact contact_0
WHERE contact_0.ParentBranchID = branch_0.BranchID AND
branch_0.ParentCompanyID = company_0.CompanyID AND
$searched LIKE '%$trimmed%'
ORDER BY $searched ";

GJay
11-29-2006, 01:52 PM
I meant the left hand side of the 'equation'
(A=B <= A is the 'left hand side', B is the 'right hand side')

jedimastermopar
11-29-2006, 01:57 PM
Ahh I got yah.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum