Re: Search Query on two tables not working

On Tue, 2009-07-21 at 10:07 -0700, Miller, Terion wrote: > > > On 7/21/09 12:04 PM, "Ashley Sheridan" <ash@ashleysheridan.co.uk> wrote: > > On Tue, 2009-07-21 at 12:59 -0400, Miller, Terion wrote: > > > > > > On 7/21/09 11:47 AM, "Dan Shirah" <mrsquash2@gmail.com> wrote: > > > > Why isn't this working for searching? > > > > // Run query on submitted values. Store results in $SESSION and redirect to > > restaurants.php $sql = "SELECT name, address, inDate, inType, notes, critical, > > cviolations, noncritical FROM restaurants, inspections WHERE restaurants.name > > <http://restaurants.name/><http://restaurants.name/°í› > > ûÞ»⊳šÉp¤><http://restaurants.name/><· > > êÃÃ
2ô_í è&¬http://restaurants.name/> <> '' AND restaurant.ID > > = inspection.ID"; if ($searchName) { $sql .= "AND restaurants.name > > <http://restaurants.name/><http://restaurants.name/><http://restaurants.name/><http://restaurants.name/> > > LIKE '%". mysql_real_escape_string($name) ."%' "; if(count($name2) > > == 1) { $sql .= "AND restaurants.name > > <http://restaurants.name/><http://restaurants.name/><http://restaurants.name/><http://restaurants.name/> > > LIKE '%". mysql_real_escape_string($name2[1]) ."%' "; } else { > > foreach($name2 as $namePart) { $sql .= "AND restaurants.name > > <http://restaurants.name/><http://restaurants.name/><http://restaurants.name/><http://restaurants.name/> > > LIKE '%". mysql_real_escape_string($namePart) ."%' "; } } } > > if ($searchAddress) { $sql .= "AND restaurants.address LIKE '%". > > mysql_real_escape_string($address) ."%' "; } $sql .= "ORDER BY > > restaurants.name > > <http://restaurants.name/><http://restaurants.name/><http://restaurants.name/><http://restaurants.name/> > > ;"; $result = mysql_query($sql); > > I'm not sure about MySQL, but in Informix my queries will crash when trying to just > > append the ORDER BY clause by itself. > > > > $sql .= "ORDER BY restaurants.name > > <http://restaurants.name><http://restaurants.name><http://restaurants.name><http://restaurants.name> > > ;"; > > > > Also, you have a semi colon before and after the ending quote. > > > > TRY > > > > $sql .= "AND 1 = 1 > > ORDER BY restaurants.name > > <http://restaurants.name><http://restaurants.name><http://restaurants.name><http://restaurants.name> > > "; > > > > > > > > > > > > > > > > Got the query to this point now: > > > > Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in > > /var/www/vhosts/getpublished.news-leader.com/httpdocs/ResturantInspections/processRestaurantSearch.php > > on line 119 > > > That means your query is invalid. Try printing the query out and posting > it here so that we can see it. > > Thanks > Ash > www.ashleysheridan.co.uk > > > > I Got this error when I echo'd the sql $results ; > You have an error in your SQL syntax; check the manual that corresponds to your MySQL server > version for the right syntax to use near 'restaurants.name LIKE '%A%' AND > restaurants.name LIKE '%A%' ORDER BY restaurants' at line 1 > Yes, but that's not your query. The problem is with your query. If you echo the query, we can see where it might be falling over. Thanks Ash www.ashleysheridan.co.uk