a1phanumeric / php-mysql-class Goto Github PK

in the current commit the settype() function was misleadingly used in the CleanData() function. The result of settype() is not the manipulated data but a boolean result code. This results in empty inserts in many cases.

Hi

Are we allowed to use this in a paid application were creating or can we just use it in free applications?

I really liked this class to use mysql with PDO, but I really need it to return the errors generated by SQL queries.
Before that I used with the php mysqli extension procedurally, but I think there are many unnecessary lines to make prepared statements and I did not want to make my applications vulnerable. This class has already helped me a lot in this, now only need to return the errors of mysql queries.
Thank you!

Hey,

first. thanks for your great work.

second. Because mysql is deprecated in php 5.5.x, I created a class by using mysqli. My class uses yours as base, but because it extends mysqli and s.o. I did not a pull request. Hope it helps anyway.

greetz,
Kanasaru

Thanks for your class. I have used it today but when I use the method exist_table, I got the error at line 43 class.DBDPO.php. That is the method add_table_prefix does not exists.

Thanks!

Dat

My Insert was failing and after looking at the query I was still getting a trailing comma at the end of the generated query. I changed line 160 to $query = substr($query, 0, -2); and it fixed the problem.

Thanks for the great script.

Hi,
How use datatypes and wheretypes with the update function ?

update($table, $set, $where, $exclude = '', $datatypes, $wheretypes)

$table is a value;
$set is an array
$where is an array

but i don't know how to use/declare datatypes and wheretypes.

Thanks

Was trying to create a check to see if a table exists, if it doesn't, then create it.

I noticed that if I try to use it on a nonexistent table, it errors on line 247 or 254 depending on if I use ArrayResult or ArrayResults respectively. Not sure if that's intended or not. I was doing a SELECT, I switched to using SHOW TABLE and that works fine

Hi @a1phanumeric

Thank you for this it's very useful. i can get the Insert function to work correctly but not the Select function, see my code below.

it returns 1 instead of the array of results

thanks

Lee

Hello, when I run the following query $db->ExecuteSQL('SET NAMES utf8'); I get the following error:

 # Line 121
 mysql_num_rows() expects parameter 1 to be resource, boolean given

