By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,321 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,321 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Jul 17 '05 #1
Share this Question
Share on Google+
23 Replies


P: n/a

On 31-Oct-2003, i.*******@onmail.co.uk (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
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)
Jul 17 '05 #2

P: n/a
i.*******@onmail.co.uk (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
Jul 17 '05 #3

P: n/a
That was beautiful sir.
Jul 17 '05 #4

P: n/a
Matthias Esken <mu******************@usenetverwaltung.org> wrote in message news:<bn**********@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.
Jul 17 '05 #5

P: n/a
>
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
Jul 17 '05 #6

P: n/a
"Tom Thackrey" <us***********@nospam.com> wrote in message news:<rc*****************@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.
Jul 17 '05 #7

P: n/a

On 31-Oct-2003, i.*******@onmail.co.uk (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
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)
Jul 17 '05 #8

P: n/a
"Tom Thackrey" <us***********@nospam.com> wrote in message news:<FW******************@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.
Jul 17 '05 #9

P: n/a

On 1-Nov-2003, i.*******@onmail.co.uk (ian justice) wrote:
"Tom Thackrey" <us***********@nospam.com> wrote in message
news:<FW******************@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
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)
Jul 17 '05 #10

P: n/a
"Tom Thackrey" <us***********@nospam.com> wrote in message news:<FW******************@newssvr14.news.prodigy. com>...
On 31-Oct-2003, i.*******@onmail.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.
Jul 17 '05 #11

P: n/a
"Tom Thackrey" <us***********@nospam.com> wrote in message news:<x%***************@newssvr29.news.prodigy.com >...
On 1-Nov-2003, i.*******@onmail.co.uk (ian justice) wrote:
"Tom Thackrey" <us***********@nospam.com> wrote in message
news:<FW******************@newssvr14.news.prodigy. 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_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.
Jul 17 '05 #12

P: n/a

On 1-Nov-2003, i.*******@onmail.co.uk (ian justice) wrote:
"Tom Thackrey" <us***********@nospam.com> wrote in message
news:<x%***************@newssvr29.news.prodigy.com >...
On 1-Nov-2003, i.*******@onmail.co.uk (ian justice) wrote:
"Tom Thackrey" <us***********@nospam.com> wrote in message
news:<FW******************@newssvr14.news.prodigy. 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_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
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)
Jul 17 '05 #13

P: n/a
I noticed that Message-ID:
<Pq***************@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 http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #14

P: n/a
"Tom Thackrey" <us***********@nospam.com> wrote in message news:<x%***************@newssvr29.news.prodigy.com >...
On 1-Nov-2003, i.*******@onmail.co.uk (ian justice) wrote:
"Tom Thackrey" <us***********@nospam.com> wrote in message
news:<FW******************@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>
Jul 17 '05 #15

P: n/a

On 4-Nov-2003, i.*******@onmail.co.uk (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
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)
Jul 17 '05 #16

P: n/a
"Tom Thackrey" <us***********@nospam.com> wrote in message news:<n_*******************@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.
Jul 17 '05 #17

P: n/a
Geoff Berrow <bl******@ckdog.co.uk> wrote in message news:<qh********************************@4ax.com>. ..
I noticed that Message-ID:
<Pq***************@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.
Jul 17 '05 #18

P: n/a
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/
Jul 17 '05 #19

P: n/a

On 5-Nov-2003, i.*******@onmail.co.uk (ian justice) wrote:
"Tom Thackrey" <us***********@nospam.com> wrote in message
news:<n_*******************@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
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)
Jul 17 '05 #20

P: n/a
i.*******@onmail.co.uk (ian justice) wrote in message news:<f6**************************@posting.google. com>...

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.


SORTED-
Finally, sorted everything out, especially the long script.
If anyone down the line comes across this posting and it helps them
out, please say so. I'm delighted to have solved the matter after a
horrifically long time.
The two major problems lay where i had realised they were likely to
be. That is, in the necessary use of the 'stripslashes' function.
Also, with just one exception, absolutely no punctuation should be
used in the sql query syntax. I had feared that server
specific/website provider nuances would play a part in the matter. The
only bit that needed it was the Wild-Card part using '%%'.
The form and scripts are detailed below.

