"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.

[Tom Thackrey]

Re: "Select&qu ot; & "Order By" OK- ''WHERE'' Does'nt Work !


On 31-Oct-2003, i.justice@onmai l.co.uk (ian justice) wrote:
[color=blue]
> 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.[/color]

I teach students how to program. Almost every student is convinced, at one
time or another, that they have found a bug in the compiler, operating
system or hardware. In every case it's a problem with the student's code.

You are suggesting that somehow the SQL select where order by is broken for
some unspecified database system. The odds are about the same as my winning
the lottery without buying a ticket.

assuming the table and columns are correctly defined and the connection is
open and the database is selected and I haven't made a typo, the following
will work
"select * from sometable where somecolumn like 'somevalue%' order by
somecolumn"

If you want to help yourself
1) echo the actual SQL statement
2) add error capture code (e.g. 'or die(mysql_error ())') to your connect,
dbselect and query calls

If you want more help here
1) show us the actual code including the echoed sql statement and error
message if any
2) show us the table definition
3) tell us which database you are using and what version
4) tell us the version of PHP and if it's in safe mode


--
Tom Thackrey

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

[Matthias Esken]

Re: "Select&qu ot; & "Order By" OK- ''WHERE'' Does'nt Work !

i.justice@onmai l.co.uk (ian justice) schrieb:
[color=blue]
> 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.[/color]

Are you sure that youre query string looks like
select id from table order by id
and not like
select id from tableorder by id
?

Please give us the version of PHP, the name of the DBMS that you're
using and some small example code.

Regards,
Matthias

[Sh0t]

Re: "Select&qu ot; & "Order By" OK- ''WHERE'' Does'nt Work !

That was beautiful sir.

[ian justice]

Re: "Select&qu ot; & "Order By" OK- ''WHERE'' Does'nt Work !

Matthias Esken <muelleimer2003 nospam@usenetve rwaltung.org> wrote in message news:<bnur1r.11 s.1@usenet.eske n.de>...[color=blue]
>
> Are you sure that youre query string looks like
> select id from table order by id
> and not like
> select id from tableorder by id
> ?[/color]
Yes
I'm writing this via web tv, however come Sunday i should be able to access a PC
and i'll post all the scripts.
[color=blue]
> Please give us the version of PHP, the name of the DBMS that you're
> using and some small example code.
>
> Regards,
> Matthias[/color]
I'm using;
PHP4u Version 3.0 Based on PHP-4.3.2
MySQL 3.23.52
Thanks for your time so far.

[ian justice]

Re: &quot;Select&qu ot; &amp; &quot;Order By&quot; OK- ''WHERE'' Does'nt Work !

>[color=blue]
>I'm using;
>PHP4u Version 3.0 Based on PHP-4.3.2
>MySQL 3.23.52
>Thanks for your time so far.[/color]
Correction MySQL 3.23.56

[ian justice]

Re: &quot;Select&qu ot; &amp; &quot;Order By&quot; OK- ''WHERE'' Does'nt Work !

"Tom Thackrey" <use.signature@ nospam.com> wrote in message news:<rcBob.844 2$e73.8118@news svr14.news.prod igy.com>...[color=blue]
>
> I teach students how to program. Almost every student is convinced, at one
> time or another, that they have found a bug in the compiler, operating
> system or hardware. In every case it's a problem with the student's code.[/color]

I'm presuming it is my fault
[color=blue]
> You are suggesting that somehow the SQL select where order by is broken for
> some unspecified database system. The odds are about the same as my winning
> the lottery without buying a ticket.[/color]

Honestly not remotely suggesting such a thing :)

I'm replying to this via webtv, so unfortunately the rest of your
message can't be quoted as it doesn't show up on the reply form.
However, hopefully on Sunday i will be able to access a PC again. I
will then post the scripts.

I had already tried the syntax you suggested without success. The
database and MySQL connection are fine, as they select, sort and
display the results of a query such as "select * from table order by
column".

The versions i am using are as follows;
PHP4u Version 3.0 Based on 4.3.2
MySQL 3.23.56
Many thanks for your time and help so far, it's very much appreciated.

[Tom Thackrey]

Re: &quot;Select&qu ot; &amp; &quot;Order By&quot; OK- ''WHERE'' Does'nt Work !


On 31-Oct-2003, i.justice@onmai l.co.uk (ian justice) wrote:
[color=blue]
> I had already tried the syntax you suggested without success. The
> database and MySQL connection are fine, as they select, sort and
> display the results of a query such as "select * from table order by
> column".
>
> The versions i am using are as follows;
> PHP4u Version 3.0 Based on 4.3.2
> MySQL 3.23.56
> Many thanks for your time and help so far, it's very much appreciated.[/color]

What error message did you get when you executed the query?
Are you sure there are records in the table that match your pattern?
If the syntax is correct, then the problem is probably with the table or
column names. Are you using reserved words for either of these? Do they
exist?



--
Tom Thackrey

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

[ian justice]

Re: &quot;Select&qu ot; &amp; &quot;Order By&quot; OK- ''WHERE'' Does'nt Work !

"Tom Thackrey" <use.signature@ nospam.com> wrote in message news:<FWHob.114 51$OS5.8313@new ssvr14.news.pro digy.com>...
[color=blue]
> What error message did you get when you executed the query?
> Are you sure there are records in the table that match your pattern?
> If the syntax is correct, then the problem is probably with the table or
> column names. Are you using reserved words for either of these? Do they
> exist?[/color]