I fixed with:

 @@ -118,8 +118,13 @@ class MySQL {
      function ExecuteSQL($query){
          $this->lastQuery     = $query;
          if($this->result     = mysql_query($query, $this->databaseLink)){
 -            $this->records   = @mysql_num_rows($this->result);
 -            $this->affected  = @mysql_affected_rows($this->databaseLink);
 +            if (gettype($this->result) === 'resource') {
 +                $this->records   = @mysql_num_rows($this->result);
 +                $this->affected  = @mysql_affected_rows($this->databaseLink);
 +            } else {
 +                $this->records  = 0;
 +                $this->affected = 0;
 +            }

If there is another way to run this query, please tell me

I believe ArrayResults() will help me maintain the format in outputs i.e. if I select one row or multiple they should both come in an array. How can I use this function ?

When it comes to website making, one important aspect is efficiency, and although this class is very simple (which I like), it's not very efficient. Manipulating data in large tables is very slow, and if what you're trying to do (select or delete mostly) can't be done, your current functions are wasteful. Mathematical functions however, are extremely fast because they don't retrieve the actual data. The one in particular that would make this class efficient is using COUNT to make sure data exists before trying to retrieve/delete it. A separate Count function could be useful, or simply add that into the existing functions.

And as for the problem in one of your functions, while browsing the code, I found this in your Select function:
$query = substr($query, 0, -5);
It works perfectly fine, if you leave $operand at it's default. But trying to use OR would have undesired results. It needs to be changed to:
$query = substr($query, 0, ( strlen( $operand ) + 2 ) * -1 );

Edit: As another thought, retrieving all columns is also somewhat inefficient, and the class doesn't have an option for choosing the columns retrieved.

Hi, I want to request for REPLACE function like this command:
http://dev.mysql.com/doc/refman/5.0/en/replace.html

Thanks

Add composer support so that people using it for package management can also use this class.

As SecureData is currently written, the function loops to the $key of $data, but references $dataType through an $i variable that is being incremented separately.

Personally I prefer the approach where $dataType contains a list of $data variables, like in this example:

$data = [
    'user' => 'myUserName',
    'password' => 'password',
    'email' => '[email protected]'
];
$dataType = [
    'user' => 'str',
    'password' => 'str',
    'email' => 'email'
];

that way, we can simply loop through $data, use

$data[$key] = $this->CleanData($data[$key], $types[$key]);

I really like your work,I am trying to do a login... but I just can't find a way to manipulate the result...

if($this->records > 0){
$this->ArrayResults();
return $this->arrayedResult;
}else{
return true;
}

When we use ExecuteSQL, commonly here we can't use :

$ret = $db->ExecuteSQL (....);
if (empty($ret)) {
}

coz it's always "TRUE", so you don't know it's empty.

in PHP v5.5 mysql functions are removed. Please revert back your commit because I thought it started working because of that silly thing. I spent all night before trying to figure it out and forgot somehow I really don't remember but the day after I went back to 5.4 because I found out other functions were removed. So now I realized that the real reason your script was removed was because I was using the wrong version. Please update you readme to note this as well if you don't plan on changing it. Here's the source http://www.php.net/mysql_query

(sorry for any spelling and grammar mistakes, its late and I'm exhausted. Read the source it'll explain all)

EDIT: I mean my commit

it would be nice if the SecureData-function could be extended by variable-taypes like in the Wordpress database-class (https://codex.wordpress.org/Class_Reference/wpdb#INSERT_rows).
This can increase the database-security much more and ensure that no values enter the db that shouldn't.
i think that types like string, integer, float, boolean (true/false || 1/0) and datetime date timestamp would be nice.

It would be useful to have a simple function for closing the connection

Started using your class and it works great except when I had a typo in my SQL and was wondering why nothing was being inserted into my DB. A great addition to the script would be to display an error if a query fails. Keep up the good work.

When I use a SELECT statement that will return only ONE result, the returned arrayedResult is wrong ;(

can i use this method ?
" $where['a.user_id'] = $user_id;
$where['a.oder_id'] = $oder_id;
$where['a.user_id'] = 'b.user_id';
$from = TB_ORDER.'.a, '.TB_USERS.' .b';
$details = $this->Select($from, $where); "

thanks for your code

Is there a function to INSERT rows when there is no, or UPDATE if exists, using one MySQL query?

I use mysql version 5.6.21 - MySQL Community Server (GPL

Warning: mysql_query(): 6 is not a valid MySQL-Link resource in D:\WEB\exper\GRABBING\videohive\inc\class.MySQL.php on line 208

please help :)

MySQL functions should not be used at all, please consider switching to using MySQLi or PDO.

MySQLi is probably best with this class. Will do this, if I get chance.

Please fix error when result is empty, add check "empty($results)"

DBPDO.php line 93
// Allows the user to retrieve results using a
// column from the results as a key for the array
if($key != null && !empty($results) && $results[0][$key]){

Hello,

I'm using this wonderfull class in a IRC logbot made in PHP ofcourse ;) with SmartIRC.... But after 115-125 queries it stops working, well, i don't get the usual 1 as return, no error's or such....

As soon as i click on empty truncate function in PHPmyAdmin, restart the bot it works like a charm again.... Is this a bug in the script it self, am i doing something wrong or is it something with the Db?

Code to add something:

function logtext(&$irc, &$data){

            $oMySQL = new MySQL('phpircbot', 'USER', 'PASSWORD', 'HOST');

            $message    = $data->message;
            $channel    = $data->channel;
            $nick       = $data->nick;
            $ident      = $data->ident;
            $host       = $data->host;
            $from       = $data->from;
            $type       = $data->type;
            $date       = date( 'j-n-Y' );
            $time       = date( 'G:i:s' );


            $newLog = array('nick' => $nick,  
              'ident' => $ident,  
              'host' => $host,  
              'type' => $type,  
              'from' => $from,  
              'channel' => $channel,  
              'message' => $message, 
              'time' => $time  
               );

            print_r($newLog);

            $oMySQL->Insert('irclog', $newLog);

            echo $lastError;

        }

when something needs to be logged the function will start, first it creates a new connection, get's all the data then creates the array, prints the array into the console (debug) and sends it into the class to de DB.....

Or should i just start once the new mysql connection as soon as the bot's starts?

Thanks in advance,

//sem

EDIT:
Truncate whole table while bot is running also fixes the problem, so i guess it's a DB problem,.. But still the question tho ;)

Hello, well u calss is very very cool !

but how i get the last record using mysql_insert_id() ?,
//return the last id record
function getLastId(){

    return  mysql_insert_id();

}

i have added a function to do it, but maybe u can do it without my function ?

thans to you

It should be nice cosiderate only one way to show results because if your are using this library then you need "two" ways to process data of a "single" or a "multiple" arrayed result. I just commented your code to avoid this.

function ArrayResults()
{

    //if ($this->records == 1) {
    //    return $this->ArrayResult();
    //}

    $this->arrayedResult = array();
    while ($data = mysql_fetch_assoc($this->result)) {
        $this->arrayedResult[] = $data;
    }
    return $this->arrayedResult;
}

Thanks