"Select" & "Order By" OK- ''WHERE'' Does'nt Work !

[ian justice]

Before i post actual code, as i need a speedyish reply.
Can i first ask if anyone knows off the top of their head, if there is
a likely obvious cause to the following problem.
For the moment i've reduced my form request to a simple text string
entry, instead of my desired optional parameters. As i have been stuck
with a single unfathomable glitch for over a year.
Basically, if i enter queries such as ;
"select * from table" "select * from table order by artist",
it works perfectly. However if i introduce the 'where' option, as in
"select * from table where artist like whatever". It comes back with
"could not execute query". This happens even if i append "order by" to
it.
I'll post all the various codes if anyone can please help.

[ian justice]

"Tom Thackrey" <us***********@ nospam.com> wrote in message news:<FW******* ***********@new ssvr14.news.pro digy.com>...

On 31-Oct-2003, i.*******@onmai l.co.uk (ian justice) wrote:

The versions i am using are as follows;
PHP4u Version 3.0 Based on 4.3.2
MySQL 3.23.56

If the syntax is correct

I'm relatively new to computers, so this may be a ludricous
suggestion. As in it would
leave my Database open to malicious destruction. However, if it
would'nt, ( i simply do
not know ). I could give you the URL of a copy of the Form that i'm
using and the PHP
script URL. Although presumably, that i not necessary as the Form has
that as the 'action'
and you can't view the PHP code.
I will still post the PHP code tomorrow.

[ian justice]

"Tom Thackrey" <us***********@ nospam.com> wrote in message news:<x%******* ********@newssv r29.news.prodig y.com>...

On 1-Nov-2003, i.*******@onmai l.co.uk (ian justice) wrote:

"Tom Thackrey" <us***********@ nospam.com> wrote in message
news:<FW******* ***********@new ssvr14.news.pro digy.com>...
You'd save us all a lot of time if you'd just post the sql message that's faillingalong with the error message.

Sorry, i will paste the whole script on Tuesday at the latest. I'll
keep quiet
until then. Although i do know i kept it as basic as could be.
For instance the working parts of the HTML Form were,
<form action="page_na me.php" method="POST"> I also tried "GET" with
exactly the same outcome.
<input type="text" name="write">
The 'crucial' ? part of the PHP Script was simply
"$sql=$writ e". Which works fine without the where option.

[Tom Thackrey]

On 1-Nov-2003, i.*******@onmai l.co.uk (ian justice) wrote:

"Tom Thackrey" <us***********@ nospam.com> wrote in message
news:<x%******* ********@newssv r29.news.prodig y.com>...

On 1-Nov-2003, i.*******@onmai l.co.uk (ian justice) wrote:

"Tom Thackrey" <us***********@ nospam.com> wrote in message
news:<FW******* ***********@new ssvr14.news.pro digy.com>...
You'd save us all a lot of time if you'd just post the sql message that's faillingalong with the error message.

Sorry, i will paste the whole script on Tuesday at the latest. I'll
keep quiet
until then. Although i do know i kept it as basic as could be.
For instance the working parts of the HTML Form were,
<form action="page_na me.php" method="POST"> I also tried "GET" with
exactly the same outcome.
<input type="text" name="write">
The 'crucial' ? part of the PHP Script was simply
"$sql=$writ e". Which works fine without the where option.

I gather you are entering the sql in an <input field. You do know that if
you have magic quotes on your input will be escaped with back slashes
automatically which would transform "select * from table where col like
'something'" to "select * from table where col like \'something\'" causing a
mysql syntax error.

I suggest that you code in such a way that these type of errors become
obvious, for example:

