‘No index used in query …’ mysqli

@orreipApr 22.2006

Can somebody explain what this error is for ?

~~[COLOR=Navy]~~Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message <<<No index used in query/prepared statement SELECT * FROM user WHERE email = ‘[email protected]‘>>>.[/COLOR]
My table structure is something like:
-id PK AutoIncrement
-user VARCHAR UNIQUE
-email VARCHAR UNIQUE.
When i execute the script and i have only one user in this db everything works good, but when i have 2 users the script throws the above error.
Is true that i don’t have an index on any fields, but i don’t understand why my script can’t execute the query anyway. If i write the exact query in phpMyAdmin it works.

Is it a bug with mysqli, or am i missing something ?
If somebody encountered the same problem and managed to deal with it can please help me solve it !

PS: I have the lastest WAMP installed on XP SP2. I’m using the object oriented feature of mysqli with simple queries.

to post a comment

PHP

8 Comments(s) _↴

@chazzyApr 22.2006 — #post some of your code. i think it's complaining becaue you might have set it up to only expect 1 row, yet if email is not unique, you can get multiple rows.

@orreipauthorApr 22.2006 — #This is the function:

public function email_taken($email) {

$email=$this->injection_avoid($email);

$query="SELECT * FROM user WHERE email = '$email'";

$result=$this->mysqli->query($query);

if ($result->num_rows) //the email address already exists

$ret=true;

else $ret=false;

$result->free();

return $ret;

}

And i set up the email in DB to be UNIQUE.

@chazzyApr 22.2006 — #if you just want to count the # of rows, try this query

[code=php]
 $query = "SELECT COUNT(*) as cnt FROM user WHERE email='".$email."'";
 [/code]

and then parse the value of cnt. also, you're not checking that your query is actually working - throw a $mysqli->error ($this->error)

@orreipauthorApr 22.2006 — #i solved it ?

the problem was in my DB structure. I've put both user and email as UNIQUE under just one "user" index.

I have another problem. i took this code from manual with little modifications.

[code=php]
 <?php
 $mysqli = new mysqli("localhost", "root", "", "test");  // the exception is thrown here
 //what's below is never executed
 /* check connection */
 if (mysqli_connect_errno()) {
 printf("Connect failed: %sn", mysqli_connect_error());
 throw new Exception("Cannot connect to the database !!!");
 }
 
 /* close connection */
 $mysqli->close();
 ?>
 [/code]

Altough it writes there to use mysqli_connect_errno() to verify if the conn. was sucessful, that line never gets executed.

For the same reason i didn't throw any errors after the query in my email_taken() function.

Is this normal ? Is there some settings that i don't know about ?

@chazzyApr 22.2006 — #you need to make it mutually exclusive. you can't close the connection if the object was never created/failed to be created. so where it has $mysqli->close(); replace it with an else block that contains it.

Also, just wondering what version of mysql you're running. that seems like a new error in 5.1, don't remember seeing something like that before.

@orreipauthorApr 22.2006 — #you need to make it mutually exclusive. you can't close the connection if the object was never created/failed to be created. so where it has $mysqli->close(); replace it with an else block that contains it.

Also, just wondering what version of mysql you're running. that seems like a new error in 5.1, don't remember seeing something like that before.[/QUOTE]

I'm using PHP 5.1.2. The strange thing is that after i restarted my computer and the IF statement get's evaluated. I do think that there is a bug there.

As for my email_taken function the correct version should be something like this:

[code=php]
 public function email_taken($email) {
 $email=$this->injection_avoid($email);
 $query="SELECT * FROM user WHERE email = '$email'";
 if (!$result=$this->mysqli->query($query)) throw new Exception("Failed to execute the query");
 if ($result->num_rows) //the email address already exists
 $ret=true;
 else $ret=false;
 $result->free();
 return $ret;
 }
 [/code]

And when i call the function to try like this :

[code=php]
 try {
 if (email_taken('[email protected]') { do something}
 }catch (Exception $e) {
 echo $e->getMessage();
 exit();
 }
 [/code]

@chazzyApr 22.2006 — #actually i'm more concerned about the mysql version, ie that's why i asked what version it was. i assumed you were using a php 5.1.x line with exceptions and OOP.

as for the exception, i was thinking like this:

[code=php]
 public function email_taken($email) {
 $email=$this->injection_avoid($email);
 $query="SELECT * FROM user WHERE email = '$email'";
 try{
 $result=$this->mysqli->query($query);
 $ret= ($result->num_rows) ? true :false;
 $result->free();
 return $ret;
 }
 catch(Exception $e){
 throw new Exception("Failed to execute the query: ".$e->getMessage());
 } 
 }
 [/code]

But I really need to brush up on my exceptions, always gave me problems even in college. Not even sure if the syntax is correct.

Great, now you're making me need to install php 5.1.12 on my system and play around w/ exceptions. Thanks ;-)

@orreipauthorApr 22.2006 — #result->free() will result in an error because result object was not created in the first place in the SQL server goes down. As for exceptions if you throw one you need to catch it somewhere after, or it will give an error.

Also in #PHP _↴

preg_replace url rel's [RESOLVED] Fatal error: Call to undefined function: ftp_connect Problem in highlighting display data using SELECT Statement with css class

Success!

Help @orreip spread the word by sharing this article on Twitter...

Tweet This

‘No index used in query …’ mysqli

8 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

‘No index used in query …’ mysqli

8 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

8 Comments(s) _↴

Also in #PHP _↴