'select all' selects all NOOOOOOO!

[perplexed]

I'm still having a mountain of baffling problems getting basic and simple scripts and queries to work with my new host here on WHUK.

These scripts have worker perfectly (and also instantly upon creation) with my previous hosts servers for the best part of a decade. WHUK are using the same MySql version as the scripts were coded.

Please can someone tell me, if it is me at fault, what is wrong with the following query. I wish to select all COLUMNS not all RECORDS in the database - which is exactly what it spews out.

Surely i don't have to replace the '*' with the name of every single column?. I'm using the same version and '*' is a perfectly valid shorthand to use.

PHP Code:

$sql=stripslashes ("SELECT * FROM table_name WHERE '$a' LIKE '%$b%' ORDER BY '$c'"); 


[jon123]

I don't see anything wrong with your query.
I can only assume that your problem is with $b.
If $b is empty then it would explain why it prints all the records as the % relates to anything.

Can you give an example of what $b is? and were register_globals on or off with your previous host, and this one.

[perplexed]

Originally Posted by jon123

I don't see anything wrong with your query.
I can only assume that your problem is with $b.
If $b is empty then it would explain why it prints all the records as the % relates to anything.

Can you give an example of what $b is? and were register_globals on or off with your previous host, and this one.

Been a long time since had to compile php and MySql scripts for a specific purpose (as ony needed to do it the initial time and leave them running for years).....but....mention of register_globals will be well worth my checking out. Lycos has closed down and wiped it's servers clean, however, i'm sure i printed out the variables and settings for their MySql.

$a, $b and $c are web page form fields. It would read, as an example, select 'songs' from 'database' where 'title' like '%love%' order by 'artist'.

The inclusion of the wildcards '%' mean that it would select all coulumns in a record row containing song titles such as 'I Love You' 'Do You Love Me' etc.

The actual web page contains various types of queries using varying number of fields as one wishes or needs and with or without the wildcard option.

[black-dog]

Originally Posted by perplexed

I'm still having a mountain of baffling problems getting basic and simple scripts and queries to work with my new host here on WHUK.

These scripts have worker perfectly (and also instantly upon creation) with my previous hosts servers for the best part of a decade. WHUK are using the same MySql version as the scripts were coded.

Please can someone tell me, if it is me at fault, what is wrong with the following query. I wish to select all COLUMNS not all RECORDS in the database - which is exactly what it spews out.

Surely i don't have to replace the '*' with the name of every single column?. I'm using the same version and '*' is a perfectly valid shorthand to use.

PHP Code:

$sql=stripslashes ("SELECT * FROM table_name WHERE '$a' LIKE '%$b%' ORDER BY '$c'"); 


As others have said, it is most likely a register globals issue.

Add the following line to your code

echo $sql;

and see if the query actually contains what you think it contains.

All modern installations will have register globals disabled. This is a Good Thing but may need some tweaking of legacy code.

The code looks like it could be potentially very insecure. Assuming the variables are coming from a POSTed form you should have

PHP Code:

$a=mysql_real_escape_string($_POST['a']);
$b=mysql_real_escape_string($_POST['b']);
$c=mysql_real_escape_string($_POST['c']);

$sql="SELECT * FROM table_name WHERE '$a' LIKE '%$b%' ORDER BY '$c'"; 


You may not need the stripslashes.

[perplexed]

Result from adding the echo $sql shows that all three of the form fields are not receiving the values. This means the query reads simply as 'select all' !

SELECT * FROM table WHERE '' LIKE '%%' ORDER BY ''


Incidentally, i've never seen the mysql_real_escape_string mentioned before. Again that shows how long ago it was since i wrote the code. The database whilst being hosted on my website and accessed via a webpage is just for personal use. As a result one needs a username and password to access the search page.

[perplexed]

Too be honest i've just spent an hour trying a myriad of ways to get this database working and i despair. It's becoming the most frustrating, enraging and infuriating task.

I've looked under 'Server variables and settings' and in a very long list can find no mention whatsoever of 'global_variables'...so are they on or off? haven't got a clue and before i smash my computer against the wall...i'm walking away and will try again tomorrow.

Final annoying straw...par for the course i guess. I just went to add the above paragraphs in an edit...and as always when one presses save you are told you don't have permisiion to do so..refresh the page and login again. The backbreaker being, after doing that the edit option has gone and i can only 'add reply'....nice!

[scottie2212]

Originally Posted by perplexed

Too be honest i've just spent an hour trying a myriad of ways to get this database working and i despair. It's becoming the most frustrating, enraging and infuriating task.