$result = mysql_query($sq l) or die("Error: $sql failed because
".mysql_error() );

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@wil lglen.net (it's reserved for spammers)

[Geoff Berrow]

I noticed that Message-ID:
<Pq************ ***@newssvr29.n ews.prodigy.com > from Tom Thackrey
contained the following:

I gather you are entering the sql in an <input field. You do know that if
you have magic quotes on your input will be escaped with back slashes
automaticall y which would transform "select * from table where col like
'something'" to "select * from table where col like \'something\'" causing a
mysql syntax error.

And use
$sql =stripslashes($ write);

--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[ian justice]

"Tom Thackrey" <us***********@ nospam.com> wrote in message news:<x%******* ********@newssv r29.news.prodig y.com>...

On 1-Nov-2003, i.*******@onmai l.co.uk (ian justice) wrote:

"Tom Thackrey" <us***********@ nospam.com> wrote in message
news:<FW******* ***********@new ssvr14.news.pro digy.com>...
You'd save us all a lot of time if you'd just post the sql statement that's failing along with the mysql error message.

The basic and simple form and scripts are given below.
#######SHORT FORM########
<form method="get" action="file_na me.php">
Write In Here <input type="textarea" name="write" rows="1"
cols="400"><br> <br>
<input type="submit" value="SEARCH">
<input type="reset" value="RESET">

######SHORT SCRIPT########
<?php
$conn=@mysql_co nnect("localhos t", "user_name" , "password") or
die("could not connect");
$rs=@mysql_sele ct_db("database _name", $conn) or die("could not connect
to database");
$sql="$write";
$rs=mysql_query ($sql, $conn) or die("could not execute query");
$list="<table border=\"1\" cellpadding=\"2 \">";
$list.="<tr><th >SONG</th>";
$list.="<th>FOL DER</th>";
$list.="<th>FOR MAT</th>";
$list.="<th>ART IST</th></tr>";
while($row=mysq l_fetch_array($ rs))
{
$list.="<tr>";
$list.="<td>".$ row["SONG"]."</td>";
$list.="<td>".$ row["FOLDER"]."</td>";
$list.="<td>".$ row["FORMAT"]."</td>";
$list.="<td>".$ row["ARTIST"]."</td>";
$list.="</tr>";
}
$list.="</table>";
echo($list);
?>

############### ############### ############### ############### ############### ####
BE A GOD----SEND ME TO HEAVEN
If you want to have a great time and loads of fun. Please feel free to
tackle the problem with my 'Utopian script', which would send me
soaring to Heaven, emotionally, that is.
It produces the following parse error "Parse error: parse error in
/data/members/paid/x/x/user_name/htdocs/directory_name/file_name.php
on line 9"

###LONG SCRIPT#######

<?php
$conn=@mysql_co nnect("localhos t", "user_name" , "password") or
die("could not connect");
$rs=@mysql_sele ct_db("database _name", $conn) or die("could not connect
to database");
$sql="select * from table_name where";
if(isset($song) ){
$sql.="song="$s ong" and"
}
if(isset($folde r)){
$sql.="folder=" $folder" and"
}
if(isset($forma t)){
$sql.="format=" $format" and"
}
if(isset($artis t)){
$sql.="artist=" $artist" and"
}
$sql=ereg_repla ce("and", "", "$sql");
if(isset($order )){
$sql.="order="$ order"
}
$rs=mysql_query ($sql, $conn) or die("could not execute query");
$list="<table border=\"1\" cellpadding=\"2 \">";
$list.="<tr><th >SONG</th>";
$list.="<th>FOL DER</th>";
$list.="<th>FOR MAT</th>";
$list.="<th>ART IST</th></tr>";
while($row=mysq l_fetch_array($ rs))
{
$list.="<tr>";
$list.="<td>".$ row["song"]."</td>";
$list.="<td>".$ row["folder"]."</td>";
$list.="<td>".$ row["format"]."</td>";
$list.="<td>".$ row["artist"]."</td>";
$list.="</tr>";
}
$list.="</table>";
echo($list);
?>

######LONG FORM#######

<form action="form_na me.php" method="GET">

<b>SONG</b><br>
<input type="text" name="song" size="50"><br>
<b>FOLDER</b><br>
<input type="text" name="folder" size="2"><br>
<b>FORMAT</b><br>
<input type="text" name="format" size="20"><br>
<b>ARTIST</b><br>
<input type="text" name="artist" size="40"><br>

The percent sign can be used as a wildcard. You can place it at the
start, end or both ends with appropiate results.<br>

<b>ORDER RESULTS BY;</b><br>
<input type="radio" name="order" value="order by song"
checked><b>SONG </b><br>
<input type="radio" name="order" value="order by
folder"><b>FOLD ER</b><br>
<input type="radio" name="order" value="order by
artist"><b>ARTI ST</b>&nbsp

<input type="submit" value="SEARCH"> <input type="reset"
value="RESET">< br><br>
</form>

[Tom Thackrey]

On 4-Nov-2003, i.*******@onmai l.co.uk (ian justice) wrote:

The basic and simple form and scripts are given below.
#######SHORT FORM########
<form method="get" action="file_na me.php">
Write In Here <input type="textarea" name="write" rows="1"
cols="400"><br> <br>
<input type="submit" value="SEARCH">
<input type="reset" value="RESET">

######SHORT SCRIPT########
<?php
$conn=@mysql_co nnect("localhos t", "user_name" , "password") or
die("could not connect");
$rs=@mysql_sele ct_db("database _name", $conn) or die("could not connect
to database");
$sql="$write";
Unless you have register globals on you need to code the above as
$sql = $_POST['write'];
$rs=mysql_query ($sql, $conn) or die("could not execute query");
This would be more useful as
$rs=mysql_query ($sql, $conn) or die("could not execute query [$sql] because
".mysql_error() );

$list="<table border=\"1\" cellpadding=\"2 \">";
$list.="<tr><th >SONG</th>";
$list.="<th>FOL DER</th>";
$list.="<th>FOR MAT</th>";
$list.="<th>ART IST</th></tr>";
while($row=mysq l_fetch_array($ rs))
{
$list.="<tr>";
$list.="<td>".$ row["SONG"]."</td>";
$list.="<td>".$ row["FOLDER"]."</td>";
$list.="<td>".$ row["FORMAT"]."</td>";
$list.="<td>".$ row["ARTIST"]."</td>";
$list.="</tr>";
}
$list.="</table>";
echo($list);
?>

############### ############### ############### ############### ############### ####
BE A GOD----SEND ME TO HEAVEN
If you want to have a great time and loads of fun. Please feel free to
tackle the problem with my 'Utopian script', which would send me
soaring to Heaven, emotionally, that is.
It produces the following parse error "Parse error: parse error in
/data/members/paid/x/x/user_name/htdocs/directory_name/file_name.php
on line 9"

###LONG SCRIPT#######

<?php
$conn=@mysql_co nnect("localhos t", "user_name" , "password") or
die("could not connect");
$rs=@mysql_sele ct_db("database _name", $conn) or die("could not connect
to database");
$sql="select * from table_name where";
if(isset($song) ){
$sql.="song="$s ong" and"
The above has unescaped quotes and no ;, it also needs a space after the
'and' or you will end up with '... andfolder=...'
it should be
$sql.="song=\"$ song\" and ";
the rest of these have the same problem
}
if(isset($folde r)){
$sql.="folder=" $folder" and"
}
if(isset($forma t)){
$sql.="format=" $format" and"
}
if(isset($artis t)){
$sql.="artist=" $artist" and"
}
$sql=ereg_repla ce("and", "", "$sql");
Huh? take the above out and replace it with

$sql .= ' 1 ';
if(isset($order )){
$sql.="order="$ order"
should be
$sql .= "order=\"$order \"";

}
$rs=mysql_query ($sql, $conn) or die("could not execute query");
see earlier comment about mysql_error()
$list="<table border=\"1\" cellpadding=\"2 \">";
$list.="<tr><th >SONG</th>";
$list.="<th>FOL DER</th>";
$list.="<th>FOR MAT</th>";
$list.="<th>ART IST</th></tr>";
while($row=mysq l_fetch_array($ rs))
{
$list.="<tr>";
$list.="<td>".$ row["song"]."</td>";
$list.="<td>".$ row["folder"]."</td>";
$list.="<td>".$ row["format"]."</td>";
$list.="<td>".$ row["artist"]."</td>";
$list.="</tr>";
}
$list.="</table>";
echo($list);
?>

######LONG FORM#######

<form action="form_na me.php" method="GET">

<b>SONG</b><br>
<input type="text" name="song" size="50"><br>
<b>FOLDER</b><br>
<input type="text" name="folder" size="2"><br>
<b>FORMAT</b><br>
<input type="text" name="format" size="20"><br>
<b>ARTIST</b><br>
<input type="text" name="artist" size="40"><br>

The percent sign can be used as a wildcard. You can place it at the
start, end or both ends with appropiate results.<br>

<b>ORDER RESULTS BY;</b><br>
<input type="radio" name="order" value="order by song"
checked><b>SONG </b><br>
<input type="radio" name="order" value="order by
folder"><b>FOLD ER</b><br>
<input type="radio" name="order" value="order by
artist"><b>ARTI ST</b>&nbsp

<input type="submit" value="SEARCH"> <input type="reset"
value="RESET">< br><br>
</form>

It's always useful to display your sql statements especially when you build
them in parts. That's why using the or die() to display the sql and the
error message is way more helpful than just displaying "i have an error,
guess what it is"

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@wil lglen.net (it's reserved for spammers)

[ian justice]

"Tom Thackrey" <us***********@ nospam.com> wrote in message news:<n_******* ************@ne wssvr21.news.pr odigy.com>...

Unless you have register globals on you need to code the above as
$sql = $_POST['write'];
They are turned on as far as i know. If it's of any help or
assistance, all the specifications are contained in this link
"http://www.php4u.info/phpinfo.php"

$rs=mysql_query ($sql, $conn) or die("could not execute query");

This would be more useful as
$rs=mysql_query ($sql, $conn) or die("could not execute query [$sql] because
".mysql_error() );

I had since added that extension, with very interesting results. There
is a possibility, that 'stripslashes' are involved. I'll have to go
and learn what they are first. I am very new to computers, never ever
haven even touched one until about eighteen months ago.

<?php
$conn=@mysql_co nnect("localhos t", "user_name" , "password") or
die("could not connect");
$rs=@mysql_sele ct_db("database _name", $conn) or die("could not connect
to database");
$sql="select * from table_name where";
if(isset($song) ){
$sql.="song="$s ong" and"

The above has unescaped quotes and no ;, it also needs a space after the
'and' or you will end up with '... andfolder=...'
it should be
$sql.="song=\"$ song\" and ";
the rest of these have the same problem

The 'and' mention looks to be a spectaculary thick oversight on my
part.
I'll have to look into this in detail. So much of the problems could
be that, ( i'm not removing blame from my scripting ), server specific
syntax. For example in the vast amount of reading i've done on php (
yes, apparently fruitless ), i had never seen mention of where, as in
my simple html form. One can enter a query successfully without using
any quotes or capitals.
Incidentally, if it would be of any help, i could publish here the
URL'S of my forms on nmy website. As, i'm sure the error messages now
being detailed would mean far more to yourself than me. I obviously
don't wish to leave my database open to malicious attack. Bearing in
mind it's an open text field entry. Presumably some bright spark could
easily drop the whole thing ???.

$sql=ereg_repla ce("and", "", "$sql");

Huh? take the above out and replace it with

$sql .= ' 1 ';

This ereg_replace 'thing' was something i found recommended in a forum
once. :) sorry. I'm very interested in your quoting of the '1'. As in
my database interface from my website provider, that is sometimes in
their syntax. Incidentally, they show the syntax of a request upon
successful ( or otherwise ) completion. However, if i copy and paste
it into their text field and run it, it astoundingly always says
syntax error !!!. Again, allowing for me being uneducated in computer
matters, i wonder if this is a result of 'stripslashes' etc. ?.
It's always useful to display your sql statements especially when you build
them in parts. That's why using the or die() to display the sql and the
error message is way more helpful than just displaying "i have an error,
guess what it is"

Taken on board.

[ian justice]

Geoff Berrow <bl******@ckdog .co.uk> wrote in message news:<qh******* *************** **********@4ax. com>...

I noticed that Message-ID:
<Pq************ ***@newssvr29.n ews.prodigy.com > from Tom Thackrey
contained the following:

I gather you are entering the sql in an <input field. You do know that if
you have magic quotes on your input will be escaped with back slashes
automaticall y which would transform "select * from table where col like
'something'" to "select * from table where col like \'something\'" causing a
mysql syntax error.

And use
$sql =stripslashes($ write);

APOLOGIES IF THIS IS NOT PROTOCOL. BUT, SOME THINGS IN LIFE HAVE TO BE
IN BIG BLOCK LETTERS. I HAVE JUST ADDED THE 'STRIPSLASHES' TO MY
SIMPLE TEST FORM. EVERYTHING WORKS. SO, IF ONLY I CAN WORK IT INTO MY
LONG VERSION. ALTHOUGH, I FEAR I HAVE MANY MORE MISTAKES LURKING
WITHIN THAT SCRIPT. HOWEVER, FOR NOW, MANY, MANY SINCERE THANKS. I'M
ABSOLUTELY STUNNED.

[Geoff Berrow]

I noticed that Message-ID:
<f6************ **************@ posting.google. com> from ian justice
contained the following:

HOWEVER, FOR NOW, MANY, MANY SINCERE THANKS. I'M
ABSOLUTELY STUNNED.

Yeah, I get like that when my scripts run. %-)
--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Tom Thackrey]

On 5-Nov-2003, i.*******@onmai l.co.uk (ian justice) wrote:

"Tom Thackrey" <us***********@ nospam.com> wrote in message
news:<n_******* ************@ne wssvr21.news.pr odigy.com>...

Unless you have register globals on you need to code the above as
$sql = $_POST['write'];

They are turned on as far as i know. If it's of any help or
assistance, all the specifications are contained in this link
"http://www.php4u.info/phpinfo.php"

$rs=mysql_query ($sql, $conn) or die("could not execute query");

This would be more useful as
$rs=mysql_query ($sql, $conn) or die("could not execute query [$sql]
because
".mysql_error() );

I had since added that extension, with very interesting results. There
is a possibility, that 'stripslashes' are involved. I'll have to go
and learn what they are first. I am very new to computers, never ever
haven even touched one until about eighteen months ago.

<?php
$conn=@mysql_co nnect("localhos t", "user_name" , "password") or
die("could not connect");
$rs=@mysql_sele ct_db("database _name", $conn) or die("could not connect
to database");
$sql="select * from table_name where";
if(isset($song) ){
$sql.="song="$s ong" and"

The above has unescaped quotes and no ;, it also needs a space after the
'and' or you will end up with '... andfolder=...'
it should be
$sql.="song=\"$ song\" and ";
the rest of these have the same problem

The 'and' mention looks to be a spectaculary thick oversight on my
part.
I'll have to look into this in detail. So much of the problems could
be that, ( i'm not removing blame from my scripting ), server specific
syntax. For example in the vast amount of reading i've done on php (
yes, apparently fruitless ), i had never seen mention of where, as in
my simple html form. One can enter a query successfully without using
any quotes or capitals.
Incidentally, if it would be of any help, i could publish here the
URL'S of my forms on nmy website. As, i'm sure the error messages now
being detailed would mean far more to yourself than me. I obviously
don't wish to leave my database open to malicious attack. Bearing in
mind it's an open text field entry. Presumably some bright spark could
easily drop the whole thing ???.

$sql=ereg_repla ce("and", "", "$sql");

Huh? take the above out and replace it with

$sql .= ' 1 ';

This ereg_replace 'thing' was something i found recommended in a forum
once. :) sorry. I'm very interested in your quoting of the '1'. As in
my database interface from my website provider, that is sometimes in
their syntax. Incidentally, they show the syntax of a request upon
successful ( or otherwise ) completion. However, if i copy and paste
it into their text field and run it, it astoundingly always says
syntax error !!!. Again, allowing for me being uneducated in computer
matters, i wonder if this is a result of 'stripslashes' etc. ?.

It's always useful to display your sql statements especially when you
build
them in parts. That's why using the or die() to display the sql and the
error message is way more helpful than just displaying "i have an error,
guess what it is"

Taken on board.

In looking at your phpinfo() I see that you have register_global s On,
safe_mode On, and magic_quotes On.

Register Globals On means that you do not have to use $_POST[] and $_GET[]
to retrieve values from the previous page. The values will be available
directly as global variables. (It's still a good idea to use $_POST etc.
it's safer and doesn't rely on register globals being On.)

Safe Mode On restricts the things you can do, like where you can create or
read files, etc.

Magic Quotes On causes any input from the user to be escaped with slashes.
If the user enters "Don't feed the bears.", the string will appear in your
PHP program as "Don\'t feed the bears." this is suitable for entering as
data in a database, but could cause problems if you are trying to pass whole
sql statements. Use stripslashes() to remove these escapes.

The $str .= ' 1 '; I suggested was to compensate for the ' and ' you put
after each argument. Doing it your way would result in something like

select * from table where song='cherish' and order by somename

adding the 1 makes it legal syntax

select * from table where song='cherish' and 1 order by somename

it also works when no field is specified

select * from table where 1 order by somename



--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@wil lglen.net (it's reserved for spammers)