Changed servers - code doesn't work anymore

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

vdupuy
Forum Newbie
Posts: 6
Joined: Fri Aug 11, 2006 1:09 pm

Changed servers - code doesn't work anymore

Post by vdupuy »

Hey,

My site is currently hosted on a server using phpMyAdmin 2.6.4-pl3 and MySQL 3.23.58.

I am working on migrating my site to another hosting company, using phpMyAdmin 2.7.0-pl2 and MySQL 4.0.24.

Here's the deal. I have several instances where I use the following code:

296 $conn = mysql_connect("SERVER","USER","PW");
297 $sql = "SELECT * FROM pixels WHERE viewable = 'Y'";
298 $total = mysql_num_rows(mysql_query($sql, $conn));

It used to work fine, but now I get this:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /.../index.php on line 298

I really don't understand what can cause this. Anyone?

Thanks!

Vincent
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Did you copy the database over to the new server and set the correct username and password?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

check that your connection is working first and formost... then... mysql_query returns a resource on success (with select queries) or false on failure, so you need to check that it returns a resource before you attempt to send a resource to mysql_num_rows.

You can read about mysql_query's return values on php.net: http://fr3.php.net/manual/en/function.mysql-query.php
vdupuy
Forum Newbie
Posts: 6
Joined: Fri Aug 11, 2006 1:09 pm

Post by vdupuy »

Yeah, I did, if fact I was getting a bunch of connection errors at first, since the new hosting provider doesn't support LOCALHOST as the server name. Once I changed it to what they told me to use, and set the right user and pw, I'm not getting the connection error anymore. I get this one instead.
vdupuy
Forum Newbie
Posts: 6
Joined: Fri Aug 11, 2006 1:09 pm

Post by vdupuy »

How should I do that?

I just ran the query in MySQL and got 1838 rows, which is what I should get.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Their server settings may require all non-keywords be in backticks. ex.

Code: Select all

SELECT * FROM `table` WHERE `field` = 'foo'
Also, you could optimize the query a tiny bit by using COUNT(*) or COUNT(`someField`) instead of * and requesting how many records were found.

edit: :oops:
Last edited by feyd on Fri Aug 11, 2006 4:53 pm, edited 1 time in total.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Code: Select all

if($resource = mysql_query($query)){ // check that mysql_query was successful
    $num_rows = mysql_num_rows($resource);
}
else{
    echo "Error: " . mysql_error() . "\n";
    echo "Query: " . $query . "\n"; // [feyd] Ninja forgot a $! 
}
vdupuy
Forum Newbie
Posts: 6
Joined: Fri Aug 11, 2006 1:09 pm

Post by vdupuy »

No luck so far... but I noticed that I'm getting the same error with mysql_fetch_array, as below.

<?
190 $sql = "SELECT * FROM pixels WHERE viewable= 'Y' AND adult = 'N' ORDER BY created DESC LIMIT 0,4";
191 $result = mysql_query($sql, $conn);
192
193 $i = 0;
194 while($row = mysql_fetch_array($result))
195 {
blablabla

This also produces

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /.../index.php on line 194
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

it's because your queries are failing. Something is wrong with your SQL syntax
vdupuy
Forum Newbie
Posts: 6
Joined: Fri Aug 11, 2006 1:09 pm

Post by vdupuy »

Indeed. What is weird is that it works perfectly on the other server. This is what I got from your test:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/v/d/u/vdupuytpw/html/index.php on line 298
Error: Query was empty Query:

I also tried putting the arguments in backticks, no luck.

What else could be different between the two servers??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

sprinkle mysql_error() calls in after mysql_query().
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Post more of the code please.
User avatar
bokehman
Forum Regular
Posts: 509
Joined: Wed May 11, 2005 2:33 am
Location: Alicante (Spain)

Post by bokehman »

My guess is the table does not exist on the new server.
vdupuy
Forum Newbie
Posts: 6
Joined: Fri Aug 11, 2006 1:09 pm

Post by vdupuy »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Here is what I have, which is working on the other server:

Code: Select all

<?
	$conn = mysql_connect("mysql155.secureserver.net","xxxx","xxxx");
	$db = mysql_select_db("pixels",$conn);

	session_start();
	
	$error = false;
	if ($_POST["login"])
	{
		$sql = "SELECT * FROM pixels WHERE email = '" . $_POST["email"] . "' AND password = '" . $_POST["password"] . "'";
		$result = mysql_query($sql, $conn);
		
		if (mysql_num_rows($result) > 0)
		{
			$row = mysql_fetch_array($result);
			
			$_SESSION["user"] = $row["id"];
			
			header("Location: edit.php");
			exit;
		}
		else
			$error = true;
	}
?>

	<?
			$sql = "SELECT * FROM pixels WHERE viewable= 'Y' AND adult = 'N' ORDER BY created DESC LIMIT 0,4";
			$result = mysql_query($sql, $conn);
			
			$i = 0;
			while($row = mysql_fetch_array($result)) //****LINE GENERATING ERROR MENTIONNED****
			{
				$voted = 0;
			
				$i++;
				$sql = "SELECT * FROM reviews WHERE pid = " . $row["id"];
				$aresult = mysql_query($sql, $conn);
				
				$sql = "SELECT * FROM votes WHERE pid = " . $row["id"];
				$cresult = mysql_query($sql, $conn);
				
				$sql = "SELECT SUM(rating) FROM votes WHERE pid = " . $row["id"];
				$dresult = mysql_query($sql, $conn);
				
				$rating = mysql_fetch_array($dresult);
				
				$score = round($rating[0]/mysql_num_rows($cresult), 2);
				
				if ($_COOKIE["pp"][$row["id"]])
				{
					$voted = $_COOKIE["pp"][$row["id"]];
				}
				else
				{
					$sql = "SELECT rating FROM votes WHERE ip = '" . $_SERVER["REMOTE_ADDR"] . "' AND pid = " . $row["id"];
					$eresult = mysql_query($sql, $conn);
					
					$voted = mysql_result($eresult,0,'rating');
					
					if(!$voted)
						$voted = 0;
				}
		?>

?
		$sql = "SELECT * FROM pixels WHERE viewable = 'Y'";
		$total = mysql_num_rows(mysql_query($sql, $conn)); // **** THE OTHER ERROR LINE

	?>

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I don't see sprinkles of mysql_error().
Post Reply