Search Query on two tables not working

Why isn't this working for searching?

// Run query on submitted values. Store results in $SESSION and redirect=
to restaurants.php $sql =3D "SELECT name, address, inDate, inType, =
notes, critical, cviolations, noncritical FROM restaurants, inspections WHE=
RE restaurants.name <> '' AND restaurant.ID =3D inspection.ID"; if ($sea=
rchName) { $sql .=3D "AND restaurants.name LIKE '%". mysql_real_esca=
pe_string($name) ."%' "; if(count($name2) == 1) { $sql=
.=3D "AND restaurants.name LIKE '%". mysql_real_escape_string($name2[1]) .=
"%' "; } else { foreach($name2 as $namePart) { =
$sql .=3D "AND restaurants.name LIKE '%". mysql_real_escape_string($nameP=
art) ."%' "; } } } if ($searchAddress) { $sql .=3D "=
AND restaurants.address LIKE '%". mysql_real_escape_string($address) ."%' "=
; } $sql .=3D "ORDER BY restaurants.name;"; =
$result =3D mysql_query($sql);

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search Query on two tables not working

On Tue, Jul 21, 2009 at 12:26 PM, Miller,
Terion wrote:
> Why isn't this working for searching?

Check your concatenation in the query. You need some white space
padding your SQL segments, otherwise the text all starts to run
together.

(I had to reformat it. For some reason, most of the code snippets you
post end up all run together on a single line, at least in Gmail.)


// 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 <> ''
AND restaurant.ID = inspection.ID";

if ($searchName) {
// ADDED SPACE HERE
$sql .= " AND restaurants.name LIKE '%".
mysql_real_escape_string($name) ."%' ";
if(count($name2) == 1) {
// ADDED SPACE HERE
$sql .= " AND restaurants.name LIKE '%".
mysql_real_escape_string($name2[1]) ."%' ";
} else {
foreach($name2 as $namePart) {
// ADDED SPACE HERE
$sql .= " AND restaurants.name LIKE '%".
mysql_real_escape_string($namePart) ."%' ";
}
}
}

if ($searchAddress) {
// ADDED SPACE HERE
$sql .= " AND restaurants.address LIKE '%".
mysql_real_escape_string($address) ."%' ";
}
// ADDED SPACE HERE
$sql .= " ORDER BY restaurants.name;";
$result = mysql_query($sql);


Andrew

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search Query on two tables not working

Turned off the redirects on the whole script and tried to the the query to =
echo and these are the errors I got:

Notice: Undefined offset: 1 in /var/www/vhosts/getpublished.news-leader.com=
/httpdocs/ResturantInspections/processRestaurantSearch.php on line 89

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result=
resource in /var/www/vhosts/getpublished.news-leader.com/httpdocs/Restur an=
tInspections/processRestaurantSearch.php on line 119


On 7/21/09 11:32 AM, "Kevin Smith" wrote:

Can you supply the actual generated SQL, I can potentially see a
problem, but need to see the final SQL statement.

Miller, Terion wrote:
> Why isn't this working for searching?
>
> // Run query on submitted values. Store results in $SESSION and redir=
ect to restaurants.php $sql =3D "SELECT name, address, inDate, inTyp=
e, notes, critical, cviolations, noncritical FROM restaurants, inspections =
WHERE restaurants.name<> '' AND restaurant.ID =3D inspection.ID"; if ($=
searchName) { $sql .=3D "AND restaurants.name LIKE '%". mysql_real_e=
scape_string($name) ."%' "; if(count($name2) == 1) { $=
sql .=3D "AND restaurants.name LIKE '%". mysql_real_escape_string($name2[1]=
) ."%' "; } else { foreach($name2 as $namePart) { =
$sql .=3D "AND restaurants.name LIKE '%". mysql_real_escape_string($na=
mePart) ."%' "; } } } if ($searchAddress) { $sql .=
=3D "AND restaurants.address LIKE '%". mysql_real_escape_string($address) .=
"%' "; } $sql .=3D "ORDER BY restaurants.name;"; =
$result =3D mysql_query($sql);
>
>





--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search Query on two tables not working

--000e0cd6ecccde43c6046f3a00a6
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

