Page 1 of 2

[SOLVED]Query OK but still error?

Posted: Wed Feb 21, 2007 1:37 pm
by UrButtFullOfArr0ws
The Ninja Space Goat | 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]


I get the following error: 

Warning: mysql_data_seek(): Offset 0 is invalid for MySQL result index 2 (or the query data is unbuffered) 

mysql_data_seek is used in a function (made not by me, found on php.net) which i know 500% that it works for other queries:

Code: Select all

function mysql_fetch_all($result) { 
$return = array(); 
mysql_data_seek($result, 0); 
while ($row = mysql_fetch_assoc($result)) { 
$return[] = $row; 
} 
return $return; 
}
The query in PHP is this:

Code: Select all

"select * from accounts where email = '" . mysql_real_escape_string($_POST['email']) . "' and pass = '" . mysql_real_escape_string($_POST['pass']) . "'"
So the outcome would be (if username is a@a.com and password 1):

select * from accounts where email = 'a@a.com' and pass = '1'

What the query obviously does is it looks whether there is a data row in the db with that email and pass (if he is registered AND if the pass corresponds to the email)
Any suggestions?

Thx in advance.


The Ninja Space Goat | 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]

Posted: Wed Feb 21, 2007 1:59 pm
by RobertGonzalez
Are you sure your query is executing without errors?

Posted: Wed Feb 21, 2007 2:02 pm
by UrButtFullOfArr0ws
When i executed from mysql console, with the exact cases, letters, spaces, complete copy except mysql_real_escape_string() the query came out ok and it gave result of course.
Also PHP didn't return any MySQL warnings/errors about the query.

Posted: Wed Feb 21, 2007 2:07 pm
by RobertGonzalez
Show how you are passing the result of the query to your function. Post more code please.

Posted: Wed Feb 21, 2007 2:14 pm
by UrButtFullOfArr0ws

Code: Select all

function mysql_fetch_all($result) { //the function
    $return = array();
    mysql_data_seek($result, 0);
    while ($row = mysql_fetch_assoc($result)) {
        $return[] = $row;
    }
    return $return;
}
$query = "select * from accounts where email = '" . mysql_real_escape_string($_POST['email']) . "' and pass = '" . mysql_real_escape_string($_POST['pass']) . "'"; //the actual query
$res = mysql_query($query, $con); //execute the query
$bigarray = mysql_fetch_all($res); //call the function
That's the essential part, as above is the connection to the db and lower i use a simple IF - ELSE statement to check the data.

Posted: Wed Feb 21, 2007 2:21 pm
by Kieran Huggins
after every MySQL command use:

Code: Select all

or die('error: '.MySQL_error());

Posted: Wed Feb 21, 2007 2:24 pm
by RobertGonzalez
Try this...

Code: Select all

<?php
function mysql_fetch_all($result) { //the function
    $return = array();
    
    mysql_data_seek($result, 0);
    
    while ($row = mysql_fetch_assoc($result)) {
        $return[] = $row;
    }
    
    return $return;
}

// Set some vars
$email = mysql_real_escape_string($_POST['email']);
$pass = mysql_real_escape_string($_POST['pass']);

//the actual query
$sql = "SELECT * 
		FROM `accounts` 
		WHERE `email` = '$email' 
		AND `pass` = '$pass'";

//execute the query
$res = mysql_query($sql, $con) or die('Error in the query: ' . mysql_error());

//call the function 
$bigarray = mysql_fetch_all($res);
?>

Posted: Wed Feb 21, 2007 2:51 pm
by UrButtFullOfArr0ws
Thx but unfortunately didn't work :?
On first glance i didn't believe it was going to do much anyway couse i got the same way of writing the query in some other files too. All of the others seem to work fine.
At some point however the fact it might have something to do with the database and not with the query itself crossed my mind.
Any other suggestions?

Posted: Wed Feb 21, 2007 3:22 pm
by RobertGonzalez
Try using this function...

Code: Select all

<?php
function mysql_fetch_all($result) { //the function
    $return = array();
    
    if (mysql_num_rows($result)) {
        mysql_data_seek($result, 0);
        
        while ($row = mysql_fetch_assoc($result)) {
            $return[] = $row;
        }
    }
    
    return $return;
}
?>

Posted: Wed Feb 21, 2007 3:44 pm
by UrButtFullOfArr0ws
Going good now. The error dissapeared but im not getting any results.
Going to do some debugging but now im goin to sleep. :D

Posted: Wed Feb 21, 2007 3:47 pm
by RobertGonzalez
You are not getting results because your query is empty. That is why you were getting an undefined offset. You were telling PHP to move the result pointer to a position that didn't exist. I would seriously consider doing a var_dump() after reading the result into an array with mysql_fetch_array().

Posted: Thu Feb 22, 2007 9:38 am
by UrButtFullOfArr0ws
OK i'm back.
Is this the correct syntax?

Code: Select all

$res = mysql_query($sql, $con) or die('Error in the query: ' . mysql_error()); 
 
$bigarray = mysql_fetch_array($res); 
var_dump($bigarray);
IF so then i got this:
bool(false)
from var_dump().

Any suggestions and anything i overlooked?
Thx a lot for helping :) :) :)

Posted: Thu Feb 22, 2007 9:45 am
by Begby
Did you echo your SQL just to make sure the SQL statement is correct and not coming up with empty string where the user name and password go?

Posted: Thu Feb 22, 2007 9:47 am
by UrButtFullOfArr0ws
That's the first thing i did after i saw i got an error, i started puttin echoes everywhere for everything that might be the cause of the problem.

Posted: Thu Feb 22, 2007 10:46 am
by volka
Then what does

Code: Select all

$res = mysql_query($sql, $con) or die('Error in the query: ' . mysql_error());
echo '<p># result for "', $sql, '": ', mysql_num_rows($res), "</p>\n";
$bigarray = mysql_fetch_all($res);
print?