I've looked under 'Server variables and settings' and in a very long list can find no mention whatsoever of 'global_variables'...so are they on or off? haven't got a clue and before i smash my computer against the wall...i'm walking away and will try again tomorrow.

Final annoying straw...par for the course i guess. I just went to add the above paragraphs in an edit...and as always when one presses save you are told you don't have permisiion to do so..refresh the page and login again. The backbreaker being, after doing that the edit option has gone and i can only 'add reply'....nice!

The php.ini variable is register globals, I recommend you don't touch it.

I am assuming your scripts use the GET method to parse the data to your variables.
All you need to do is add is $a = $_GET['a']; $b = $_GET['b']; $c = $_GET['c']; etc.

[black-dog]

Originally Posted by perplexed

Result from adding the echo $sql shows that all three of the form fields are not receiving the values. This means the query reads simply as 'select all' !

SELECT * FROM table WHERE '' LIKE '%%' ORDER BY ''


Incidentally, i've never seen the mysql_real_escape_string mentioned before. Again that shows how long ago it was since i wrote the code. The database whilst being hosted on my website and accessed via a webpage is just for personal use. As a result one needs a username and password to access the search page.

Fair enough, but I would recommend the use of mysql_real_escape_string in ALL cases of user supplied data, password protected or not. It's just a good habit to get into and helps prevent sql injection (Google it for more info).

[black-dog]

Originally Posted by perplexed

Too be honest i've just spent an hour trying a myriad of ways to get this database working and i despair. It's becoming the most frustrating, enraging and infuriating task.

I've looked under 'Server variables and settings' and in a very long list can find no mention whatsoever of 'global_variables'...so are they on or off? haven't got a clue and before i smash my computer against the wall...i'm walking away and will try again tomorrow.

OK, calm down and take a deep breath. The first thing is not to blame the hosting, all installations will be like this soon, it's just the PHP upgrade.

Your variables are not getting to your database query, that much is obvious from the echo $sql statement.

In the past, register_globals was on by default. This means, among other things that if you write

http://www.example.com?myvariable=foo&myothervariable=bar

