Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macintosh variables in sql query's

    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.

    Code:
    <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%') ";

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    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

  • #3
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Code:
    $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 ";

  • #4
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    I meant the left hand side of the 'equation'
    (A=B <= A is the 'left hand side', B is the 'right hand side')

  • #5
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ahh I got yah.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •