UPDATE_TIME for InnoDB in MySQL 5.7
The MySQL 5.7 changelog mentions: Beginning with MySQL 5.7.2, UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables. Previously, UPDATE_TIME displayed a NULL value for InnoDB tables. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache. This is great news! However, I would in fact need the UPDATE_TIME to persist across database server resets. Is this feature being considered or discussed? Where might I find it online? Thank you to the MySQL team and to Oracle for filling in InnoDB;s missing features! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Show ROUTINE body, not PROCEDURE
On Thu, Mar 28, 2013 at 11:15 PM, Peter Brawley peter.braw...@earthlink.net wrote: Can someone run the server with --skip-grant-tables to retrieve this code? Possibly, I'll ask. However, it is highly unlikely that such a thing was done to create the routine. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Show ROUTINE body, not PROCEDURE
On Wed, Mar 27, 2013 at 5:46 PM, h...@tbbs.net wrote: 2013/03/27 08:01 +0200, Dotan Cohen Actually, it is the user that I am logged in as that created the function. That is why I find it hard to believe that one needs root / admin access to see its definition. And that user set DEFINER other than itself, and that worked??? That takes SUPER. The guy who wrote the routine is no longer available, but he never had access to any other account. The guy with root did _not_ add this routine for him, that is for certain! Therefore I conclude that the current MySQL user did create this routine and I'm missing something to understand how to see its definition. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Show ROUTINE body, not PROCEDURE
On Sun, Mar 24, 2013 at 11:08 PM, Peter Brawley peter.braw...@earthlink.net wrote: Log in as admin@localhost. Thanks. I don't have the admin or root privileges on this database. Is that the only way to see the code behind the function? -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Show ROUTINE body, not PROCEDURE
A MySQL database is using a stored function avg_sales in some SQL queries. I am having a hard time finding the code for this function, in order to track down a bug that I suspect is in the function. Here is what I get when I try to query MySQL for the function code: mysql SELECT * FROM information_schema.ROUTINES\G *** 1. row *** SPECIFIC_NAME: avg_sales ROUTINE_CATALOG: NULL ROUTINE_SCHEMA: ROUTINE_NAME: avg_sales ROUTINE_TYPE: FUNCTION DTD_IDENTIFIER: int(11) ROUTINE_BODY: SQL ROUTINE_DEFINITION: NULL EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2013-03-14 02:21:14 LAST_ALTERED: 2013-03-14 02:21:14 SQL_MODE: ROUTINE_COMMENT: DEFINER: admin@localhost CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: utf8_general_ci 2 rows in set (0.00 sec) mysql SHOW CREATE FUNCTION avg_sales; +---+--+-+--+--++ | Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation | +---+--+-+--+--++ | avg_sales | | NULL| latin1 | latin1_swedish_ci| utf8_general_ci| +---+--+-+--+--++ 1 row in set (0.00 sec) mysql SHOW CREATE PROCEDURE avg_sales; ERROR 1305 (42000): PROCEDURE avg_sales does not exist mysql SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES; ++ | ROUTINE_DEFINITION | ++ | NULL | | NULL | ++ 2 rows in set (0.00 sec) mysql SELECT param_list,returns,body FROM mysql.proc; ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'proc' mysql What else should I try to get the code? This is on MySQL 5.1.61 running on CentOS. Thanks. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL on 64 bit Windows 7?
On Thu, Mar 1, 2012 at 23:13, Johnny Withers joh...@pixelated.net wrote: I would imagine the installer is 32-bit only just so they don't have to release two versions of it. I'm sure it'll allow you to download the 64-bit version of the server though. I see, thanks. I did not realise that a Windows installer might install applications of a different architecture than itself. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: What is wrong with this outer join?
On Thu, Oct 20, 2011 at 16:11, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: We do! First though, are you referencing the online documentation or the packaged documentation? The reason I ask is that the online documentation does have some user contributions and comments to go along with the text itself. That outside content is not included with the packaged documentation. I am in fact referring to the online documentation. Official documentation examples would help. Take for instance this page from the PHP manual for instance: http://il2.php.net/manual/en/function.mail.php Four usage examples including code covering real-world scenarios. Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to select the id of 2+ records for given user?
2011/10/20 Halász Sándor h...@tbbs.net: Well done--but Although, it seems, it is everyone s experience that the desired order is the order that MySQL yields, all guarantee of that is explicitly deny'd (look up 'GROUP BY'). It is better to be safe and to use MIN: select * from table1 where messageID NOT IN ( select MIN(messageID) from table1 group by userID ) Thanks. I actually used ORDER BY and LIMIT, I should have used MIN. This is how one learns! Thank you! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
What is wrong with this outer join?
mysql select * from beers; ++---++ | ID | name | colour | ++---++ | 1 | carlsburg | 2 | | 2 | tuburg| 1 | | 3 | tuburg| 9 | ++---++ 3 rows in set (0.00 sec) mysql select * from colours; +++ | id | colour | +++ | 1 | red| | 2 | green | | 3 | blue | +++ 3 rows in set (0.00 sec) mysql select * from beers inner join colours on beers.colour = colours.ID; ++---++++ | ID | name | colour | id | colour | ++---++++ | 1 | carlsburg | 2 | 2 | green | | 2 | tuburg| 1 | 1 | red| ++---++++ 2 rows in set (0.00 sec) mysql select * from beers outer join colours on beers.colour = colours.ID; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join colours on beers.colour = colours.ID' at line 1 So I've gone looking the fine manual, here: http://dev.mysql.com/doc/refman/5.6/en/join.html The manual references natural outer joins and requires curly brackets and I'm frankly not making sense of it. Left, right, and inner joins work as I expect them too, and fishing for examples in google doesn't find anything unusual. How exactly am I erring? Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is wrong with this outer join?
On Wed, Oct 19, 2011 at 16:33, Michael Dykman mdyk...@gmail.com wrote: Try this. I sometime get wierd results when I fail to use aliases in a join. Also, the parentheses are required. - md select * from beers b inner join colours c on (b.colour = c.ID); Thank you Michael. That does work, however when I convert it to an outer join I get the same error as before: mysql select * from beers b inner join colours c on (b.colour = c.ID); ++---++++ | ID | name | colour | id | colour | ++---++++ | 1 | carlsburg | 2 | 2 | green | | 2 | tuburg| 1 | 1 | red| ++---++++ 2 rows in set (0.30 sec) mysql select * from beers b outer join colours c on (b.colour = c.ID); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join colours c on (b.colour = c.ID)' at line 1 mysql -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is wrong with this outer join?
On Wed, Oct 19, 2011 at 18:00, Shawn Green (MySQL)
shawn.l.gr...@oracle.com wrote:
This is a simple misunderstanding. From the page you quote, the syntax
patterns for an OUTER join are these:
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
Notice that in the second, the [OUTER] is nested inside of [{LEFT|RIGHT}
[OUTER]] and in the first it follows the NON-OPTIONAL choice of
{LEFT|RIGHT). Neither one of these syntax patterns allows the keyword OUTER
to appear without either the LEFT or RIGHT keyword before it.
To make this crystal clear those patterns allow LEFT JOIN, RIGHT JOIN, LEFT
OUTER JOIN, or RIGHT OUTER JOIN but not just OUTER JOIN.
Thank you Shawn! I see that I am getting support right from the top!
So far as I understand, an outer join should return all matched and
unmatched rows (essentially all rows) from both tables. So it is not
clear to me what is the difference between a right outer join and a
left outer join, and how they differ from a regular outer join. But
don't answer that, I'll google it and post back for the fine archives.
Thanks!
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to select the id of 2+ records for given user?
Assuming a table such this: | ID | messageID | userID | ||-|| | 1 | 345 | 71 | | 2 | 984 | 71 | | 3 | 461 | 72 | | 4 | 156 | 73 | | 5 | 441 | 73 | | 6 | 489 | 73 | | 7 | 483 | 74 | | 8 | 523 | 74 | | 9 | 723 | 74 | I need the second, third, fourth, etc messageID for each userID. So I would get a results table such as: | ID | messageID | userID | ||-|| | 2 | 984 | 71 | | 5 | 441 | 73 | | 6 | 489 | 73 | | 7 | 483 | 74 | | 9 | 723 | 74 | I've tried playing with count and group by and limit, but I've not found a solution. I can easily get all the rows and then remove the rows that I don't need in PHP, but I'd still like to know if an all-MySQL solution is possible. Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is wrong with this outer join?
On Wed, Oct 19, 2011 at 21:10, Shawn Green (MySQL)
shawn.l.gr...@oracle.com wrote:
What you are describing is a FULL OUTER JOIN. This is not supported, yet, in
MySQL. We only support INNER, NATURAL, LEFT, and RIGHT.
To simulate a FULL OUTER JOIN, you need to construct a UNION of a LEFT and a
RIGHT like this:
(
SELECT ...
FROM basetable
LEFT JOIN jointable
ON basetable.PKID = jointable.base_id
) UNION ALL(
SELECT ...
FROM basetable
RIGHT JOIN JOINtable
ON basetable.PKID = jointable.base_id
...
WHERE basetable.PKID is NULL
...
)
The first half of the UNION finds all rows in basetable plus any rows where
the jointable matches. The second half identifies only rows in jointable
that have no match with a row in basetable.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thank you Shawn. I very much appreciate your help, and I also
appreciate your employer's initiative to have such a position
monitoring the mailing list. Is that an Oracle-created position, or
did it exist at Sun as well?
If I'm already talking with the MySQL Principal Technical Support
Engineer then I have to suggest that the MySQL manual include more
example code. I'm a read-the-manual kind of guy and the C# / PHP
manuals are usually enough to get me unstuck. The MySQL and Java (only
mentioned as it is another Sun/Oracle product) manuals usually do not
provide code examples and I must google for them from unreliable blogs
and forum postings. I personally find concise code examples much more
intuitive and informative than full-format [{(someOption |
anotherOption), somethingHere} rarelyUsedFeature] which I might or
might not mentally parse. I can gladly make more specific suggestions
if Oracle sees the idea as actionable.
I mention this as constructive criticism, take no offense! I'm only at
the beginning of my career and I don't claim to have the expertise or
experience to tell Oracle how to run their show, I only voice my
concern as a consumer of the product and one with an interest in
keeping the product and technology viable. I have nothing but
appreciation to Oracle for continuing to develop Java, MySQL and for
having the good sense to pass OOo onto the Apache foundation.
Thank you.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to select the id of 2+ records for given user?
On Thu, Oct 20, 2011 at 00:06, Basil Daoust bdao...@lemonfree.com wrote: For me given the sample data the following worked. The inner select says find all first messages, the outer says give me all messages that are thus not first messages. select * from table1 where messageID NOT IN ( select messageID from table1 group by userID ) Some times just playing with the data will result in an aha moment. Wow, nice! That looks to be the elegant, simple solution that I do need. The real-world example is more contrived but I should be able to use this method to do it cleanly. It will involve a sort and a limit on the inner select. Thanks! I'm assuming where you show row 7 you meant row 8? Yes, that is what I meant. As someone wiser than myself once said: to err is human, but to really mess things up we need a computer! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to select the id of 2+ records for given user?
On Thu, Oct 20, 2011 at 00:11, Derek Downey de...@orange-pants.com wrote: Ah-hah! :) Actually, I did something similar to that a month or so ago. I ran into a speed limitation on a not-small database (~3mill rows). So be careful. Luckily in my case, I put all the 'minimum' ids in a memory table with an index and it solved it. It also was a cleanup script, and not something I need to run everyday. Yes, this is also a cleanup script for a bunch of vBulletin private messages that should not have been sent. Have you seen the vBulletin database? Over 200 tables, no documentation! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
In general, cheaper to INNER JOIN or two separate queries
I need two fields from two different tables. I could either run two queries, or a single INNER JOIN query: $r1=mysql_query(SELECT fruit FROM fruits WHERE userid = 1); $r2=mysql_query(SELECT beer FROM beers WHERE userid = 1); --or-- $r=mysql_query(SELECT fruits.fruit, beers.beer FROM fruits INNER JOIN beers ON fruits.userid = beers.userid WHERE beers.userid = 1); In general, which is preferable? I don't have access to the production machine to benchmark at the moment, but which is best practice? Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 04:00, Hank hes...@gmail.com wrote:
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:
$sql=INSERT into table VALUES ('$id','$val');
where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error. Otherwise, without the single quotes, the statement
would be:
INSERT into table VALUES (,'');
which would cause a syntax error. If you include the single quotes, it
becomes:
INSERT into table VALUES ('','')
which won't cause a syntax error, but might cause some logic errors in the
database. The choice is yours.
Thanks, that is a good point. I would actually prefer errors to arise
on insert then a potentially inconsistent database or bad data. I
should definitely learn to use stored procedures, I know.
That said, I do go to great lengths to validate my data. What is an
alpha value? I do check is_numeric() and null, of course.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 07:47, Reindl Harald h.rei...@thelounge.net wrote: what ugly style - if it is not numeric and you throw it to the database you are one of the many with a sql-injection because if you are get ivalid values until there you have done no sanitize before and do not here $sql=INSERT into table VALUES ( . (int)$id . ,' . mysql_real_escape_string($val) . '); or using a abstraction-layer (simple self written class) $sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val) . '); all other things in the context of hand-written queries are all the nice one we read every day in the news and should NOT recommended because the next beginner reading this makes all the mistakes again Thanks, Reindi. I actually do something like this (simplified, in real code I use an array and a small custom function): $mysqlName=mysql_real_escape_string($name); Then, in the query I can see that all my variables start with $mysql* so I know that they have been sanitized. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 18:11, Reindl Harald h.rei...@thelounge.net wrote:
it is not because it is clear that it is sanitized instead hope and pray
thousands of layers somewhere else did it - for a inline-query the best
solution, if you are using a framework you will never have the insert into
at this place!
what i meant as ugly is that you are somewhere writing an inline-query and
are not sure if it is a number or not - so it is NOT sanitized before
because if you tell me it is you sanitze does not work if you get a
non-integer
at this point and you sanitze-method has to throw the error long before
if it is really working
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
The resulting SQL query is easy to read, and I know that everything is
escaped. No operations are ever to be performed on $M. I need to look
into a way of making it immutable (add and read only). I could do it
with an object but I prefer an array. Actually, an array wrapped in an
object could perform the escaping itself, making me doubly sure that
some other dev didn't forget to escape while playing with the code.
By the way, I've never gotten a godd explanation about why to wrap the
variables in PHP MySQL queries with curly brackets. I don't even
remember where I picked up the habit. Does anybody here know?
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:11, Hank hes...@gmail.com wrote:
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
I'm not sure I'm seeing why, in particular, you are using an array here?
I want to be sure that all variables in the query are escaped. I don't
trust myself or anyone else to do this to every variable right before
the query:
$someVar=mysql_real_escape_string($someVar);
Furthermore, I don't want to clutter the query with
mysql_real_escape_string() all over the place. Therefore, I escape
everything before it goes into the array, so I know that all the data
in the array have been escaped. I can then use the array members in
the query.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 02:09, Hank hes...@gmail.com wrote:
I want to be sure that all variables in the query are escaped. I don't
trust myself or anyone else to do this to every variable right before
the query:
$someVar=mysql_real_escape_string($someVar);
But you're doing exactly that right before the query anyway with:
$M[username]=mysql_real_escape_string($username);
You're just complicating things with the addition of an unneeded array. It
seems much simpler and less cluttered to just do:
$someVar=mysql_real_escape_string($someVar);
before your insert. All you are doing is changing $someVar to $M[...]
and then using $M[...] in the query. I really don't see the difference or
benefit of using your array here. Both methods are doing exactly the same
thing, except one is more convoluted.
I know that this has been escaped:
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
This, I don't know if it has been escaped or not:
$query=INSERT INTO table (username) VALUES ('{$username}');
Now on the other hand, if you have several elements in the array $M to be
inserted, and have a function like this to escape them all at once:
for each ($M as $val) $val= mysql_real_escape_string($val);
then your method starts to make more sense.
I could foreach it. Or not. It doesn't matter. The point is having
known-safe variables being used in the query, which are also easy to
read.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net wrote:
i would use a samll class holding the db-connection with insert/update-methods
pass the whole record-array, lokk what field types are used in the table
and use intval(), doubleval() or mysql_real_escape-String
so you never write insert into inline and if the function is well desigend
you
can throw the whole $_POST to it without thinikng about datatypes and ignore
automatically hidden-fields which are not used in the database
having as simple class with $db-fetch_all(), $db-insert, $db-update
has also the benefit that you can easy switch between mysql/mysqli
without the big overhead of a whole abstraction-layer and extend
this class with often used methods to make development faster
and much more stable as dealing the whole time with inline code
a basic class is written in few hours and can be extended whenever
needed - i wrote one ten years ago and heavily use it these days
as all the years
public function insert($table, array $data)
{
// so here you know where to look for fieldnames/fieldtypes
// prepare the data aray with escaping/intval()/doubleval()
// and generate finally the insert
//
// as return value use 0 on errors or the insert-id
}
You are right, using a class has many benefits. I might do that on a
future project. Thanks.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net wrote: i would use a samll class holding the db-connection with insert/update-methods pass the whole record-array, lokk what field types are used in the table and use intval(), doubleval() or mysql_real_escape-String By the way, the database connection is include()ed from a file outside the webroot. This way if Apache is ever compromised or for whatever reason stops parsing the PHP, the resulting code returned to the browser won't have the daabase info (especially the password). -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Sun, Sep 18, 2011 at 17:44, Brandon Phelps bphe...@gls.com wrote: Personally I don't use any quotes for the numeric types, and single quotes for everything else. Ie: Thanks, Brandon. I understand then that quote type is a matter of taste. I always use double quotes in PHP and I've only recently started putting ticks around table and column names. I'll stick to your convention of no quotes around numerics and single quotes around everything else. Have a terrific week! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update on inner join - looks good to me, where did I go wrong?
On Sat, Sep 10, 2011 at 01:48, Carsten Pedersen cars...@bitbybit.dk wrote: `userTable.userid` = `userTable`.`userid` Thank you Carsten. That was indeed the problem! Have a peaceful weekend. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Update on inner join - looks good to me, where did I go wrong?
I'm trying to update on an join, but I can't find my error: UPDATE `userTable` SET `someField`=Jimmy Page FROM `userTable` INNER JOIN `anotherTable` ON `userTable.userid`=`anotherTable.userid` WHERE `userTable.someField`=Jim Morrison AND `anotherTable.date` NOW(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM That error is for reserved words, and I am escaping all the fields and tables (using the backticks). So why the error? This is on a CentOS 4 or 5 server, with MySQL 5.0.77, accessed from the CLI. Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update on inner join - looks good to me, where did I go wrong?
Now that I've got the syntax right, MySQL is complaining that a field does not exist, which most certainly does: mysql UPDATE - `userTable` - INNER JOIN `anotherTable` - ON `userTable.userid`=`anotherTable.userid` - SET `userTable.someField`=Jimmy Page - WHERE `userTable.someField`=Jim Morrison - AND `anotherTable.date` NOW(); ERROR 1054 (42S22): Unknown column 'userTable.someField' in 'field list' mysql mysql SELECT count(someField) FROM userTable; +---+ | count(someField) | +---+ | 5076 | +---+ 1 row in set (0.00 sec) mysql What could be the issue here? Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Backup solution for non-technical user
On Fri, May 13, 2011 at 10:21, Joerg Bruehe joerg.bru...@oracle.com wrote: Hi everybody! Dotan Cohen wrote: Is there a simple browser-based MySQL backup solution for non-technical users. [[...]] The main features needed are: 1) Automatic scheduled off-site backups (via SSH or FTP) Off-site = good (for reliability purposes). 2) Backup multiple databases and all their tables Definitely a must have. 3) Single-table recovery via GUI (the user simply chooses which database and which table to recover) If your backup/recovery tool has this feature and your users ever go that route, you (your DBA / your authorities) must be aware that this will break any dependencies between that recovered table and all other, un-recovered ones. Example: Assume a new entry is added to the customer table, then (at least) one order is entered for this customer. Before, during, or after that, some garbage change is done to the customer table, it is detected, and someone decides let's recover the customer table from the last good backup. This will get rid of the garbage, but will also make the orders for new customer be pointing to nowhere. IOW: As soon as you have relationships crossing table boundaries, a single-table recovery is a very risky operation, and it will violate any referential integrity constraints involving that table. [[...]] Regards, Joerg Thanks Joerg for that insight. In fact, this is a very simple installation with no joins but I will keep that in mind for the future. Terrific point. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Backup solution for non-technical user
On Tue, May 10, 2011 at 22:58, Michael Heaney mhea...@jcvi.org wrote: Check out Zmanda: http://zmanda.com/zrm-mysql-enterprise.html Michael Heaney JCVI On Wed, May 11, 2011 at 10:00, Johan De Meersman vegiv...@tuxera.be wrote: Zmanda ZRM backup, although the fancy webinterface is only available in the commercial version. Backups are stored on the host that runs the server, and of course it serves multiple MySQL machines. Webinterface is annoyingly slow, though :-) Thanks, I passed the suggestion on. Might be what he is looking for. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Backup solution for non-technical user
Is there a simple browser-based MySQL backup solution for non-technical users. The server is running Red Hat Enterprise Linux. The main features needed are: 1) Automatic scheduled off-site backups (via SSH or FTP) 2) Backup multiple databases and all their tables 3) Single-table recovery via GUI (the user simply chooses which database and which table to recover) 4) FOSS-license a big plus, but other licenses considered I have ruled out cron/mysqldump for the GUI (browser-based) recovery requirement.I found phpMyBackupPro which looks like a possible solution, and I'd really appreciate other MySQL users' input on the topic. Thank you! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
Actually, I'm the customer! But assuming that a customer exists, that implies compensation, and therefore fair bait. Then that's different altogether. you get to decide what information is displayed, and what information is 'sensed', and on what platform. Yes, but before I get to that stage (relatively easy today with high level languages such as PHP or C#) I need to decide how to organise the data. What do you want to sense and what do you want to display(not to say I'm an expert, but I like to think in CS)? The application will pull calendar records by category: entertainment, food, gov, transportation, etc. The idea is that the user could query for, say, postal offices open after 17:00 on Thursday, or films between 20:00 and 22:00 on Monday, or buses leaving Amsterdam to Rotterdam on Monday morning. By the way, I figured out what the troll issue was. I was confusing your signature for the body of the post. It was familiar, too, so I thought that it may have been a troll post on /. or such. It turns out that I had seen it on the Python-tutor list, and their I also mistook it for a troll as the length of the sig far outweighs the length of your typically concise and to-the-point post. Constructive advice: trim the sig! Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
If you are doing this often, you could leave spaces in the left and right values so that you could minimize the number of rows that need to be updated. The article makes every leaf use x and x+1 for left and right which forces another update to add a child. If instead you used x and x+20 you'd leave space for more children without any updates. This could be applied from top to bottom, starting with the root category getting 0 and MAX_INT for its values. Then I would have to check what values are available when inserting, and possibly normalise every so often. I'll think about that, and when I have enough data in the database I'll set up a test system to play with the possibility. However, it's probably not even worth applying that complexity until you prove that frequent category additions are causing problems. Most systems will be querying against the categories table far more frequently, and that's where this model pays off. If you want to see all products in category X and its subcategories, it's a single *non-recursive* query. That's huge if you are doing a lot of searches like this. You are right, that non-recursive bit is important. In fact, I think that I'm convinced. Thanks! But what a mess this would be if the two methods go out of sync! Sure, but these values would be maintained by your code--not end-users. It just comes down to making sure your code is correct through appropriate unit tests. By moving the logic to a stored procedure, you can ensure the table is locked during the updates to keep two users from adding a new category simultaneously. So long as it is in fact my code, that's fine. But when others start maintaining it and not reading comments, it may get ugly. That does not apply to this particular pet project, but it is a consideration for future projects. That pays off more? For the guy writing code or for the database memory requirement? Performance-wise. The nested set method looks to be moderately more complex code-wise, but luckily that is done just once while querying the database is done again and again. As with all optimizations, it's best to measure and make sure there's a problem before trying to solve it. Once you've built a few hierarchical systems, you'll be able to make a gut call up front. I see, thanks. Good point about making sure that the problem exists before trying to fix it, I've seen people optimise away where there is no bottleneck. Only two update statements, but they are affecting on average half the database's rows! Of a single table: categories. Hopefully you have far more items that get categorized than you do categories. True. Which do you call the hierarchical model? That term is not used in the linked article. Well, both models are hierarchical in the sense that there's a parent-child relationship. By hierarchical here I mean that the method of implementation involves each category pointing to its parent directly via a parent_id column. Searching for all subcategories of category X requires searching first for all children, then all grandchildren, and so on, resulting in a recursive query. Using the nested sets model requires a single non-recursive query to get the same data. I do agree that the non-recursive method at retrieval time advantage far outweighs the update-half-the-table issue upon addition of an additional category. Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
Yes, and an edge list model may perform better in other respects too: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html Thanks. I am currently reading Trees and Hierarchies in SQL for Smarties by Joe Celko, which also deals with a similar model. It is revealing and an interesting way of looking at the issue of organising data. In addition to those two links, this one also seems relevant: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Fri, Jan 21, 2011 at 12:29, Richard Quadling rquadl...@gmail.com wrote: Changing data in a database is the role of the database engine. It is much more efficient to have the cost on the insert than it is on the select. Agreed. On insert I could even delegate the operation to another thread which does not timeout with the pageload. The adjacent list model is very expensive at n-levels for the select, but trivial cost for the insert. If you are inserting millions of rows but only occasionally looking at the data, then stick with the adjacent list model. But if tags and n-levels are regularly accessed and form a main part to the functionality of the app, then you may want to reconsider. I've already reconsidered after some sleep and coffee! Sure, the insert for the nested set model is more expensive in terms of the number of rows to amend, but indexing will certainly should certainly help. If you have tools to help optimize the tag table and the queries you use, then I'd follow the recommendations (I use MS SQL, so my Query Optimization tools help me here). The nested set model is extremely efficient on the select. Interesting. I am using MySQL for this application, but another hat I wear is learning C# with MS tools and I will have to look into the Query Optimisation. It is a trade off that you have to decide upon, based upon your data and needs. If, as I suspect, you are going to be doing a LOT of selects on the tags and (in the future) to multiple levels, then this aspect needs to be very efficient. You suspect correctly. For me it is well worth the effort of moving from the adjacent list model to the nested set model. Both mechanisms work. In my opinion, the adjacent list model is for truly simply lookups, not for complicated n-levels. One of the changes I made to the nested set model was for a Bill Of Materials module. The client made complex machinery (industrial lathes). The sum quantity for all the parts were in the 20,000 region. Each nut, bolt, screw, etc. Many sub-assemblies. The parent/child node logic was massive in dealing with retrieving questions like How many machines can we build?, What stock do we need to buy/make to complete an order of 20 lathes?. Lot's of recursion into each level to build the list. Getting the results would take 3 or 4 minutes (this is in a non SQL environment using a peer-to-peer modified D-ISAM database - it was already slow because of all that). When I moved to the nested set model, no recursion and 1 query (more or less) and I have all the results I needed. It was seconds in comparison. Thanks, I enjoy reading these real-life scenarios. This was a terrific example. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
I am designing an application that make heavy usage of one-to-many tags for items. That is, each item can have multiple tags, and there are tens of tags (likely to grow to hundreds). Most operation on the database are expected to be searches for the items that have a particular tag. That is, users will search per tags, not per items. These are the ways that I've thought about storing the tags, some bad and some worse. If there is a better way I'd love to know. 1) Each item will get a row in a tags table, with a column for each tag. mysql CREATE TABLE tags ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, item VARCHAR(100), tag1 bool, tag2 bool, tagN bool ); With this approach I would be adding a new column every time a new category is added. This looks to me a good way given that users will be searching per tag and a simple SELECT item FROM tags WHERE tag1=true; is an easy, inexpensive query. This table will get very large, there will likely be literally thousands of items (there will exist more items than tags). 2) Store the applicable tags one per line in a text field in the items table. mysql CREATE TABLE items ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, item VARCHAR(100), tags text, ); This looks like a bad idea, searching by tag will be a mess. 3) Store the tags in a table and add items to a text field. For instance: mysql CREATE TABLE tags ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, tagName VARCHAR(100), items text, ); This looks to be the best way from a MySQL data retrieval perspective, but I do not know how expensive it will be to then split the items in PHP. Furthermore, adding items to tags could get real expensive. Caveat: at some point in the future there may be added the ability to have a tag hierarchy. For instance, there could exist a tag restaurant that will get the subtags italian and french. I could fake this with any approach by having a table of existing tags with a parentTag field, so if I plan on having this table anyway would method 3 above be preferable? Note: this message is cross-posted to the MySQL and the PHP lists as I am really not sure where is the best place to do the logic. My apologies to those who receive the message twice. Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 17:00, Richard Quadling rquadl...@gmail.com wrote: I'd have my items table, my tags table and a join table for the two. My join table is really simple. UniqueID, ItemID, TagID. Yes, that is the first approach that I mentioned. It looks to be a good compromise. I'd recommend using a nested set approach for the tags (http://dev.mysql.com/tech-resources/articles/hierarchical-data.html gives a good explanation on the issues and methodology of nested sets). That is terrific, at least the first half. The second half, with the Venn diagrams, is awkward! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 18:20, Dotan Cohen dotanco...@gmail.com wrote: On Thu, Jan 20, 2011 at 17:00, Richard Quadling rquadl...@gmail.com wrote: I'd have my items table, my tags table and a join table for the two. My join table is really simple. UniqueID, ItemID, TagID. Yes, that is the first approach that I mentioned. It looks to be a good compromise. Sorry, that was _not_ the first approach that I mentioned. It looks to be the right method though, thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 17:22, Jerry Schwartz je...@gii.co.jp wrote: I think the canonical way would be to have one table for your items, one table for your tags, and one table for your tag assignments. Thank you, I do agree that this is the best way. Other posters seem to agree as well! Using an ever-lengthening bitmap for the tag assignments is a trap for the unwary. The path to perdition is lined with the bodies of those who believed We'll never need more than x... 640 kb? As for setting up a hierarchy, that's trickier. One way to handle that is to work like libraries do: 10 is fiction, 10.05 is crime novels, 10.05.07 is British authors, and so forth. Your `tags` table then looks like Thanks. I prefer the parent tag field, though, I feel that it is more flexible. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 17:22, Peter Brawley peter.braw...@earthlink.net wrote: I'd exclude (1) because new tags require restructuring the table, (2) and (3) because they break a cardinal rule of design and will be a mess to query, leaving ... 4) Standard many-many bridge table: mysql CREATE TABLE items_tags ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, itemID int, tagID INT ); Will not require a major overhaul if you later turn categories into a tree. Terrific, Peter, this looks like the right direction. I appreciate the input. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 19:21, Richard Quadling rquadl...@gmail.com wrote: That is terrific, at least the first half. The second half, with the Venn diagrams, is awkward! When you get heavily nested data, the adjacent set model (where you have a parentid for every uniqueid), you very quickly get into complicated logic trying to traverse n-levels. The nested set model is specifically built to handle this issue. I'd recommend getting to grips with it. It will make finding items belonging to a group (or a super group) a LOT easier. Especially if you have multiple tag hierarchies. Is that strategy widely deployed, then? It seems so unruly having to change on average half the database records for every new leaf. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 20:50, David Hutto smokefl...@gmail.com wrote: Pseudo = Design Algorithm Design Algorithm = Actual Code Actual Code = Alterable db tables Alterable db tables = manipulated data through the app interface with data -- The lawyer in me says argue...even if you're wrong. The scientist in me... says shut up, listen, and then argue. But the lawyer won on appeal, so now I have to argue due to a court order. Furthermore, if you could be a scientific celebrity, would you want einstein sitting around with you on saturday morning, while you're sitting in your undies, watching Underdog?...Or better yet, would Einstein want you to violate his Underdog time? Can you imagine Einstein sitting around in his underware? Thinking about the relativity between his pubic nardsac, and his Fruit of the Looms, while knocking a few Dorito's crumbs off his inner brilliant white thighs, and hailing E = mc**2, and licking the orangy, delicious, Doritoey crust that layered his genetically rippled fingertips? But then again, J. Edgar Hoover would want his pantyhose intertwined within the equation. However, I digress, momentarily. But Einstein gave freely, for humanity, not for gain, other than personal freedom. An equation that benefited all, and yet gain is a personal product. Also, if you can answer it, is gravity anymore than interplanetary static cling? Is this a troll? Am I about to be baited? -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 21:24, David Hutto smokefl...@gmail.com wrote: Is this a troll? Am I about to be baited? Baited to deploy what is designed to the consumer's specification? Surely. From what is wanted to what is needed. Troll on that. Actually, I'm the customer! But assuming that a customer exists, that implies compensation, and therefore fair bait. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] RE: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 21:40, Jerry Schwartz je...@gii.co.jp wrote: Thanks. I prefer the parent tag field, though, I feel that it is more flexible. [JS] I disagree. The method I proposed can be extended to any depth, and any leaf or branch can be retrieved with a single query. I suppose for retrievals this structure has advantages, but unless MySQL has a ++ operator (or better yet, one that adds or subtracts 2 from an int) then it looks to be a pain to add nodes. But I will play with the idea. Maybe after I write the code (I'm saving that for tomorrow) I'll see it differently. Thanks. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 22:05, David Harkness davi...@highgearmedia.com wrote: Thanks for the link. That article proposes an interesting way to organize the categories. Have you implemented this in the wild? Clearly the design would work as it's pretty simple, and I like that it removes the need for recursive queries. I am also interested in knowing if this approach is used in any production code. Dotan, the Venn diagrams are just used to explain the concept. If you use the code to determine the left and right values, you can ignore the diagrams entirely. As long as you're not adding/removing categories every minute, having to recalculate left and right values isn't that big of a deal. I understood that. My concern is exactly with adding new nodes. There is no incrementor (++i) in SQL, so knowingly coding a solution that will require incrementing two fields in half the database rows seems irresponsible. Also, there's no reason you couldn't keep the parent_id field with the nested sets. It would come in handy for certain types of queries, though it's not necessary. That is true. I could store both methods, and experiment to see which is preferable. But what a mess this would be if the two methods go out of sync! Isn't there a name for that in SQL, something along the lines of not storing the same data in two places lest one should change and not the other? The term escapes me. I disagree. The method I proposed can be extended to any depth, and any leaf or branch can be retrieved with a single query. The nested set method can be extended to any depth, and it pays off more the larger the hierarchy grows. While you can retrieve any branch (all ancestors) of a node with a single SQL query, the SQL engine itself actually must perform a recursive query meaning multiple hits on the parent_id index. That pays off more? For the guy writing code or for the database memory requirement? I suppose for retrievals this structure has advantages, but unless MySQL has a ++ operator (or better yet, one that adds or subtracts 2 from an int) then it looks to be a pain to add nodes. ++ or += wouldn't be any better here than x = x + 2. Once you're modifying indexed values, you'll pay a much higher price writing to disk than += could ever save you in CPU cycles. The beauty is that inserting a node requires only two update statements that will fix *all* categories that need to be adjusted. Only two update statements, but they are affecting on average half the database's rows! Adding categories to the hierarchical model is definitely faster so it comes down to your insert-to-select ratio. Moving a subtree is also much easier with the hierarchical model. Which do you call the hierarchical model? That term is not used in the linked article. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Selecting next/previous records based on
I have a table of photos, that I display in chronological order on an index page: http://dotancohen.com/eng/pictures/index.php I'd like to have next (and previous) buttons on the page that displays individual photos, but the photos ID numbers are not chronological. So I need to find a way to select the photo that is next chronologically, with that information being available in a datetime column. I was thinking of using this query: SELECT * FROM photos WHERE datetime '$datetimeOfCurrentPicture' ORDER BY datetime asc LIMIT 1,1 Is there maybe a better way to do this? Thanks in advance. Dotan Cohen http://lyricslist.com/lyrics/lyrics/44/402/pink_floyd/atom_heart_mother.html http://what-is-what.com/what_is/sql_injection.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Safe DB Distribution
On 30/01/07, peter lovatt [EMAIL PROTECTED] wrote: Hi You probably cant make it 100% secure, because php is not a fully compiled language, and as such an expert techie could probably add extra code to your app that wouild allow access to the database, BUT you can get pretty close. You will need to encrypt everything in the database using MySql encryption functions http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html This will mean you can only access the data using the password it was encrypted with. This will stop anyone installing the database accessing the data using another MySql client. Next you need to encrypt the php so that the user cannot get the encryption password. There are a couple of options I can think of, there are probably more. The first is Zend Accelerator ( http://www.zend.com) , which I think compiles the php (check this though). The second is ioncube ( http://www.ioncube.com/) which is intended to prevent unauthorised access to php code. As above, your app needs MySql, and is not open source so you need a mysql licence. Hope this helps Peter Actually, I'm pretty sure that you _can_ compile PHP with the Zend optimizer. Another option: host the MySQL server on your own hardware, and configure the php script to connect to that. Then you can control everything coming in/going out. What are you trying to protect? And what's the sense in protecting it such, if in any case the php script has access to it? Dotan Cohen http://lyricslist.com/lyrics/artist_albums/355/moody_blues.html http://music-lyriks.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM issues for UTF-8?
On 15/01/07, Gabriel PREDA [EMAIL PROTECTED] wrote: Read here: http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html have fun ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer Thanks. Been quite a while since I've deserved a good RTFM! :) Dotan Cohen http://what-is-what.com/what_is/html_email.html http://ultu.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM issues for UTF-8?
On 15/01/07, Gabriel PREDA [EMAIL PROTECTED] wrote: Why is MyISAM problematic... MyISAM is a storage engine with some features... InnoDB is another storage engine with other features... and so on... As far as I know MyISAM is default storage engine... unless you specify by hand another storage engine: CREATE TABLE xyz (colX INT NULL) ENGINE=_STORAGE_ENGINE_ I used UTF8 with MyISAM... and with InnoDB for Romanian characters and Chinese characters... and any combination worked well... And as far as I know... instead of all those commands you can issue: SET NAMES utf8; and it will suffice... I know it does for me ! Thanks, Gabriel. What exactly is the NAMES in SET NAMES utf8;? Why can't I find any reference to it? Dotan Cohen http://technology-sleuth.com/technical_answer/how_can_i_be_safe_online.html http://what-is-what.com/what_is/bios.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM issues for UTF-8?
I'm storing Hebrew text in a MySQL database as utf-8. I recently saw a referece to MySQL utf-8 implementations that warned that if one stores utf-8 text, he must use the problematic MyISAM engine. So, must I use this engine? How do I check which engine I'm using? I've never set anything, and my database so far runs fine. Thanks in advance for any insights. Note that I add this code after every database connection (I use php): mysql_query(SET character_set_client=utf8); mysql_query(SET character_set_connection=utf8); mysql_query(SET character_set_database=utf8); mysql_query(SET character_set_results=utf8); mysql_query(SET character_set_server=utf8); Dotan Cohen http://what-is-what.com/what_is/computer.html http://lyricslist.com/lyrics/artist_albums/255/hill_faith.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: only update if values different
On 09/12/06, Nick Meyer [EMAIL PROTECTED] wrote: What is the best way to UPDATE a row only if values are different? We have a mainframe extract that literally has 100,000 rows and am worried about the performance of just running INSERTs each night. Is there a simple comparison command or would you have to nest a SELECT statement? Thank you, Nick You have to check the line first, then compare and update if neseccary. I've been through that recently, and decided that it was better to just UPDATE. Of course, I had much less rows than you do. Maybe you could store an array of changed rows in whatever language you are programming, then update from that? Or a text file, another DB, whatever... Dotan Cohen http://what-is-what.com/what_is/digg.html http://dramatherapy.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How many records in table?
What's a quick query to determine how many records a given table contains? I don't think that a SELECT query is appropriate, as I don't intend on doing anything with the data selected. Note that I'm interfacing with MySQL via php, if that matters. Thanks. Dotan Cohen http://what-is-what.com/what_is/copyleft.html http://lyricslist.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How many records in table?
On 06/12/06, Mikhail Berman [EMAIL PROTECTED] wrote: Use SELECT in with count(*) SELECT count(*) from YOUR_TABLE Mikhail Berman Thanks, Mikhail. Will do. Dotan Cohen http://what-is-what.com/what_is/xss.html http://english-lyrics.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sorting MySQL queries
I have a list of subjects, such as Linux, Open Source, and the World Wide Web. The subjects are stored in MySQL and being retrieved via php. I currently organize them alphabetically with MySQL's ORDER BY ASC argument, however, if there is a preceding the or a then that is considered as part of the alphabetical order. Thus, all the subjects starting with the are grouped together, as are the subjects starting with a . How can I order by ascending, without taking the preceding the or a into account? ** Example: Now, the list is ordeded like this: a Distribution a Text Editor a Virus Bluetooth Copyleft DRM Fedora Firefox However, I'd like it to be ordered like this: Bluetooth Copyleft a Distribution DRM Fedora Firefox a Text Editor a Virus Current code: $query = SELECT subject FROM table ORDER BY subject asc; $result = mysql_query($query); Thanks in advance. Dotan Cohen http://what-is-what.com/what_is/world_wide_web.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting MySQL queries
On 06/11/06, Christian Hammers [EMAIL PROTECTED] wrote:
On 2006-11-06 Dotan Cohen wrote:
I have a list of subjects, such as Linux, Open Source, and the
World Wide Web. The subjects are stored in MySQL and being retrieved
via php. I currently organize them alphabetically with MySQL's ORDER
BY ASC argument, however, if there is a preceding the or a then
that is considered as part of the alphabetical order. Thus, all the
subjects starting with the are grouped together, as are the
subjects starting with a . How can I order by ascending, without
taking the preceding the or a into account?
Make a second column that only contains
ALTER TABLE table ADD cooked_subject;
UPDATE table SET cooked_subject = ereg_replace('^(a|the) ', '', subject);
(I don't know how the regular expression function was called exactly but you
get the idea)
SELECT subject FROM table ORDER BY cooked_subject;
Of course you could also put the regular expression in the SELECT but that
would be slower.
Would it really slow it down that much? I'll consider the
cooked_subject idea then, if noone has any other suggestions.
Thanks.
Dotan Cohen
http://www.lyricslist.com/lyrics/artist_albums/64/beatles.php
http://gmail-com.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Preventing SQL injection
I'm creating a forum in php where users are able to store comments in a text field (think blog comments). To prevent SQL injection, I'm using the php function mysql_real_escape_string() on data going into the text field. Is this really enough to be safe, or should I be doing more? Thanks in advance. Dotan Cohen http://what-is-what.com/what_is/bluetooth.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should we petition GoDaddy?
On 4/16/06, Nicolas Verhaeghe [EMAIL PROTECTED] wrote: I am slowly considering leaving GoDaddy, who has a very good bandwidth and ok tech support (I have seen better but much much worse) and acceptable prices, but unfortunately does not support MySQL 5 and PHP 5 either. I wonder if I could give these people a chance but one thing I'd like to do is see if we could petition these guys and simply threaten to close out our accounts if they do not get on with the music. The current version of MySQL is 3.23. I have no time upgrading my dedicated server to a RedHat machine with custom PHP and MySQL, I don't have the time to do that. Who think we could make them make the right move and pretty quickly? When you do leave, make sure and tell them why. Also stress your opinion of their move to MS software. Dotan http://IE-Only.com
Re: It's party time!
On 2/25/06, Martijn Tonies [EMAIL PROTECTED] wrote: I'm sorry to say, there won't be any version for Linux. Not this year, at least. You can, however, connect to MySQL running on a Linux host. Martijn Tonies Too bad. Do you provide the winbox so that I can connect? 'Cause I've nothing but Kubuntu and Fedora at home. Dotan Cohen http://technology-sleuth.com/
Re: GUI (linux X based or web based) for mysql
On 12/31/05, Mechtilde Stehmann [EMAIL PROTECTED] wrote: Hello, Dotan Cohen schrieb: I keep a lot of personal information in a MySQL database. I have a few querys premade for the wife to use, but we have gotten to the point where we need a solution for her to be able to run her own queries. She is NOT about to learn SQL, and myPHPadmin is a little too compicated. I looked at an Access installation the other day and it seemed to be just what we need- a GUI for non-programmers to use a database. Is there any such beast for MySQL? Anthing that will run on Fedora Core 4 would be great. Or even a web-based solution would be fine- I do have apache on localhost. You can use OpenOffice.org 2.0 via ODBC or JDBC and you can use a GUI nearly like Access under Linux. Mechtilde Thanks. I was directed earlier to Base as an option. While it works, it is very complicated for the non-techie, so what I did was write a few simple php scripts for common queries and we run that in firefox. As I already run apache on the machine, this was not much trouble. Another plus is that the querys are available on the other machine on the home network- we just surf to the machine's IP address. Dotan Cohen http://technology-sleuth.com/question/why_are_internet_greeting_cards_dangerous.html *-
Re: GUI (linux X based or web based) for mysql
On 12/18/05, Daniel Kasak [EMAIL PROTECTED] wrote: Dotan Cohen wrote: I keep a lot of personal information in a MySQL database. I have a few querys premade for the wife to use, but we have gotten to the point where we need a solution for her to be able to run her own queries. She is NOT about to learn SQL, and myPHPadmin is a little too compicated. I looked at an Access installation the other day and it seemed to be just what we need- a GUI for non-programmers to use a database. Is there any such beast for MySQL? Anthing that will run on Fedora Core 4 would be great. Or even a web-based solution would be fine- I do have apache on localhost. I have a number of open-source Perl projects that may help you out, at: http://entropy.homelinux.org/axis_not_evil They're more targetted at developers than end-users at the moment ... I have plans ... Anyway, with minimal effort you can get a very nice looking Gtk2-based GUI going with the help of Glade to make your interface, and a couple of lines of Perl code to connect your database to your GUI. The current release of Gtk2::Ex::DBI ( you just motivated me to make the next release, which I'd been procrastinating over for ages ) has a built-in search dialog, that you can trigger in 2 ways - by right-clicking in a field in the GUI and selecting 'find' from the context-sensitive menu, or by putting a button on your form and connecting it to the find_dialog() method of Gtk2::Ex::DBI. This will give you a dialog that lets you add criteria that gets appended to the 'where' clause of the query ... ie it doesn't do a slow search on every record ( like Access ), but lets you send your criteria to the database for rapid results. Admittedly, the documentation is a little sparse, but I'm working on a nice demo application that shows off all the features of the 3 modules. Hopefully over Christmas I'll get time to finish it off. Oh yeah ... did I mention that it's all cross-platform goodness? :) I've tested on Linux, Windows 2000 and OS-X. You will of course need to install Gtk2 for the last 2, but it's not that hard - at least on Windows. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au Thanks. I'll probably take a look at that soon. I don't know any perl, so I'll probably go with the Base solution, but this does look good. Dotan Cohen http://technology-sleuth.com/technical_answer/what_are_the_advantages_of_lcd_monitors.html +
GUI (linux X based or web based) for mysql
I keep a lot of personal information in a MySQL database. I have a few querys premade for the wife to use, but we have gotten to the point where we need a solution for her to be able to run her own queries. She is NOT about to learn SQL, and myPHPadmin is a little too compicated. I looked at an Access installation the other day and it seemed to be just what we need- a GUI for non-programmers to use a database. Is there any such beast for MySQL? Anthing that will run on Fedora Core 4 would be great. Or even a web-based solution would be fine- I do have apache on localhost. Thank you. Dotan Cohen http://technology-sleuth.com/question/why_are_internet_greeting_cards_dangerous.html
Re: GUI (linux X based or web based) for mysql
On 12/16/05, Greg Maruszeczka [EMAIL PROTECTED] wrote: Dotan Cohen wrote: I keep a lot of personal information in a MySQL database. I have a few querys premade for the wife to use, but we have gotten to the point where we need a solution for her to be able to run her own queries. She is NOT about to learn SQL, and myPHPadmin is a little too compicated. I looked at an Access installation the other day and it seemed to be just what we need- a GUI for non-programmers to use a database. Is there any such beast for MySQL? Anthing that will run on Fedora Core 4 would be great. Or even a web-based solution would be fine- I do have apache on localhost. Check out Open Office 2.0 and its Base app, available in windows and *nix versions. I think it can use MySQL through either ODBC or JDBC connectors, though I haven't played with it much (yet) to evaluate it. Thanks, I will take a look at it. I thought that Base uses it's own database backend- otherwise whoever wants to install a complete OO package must then also install mysql. We already use OO apps (Writer and Calc), so this is not a problem. Dotan http://technology-sleuth.com/technical_answer/what_are_the_advantages_of_lcd_monitors.html :
Re: Not operator in Select statements
On 10/24/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: On Mon, 2005-10-24 at 06:09 +0200, Dotan Cohen wrote: How do I use a not operator in the WHERE clause? The obvious != and NOT didn't work for me. Something along the lines of: $query = SELECT album, year FROM albums WHERE year!=1990 ORDER BY year ASC; The above query is syntactically correct. If it didn't work for you, then your problem lies elsewhere. Perhaps post your error message? By the way, your question would have been answered by reading the manual... Jasper Bryant-Greene Thank you Jasper. I remember from the manual that this _should_be correct. But when it doesn't work I am inclinded to ask. I will go try to track down the cause of my disappointment with the query. Thank you. Dotan Cohen http://lyricslist.com/lyrics/artist_albums/332/mccartney_paul.php McCartney, Paul Song Lyrics
Not operator in Select statements
How do I use a not operator in the WHERE clause? The obvious != and NOT didn't work for me. Something along the lines of: $query = SELECT album, year FROM albums WHERE year!=1990 ORDER BY year ASC; $query = SELECT album, year FROM albums WHERE year NOT 1990 ORDER BY year ASC; Thanks in advance. Dotan Cohen http://technology-sleuth.com/question/what_is_a_cellphone.html
left not working
Why would this work: SELECT question, short, longa, technical FROM -snip- WHERE filename='$filename' But this not: SELECT question, short, left(longa,40), technical FROM -snip- WHERE filename='$filename' The line that looks like this: $long =mysql_result($result, 0, longa); Is throwing this error: Warning: mysql_result(): longa not found in MySQL result index 4 in *** on line 13 What is wrong with m syntax? Dotan http://lyricslist.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: left not working [solved]
On 10/21/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: On Fri, 2005-10-21 at 06:34 +0200, Dotan Cohen wrote: Why would this work: SELECT question, short, longa, technical FROM -snip- WHERE filename='$filename' But this not: SELECT question, short, left(longa,40), technical FROM -snip- WHERE filename='$filename' If you want it to work like that, you need to do: left(longa,40) AS longa The line that looks like this: $long =mysql_result($result, 0, longa); Is throwing this error: Warning: mysql_result(): longa not found in MySQL result index 4 in *** on line 13 Jasper Bryant-Greene General Manager Album Limited Thank you. That did it. Dotan http://lyricslist.com/lyrics/artist_albums/155/depeche_mode.php Depeche Mode Song Lyrics -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting x characters from text
I have this SQL statement: SELECT ID, title, date, content FROM news ORDER BY date asc LIMIT 0,10 However, I only need the first 40 characters of the content field, which in some cases is over 5000 characters long. Is there a way to request only the first 40 characters of a field? Thanks in advance. Dotan Cohen http://www.technology-sleuth.com/mobile/cellphones.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
linux timestamp
Hi all, I have a field in a mysql database v4.0.18 that contains a linux timestamp. I have been googleing for a solution that would return to me all the entries where the timestamp falls on, say a wednesday, or between 2pm to 3pm. I am led to believe that it is possible, but I have found no examples. Something like: SELECT * from listings WHERE timestamp(day==wednesday) or SELECT * from listings WHERE timestamp(14:00 = time = 15:00) Of course, I don't expect these examples to work, I'm just trying to illustrate what I'm trying to accomplish. Until now, I have been pulling all the fields and checking the timestamp with php. But I believe that there must be a better way. Thanks. Dotan Cohen http://lyricslist.com/lyrics/artist_albums/109/carlisle_belinda.php Carlisle, Belinda Song Lyrics -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: linux timestamp
On 9/27/05, Nigel Wood [EMAIL PROTECTED] wrote:
Dotan Cohen wrote:
Hi all, I have a field in a mysql database v4.0.18 that contains a
linux timestamp. I have been googleing for a solution that would
return to me all the entries where the timestamp falls on, say a
wednesday, or between 2pm to 3pm. I am led to believe that it is
possible, but I have found no examples. Something like:
SELECT * from listings WHERE timestamp(day==wednesday)
or
SELECT * from listings WHERE timestamp(14:00 = time = 15:00)
Fair warning: Because MySQL won't be able to make proper use of it's
indexes the following queries will be VERY slow with any reasonable
sized data set. If your going to be performing these queries often I'd
recommend either storing the field as a datatime (you can do date time
to unixtime conversion in MySQL using the unix_timestamp() function) or
denormalising the data and storing both.
SELECT * from listings WHERE date_format('%W',from_unixstamp(timestamp)) =
'Wednesday';
SELECT * from listings WHERE cast(
date_format('%H',from_unixstamp(timestamp)) as unsigned) between 14 and 15;
Thank you everyone. I'll set up the day and hour fields. It is a big
database, and I will be accessing often. I especially appreciated the
link to the proper chapter in the manual, as I am not as familiar with
the mysql manual as I probably should be.
Thanks.
Dotan Cohen
http://lyricslist.com/lyrics/artist_albums/389/osbourne_ozzy.php
Osbourne, Ozzy Song Lyrics
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default setup on new system
Hi list, I just setup mysql and apache on my Fedora Core 4 machine. I intend to use MySQL through php. I understand that I must first create a user, but I can't find out how! Even on the mysql site the tutorial starts off assuming that there is already a user created. I have tried googleing for mysql, newbie, create user, tutorial and installation but have come up with nothing. Please, how do I create the first (and subsequent) user? Thanks! Dotan Cohen http://lyricslist.com/lyrics/artist_albums/373/newton-john_olivia.php Newton-John, Olivia Song Lyrics -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
