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

On 31-Oct-2003, [email]i.justice@onmail.co.uk[/email] (ian justice) wrote:

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

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
[url]www.creative-light.com[/url]
tom (at) creative (dash) light (dot) com
do NOT send email to [email]jamesbutler@willglen.net[/email] (it's reserved for spammers)

[Matthias Esken]

[email]i.justice@onmail.co.uk[/email] (ian justice) schrieb:

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

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]

That was beautiful sir.

[ian justice]

Matthias Esken <muelleimer2003nospam@usenetverwaltung.org> wrote in message news:<bnur1r.11s.1@usenet.esken.de>...

>
> 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
> ?

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.

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

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]

>

>I'm using;
>PHP4u Version 3.0 Based on PHP-4.3.2
>MySQL 3.23.52
>Thanks for your time so far.

Correction MySQL 3.23.56

[ian justice]

"Tom Thackrey" <use.signature@nospam.com> wrote in message news:<rcBob.8442$e73.8118@newssvr14.news.prodigy.c om>...

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

I'm presuming it is my fault

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

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]

On 31-Oct-2003, [email]i.justice@onmail.co.uk[/email] (ian justice) wrote:

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

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
[url]www.creative-light.com[/url]
tom (at) creative (dash) light (dot) com
do NOT send email to [email]jamesbutler@willglen.net[/email] (it's reserved for spammers)

[ian justice]

"Tom Thackrey" <use.signature@nospam.com> wrote in message news:<FWHob.11451$OS5.8313@newssvr14.news.prodigy. com>...

> 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?

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]

On 1-Nov-2003, [email]i.justice@onmail.co.uk[/email] (ian justice) wrote:

> "Tom Thackrey" <use.signature@nospam.com> wrote in message
> news:<FWHob.11451$OS5.8313@newssvr14.news.prodigy. com>...
>

> > 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?

>
> 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");

