Page 1 of 2
Changed servers - code doesn't work anymore
Posted: Fri Aug 11, 2006 1:48 pm
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
Posted: Fri Aug 11, 2006 1:50 pm
by Chris Corbyn
Did you copy the database over to the new server and set the correct username and password?
Posted: Fri Aug 11, 2006 1:52 pm
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
Posted: Fri Aug 11, 2006 1:53 pm
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.
Posted: Fri Aug 11, 2006 1:59 pm
by vdupuy
How should I do that?
I just ran the query in MySQL and got 1838 rows, which is what I should get.
Posted: Fri Aug 11, 2006 2:34 pm
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:

Posted: Fri Aug 11, 2006 2:35 pm
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 $!
}
Posted: Fri Aug 11, 2006 4:35 pm
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
Posted: Fri Aug 11, 2006 4:39 pm
by Luke
it's because your queries are failing. Something is wrong with your SQL syntax
Posted: Fri Aug 11, 2006 5:03 pm
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??
Posted: Fri Aug 11, 2006 5:08 pm
by feyd
sprinkle
mysql_error() calls in after
mysql_query().
Posted: Fri Aug 11, 2006 6:02 pm
by Benjamin
Post more of the code please.
Posted: Fri Aug 11, 2006 6:41 pm
by bokehman
My guess is the table does not exist on the new server.
Posted: Sat Aug 12, 2006 9:00 am
by vdupuy
feyd | Please use 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
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]
Posted: Sat Aug 12, 2006 9:11 am
by feyd
I don't see sprinkles of
mysql_error().