Re: Fwd: [PHP-DB] MySQLi connections
Thank you for your response. I am glad to hear that the structure is not a problem, but I suspect he new about singletons (I forgot that term) as he was an old C/C++ progammer (now retired), although I gather he was not a PHP guy. In all other respects, his code and documentation seem very good in comparison with other code from php programmers I've had to modify. It's a little difficult for me at times because he modified the PHP source code, adding his own custom functions. I'm just glad it's not something I have too worry about. I wish I had the time to become a PHP expert, a C++ expert, a Java expert, and time to save the world. Thanks again, Jeff Chris wrote: J. Hill wrote: Reading the thread on mysqli connection issues, I am curious if anyone knows of a downside to creating a connection from a configuration page and using it as a global in all functions? Good way to do it. You create the connection at the start and use the same thing throughout the whole script. I am used to creating a class and a database handle for functions to use, but I inherited an intranet that just uses a single "$mysqli = mysqli_connect " in a global main file and the just uses "global $mysqli" in all of it's functions (several hundred) that interact with the database. Since I have not seen this structure used elsewhere, I assume there is a good reason not to use it, but I haven't found one (except for the security issue in the use of globals). Just "old style", nothing wrong with using it that way. The person who wrote it probably just didn't know about singletons. I can't see a security issue with it either. $mysqli is set in the first file included (an 'init' type script). As long as register_globals is off, it can't be overwritten by a $_GET or $_POST .. of course you can destroy it yourself, but that's it. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Fwd: [PHP-DB] MySQLi connections
Reading the thread on mysqli connection issues, I am curious if anyone knows of a downside to creating a connection from a configuration page and using it as a global in all functions? I am used to creating a class and a database handle for functions to use, but I inherited an intranet that just uses a single "$mysqli = mysqli_connect " in a global main file and the just uses "global $mysqli" in all of it's functions (several hundred) that interact with the database. Since I have not seen this structure used elsewhere, I assume there is a good reason not to use it, but I haven't found one (except for the security issue in the use of globals). Could anyone point me towards any documentation on why such a structure is bad? Thanks, Jeff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Table optimization ideas needed
From a quick perusal of the article Chris mentions, I'd generally agree with that view about table optimization -- I'm not an expert on Postgres, but the recommendations generally seem to apply to MySQL as well. My basic view is that, if you are routinely doing a select on millions of rows, you probably need to take a step back and consider your general structure. Without revising the structure and other than indexing as Chris suggested, a couple off-the-cuff ideas: if the stability of the table is not critical, use MyISAM tables rather than InnoDB tables; try using stored procedures (MySQL>=5.0). While it isn't always true, my experience is that any table with a million rows or more is a problem created because the initial assumption was that the table would never grow that large so the general data structure was not fully thought through. Google is capable of handling searches through billions of rows of data not because it uses supercomputers but because of its data structure. Just my two centavos, Jeff Chris wrote: Shelley wrote: Hi all, I made a post a week ago to ask for the idea of the fastest way to get table records. Fyi, http://phparch.cn/index.php/mysql/35-MySQL-programming/126-fastest-way-to-get-total-records-from-a-table Look at the time even a 'count(1)' took. Then you can imagine how much time sql such as "select a,b from table_name where c='d'" will take. I have a lot of tables like that. So my questions is: What's your practice to optimize tables like that? I pretty much follow what I've said in this article: http://www.designmagick.com/article/16/PostgreSQL/How-to-index-a-database -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help with JOIN query
I may be a little confused: the desire is to return all the rows from TableA that match the record_id of a row in TableB that has the MAX timestamp? If so, why not something like: SELECT * FROM TableA a, TableB b WHERE a.record_id=b.record_id && timestamp=(SELECT MAX(timestamp) FROM TableB) ORDER BY action; I'm guessing I'm confused, that it's something more complicated you're looking for. Jeff Krister Karlström wrote: This will give you: ERROR : Invalid use of group function It seems like the use of an aggregate (or how is it spelled?) function is not allowed in a join statement... /Krister Jon L. wrote: You can try adding a quick test to the ON statement... SELECT * FROM TableA INNER JOIN TableB ON TableA.record_id = TableB.record_id AND TableB.timestamp = MAX(TableB.timestamp) Now, I haven't tested it. I can only say the theory of it is accurate. - Jon L. On Thu, Mar 6, 2008 at 12:46 PM, Graham Cossey <[EMAIL PROTECTED]> wrote: I can't see how to accomplish what I need so if anyone has any suggestions they would be gratefully received... I'm using mysql 4.0.20 by the way. I have two tables : TableA record_id product_ref TableB timestamp record_id action I want to create a SELECT that joins these 2 tables where the JOIN to TableB only returns the most recent entry by timestamp. At present (using PHP) I do a SELECT on TableA then for each record returned I perform a 2nd SELECT something like : "SELECT timestamp, action FROM TableB WHERE record_id = '$record_id' ORDER BY timestamp DESC LIMIT 1" I now want to do it with one query to enable sorting the results by 'action' from TableB. Any suggestions? Hopefully I've made sense, if not I'll happily try and explain further on request. -- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Insecure Hashes (was Re: Beginners Problem)
Well, if you're interested in some greater level of security where you can decrypt, you might want to check out rijndael encryption (a type of AES). Shane Kretzmann wrote a good rijndael php class that I think is still on phpclasses. It can be a bit difficult working with binary passwords, but if you really need security, it seems like one of the better options. I've been testing it myself, and the only issue I see is, of course: keeping the key secret. I've been looking at a few methods, but I'm always interested in others ideas on that issue. Regards, J. Hill Cristian Vrabie wrote: It's true md5 is very old but is not completely obsolete. Used in combination with a random salt is still hard to decode. Maybe i won't use it as encryption for passwords any more but I would use it for digital signature or data integrity check. [EMAIL PROTECTED] wrote: Whilst reviewing my penetration testing I have noticed that both the md5 and sha1 hashing algorithms are now considered less secure than previously thought. Migration to sha256 is encouraged: http://www.owasp.org/index.php/Cryptography#Algorithm_Selection Then I found the comment below from: http://uk3.php.net/manual/en/function.md5.php http://md5.rednoize.com offers a service to reverse engineer md5 hashes. Very useful if you got a md5 hash and need the plain text string of this md5 hash. The website has currently over 47 million hashes stored. It also has support for SHA-1 hashes. Consequently I shall be updating my authentication class. Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] multi_query "Commands out of sync" after Insert
I wouldn't think there would be a result set from a mysqli multi_query
when the query is a group of insert statements (there is no
auto_increment column), but I get a "commands out of sync" error on my
next query.
Originally, I had:
foreach ( . . . .
$sql. = "insert into psc (pid, lft, rgt) values
($pid, $lft, $rgt);";
}
if(!$mysqli->multi_query($sqlgeo)) {
echo $mysqli->error;
}
. . . [next sql statement, which fails with the "commands out of
sync" error]
Now, the only solution I've found is to change my simple multi_query to:
if($mysqli->multi_query($sqlgeo)) {
do{if($result=$mysqli->store_result()){$result->close();}}
while($mysqli->next_result());
}else{
echo $mysqli->error;
}
It seems like there should be a better solution than my current one?
Thanks for any suggestions,
Jeff
Using PHP 5.2.3, MySQL standard-5.0.27 on Debian Etch
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