change it to
$rs=mysql_query($sql, $conn) or die ("could not execute query because
".mysql_error());

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

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
[url]www.creative-light.com[/url]
tom (at) creative (dash) light (dot) com
do NOT send email to [email]jamesbutler@willglen.net[/email] (it's reserved for spammers)

[ian justice]

"Tom Thackrey" <use.signature@nospam.com> wrote in message news:<FWHob.11451$OS5.8313@newssvr14.news.prodigy. com>...

> On 31-Oct-2003, [email]i.justice@onmail.co.uk[/email] (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" <use.signature@nospam.com> wrote in message news:<x%Wob.939$vn3.605@newssvr29.news.prodigy.com >...

> On 1-Nov-2003, [email]i.justice@onmail.co.uk[/email] (ian justice) wrote:
>

> > "Tom Thackrey" <use.signature@nospam.com> wrote in message
> > news:<FWHob.11451$OS5.8313@newssvr14.news.prodigy. com>...
>> You'd save us all a lot of time if you'd just post the sql message

that's failling

>>along 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_name.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=$write". Which works fine without the where option.

[Tom Thackrey]

On 1-Nov-2003, [email]i.justice@onmail.co.uk[/email] (ian justice) wrote:

> "Tom Thackrey" <use.signature@nospam.com> wrote in message
> news:<x%Wob.939$vn3.605@newssvr29.news.prodigy.com >...

> > On 1-Nov-2003, [email]i.justice@onmail.co.uk[/email] (ian justice) wrote:
> >

> > > "Tom Thackrey" <use.signature@nospam.com> wrote in message
> > > news:<FWHob.11451$OS5.8313@newssvr14.news.prodigy. com>...
> >> You'd save us all a lot of time if you'd just post the sql message

> that's failling

> >>along 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_name.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=$write". 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($sql) or die("Error: $sql failed because
".mysql_error());





--
Tom Thackrey
[url]www.creative-light.com[/url]
tom (at) creative (dash) light (dot) com
do NOT send email to [email]jamesbutler@willglen.net[/email] (it's reserved for spammers)

[Geoff Berrow]

I noticed that Message-ID:
<Pq0pb.980$Kn5.626@newssvr29.news.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
>automatically 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 [url]http://www.ckdog.co.uk/rfdmaker/[/url]

[ian justice]

"Tom Thackrey" <use.signature@nospam.com> wrote in message news:<x%Wob.939$vn3.605@newssvr29.news.prodigy.com >...

> On 1-Nov-2003, [email]i.justice@onmail.co.uk[/email] (ian justice) wrote:
>

> > "Tom Thackrey" <use.signature@nospam.com> wrote in message
> > news:<FWHob.11451$OS5.8313@newssvr14.news.prodigy. 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_name.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_connect("localhost", "user_name", "password") or
die("could not connect");
$rs=@mysql_select_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>FOLDER</th>";
$list.="<th>FORMAT</th>";
$list.="<th>ARTIST</th></tr>";
while($row=mysql_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_connect("localhost", "user_name", "password") or
die("could not connect");
$rs=@mysql_select_db("database_name", $conn) or die("could not connect
to database");
$sql="select * from table_name where";
if(isset($song)){
$sql.="song="$song" and"
}
if(isset($folder)){
$sql.="folder="$folder" and"
}
if(isset($format)){
$sql.="format="$format" and"
}
if(isset($artist)){
$sql.="artist="$artist" and"
}
$sql=ereg_replace("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>FOLDER</th>";
$list.="<th>FORMAT</th>";
$list.="<th>ARTIST</th></tr>";
while($row=mysql_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_name.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>FOLDER</b><br>
<input type="radio" name="order" value="order by
artist"><b>ARTIST</b>&nbsp

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

[Tom Thackrey]

On 4-Nov-2003, [email]i.justice@onmail.co.uk[/email] (ian justice) wrote:

> The basic and simple form and scripts are given below.
> #######SHORT FORM########
> <form method="get" action="file_name.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_connect("localhost", "user_name", "password") or
> die("could not connect");
> $rs=@mysql_select_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>FOLDER</th>";
> $list.="<th>FORMAT</th>";
> $list.="<th>ARTIST</th></tr>";
> while($row=mysql_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_connect("localhost", "user_name", "password") or
> die("could not connect");
> $rs=@mysql_select_db("database_name", $conn) or die("could not connect
> to database");
> $sql="select * from table_name where";
> if(isset($song)){
> $sql.="song="$song" 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($folder)){
> $sql.="folder="$folder" and"
> }
> if(isset($format)){
> $sql.="format="$format" and"
> }
> if(isset($artist)){
> $sql.="artist="$artist" and"
> }
> $sql=ereg_replace("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>FOLDER</th>";
> $list.="<th>FORMAT</th>";
> $list.="<th>ARTIST</th></tr>";
> while($row=mysql_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_name.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>FOLDER</b><br>
> <input type="radio" name="order" value="order by
> artist"><b>ARTIST</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
[url]www.creative-light.com[/url]
tom (at) creative (dash) light (dot) com
do NOT send email to [email]jamesbutler@willglen.net[/email] (it's reserved for spammers)

[ian justice]

"Tom Thackrey" <use.signature@nospam.com> wrote in message news:<n_Rpb.492$GH.50501888@newssvr21.news.prodigy .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_connect("localhost", "user_name", "password") or
> > die("could not connect");
> > $rs=@mysql_select_db("database_name", $conn) or die("could not connect
> > to database");
> > $sql="select * from table_name where";
> > if(isset($song)){
> > $sql.="song="$song" 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_replace("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 <blthecat@ckdog.co.uk> wrote in message news:<qhd9qv48i825i3hqsrtbouedhnndfi9oie@4ax.com>. ..

> I noticed that Message-ID:
> <Pq0pb.980$Kn5.626@newssvr29.news.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
> >automatically 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:
<f6c8e066.0311050630.29199968@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 [url]http://www.ckdog.co.uk/rfdmaker/[/url]

[Tom Thackrey]

On 5-Nov-2003, [email]i.justice@onmail.co.uk[/email] (ian justice) wrote:

> "Tom Thackrey" <use.signature@nospam.com> wrote in message
> news:<n_Rpb.492$GH.50501888@newssvr21.news.prodigy .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_connect("localhost", "user_name", "password") or
> > > die("could not connect");
> > > $rs=@mysql_select_db("database_name", $conn) or die("could not connect
> > > to database");
> > > $sql="select * from table_name where";
> > > if(isset($song)){
> > > $sql.="song="$song" 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_replace("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_globals 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
[url]www.creative-light.com[/url]
tom (at) creative (dash) light (dot) com
do NOT send email to [email]jamesbutler@willglen.net[/email] (it's reserved for spammers)