######INPUT FORM######

<form method="POST" action="script_name.php">
<b>
<dl>
<dt><font color="#000099">SELECT ALL WHERE </font> <br>
<dd><input type="radio" name="a" value="song" checked><b>SONG</b><br>
<dd><input type="radio" name="a" value="folder"><b>FOLDER</b><br>
<dd><input type="radio" name="a" value="format"><b>FORMAT</b><br>
<dd><input type="radio" name="a" value="artist"><b>ARTIST</b><br><br>

<dt><font color="#000099">LIKE </font><br>
<dd><input type="textarea" name="b" rows="1" cols="50"> <font
color="#ff0000">If you don't know the exact wording, enter what you
know. As the Search employs an automatic Wild-Card
system.</font><br><br>

<dt><font color="#000099">ORDER BY</font> <br>
<dd><input type="radio" name="c" value="song" checked><b>SONG</b><br>
<dd><input type="radio" name="c" value="folder"><b>FOLDER</b><br>
<dd><input type="radio" name="c" value="artist"><b>ARTIST</b><br><br>

<input type="submit" value="SEARCH"><input type="reset" value="RESET">
</form>
######PHP SCRIPT######

<a href="form_name.php">Submit New Search</a>
<?php
$conn=@mysql_connect("localhost", "user", "password") or die("could
not connect");
$rs=@mysql_select_db("database_name", $conn) or die("could not connect
to database");
$sql=stripslashes ("select * from database_name where $a like '%$b%'
order by $c");

$rs=mysql_query($sql, $conn) or die("could not execute query 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);
?>
<a href="form_name.php">Submit New Search</a>

I'll be well chuffed if it helps someone else out in the future.
Thanks to all for helping me along.
Jul 17 '05 #21

P: n/a
i.*******@onmail.co.uk (ian justice) wrote in message news

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.


Updated Form to include instant A-Z, with simple HTML and 3 copies of
the PHP script;
<form method="POST" action="artist_script.php">
<center>ARTIST A-Z</center>
<input type="radio" name="aaz" value="A">A
<input type="radio" name="aaz" value="B">B
<input type="radio" name="aaz" value="C">C
<input type="radio" name="aaz" value="D">D
<input type="radio" name="aaz" value="E">E
<input type="radio" name="aaz" value="F">F
<input type="radio" name="aaz" value="G">G
<input type="radio" name="aaz" value="H">H
<input type="radio" name="aaz" value="I">I
<input type="radio" name="aaz" value="J">J
<input type="radio" name="aaz" value="K">K
<input type="radio" name="aaz" value="L">L
<input type="radio" name="aaz" value="M">M
<input type="radio" name="aaz" value="N">N
<input type="radio" name="aaz" value="O">O
<input type="radio" name="aaz" value="P">P
<input type="radio" name="aaz" value="Q">Q
<input type="radio" name="aaz" value="R">R
<input type="radio" name="aaz" value="S">S
<input type="radio" name="aaz" value="T">T
<input type="radio" name="aaz" value="U">U
<input type="radio" name="aaz" value="V">V
<input type="radio" name="aaz" value="W">W
<input type="radio" name="aaz" value="X">X
<input type="radio" name="aaz" value="Y">Y
<input type="radio" name="aaz" value="Z">Z
<input type="radio" name="aaz" value="0-9">0-9<br><br>
<input type="submit" value="SEARCH"><input type="reset" value="RESET">
</form>
<form method="POST" action="folder_script.php">
<center>FOLDER A-Z</center>
<input type="radio" name="faz" value="A">A
<input type="radio" name="faz" value="B">B
<input type="radio" name="faz" value="C">C
<input type="radio" name="faz" value="D">D
<input type="radio" name="faz" value="E">E
<input type="radio" name="faz" value="F">F
<input type="radio" name="faz" value="G">G
<input type="radio" name="faz" value="H">H
<input type="radio" name="faz" value="I">I
<input type="radio" name="faz" value="J">J
<input type="radio" name="faz" value="K">K
<input type="radio" name="faz" value="L">L
<input type="radio" name="faz" value="M">M
<input type="radio" name="faz" value="N">N
<input type="radio" name="faz" value="O">O
<input type="radio" name="faz" value="P">P
<input type="radio" name="faz" value="Q">Q
<input type="radio" name="faz" value="R">R
<input type="radio" name="faz" value="S">S
<input type="radio" name="faz" value="T">T
<input type="radio" name="faz" value="U">U
<input type="radio" name="faz" value="V">V
<input type="radio" name="faz" value="W">W
<input type="radio" name="faz" value="X">X
<input type="radio" name="faz" value="Y">Y
<input type="radio" name="faz" value="Z">Z
<input type="radio" name="faz" value="0-9">0-9<br><br>
<input type="submit" value="SEARCH"><input type="reset" value="RESET">
</form>
<form method="POST" action="song_script.php">
<center>SONG A-Z</center>
<input type="radio" name="saz" value="A">A
<input type="radio" name="saz" value="B">B
<input type="radio" name="saz" value="C">C
<input type="radio" name="saz" value="D">D
<input type="radio" name="saz" value="E">E
<input type="radio" name="saz" value="F">F
<input type="radio" name="saz" value="G">G
<input type="radio" name="saz" value="H">H
<input type="radio" name="saz" value="I">I
<input type="radio" name="saz" value="J">J
<input type="radio" name="saz" value="K">K
<input type="radio" name="saz" value="L">L
<input type="radio" name="saz" value="M">M
<input type="radio" name="saz" value="N">N
<input type="radio" name="saz" value="O">O
<input type="radio" name="saz" value="P">P
<input type="radio" name="saz" value="Q">Q
<input type="radio" name="saz" value="R">R
<input type="radio" name="saz" value="S">S
<input type="radio" name="saz" value="T">T
<input type="radio" name="saz" value="U">U
<input type="radio" name="saz" value="V">V
<input type="radio" name="saz" value="W">W
<input type="radio" name="saz" value="X">X
<input type="radio" name="saz" value="Y">Y
<input type="radio" name="saz" value="Z">Z
<input type="radio" name="saz" value="0-9">0-9<br><br>
<input type="submit" value="SEARCH"><input type="reset" value="RESET">
</form>
Jul 17 '05 #22

P: n/a
After using the Database over a period of time. I came across
performance glitches. Not least, that i no longer had the ability to
put forward more complex queries involving several fields, than
provided for at present.
However, if anyone in the future comes across the same type of
problems. Just reply to this posting and i'll post the scripts
necessary.
Everything on the Database and interacting page is perfectly tailored
to my needs and working as desired.
When i get the time, i will post ALL the scripts and forms involved,
in a single post.
Jul 17 '05 #23

P: n/a
we*********@manchesterpaul.net (Stupidgirl) wrote in message news:<30**************************@posting.google. com>...
After using the Database over a period of time. I came across
performance glitches. Not least, that i no longer had the ability to
put forward more complex queries involving several fields, than
provided for at present.
However, if anyone in the future comes across the same type of
problems. Just reply to this posting and i'll post the scripts
necessary.
Everything on the Database and interacting page is perfectly tailored
to my needs and working as desired.
When i get the time, i will post ALL the scripts and forms involved,
in a single post.


CONFUSION CLARIFICATION 1:
The author of the above message was actually myself (i.Justice @ onmail.co.uk).
My girlfriend had been posting to Google Groups and i inadvertently used her Login.

CONFUSION CLARIFICATION 2.
Pretty bad wording by myself. To make it clear, with my updated script, i can now
successfully use complex queries.
Jul 17 '05 #24

This discussion thread is closed

Replies have been disabled for this discussion.