MySQL driving me crazy - a server issue?

[MortimerJazz]

I'm transferring some php scripts from one server to my new eukhost.

However, for some reason the MySQL isn't working - and it's driving me mad!!

Here's the PHP I'm using:

PHP Code:

$recent_sql = mysql_query("SELECT id, 
                            title, 
                            type, 
                            price,
                            description, 
                            hire, 
                            age, 
                            adults, 
                            requirements, 
                            overnight, 
                            picture, 
                            dim, 
                            gallerypics
                              FROM range 
                            WHERE type='$choice'
                            ORDER BY price ASC"); 


I keep being told though that that "supplied argument is not a valid MySQL result resource."

Is there any reason behind the scenes why this wouldn't work? As I can't see anything wrong with it, and as I said it worked on my previous server.

ANY help would be REALLY appreciated!!

[DavidAllen]

Doesn't look wrong - are you sure it's the SQL Server that's the problem? The error suggests that the SQL Server call didn't return a result set. I assume if you have something like

$result = mysql_query($recent_sql, $link_id) or die('Query failed: ' . mysql_error().'<br>'.$query);
$row = mysql_fetch_row($result) or die(mysql_error());

that would tell you more about the problem - I'm guessing it's either a field/table name issue or the $link_id.

[MortimerJazz]

Mmmmm - still having problems.

It might have something to do with the way I'm reading the variable I'm passing in the header.

The URL is something like this www.mydomain.com/view.php?id=1

and the PHP I'm using is:

PHP Code:

$choice = $_GET['id']; 


Is that the problem?

P.S. MASSIVELY appreciate your help here by the way ...

[DavidAllen]

that looks fine - you say you transfered scripts from another server - have you got the connection string right? - I presume the table and field names are the same - but what about the database name?

[MortimerJazz]

Yeah, the database is being selected fine.

The only error message I'm getting is:

Query failed: Unknown column 'castle' in 'where clause'

I get that when I'm using the following MySQL:

PHP Code:

$recent_sql = mysql_query("SELECT id, 
                            title, 
                            type, 
                            price,
                            description, 
                            hire, 
                            age, 
                            adults, 
                            requirements, 
                            overnight, 
                            picture, 
                            dim, 
                            gallerypics
                              FROM range 
                            WHERE type=$choice"); 


The string 'castle' is what is passed in the header. It should be the value of the 'type' field rather than the name of the column

[DavidAllen]

Ah ok i see
try mysql_query("SELECT id, blah, blah, etc FROM range WHERE type = '".$choice."'")

trouble was where clause looked like - where type=castle rather than what you wanted which is WHERE type='castle'

[MortimerJazz]

Still no luck I'm afraid.

I'm just getting "Query Failed:" without any further explanation now

[DavidAllen]

did you get the quote marks right - single double .$choice.double single double ?

[MortimerJazz]

Yup - I just don't get it

PHP Code:

$recent_sql = mysql_query("SELECT
                            type, 
                            price,
                            description, 
                            hire, 
                            age, 
                            adults, 
                            requirements, 
                            overnight, 
                            picture, 
                            dim, 
                            gallerypics
                              FROM range 
                            WHERE type='".$choice."'");


$result = mysql_query($recent_sql, $link_id) or die('Query failed: ' . mysql_error().'<br>'.$query);
$row = mysql_fetch_row($result) or die(mysql_error()); 


[DavidAllen]

OK sorry - thought you hadn't used my original code
just need to use $recent_sql="SELECT etc as above" ie don't need mysql_query - that is done on the line below $result=

[MortimerJazz]

I'm sorry to be a pain.

I've now got my query looking like this:

PHP Code:

$recent_sql = "SELECT type, price, description, hire, age, adults, requirements, overnight, picture, dim, gallerypics FROM range WHERE type='".$choice."'"; 


... but still no luck

[MortimerJazz]

I don't get what $link_id is ... could that have anything to do with it?

[DavidAllen]

that line should be ok now
that has put the SQL Server into a variable called recent_sql
Now need to do the actual call
so
$result=mysql_query($recent_sql);;
should give the data from the query into an object called $result
to get from the $result object holing all the data to individual rows we need the next line
$row = mysql_fetch_row($result);
which leaves us pointing at the first row and can access the fields in that row like :
echo $row['price'];
Only difference is that the $link_id optional paremeter has been removed from the mysql_query call - I use $link_id as the return from the module that does the database connection - you don't need it

[DavidAllen]

I don't get what $link_id is ... could that have anything to do with it?

we cross posted - yes - get rid of it

[MortimerJazz]

So frustrating:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/bounce6/public_html/inf.php on line 37

Thank you very much for your time

I've gone through testing everything.

The script connects to the database, it finds the database ... and there's definitely a column called type in the table.

[MortimerJazz]

Hang on, I think I might have it!

[MortimerJazz]

Ok, I've got rid of the error message which suggests that the query's now running through fine.

I've got one last question then I promise I'll leave you alone!

I'm trying to use a while loop to cycle through the database and populate various variables.

This is the entire code:

PHP Code:

$recent_sql = "SELECT type, price, description, hire, age, adults, requirements, overnight, picture, dim, gallerypics FROM range WHERE type='$choice'";


while ($recent = mysql_fetch_array($recent_sql)){
$type=$recent['type']; 
} 


Now the wierd thing is that without changing the MySQL call, suddenly I'm getting error messages again saying that it's not a valid resource.

I dont' get it ...

[DavidAllen]

you missed the $result line
so you get

PHP Code:

$result=mysql_query($recent_sql);
while ($row = mysql_fetch_array($result)) {
 $type=$row['type'];  //or whatever else you want to do for each row in the result
} 


[MortimerJazz]

You Sir are an absolute legend - not to mention a life saver.

And you're just up the road in Amersham!

Thanks so much for all your help!

[DavidAllen]

Originally Posted by MortimerJazz

You Sir are an absolute legend - not to mention a life saver.

And you're just up the road in Amersham!

Thanks so much for all your help!

No Problem - anytime. I know how frustrating minor mistakes can be