then the two variables $myvariable and $myothervariable will be available in your script.This is a potential security risk in badly written scripts. (there is loads on this on the web so I won't explain further here) As of PHP 5 register_globals is off by default and scripts relying on it have to be rewritten. That's just fact, deal with it, we all have to.

So, how are your variables getting into the query?

If they are coming from a form using the POST method then the code I posted earlier will work. If they are coming from the URL as above, replace POST with GET (as Scottie has mentioned) If you are not sure if it is GET or POST then do

PHP Code:

$a=mysql_real_escape_string($_REQUEST['a']);
$b=mysql_real_escape_string($_REQUEST['b']);
$c=mysql_real_escape_string($_REQUEST['c']); 


Before the $sql=... bit.


If that fails, tell me where your variables are coming from and I'll help you get it to work.

[perplexed]

Black-dog first of all most importantly many thanks for the replies. I was fearing that my requests for help would have exceeded peoples patience limits.

One thing i should make clear is that i am not blaming my new hosts for my long serving scripts falling flat.

Your comment about the php upgrade is enlightning. My Lycos one (i'll check in detail later) was a very similar version i am sure, however, maybe it was slightly one update back?.

I'll address the queries that you need answering tonight or tomorrow - i'm just off out right now.

I was going to post the form scripts and complete script for the form action but thought it would either bore/annoy you or take up to much space on the forum. Having said that they are very small.

When i was trying the code snippet you provided i did indeed place it ahead of the mysql query. As to why it didn't work, i will try again when in a calmer frame of mind lol.

I will indeed change all my scripts across my site to use the (new to me) 'mysql_real_escape_string'. First of all i need to get the database running again. It's not accessible to others anyway, as only myself has a username and password for it.

Also, scottie mentioning php.ini now reminds me my parameter print out was obtained from the php documentation. I was searching for it in the mysql docs.

I apologise if this is all now more fitting in the php forum???, but, i find it impossible to separate php and mysql as they 99% of the time go hand in hand. I didn't wish to duplicate my post in both forums. Initially, i presumed the fault lay in the syntax of my mysql scripting.

[black-dog]

Originally Posted by perplexed

Black-dog first of all most importantly many thanks for the replies. I was fearing that my requests for help would have exceeded peoples patience limits.

I'm a coder - I have endless patience...

Originally Posted by perplexed

was going to post the form scripts and complete script for the form action but thought it would either bore/annoy you or take up to much space on the forum. Having said that they are very small.

Usual advice is to cut down scripts to the minimum that still shows the error

Originally Posted by perplexed

I will indeed change all my scripts across my site to use the (new to me) 'mysql_real_escape_string'. First of all i need to get the database running again. It's not accessible to others anyway, as only myself has a username and password for it.

mysql_real_escape_string() won't solve this problem, but it's good practice to sanitise any user input, even if it is password protected.

Originally Posted by perplexed

Also, scottie mentioning php.ini now reminds me my parameter print out was obtained from the php documentation. I was searching for it in the mysql docs.

You really don't need to be making any changes here, even if you can.

Originally Posted by perplexed

I apologise if this is all now more fitting in the php forum???

Oh we'll probably let you off...

[perplexed]

Originally Posted by black-dog

Your variables are not getting to your database query, that much is obvious from the echo $sql statement.

In the past, register_globals was on by default. This means, among other things that if you write

http://www.example.com?myvariable=foo&myothervariable=bar

then the two variables $myvariable and $myothervariable will be available in your script.This is a potential security risk in badly written scripts. (there is loads on this on the web so I won't explain further here) As of PHP 5 register_globals is off by default and scripts relying on it have to be rewritten. That's just fact, deal with it, we all have to.

PHP Code:

$a=mysql_real_escape_string($_REQUEST['a']);
$b=mysql_real_escape_string($_REQUEST['b']);
$c=mysql_real_escape_string($_REQUEST['c']); 


Before the $sql=... bit.


If that fails, tell me where your variables are coming from and I'll help you get it to work.

OK after giving up on my whole website in despair for a couple of months (because i can't get simple scripts and webpages displaying that have worked elsewhere for years) i'm trying to get the time to have another stab.

My query is POSTED from the webpage form. I added the php code you suggested.

What happens now is instead of the whole database table displaying, and not just the selected rows, none of it displays.

However, a possible step nearer to solving the problem is that the MySql query now contains the fields entered into the search form - previously the fields were showing empty.

So whilst not displaying the results yet, the query shows up as;

Select * from database_name where artist like 'beatles' song like '%love%' order by 'folder'

This should normally display all songs by the beatles with the word 'love' anywhere in the title. (folder refers to the physcal folder i have music scores stored in)

Do you think the problem now moves on to the target (of the POST command) script of the query page? If so i can supply the script for that, it is only a few lines.

[black-dog]

Originally Posted by perplexed

So whilst not displaying the results yet, the query shows up as;

Select * from database_name where artist like 'beatles' song like '%love%' order by 'folder'

This should normally display all songs by the beatles with the word 'love' anywhere in the title. (folder refers to the physcal folder i have music scores stored in)

Do you think the problem now moves on to the target (of the POST command) script of the query page? If so i can supply the script for that, it is only a few lines.

Not sure what you mean by 'moves on'. Your query is wrong for a start. It should be Select * from database_name where artist like 'beatles' AND song like '%love%' order by 'folder' The AND is missing. How is the query being constructed? Is error reporting on (if not, add the line error_reporting(E_ALL); at the top of your script) and have you any undefined variable errors?

[perplexed]

Hi black-dog, i was hoping you were still around these forums lol.

First of all i must clarify that 'AND' is included in the query, i had just made a typo when posting it here.

Secondly, wow i like the error_reporting(E_ALL) function, i think i'll be adding that to every single script i ever write.

It threw up the following error,

Fatal error: Function name must be a string in E:\inetpub\vhosts\website.com\subdomains\xxxx\http docs\file.php on line 21

Rather than come running straight back here, i did a little research on Google. Someone with a similar problem was told that

$_POST is an array not a function, access its elements with [] not ()

Now the nearest line to 21 is,

$sql=stripslashes ("SELECT * FROM database_name WHERE '$a' LIKE '%$b%' ORDER BY '$c'");

I do know that when a line is quoted as a php error it can actually be in the previous line/s. Therefore, i'll mention the segment above which is,

$a=mysql_real_escape_string($_POST['a']);
$b=mysql_real_escape_string($_POST['b']);
$c=mysql_real_escape_string($_POST['c']);

Would you know if i should replace any of the curved brackets with square ones in any of the above?.

Only in the escape part, there are square brackets, although those are surrounded by circular ones. Alternatively is the fault with the select segment or the inclusion of stripslashes (which had to used on lycos).

[black-dog]

Get rid of the stripslashes on line 21