[SOLVED] SQL error with classes/functions

[BRUm]

I've recently started learning OOP, and I have a rather simple problem, yet I don't know how to solve it. Obviously I'm using a certain aspect of OOP wrong:

 

<?php

include "connect.php";

//All classes placed and called from here

class database {

public function query($x) {

$query = mysql_query($x);
print_r($query);
$result = mysql_fetch_array($query);

}

}

//Test above query
$database = new database();
$database->query("select * from `users` where `username` = 'admin'");
print $result[0];
print $result[1];

?>

 

It gives me an sql error regarding line 13, so it seems it's not parsing the sql query I'm using the $database->query().

 

Thanks for any help in advance.

[mrdamien]

Without seeing the error message I can only guess the problem:

 

mysql_query  ( string $query  [' date= resource $link_identifier  ] )

 

link_identifier

 

    The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If by chance no connection is found or established, an E_WARNING level warning is generated.

 

You need to pass the link_identifier (from $link = mysql_connect()) to your class.

Its basically a scope issue, your database class isn't looking outside of itself for the link_identifier

[BRUm]

Ah I see. So does this mean I'm going to have place a new DB connect in the class? Wouldn't this mean every time I run the class, it will start a new DB connection?

 

This is my error:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in .../classes.php on line 13

[rhodesa]

I would place everything inside your class:

 

<?php
#Get rid of this and move it into class
#include "connect.php";

//All classes placed and called from here

class database {

var $cnx;

public function database ( $host, $user, $pass, $db ) {
  //Run your connect info here
  $this->cnx = mysql_connect($host,$user,$pass);
  mysql_select_db($db,$this->cnx);

}

public function query($x) {

$query = mysql_query($x,$this->cnx);
print_r($query);
$result = mysql_fetch_array($query);
return $result;
}

}

//Test above query
$database = new database('hostname','user','password','database');
$result = $database->query("select * from `users` where `username` = 'admin'");
print $result[0];
print $result[1];

?>

[BRUm]

Thanks for your help. Is there a way to omit the database connection within the class? It would cause quite a memory loadon my server to have a new connection established every time the class is used.

[rhodesa]

in your connect.php:

 

$CNX = mysql_connect(...);

 

in your class:

 

public function database ( ) {
  global $CNX;
  $this->cnx = $CNX;
}

 

BUT...the better way is to keep it like i posted originally, and initiate the class once. So, I would put this in your connect.php:

 

$DB= new database('hostname','user','password','database');

 

and then in your script, use the same $DB object over and over:

 

print_r($DB->query("select * from `users` where `username` = 'admin'"));
print_r($DB->query("select * from `users` where `username` != 'admin'"));

[BRUm]

Thank you very much for all your helps guys.

[BRUm]

OK I've implemented the code as you've shown, yet I still get an SQL resource error:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in .../connect.php on line 31

 

Connect.php

<?php

//Class db connection
class database {

var $cnx;

public function database ( $host, $user, $pass, $db ) {
  //Run connect info here
  $this->cnx = mysql_connect($host,$user,$pass);
  mysql_select_db($db,$this->cnx);
}

public function query($x) {

$query = mysql_query($x,$this->cnx);
$result = mysql_fetch_array($query); //This is line 31
return $result;
}

}

$db = new database('localhost','***','***','***');

?>

 

The script using the class above:

<?php

include "connect.php";

//All classes placed and called from here

//Test query function in database class
print_r($db->query("select * from `users` where `username` = 'admin'"));

?>

[duclet]

Trying changing this line

$query = mysql_query($x,$this->cnx);

to

$query = mysql_query($x,$this->cnx) or die('SQL: '.$x.'<br />Error: '.mysql_error());

 

That way we can actually see what is the problem with SQL.

[trq]

The connection resource argument is optional in most mysql functions, leaving it out is a simpler option (unless your planning on using multiple database servers) as the mysql_* functions are smart enough to simply use the current open connection.

 

The problem you are experiencing happens when your query has failed, since your function has no error handling at all. Your function should look more like....

 

<?php

public function query($sql) {

  if ($result = mysql_query($sql)) {
    if (mysql_num_rows($result)) {
      return mysql_fetch_assoc($result);
    }
  }
  return false;
}

?>

 

However, having shown you that. This function is only usefull for retrieving a single record.

[BRUm]

OK when I implement error handling it gives me this very strange error:

 

Error: Table 'civ_table.users' doesn't exist

 

I don't have a clue where the .users has come from :/

[twostars]

OK when I implement error handling it gives me this very strange error:

 

Error: Table 'civ_table.users' doesn't exist

 

I don't have a clue where the .users has come from :/

print_r($db->query("select * from `users` where `username` = 'admin'"));

 

If your database name is civ_table (weird name for a database though), then it all makes sense.

[BRUm]

The name isn't important, I changed the name slightly for the nature of my code ^^

 

But nevertheless, it's giving me that error when the DB and table given to connect to are correct.

[trq]

Can you post your current code? Both the class, and the calling code.

 

It might also help if you post your db structure.

[BRUm]

OK Here's the code:

 

<?php
#Connect.php

//Class db connection
class database {

var $cnx;

public function database ( $host, $user, $pass, $db ) {
  //Run connect info here
  $this->cnx = mysql_connect($host,$user,$pass);
  mysql_select_db($db,$this->cnx);
}

public function query($x) {

$query = mysql_query($x,$this->cnx) or die('SQL: '.$x.'<br />Error: '.mysql_error());
$result = mysql_fetch_array($query);
return $result;
}

}

$db = new database('localhost','civ_admin','password','civ_db');
?>

 

<?php
#Calling code - classes.php

include "connect.php";

//All classes placed and called from here

//Test query function in database class
print_r($db);
print_r($db->query("select * from `users` where `username` = 'admin'"));

?>

 

The DB structure is nothing special. It's meant to call the table `users` within the `civ_db` database, and retrieve the row where `user` = `admin`.

[trq]

There is nothing wrong with your code, you either have a spelling mistake in your db/table name or are using incorrect captialization.

[BRUm]

Argh stupid me! I don't even have a `users` table lol I changed it to `data`.

 

Can anyone tell me what this error means:

 

Fatal error: Cannot redeclare class database in connect.php on line 20

[BRUm]

Nevermind, fixed it with require_once.