I have a search page that asks for zipcodes between a range of zipcodes. The code shows what I've entered for my SQL in Phakt but I get no results eventhough I do get results thru testing with phpmyadmin.

SQL:
SELECT *
FROM Table1
WHERE ZipCode BETWEEN '%MMColParam%' AND '%MMColParam2%'
ORDER BY ZipCode ASC

Variables:
MMColParam % $HTTP_POST_VARS["ZipCodeStart"]
MMColParam2 % $HTTP_POST_VARS["ZipCodeEnd"]



My ZipCode field was set to varchar(10) but I changed it to int(5) and that didn't help. <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>


Please enlighten me on what I've done wrong.
Thanks in advance. <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>


PMJ

Edited by - pjurkonis on 02 Jan 2002 00:27:30

Edited by - pjurkonis on 02 Jan 2002 00:28:35

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
SELECT *
FROM Table1
WHERE ZipCode BETWEEN '%MMColParam%' AND '%MMColParam2%'
ORDER BY ZipCode ASC
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Try:-

SELECT *
FROM Table1
WHERE ZipCode BETWEEN '%".$MMColParam."%' AND '%"$.MMColParam2."%'
ORDER BY ZipCode ASC

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
SELECT *
FROM Table1
WHERE ZipCode BETWEEN '%MMColParam%' AND '%MMColParam2%'
ORDER BY ZipCode ASC
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Try:-

SELECT *
FROM Table1
WHERE ZipCode BETWEEN '%".$MMColParam."%' AND '%"$.MMColParam2."%'
ORDER BY ZipCode ASC

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

I changed my code as you had it:
SELECT *
FROM Table1
WHERE ZipCode BETWEEN '%".$MMColParam."%' AND '%"$.MMColParam2."%'
ORDER BY ZipCode ASC

I got an error:
Parse error: parse error, expecting "T_VARIABLE" or `'$'' in /home2/hsphere/local/home/pjurkoni/flthou.pal2k.com/results3.php on line 19

Here are lines 18-20 of the code:

(18) $results2=$FamilyLife-&gt;Execute("SELECT * FROM Table1 WHERE ZipCode BETWEEN '%".$" .
($results2__MMColParam) . "."%' AND '%"$." .
($results2__MMColParam2) . "."%' ORDER BY ZipCode ASC" or DIE($FamilyLife-&gt;ErrorMsg());
(19) $results2_numRows=0;
(20) $results2__totalRows=$results2-&gt;RecordCount();


PMJ

Edited by - pjurkonis on 02 Jan 2002 06:04:33

Yep, my fault, there was a typo in my code, which you took into your modification too.

The line:-
WHERE ZipCode BETWEEN '%".$MMColParam."%' AND '%"$.MMColParam2."%'

Should read:-

WHERE ZipCode BETWEEN '%".$MMColParam."%' AND '%".$MMColParam2."%'


Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

That didn't work either:

The SQL:
SELECT *
FROM Table1
WHERE ZipCode BETWEEN '%".$MMColParam."%' AND '%".$MMColParam2."%'
ORDER BY ZipCode ASC

Variables I use:
MMColParam % $HTTP_POST_VARS["ZipCodeStart"]
MMColParam2 % $HTTP_POST_VARS["ZipCodeEnd"]

The error I got:
""Parse error: parse error, expecting `T_VARIABLE' or `'$'' in /home2/hsphere/local/home/pjurkoni/flthou.pal2k.com/results3.php on line 18""

Line 18 of my code:
$results2=$FamilyLife-&gt;Execute("SELECT * FROM Table1 WHERE ZipCode BETWEEN '%".$" . ($results2__MMColParam) . "."%' AND '%".$" . ($results2__MMColParam2) . "."%' ORDER BY ZipCode ASC" or DIE($FamilyLife-&gt;ErrorMsg());

Thanks

PMJ

Well it would seem that line 18 is nothing like the code previously posted, but thankfully it is more obvious (even to the layman) where the problem is.

Line 18 was syntactically incorrect. Compare your original, with the syntactically correct version here:-

$results2=$FamilyLife-&gt;Execute("SELECT * FROM Table1 WHERE ZipCode BETWEEN '%" . ($results2__MMColParam) . "%' AND '%" . ($results2__MMColParam2) . "%' ORDER BY ZipCode ASC" or DIE($FamilyLife-&gt;ErrorMsg());

This should now resolve your issue.

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

I am a newbie to PHP & SQL but not to programming.
Not to be disrespectful but that's what was in my first code and it did'nt work. I cut and pasted what you printed and then looked at the SQL from the recordsetPHP and it was like my first code.

Is there something else going wrong here?

PMJ

OK, I didn't realise that as you only posted the settings earlier.

I've made time to work on this problem today, and going back to your original SQL that you posted. This *should* work. I even created a table with the same columns you used, and took your SQL verbatim.

This then only leaves the Parameters as a problem. What we can do to find out where the root cause is to simply take your SQL and output it to the page.

To do this, go into code view, and just above the line:-

$results2=$FamilyLife-&gt;Execute("SELECT * FROM Table1 WHERE ZipCode BETWEEN '%".$" .
($results2__MMColParam) . "."%' AND '%"$." .
($results2__MMColParam2) . "."%' ORDER BY ZipCode ASC" or DIE($FamilyLife-&gt;ErrorMsg());

enter the following:-

echo "SELECT * FROM Table1 WHERE ZipCode BETWEEN '%" . ($results2__MMColParam) . "%' AND '%" . ($results2__MMColParam2) . "%' ORDER BY ZipCode ASC";

This will output the SQL to the screen and will allow you to see why the syntax error is being created. In this instance it seems likely that it might be contaminated data of some form.



Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
OK, I didn't realise that as you only posted the settings earlier.

I've made time to work on this problem today, and going back to your original SQL that you posted. This *should* work. I even created a table with the same columns you used, and took your SQL verbatim.

This then only leaves the Parameters as a problem. What we can do to find out where the root cause is to simply take your SQL and output it to the page.

To do this, go into code view, and just above the line:-

$results2=$FamilyLife-&gt;Execute("SELECT * FROM Table1 WHERE ZipCode BETWEEN '%".$" .
($results2__MMColParam) . "."%' AND '%"$." .
($results2__MMColParam2) . "."%' ORDER BY ZipCode ASC" or DIE($FamilyLife-&gt;ErrorMsg());

enter the following:-

echo "SELECT * FROM Table1 WHERE ZipCode BETWEEN '%" . ($results2__MMColParam) . "%' AND '%" . ($results2__MMColParam2) . "%' ORDER BY ZipCode ASC";

This will output the SQL to the screen and will allow you to see why the syntax error is being created. In this instance it seems likely that it might be contaminated data of some form.



Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Tim, I tried adding that line in my code but no matter what I did I lost my RecordSetPHP(results2).

I think what I'll do is dump my data and upload it again. What type of field do you recommend that to be set to (int, varchar, etc) so that search routine works the best?

PMJ
------
‘Men of Integrity expect to be belived,
and when they are not,
they let time prove them right.’

You're probably best getting used to using varchar fields for zip codes. In an International sense, not all zip codes are numeric <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>