Re: identifying multi-byte characters / coverting
hi that may be but it's got weird beahviour. for example, when u get that in an edit box (like the one u compose mail in) it looks like: (the upper comma). but when i read ur mail, it appeared as it's html character code. in fact, while reading this u may see it as the character code and not the character itself and vice-versa when composing a reply. my question is: does mysql treat/store it as the character code (which would make sense) only? and does it have the upper comma look only when viewed in a browser edit box? also appears like that when u don't apply htmlentities to the output. ty abs --- [EMAIL PROTECTED] wrote: Hi abs, My first post seem to be vanished, so excuse me if this is double. I am not sure if I understand youir problem correctly. Isn' what you describe as the html code for the upper komma or how you want to call it ? ___ALL-NEW Yahoo! Messenger - so many all-new ways to express yourself http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
identifying multi-byte characters / coverting
hi when ppl paste from MS Word to a text field in a form, characters like apostrophe before s (e.g. It's) in docs looks like (it looks like a comma but at the top). in a mysqldump, i can see that it's stored as #8217;. i tried editing the in a text box and when i reprint it, it's looks like it did. are there any situations where this may cause a problem? and is there any way to filter it or convert it to a single-byte char? i'm assuiming it's multibyte, coz i didn't see it in the ascii char list. thanks abs ___ALL-NEW Yahoo! Messenger - so many all-new ways to express yourself http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
check privileges on a db as normal user
hi my site is hosted on a machine on which i am not root. so i don't have access to the mysql db. i'm quite sure that the admin on that server doesn't know how to do the security right. he gave my id almost root access on his mysql server so that i could access it. two three others have almost the same status. there are about 100 users in all. now, the privileges have been fixed, but only for me afaik. is there a way i can find out which users have access to my db's? ie. the db's i have access to? 2nd question: if i did have the encypted version of someone's mysql password, how do i actually use it? google searches only explain the situation, not how it's done. coz i know those 3-4 ppl can see my encrypted passwd. (yes, 'mysql' db is viewable to them). ty abs Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_escape_string ... when?
hi the mysql manual suggests that we use the mysql_escape_string() function when generating dynamic urls. is there any other situation when it is required? and could someone explain when/why this would be needed for urls...other than to make the browser understand that character (such as ' # etc. in the url). and if magic_quotes_gpc is on, any and all escape characters are backslashed right? how does when check what the accepted mysql escape characters are? (like -- for a comment) thanks abs Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
left join-ing for two values
hi how do u create a query to select two diff values of the same column from another table using a left join? i know i'd retrieve them with aliases but where do i set which alias is for which value? here's a simplified version of the tables table1: colours id colour table2: choices id old new old and new are both ids in the 'colours' table. what i wanna do is something like: select id, colours.colour as old1, colours.colour as new1 from choices left join colours on colours.id = old... now here, can an and clause be used so it reads on as... and colours.id = new or do i put another left join? either ways, how do i specify which join / equality is which alias or whateva? i don't wanna use two queries...as far as possible. ty abs ps: i wouldn't be going through all this just for colours ;-) also (unrelated to mysql) ;-) i didn't know that pink's song feel good time is from charlie's angels 2. just saw the video on mtv and realised. my question: the rythym guitar, bass and to some extent, the lead guitar are playing this tune throughout the song, any idea which song it's from? i don't think it's original to that song, anyone know where...? Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: left join-ing for two values
i wrote: hi how do u create a query to select two diff values of the same column from another table using a left join? i know i'd retrieve them with aliases but where do i set which alias is for which value? here's a simplified version of the tables table1: colours id colour table2: choices id old new old and new are both ids in the 'colours' table. what i wanna do is something like: select id, colours.colour as old1, colours.colour as new1 from choices left join colours on colours.id = old... now here, can an and clause be used so it reads on as... and colours.id = new or do i put another left join? either ways, how do i specify which join / equality is which alias or whateva? i don't wanna use two queries...as far as possible. lol @ me. i decided to use my brains for a change. didn't realise it was staring me in the face. use aliases...for tables too! here's what i've come up with, and works... select choices.id, a.colour as new, b.colour as old from choices left join colours a on a.id = choices.new left join colours b on b.id = choices.old if i put choices.id as just id, i get id is ambiguous (after making all changes to real table and column names) ;-). if 'from' is 'choices', shouldn't mysql assume it's from that itself and not one of the joined tables? abs ps: anyone with an answer to that song question? :P Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with Update statement
--- rmck [EMAIL PROTECTED] wrote: mysql SELECT start,ID FROM table ORDER BY id DESC LIMIT 1; ++---+ | start | ID| ++---+ | 1072603517 | 617168732 | ++---+ 1 row in set (0.01 sec) mysql So now I want to run my update statment, how do I not update the times that have been converted??? Help my current update statement: from script: OID=`echo select ID from $TBLE order by ID desc limit 1; | $MSQL -u$UI -p$PD -h$HT $DB|grep -v ID` echo update $TBLE set start = from_unixtime(start) where ID '$OID';| $MSQL -u$UI -p$PD -h$HT $DB for starters, when u use desc to get ur 'oid', it's already the highest. so when u say where ID '$OID', it won't match any rows. '=' would've atleast matched that one row. unless BOTH the above command run repeatedly after changing the highest value (so then onto the next highest, etc.) in the manual, mysql SELECT FROM_UNIXTIME(875996580); - '1997-10-04 22:23:00' so that part is fine. 2 (low tech) solutions that would work *well* are: 1. check the length of the timestamps. atleast so far, i don't think the no. of seconds would've crossed 19 chars which is the size according the date format returned by FROM_UNIXTIME (when it's without +0). so u can use where length(start) 19 and u'd only need the second command. 2. to be y10k compliant :P do an instr search. since u using that style, there will always be a - or a : (search for : ... just in case some warped -ve values got in there). so use where instr(start, ':') i don't see the point about worrying about the ids. in case some rows were edited in between and the initial query wasn't run with an order by clause, there could be some in between that haven't been reformatted. those two solutions are pretty much foolproof. since some values have already been converted, i think u've made it a varchar(19) or something, i.e. it's length is fine. put semi-colons at the end of my 'where' segments. i guess they're required. of course, these queries will run slower coz u're doing text search stuff rather than a simple id comparison. abs Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update shouldn't update
hi in the php manual, regarding 'mysql_affected_rows()' it says: When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possiblity that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query. i'm running a query that will attempt to update rows in a table. it runs an update statement for each id (loop), so mysql gets sent 10 updates. now, when i add the affected row nums, i get 10, even though i haven't changed any of the values. but if i paste any of the queries into phpmyadmin to run, it gives 'Affected rows: 0'. so how come the difference? abs Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update shouldn't update
--- Abs [EMAIL PROTECTED] wrote: hi in the php manual, regarding 'mysql_affected_rows()' it says: When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possiblity that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query. i'm running a query that will attempt to update rows in a table. it runs an update statement for each id (loop), so mysql gets sent 10 updates. now, when i add the affected row nums, i get 10, even though i haven't changed any of the values. but if i paste any of the queries into phpmyadmin to run, it gives 'Affected rows: 0'. so how come the difference? i cancelled the line that executes the query and instead pasted it in PMA, it chaged a row. but i don't see any difference in the data. are there any issues like assigning an int col with the data in single quotes? as in, assigning id = '5' instead of just id = 5 ? i'm using varchars, chars, ints, etc. no blobs or texts. still dunno why the diff. abs Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sending array data using php mail
--- Mike Johnson [EMAIL PROTECTED] wrote: For the record, there's a syntax error in there -- the closing curly brace is missing. echo {$row[Password]}\n; ^ Also, I've never tried this syntax with double-quotes. Do the curly braces keep the PHP parser from thinking that the opening for Password is a close of the string? I use single quotes in that kind of situation, FWIW. yeah, my bad, the ending curly braces were missing. when using double quotes, the curly braces are there so that it can identify the whole variable as an array type. yeah, it would probably mix up the meaning of the double quotes in the array key name. even i use single quotes for array keys, was just showing how the syntax should be in that situation. the manual uses and ' quotes in just about all contexts anyway. if the whole thing is in double quotes then it needs curly braces anyway, regardless of which quotes i use for the key. i haven't come across anything specific regarding this in the documentation. for the array stuff in strings and filehandling, it's shown with single quotes. whereas, in the array function examples, they use double quotes. not using any quotes... END_OF_EXAMPLE // Works but note that this works differently outside string-quotes echo A banana is $fruits[banana].; END_OF_EXAMPLE; so for safety, i use curly braces and single quotes. maybe double quotes can be used when u want to use a weird key name like: $a[all$myvars_start_with_all] though $a['all'.$myvars_start_with_all.'EVERYWHERE'] would be clearer in that case. (uself if u're importing variables and prefixing them with something so that u don't need to re-write code u've written and/or can adapt code written for register globals on while using it when off. just mho. abs Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
num rows / pages
hi i was trying to group my results 10 per page ($p per per page). if i use limit, then there's no way of knowing how many there are left so i can't give page numbers as: first 2 3 4 last . perhaps running the query twice, first time wihtout limit to see how many there were and the 2nd just for a particular bunch with limit. wouldn't that load the mysql db? and what if i've got 10,000 rows or so? the query will take time. any solutions? thanks abs Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: num rows / pages
--- Chris Elsworth [EMAIL PROTECTED] wrote: Use SQL_CALC_FOUND_ROWS. Documented in http://www.mysql.com/doc/en/SELECT.html You run your first query with SQL_CALC_FOUND_ROWS, then once you're done with it, you run another (SELECT FOUND_ROWS()) and you get the total resultcount you would have got, had you not LIMIT'ed it. thanks. i didn't know there was such a function. nice. now all i've to do is get the site hosting ppl to upgrade to 4.x :P fortunately, in this case, the table to browse will always be scanned entirely so my big set is the number of rows of the table (there's no where clause). so the SHOW TABLE STATUS FROM db LIKE tbl will do. 'Rows' has what i need. just in case anyone else was in a similar situation. abs Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sending array data using php mail
hi the error is in this line: $newvalue = $row[Password]; make it $newvalue.= $row[Password]; (notice ^ the dot - for concatenation with the previous value). and if u want to see each password in the browser too, in that same loop, put: echo {$row[Password]\n; so it would now look like: while ( $row = mysql_fetch_array($rows1) ) { $newvalue.=$row[Password]; echo {$row[Password]\n; } u may also want to use a br in ur echo line to make the browser output nicer, the \n affects only the html code. and while u're at it, put a \n in $newline so that ur email looks nicer. abs BT Yahoo! Broadband - Save £80 when you order online today. Hurry! Offer ends 21st December 2003. The way the internet was meant to be. http://uk.rd.yahoo.com/evt=21064/*http://btyahoo.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
setting values to default w/ null
hi i'm trying to get musql to insert the default values into a table. from the insert section in the manual: Warnings can occur under any of the following conditions: * Inserting NULL into a column that has been declared NOT NULL. The column is set to the default value appropriate for the column type. This is 0 for numeric types, the empty string ('') for string types, and the ``zero'' value for date and time types. for myisam tables, nulls are handled as above, so how does one get the default value set to a column? i know that if a column name is missing in a insert statement then it get assigned to default value. is there any way to SET it to the dafault value in an UPDATE query? so one could use a function like: switch ($type) { case text: $var = ($var != ) ? ' . $var . ' : NULL; break; (etc.) and replace the null with DEFAULT so that when mysql sees the keyword, it sets that column's value to the column's default (and not the column type's default) ty abs Download Yahoo! Messenger now for a chance to win Live At Knebworth DVDs http://www.yahoo.co.uk/robbiewilliams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: escaping slashes not present in table?
--- Daniel Kasak [EMAIL PROTECTED] wrote: Abs wrote: mysql and php question: the magic_quotes_gpc is set to 1. when i echo it to the browser, it shows the added slashes. i inserted these same values into a database. when i read the database values and printed them, i forgot to use stripslashes. but i was surprised to see that the slashes were already removed. now i know this depends on magic_quotes_runtime, which i checked, was OFF, so how did the slashes get removed without me explicitly doing so? what's more interesting is that when i view the data in the table using phpMyAdmin, the quote i put wasn't escaped with a backslash. when i tried to do a dump/export of the table, the output showed a backslash before the quote. so how come? the slashes should be showing when i say SELECT * FROM MYTABLE. abs I think this is why people recommend that you *don't* use PHP's magic quotes. I hit this problem in a few areas and decided to turn it off. Use PHP's functions: stripslashes() and addslashes() You'll be sorry later if you don't, and continue using magic quotes... that still doesn't answer the question though. if the variables already had the slashes in them because of magic_quotes_gpc, then shouldn't the table i inerted it into also have the slash in it? magic_quotes_runtime ADDS slashes if it is ON, so it leaves the data untouched if it's OFF (if this is incorrect, the documentation needs to be updated). hence, when i retrieved the data from the db, the quotes should have still been there. and the o/p to browser, phpMyadmin (and macromedia dreamweaver's test window) all don't show any slashes in the data, but 'export' in phpMyadmin adds slashes to the text it outputs. magic_quotes_gpc is On magic_quotes_runtime is Off magic_quotes_sybase is Off (to be sure) another thing that comes to mind is... does mysql store the data in the .tbl/.frm files in their text form? or is the slash we add there just to tell mysql to disregard the significance of the next character? the 2nd one seems logical considering that mysql would read data per column in the specific size it's supposed to be (and not pay attention to the mean of what it's reading). so if a varchar(50) column has the data: `qwer'y\19o` then it knows that it should read and return 10 chars/bytes. Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
binary data fields
are there any issues regarding special characters that should be escaped when storing binary data in a BLOB field? any special ascii codes that must be checked for before inserting data into a table? i remember reading something about ascii(26) causing some problems when using mysqldump and dumping back into a table. would this be related to the win32 version? abs Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
escaping slashes not present in table?
mysql and php question: the magic_quotes_gpc is set to 1. when i echo it to the browser, it shows the added slashes. i inserted these same values into a database. when i read the database values and printed them, i forgot to use stripslashes. but i was surprised to see that the slashes were already removed. now i know this depends on magic_quotes_runtime, which i checked, was OFF, so how did the slashes get removed without me explicitly doing so? what's more interesting is that when i view the data in the table using phpMyAdmin, the quote i put wasn't escaped with a backslash. when i tried to do a dump/export of the table, the output showed a backslash before the quote. so how come? the slashes should be showing when i say SELECT * FROM MYTABLE. abs Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
enum for bool in the future
hi i know this has been discussed before, storing bools in the right column type. in the case of enum as: enum(N,Y) or n,y, etc. it might be easy to read when u're looking at the database table itself, but if u had to communicate this properly to other programmers designing a front-end, they wouldn't be checking for BOOLS, rather for: if ($value==Y) { } else { } (add to this, the case of string or char, which would matter more when using a C program or any other language that doesn't type cast as per the convenience of the statement) is there any combination that would facilitate using it as just: if ($value) { //true } else { //false } ? i think enum(NULL, ) is the only option since a NULL would imply false or 'not true' in most languages. the second value could be 1 for clarity (as opposed to empty strings). but i was looking for a better option. checking for isnull, etc. is almost the same problem as the first case, which is easier to read if we had to. thanks abs Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
splitting tables based on row length
ok, i've got this table (20 columns). keeping in mind how/which web pages would access them, i decided to keep the last 7 in a different table, since that data would only be shown if they clicked on a link they were interested in (30-40% probablity). although, when they do view it, i'd retrieve all columns from both tables. Q 1. is this an advisable way to go about it? both tables created would have dynamic row length. when i started creating the first table, i realised that 7 out of the first 8 fields were fixed length. so i thought i'd furthur divide it into 2, first one with fixed length rows and the second being dynamic. so now i have 3 tables: t1 = 7 fixed len columns t2 = 6 dynamic t3 = 7 dynamic t1 and t2 will always be retrieved almost entirely and at the same time (i.e. one after the other). there's a 1-1 relation between t1, t2 and t3. and in 30-40% cases all of them. t1, t2 and t3. Q 2. any suggestions on the optimal may to go about this? i am aware of the disk seek time and data retrieval issues. Q 3. does mysql not pay attention to data that is (i) not mentioned in the SELECT fields list or (ii) WHERE clause? scanning time being considered different from return time. hence, putting t2 and t3 as one table. also, not using the mysql query cache. and would that change how the tables are designed? abs Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]