The error message was "could not execute query". This is my own error
message as in
$rs=mysql_query ($sql, $conn) or die ("could not execute query");

The records do exist i deliberately used simple one-word requests as
well. I also
checked i got the same error message by typing in a load of random
letters in the
text field.
The table is called 'music' and the columns are headed 'song' 'folder'
format' 'artist'.

Whether it's relevant to solving the puzzle, i don't know. But, i
stress again that i am
successfully connected to mysql and the database. As, it does
correctly sort and display
the result by the chosen 'order by' command, be it song, folder,
format or artist.

[Tom Thackrey]

Re: &quot;Select&qu ot; &amp; &quot;Order By&quot; OK- ''WHERE'' Does'nt Work !


On 1-Nov-2003, i.justice@onmai l.co.uk (ian justice) wrote:
[color=blue]
> "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> news:<FWHob.114 51$OS5.8313@new ssvr14.news.pro digy.com>...
>[color=green]
> > What error message did you get when you executed the query?
> > Are you sure there are records in the table that match your pattern?
> > If the syntax is correct, then the problem is probably with the table or
> > column names. Are you using reserved words for either of these? Do they
> > exist?[/color]
>
> The error message was "could not execute query". This is my own error
> message as in
> $rs=mysql_query ($sql, $conn) or die ("could not execute query");[/color]

change it to
$rs=mysql_query ($sql, $conn) or die ("could not execute query because
".mysql_error() );
[color=blue]
>
> The records do exist i deliberately used simple one-word requests as
> well. I also
> checked i got the same error message by typing in a load of random
> letters in the
> text field.
> The table is called 'music' and the columns are headed 'song' 'folder'
> format' 'artist'.
>
> Whether it's relevant to solving the puzzle, i don't know. But, i
> stress again that i am
> successfully connected to mysql and the database. As, it does
> correctly sort and display
> the result by the chosen 'order by' command, be it song, folder,
> format or artist.[/color]

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.



--
Tom Thackrey

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

[ian justice]

Re: &quot;Select&qu ot; &amp; &quot;Order By&quot; OK- ''WHERE'' Does'nt Work !

"Tom Thackrey" <use.signature@ nospam.com> wrote in message news:<FWHob.114 51$OS5.8313@new ssvr14.news.pro digy.com>...[color=blue]
> On 31-Oct-2003, i.justice@onmai l.co.uk (ian justice) wrote:
>[color=green]
> > The versions i am using are as follows;
> > PHP4u Version 3.0 Based on 4.3.2
> > MySQL 3.23.56[/color][/color]
[color=blue]
> If the syntax is correct[/color]

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]

Re: &quot;Select&qu ot; &amp; &quot;Order By&quot; OK- ''WHERE'' Does'nt Work !

"Tom Thackrey" <use.signature@ nospam.com> wrote in message news:<x%Wob.939 $vn3.605@newssv r29.news.prodig y.com>...[color=blue]
> On 1-Nov-2003, i.justice@onmai l.co.uk (ian justice) wrote:
>[color=green]
> > "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> > news:<FWHob.114 51$OS5.8313@new ssvr14.news.pro digy.com>...
>> You'd save us all a lot of time if you'd just post the sql message[/color][/color]
that's failling[color=blue][color=green]
>>along with the error message.[/color][/color]

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]

Re: &quot;Select&qu ot; &amp; &quot;Order By&quot; OK- ''WHERE'' Does'nt Work !


On 1-Nov-2003, i.justice@onmai l.co.uk (ian justice) wrote:
[color=blue]
> "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> news:<x%Wob.939 $vn3.605@newssv r29.news.prodig y.com>...[color=green]
> > On 1-Nov-2003, i.justice@onmai l.co.uk (ian justice) wrote:
> >[color=darkred]
> > > "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> > > news:<FWHob.114 51$OS5.8313@new ssvr14.news.pro digy.com>...
> >> You'd save us all a lot of time if you'd just post the sql message[/color][/color]
> that's failling[color=green][color=darkred]
> >>along with the error message.[/color][/color]
>
> 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.[/color]

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

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

[Geoff Berrow]

Re: &quot;Select&qu ot; &amp; &quot;Order By&quot; OK- ''WHERE'' Does'nt Work !

I noticed that Message-ID:
<Pq0pb.980$Kn5. 626@newssvr29.n ews.prodigy.com > from Tom Thackrey
contained the following:
[color=blue]
>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.[/color]

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]

Re: &quot;Select&qu ot; &amp; &quot;Order By&quot; OK- ''WHERE'' Does'nt Work !

"Tom Thackrey" <use.signature@ nospam.com> wrote in message news:<x%Wob.939 $vn3.605@newssv r29.news.prodig y.com>...[color=blue]
> On 1-Nov-2003, i.justice@onmai l.co.uk (ian justice) wrote:
>[color=green]
> > "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> > news:<FWHob.114 51$OS5.8313@new ssvr14.news.pro digy.com>...
>> You'd save us all a lot of time if you'd just post the sql[/color][/color]
statement that's[color=blue][color=green]
>> failing along with the mysql error message.[/color]
>[/color]
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>