>
> 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 <> '' AND restaurant.ID = inspection.ID"; if
> ($searchName) { $sql .= "AND restaurants.name LIKE '%".
> mysql_real_escape_string($name) ."%' "; if(count($name2) == 1) {
> $sql .= "AND restaurants.name LIKE '%".
> mysql_real_escape_string($name2[1]) ."%' "; } else {
> foreach($name2 as $namePart) { $sql .= "AND
> 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;";
> $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;";

Also, you have a semi colon before and after the ending quote.

TRY

$sql .= "AND 1 = 1
ORDER BY restaurants.name";

--000e0cd6ecccde43c6046f3a00a6--

Re: Search Query on two tables not working

On 7/21/09 11:47 AM, "Dan Shirah" wrote:

Why isn't this working for searching?

// Run query on submitted values. Store results in $SESSION and redirect =
to restaurants.php $sql =3D "SELECT name, address, inDate, inType, n=
otes, critical, cviolations, noncritical FROM restaurants, inspections WHER=
E restaurants.name <>=
'' AND restaurant.ID =3D inspection.ID"; if ($searchName) { $sql=
.=3D "AND restaurants.name ame/> LIKE '%". mysql_real_escape_string($name) ."%' "; if(count($n=
ame2) == 1) { $sql .=3D "AND restaurants.name ants.name/> LIKE '%". mysql_real_escape_string($=
name2[1]) ."%' "; } else { foreach($name2 as $namePart) =
{ $sql .=3D "AND restaurants.name p://restaurants.name/> LIKE '%". mysql_real_escape_string($namePart) ."%' =
"; } } } if ($searchAddress) { $sql .=3D "AND restau=
rants.address LIKE '%". mysql_real_escape_string($address) ."%' "; } =
$sql .=3D "ORDER BY restaurants.name <=
http://restaurants.name/> ;"; $result =3D 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 .=3D "ORDER BY restaurants.name urants.name> ;";

Also, you have a semi colon before and after the ending quote.

TRY

$sql .=3D "AND 1 =3D 1
ORDER BY 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/Restur an=
tInspections/processRestaurantSearch.php on line 119

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search Query on two tables not working

On Tue, 2009-07-21 at 12:59 -0400, Miller, Terion wrote:
>=20
>=20
> On 7/21/09 11:47 AM, "Dan Shirah" wrote:
>=20
> Why isn't this working for searching?
>=20
> // Run query on submitted values. Store results in $SESSION and redirec=
t to restaurants.php $sql =3D "SELECT name, address, inDate, inType,=
notes, critical, cviolations, noncritical FROM restaurants, inspections WH=
ERE restaurants.name =
<> '' AND restaurant.ID =3D inspection.ID"; if ($searchName) { $s=
ql .=3D "AND restaurants.name ..name/> LIKE '%". mysql_real_escape_string($name) ."%' "; if(count(=
$name2) == 1) { $sql .=3D "AND restaurants.name urants.name/> LIKE '%". mysql_real_escape_string=
($name2[1]) ."%' "; } else { foreach($name2 as $namePart) =
{ $sql .=3D "AND restaurants.name ttp://restaurants.name/> LIKE '%". mysql_real_escape_string($namePart) ."%=
' "; } } } if ($searchAddress) { $sql .=3D "AND rest=
aurants.address LIKE '%". mysql_real_escape_string($address) ."%' "; } =
$sql .=3D "ORDER BY restaurants.name > ;"; $result =3D mysql_query($sql=
);
> I'm not sure about MySQL, but in Informix my queries will crash when tryi=
ng to just append the ORDER BY clause by itself.
>=20
> $sql .=3D "ORDER BY restaurants.name taurants.name> ;";
>=20
> Also, you have a semi colon before and after the ending quote.
>=20
> TRY
>=20
> $sql .=3D "AND 1 =3D 1
> ORDER BY restaurants.name me> ";
>=20
>=20
>=20
>=20
>=20
>=20
>=20
> Got the query to this point now:
>=20
> Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL resu=
lt resource in /var/www/vhosts/getpublished.news-leader.com/httpdocs/Restur =
antInspections/processRestaurantSearch.php on line 119
>=20
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


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search Query on two tables not working

On 7/21/09 12:04 PM, "Ashley Sheridan" wrote:

On Tue, 2009-07-21 at 12:59 -0400, Miller, Terion wrote:
>
>
> On 7/21/09 11:47 AM, "Dan Shirah" wrote:
>
> Why isn't this working for searching?
>
> // Run query on submitted values. Store results in $SESSION and redirec=
t to restaurants.php $sql =3D "SELECT name, address, inDate, inType,=
notes, critical, cviolations, noncritical FROM restaurants, inspections WH=
ERE restaurants.name ttp://restaurants.name/> <> '' AND restaurant.ID=
=3D inspection.ID"; if ($searchName) { $sql .=3D "AND restaurant=
s.name nts.name/> LIKE '%". mysql_real_escape_string($n=
ame) ."%' "; if(count($name2) == 1) { $sql .=3D "AND r=
estaurants.name /restaurants.name/> LIKE '%". mysql_real_escape_=
string($name2[1]) ."%' "; } else { foreach($name2 as $nameP=
art) { $sql .=3D "AND restaurants.name me/> ..name/> LIKE '%". mysql_real_escape_string($namePart) ."%' "; } =
} } if ($searchAddress) { $sql .=3D "AND restaurants.address L=
IKE '%". mysql_real_escape_string($address) ."%' "; } $sql=
.=3D "ORDER BY restaurants.name nts.name/> ;"; =
$result =3D mysql_query($sql);
> I'm not sure about MySQL, but in Informix my queries will crash when tryi=
ng to just append the ORDER BY clause by itself.
>
> $sql .=3D "ORDER BY restaurants.name taurants.name> ;";
>
> Also, you have a semi colon before and after the ending quote.
>
> TRY
>
> $sql .=3D "AND 1 =3D 1
> ORDER BY restaurants.name me> ";
>
>
>
>
>
>
>
> Got the query to this point now:
>
> Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL resu=
lt resource in /var/www/vhosts/getpublished.news-leader.com/httpdocs/Restur =
antInspections/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.nam=
e LIKE '%A%' AND restaurants.name LIKE '%A%' ORDER BY restaurants' at line =
1

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search Query on two tables not working

--000e0cd1e70e190412046f3a587c
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Tue, Jul 21, 2009 at 12:41 PM, Miller, Terion <
tmiller@springfi.gannett.com> wrote:

> Turned off the redirects on the whole script and tried to the the query to
> echo and these are the errors I got:
>
> Notice: Undefined offset: 1 in /var/www/vhosts/
> getpublished.news-leader.com/httpdocs/ResturantInspections/p rocessRestaurantSearch.phpon line 89
>
> Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result
> resource in /var/www/vhosts/
> getpublished.news-leader.com/httpdocs/ResturantInspections/p rocessRestaurantSearch.phpon line 119
>
>
> On 7/21/09 11:32 AM, "Kevin Smith" wrote:
>
> Can you supply the actual generated SQL, I can potentially see a
> problem, but need to see the final SQL statement.
>
> print_r($sql);

--000e0cd1e70e190412046f3a587c--

Re: Search Query on two tables not working

Here it is...I see where it's doing the restaurant.name LIKE statement 2x w=
hich is prob messing it up right...but in the code why is it doing that twi=
ce..

SELECT name, address, inDate, inType, notes, critical, cviolations, noncrit=
ical FROM restaurants, inspections WHERE restaurants.name <> '' AND restaur=
ant.ID =3D inspection.IDAND restaurants.name LIKE '%A%' AND restaurants.nam=
e LIKE '%A%' ORDER BY restaurants.name;


On 7/21/09 12:11 PM, "Dan Shirah" wrote:

print_r($sql);


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search Query on two tables not working

On Tue, 2009-07-21 at 10:07 -0700, Miller, Terion wrote:
>=20
>=20
> On 7/21/09 12:04 PM, "Ashley Sheridan" wrote:
>=20
> On Tue, 2009-07-21 at 12:59 -0400, Miller, Terion wrote:
> >
> >
> > On 7/21/09 11:47 AM, "Dan Shirah" wrote:
> >
> > Why isn't this working for searching?
> >
> > // Run query on submitted values. Store results in $SESSION and redir=
ect to restaurants.php $sql =3D "SELECT name, address, inDate, inTyp=
e, notes, critical, cviolations, noncritical FROM restaurants, inspections =
WHERE restaurants.name =
<> '' AND restaurant.=
ID =3D inspection.ID"; if ($searchName) { $sql .=3D "AND restaura=
nts.name rants.name/> LIKE '%". mysql_real_escape_string(=
$name) ."%' "; if(count($name2) == 1) { $sql .=3D "AND=
restaurants.name ://restaurants.name/> LIKE '%". mysql_real_escap=
e_string($name2[1]) ."%' "; } else { foreach($name2 as $nam=
ePart) { $sql .=3D "AND restaurants.name name/> ts.name/> LIKE '%". mysql_real_escape_string($namePart) ."%' "; } =
} } if ($searchAddress) { $sql .=3D "AND restaurants.address=
LIKE '%". mysql_real_escape_string($address) ."%' "; } $s=
ql .=3D "ORDER BY restaurants.name rants.name/> ;"; =
$result =3D mysql_query($sql);
> > I'm not sure about MySQL, but in Informix my queries will crash when tr=
ying to just append the ORDER BY clause by itself.
> >
> > $sql .=3D "ORDER BY restaurants.name estaurants.name> ;";
> >
> > Also, you have a semi colon before and after the ending quote.
> >
> > TRY
> >
> > $sql .=3D "AND 1 =3D 1
> > ORDER BY restaurants.name name> ";
> >
> >
> >
> >
> >
> >
> >
> > Got the query to this point now:
> >
> > Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL re=
sult resource in /var/www/vhosts/getpublished.news-leader.com/httpdocs/Rest=
urantInspections/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.
>=20
> Thanks
> Ash
> www.ashleysheridan.co.uk
>=20
>=20
>=20
> I Got this error when I echo'd the sql $results ;
> You have an error in your SQL syntax; check the manual that corresponds t=
o your MySQL server version for the right syntax to use near 'restaurants.n=
ame LIKE '%A%' AND restaurants.name LIKE '%A%' ORDER BY restaurants' at lin=
e 1
>=20
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


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search Query on two tables not working

On Tue, Jul 21, 2009 at 1:20 PM, Miller,
Terion wrote:
> Here it is...I see where it's doing the restaurant.name LIKE statement 2x=
which is prob messing it up right...but in the code why is it doing that t=
wice..
>
> SELECT name, address, inDate, inType, notes, critical, cviolations, noncr=
itical FROM restaurants, inspections WHERE restaurants.name <> '' AND resta=
urant.ID =3D inspection.IDAND restaurants.name LIKE '%A%' AND restaurants.n=
ame LIKE '%A%' ORDER BY restaurants.name;
>
>

It's not the multiple LIKE statements. It is the concatenation, like I
said the last time. There is no white space between "inspection.ID"
and "AND".

You may indeed have problems with some of those repeated conditions on
restaurants.name, but if so they will be performance issues resulting
from table scans, and not the errors you are reporting.

Andrew

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search Query on two tables not working

On Tue, 2009-07-21 at 13:24 -0400, Andrew Ballard wrote:
> On Tue, Jul 21, 2009 at 1:20 PM, Miller,
> Terion wrote:
> > Here it is...I see where it's doing the restaurant.name LIKE statement 2x which is prob messing it up right...but in the code why is it doing that twice..
> >
> > SELECT name, address, inDate, inType, notes, critical, cviolations, noncritical FROM restaurants, inspections WHERE restaurants.name <> '' AND restaurant.ID = inspection.IDAND restaurants.name LIKE '%A%' AND restaurants.name LIKE '%A%' ORDER BY restaurants.name;
> >
> >
>
> It's not the multiple LIKE statements. It is the concatenation, like I
> said the last time. There is no white space between "inspection.ID"
> and "AND".
>
> You may indeed have problems with some of those repeated conditions on
> restaurants.name, but if so they will be performance issues resulting
> from table scans, and not the errors you are reporting.
>
> Andrew
>

It's always better putting in extra whitespace at both ends of each part
of the query you are concatenating if you're unsure. Like Andrew said,
MySQL will balk if you have none between keywords, but I've never heard
it complaining about an extra space or two!

Thanks
Ash
www.ashleysheridan.co.uk


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search Query on two tables not working (RESOLVED)

Yep, sure was the spaces....OMG...will I ever get it...

On 7/21/09 12:29 PM, "Ashley Sheridan" wrote:

On Tue, 2009-07-21 at 13:24 -0400, Andrew Ballard wrote:
> On Tue, Jul 21, 2009 at 1:20 PM, Miller,
> Terion wrote:
> > Here it is...I see where it's doing the restaurant.name LIKE statement =
2x which is prob messing it up right...but in the code why is it doing that=
twice..
> >
> > SELECT name, address, inDate, inType, notes, critical, cviolations, non=
critical FROM restaurants, inspections WHERE restaurants.name <> '' AND res=
taurant.ID =3D inspection.IDAND restaurants.name LIKE '%A%' AND restaurants=
..name LIKE '%A%' ORDER BY restaurants.name;
> >
> >
>
> It's not the multiple LIKE statements. It is the concatenation, like I
> said the last time. There is no white space between "inspection.ID"
> and "AND".
>
> You may indeed have problems with some of those repeated conditions on
> restaurants.name, but if so they will be performance issues resulting
> from table scans, and not the errors you are reporting.
>
> Andrew
>

It's always better putting in extra whitespace at both ends of each part
of the query you are concatenating if you're unsure. Like Andrew said,
MySQL will balk if you have none between keywords, but I've never heard
it complaining about an extra space or two!

Thanks
Ash
www.ashleysheridan.co.uk




--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php