Strip Invalid Char From Tables
Just wondering if anyone knows a way I can run a query to strip out commas from a table?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Compare CHAR BINARY With CHAR
I'm using 4.1.8-standard on linux and get this: CREATE TABLE test (field1 CHAR(20) BINARY NOT NULL, field2 CHAR(20) NOT NULL) TYPE=MyISAM; INSERT INTO test VALUES('xxx','yyy') SELECT * FROM test WHERE field1=field2 ERROR 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
Strip Punctuation With SQL?
I'm looking at doing a mod_rewrite of my urls for SEO purposes, and want to include the title of the item in the url. A sample of a title would be... "The Latest And Greatest From Whatever!!!" Now I need to change this to make it sensible to use in an url, and was thinking probably strip punctuation, lowercase, and change spaces to hyphens, so the above example would become... "the-latest-and-greatest-from-whatever" I can do this fine in PHP, but when it comes to selecting the correct record from the database, I need to be able to do something along the lines of SELECT * FROM links WHERE $title=strtolower(strreplace(" ","-",strreplace("punctuation","",linktitle))) where linktitle is the title stored in the database, and $title is the url-encoded version. Is this possible/sensible, or is it better to process the url when adding a new entry, using PHP, and comparing this field instead?
Strip The Quotes
do u know an easy way for stripping some quotes from my database??. For example "INSPEC IEE" This is a problem because this kind of entries are entered in my db as INSERT INTO Publishers VALUES (96,'"INSPEC/IEE"','"INSPEC/IEE"',' ....... The same problem appears when i also use the php to retrieve them. How i should remove the quotes from my db?
Strip HTML Tags
I have a field 'Description' in a product table, which stores the text including HTML Tags.While displaying the listing of products... I want to show only first 70 characters, but excluding HTML Tags i.e. after strip of HTML tags.Code: SELECT IF( (LENGTH(i.txtDescription) > 70), CONCAT(SUBSTRING(i.txtDescription,1,70), ' ...') FROM itemmaster i Is there any way to do so in the SQL query. Otherwise I am having other ways, which will require a lot of work, because that listing is at many different places & different ways... but from a common query.
Strip Quotes From Text Or Varchar Fields
I am wondering if there is a simple way to go through a table and remove " or ' from a record if the it contains one of those items? I am pulling this data into a webpage to populate a javascript array and have about 1200 records that some contain quotes that really mess up the array.
Invalid Use
I am having a problem with group. Following is my query but it gives an error of "Invalid use of group". SELECT refMember.MemberAcroName, refMember.MemberName, Count(DistrictLevelInformation.FormID) AS Spread FROM (refMember LEFT JOIN MainInformation ON refMember.MemberID = MainInformation.MemberID) LEFT JOIN DistrictLevelInformation ON MainInformation.ID = DistrictLevelInformation.FormID GROUP BY refMember.MemberAcroName, refMember.MemberName, MainInformation.qYear, MainInformation.Quarter ORDER BY MainInformation.qYear DESC , MainInformation.Quarter DESC , Count(DistrictLevelInformation.FormID) DESC
Invalid Use
I have tested my query on MySQL 5 and it works, sadly the server it is to be run on only has 3. I was just wondering what I need to update in the following query to get it working on 3 or if it is even possible to get it running on 3. Code: ..... and keyword IN ('keywords') group by people having count(*) >= 1 ORDER BY people ASC
Invalid Index
I try to used the MyPhpAdmin 2.8.2.4 with PHP 4.4.4, MySQL5.0.2.4 and Apcache2 and I got this error, 'invalid Index'
Invalid Use Of Group By
I have the following query: SELECT CLAIMPOLICYNUMBER AS Plan, PROVIDERSPECIALTY AS Specialty, TINNUM AS Tin, SUBTINSEQNUM AS SibTin, CHARGETYPECATEGORY AS ChargeCategory, SUM(BILLEDCHARGE) AS Billed, SUM(PAID) AS Paid, " + _ COUNT(DOCUMENTNUM) AS ChgCount FROM BAT WHERE CASENUM='0000911' AND CHARGETYPE <> 'DENTAL' AND CHARGETYPE <> 'VISION' AND CHARGETYPE <> 'ADMIN' AND PMTDATE >= '2005-06-01' AND PMTDATE <= '2005-06-30' GROUP BY CLAIMPOLICYNUMBER, PROVIDERSPECIALTY, TINNUM, SUBTINSEQNUM, CHARGETYPECATEGORY ORDER BY SUM(BILLEDCHARGE) It works great without the ORDER BY clause, but with it, I get the aforementioned error.
Directory Name Invalid
For testing purposes I have PHP installed and I have MySQL installed on my local laptop. Windows XP Pro I am just learning both of them. I have read quite a few chapters in the MySQL Manual. Enough to allow me to get around in MySQL and directly create tables an view information in them, upload .txt files into the tables, etc etc. I am receiving an error when I try to connect to MySQL via a PHP script. I have no problem connecting to MySQL via Command Line. The error I receive is "The directory name is invalid." Here is my code... I am not sure where to put the directory name or where to put the directory path in order to make it valid. I am not sure what directory it is talking about. <html> <head><title>Connect to MySQL</title></head> <body> <?php //connecting to the mysql server uses a mysql_connect command. $link = mysql_connect("locoalhost",$_POST['username'],$_POST['password']) or die("Connect Error: ", . mysql_error()); echo 'Successfully Connected. '; mysql_close($link); ?> </body> </html> Where exactly would I put the path to the directory or where would I name the directory?
Invalid Syntax
I am having trouble changing my password back to OLD_PASSWORD by the following : mysql> SET PASSWORD FOR -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd'); When I do it, it says ERROR 1064: 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 'OLD_PASSWORD ('newpwd')' at line 2
Invalid Use Of Group Function
I have the following Sql Statement... I'd like to find the sum of hrs worked by an employee in the month, the required number of hrs, and the sum of the difference between the two, the worked hrs are calculated by subtracting the max vtranstime - min vtranstime and the required hrs are calculated by subtracting max to1 - max from1.... I tried to do the following: select sum(timediff(max(vTransTime),min(vTransTime))) as worked, sum(timediff(max(translog.to1),min(translog.from1))) as required, sum(timediff(sum(timediff(max(vTransTime),min(vTransTime))), sum(timediff(max(translog.to1),min(translog.from1))))) as overtime, empid,deptname, firstname,lastname from employee,translog,department where employee.emp_magnetic_num=translog.vId group by employee.empid I am getting the following error : Invalid use of group function
Invalid Backup File
I am tring to restore a .pmb backup file and getting the message "Invalid backup file". I am very new to myssql so I don't have a clue what that means. Wrong version of mysql? Corrupted file?
Invalid Use Of Group Function
Using mysql 4.0 I get the following error message "Invalid Use of group function" with the following code: update custRP c, tbl_acctbal a set c.rpBal = sum(a.currRPBAL) where c.custid = a.custid
Invalid Argument While Using BCP (Script)
This will be a 50/50 question. It's either something that the SQL experts can answer or a scripting forum. I'm new to SQL and just coming to grips with getting familiar with scripts. I've developed a small script for my workplace that will connect to a remote server and extract the specified table that I want, zip it up and then copy it back to my local machine. This works fine until it tries to run the bcp command. If I tivoli to the remote machine and type the bcp command in command prompt it works fine. However when executed from the script it throws an invalid argument and seems to refer to the "-" minus I place in front of the U (user) or P(pwd). The script echos the users table request to a file which is then copied to the remote server (SQLtable.cmd) and then PSEXEC is called to connect to the server and run the script. e.g. psexec \%Server% -u %user% -p %password% "C: empSQLtable.cmd" PSEXEC then returns the following error. C:WINNTSYSTEM32>Bcp FrontOff..SHIFT_TER_DATA out C: empdmpnlSHIFT_TER_DATA.dat ûUsa ûPpwd (chopped off the -n) Unknown argument '¹Usa' on command line. Sorry about all the data, just hope someone can help. Must be an easy way. The small script which psexec is executing contains two simple lines. Bcp FrontOff..SHIFT_TER_DATA out C: empdmpnlSHIFT_TER_DATA.dat -Usa -Ppwd -n wwzip -9 C: emp\%username%SQLtables.zip C: emp\%username%*.*
Invalid Characters With PHP And MySQL
I am writing about a situation that our client is currently experiencing with Linux Mandrake, Apache, MySQL and PHP. We successfully deployed a website running on this type of system for a period close to two years. It has a HTML GUI component that the client can type or paste content into and it serves-up dynamic pages in PHP from a MySQL database. Recently the web site was hacked by the Travel Bug virus and it forced the client to restore the web site and server software mentioned below. Original Install Mandrake 8, MySQL 3.23.32, PHP 4.0.06 and Apache 1.3.26-x.x Current Install Mandrake 9.0, MySQL 3.23.52-1, PHP 4.2.3-1.1 and Apache 1.3.26-6.1 The current install has lead to new problems. Content with double and single quotes are showing up as squares or question marks. For example, I have a page that is showing the square symbol in place of double quotes. If I do a view source on that specific page I can see the double quotes but if I telnet into the server and query the MySQL record belonging to that page the square or double quote character is nowhere to found. The following items are the steps we tried to understand why this is happening. First, we did the following to test the integrity of the MySQL backup file. We Restored the MySQL database on our test environment which has the original software specs mentioned above but on a window server running IIS not apache. I did a restore of the MySQL database file and did not get any invalid characters. Even though the environment isn’t completely identical to the clients we can make the assumption that the dump file wasn’t corrupted. Second, possibly content editors were copying and pasting from word into the HTML GUI component thus creating these invalid characters. In most cases yes but I personally noticed the invalid character issue with a page that doesn’t use the HTML GUI component. Third, Possibly the configuration especially the character set from the original version to the current version of MySQL is not configured correctly. We believe this to be the primary issue. The resolution is to configure or reinstall MySQL and restore from backup. Fourth, has anyone tried the PHP functions to convert the invalid characters. A request was to look into the iconv() function. The iconv library functions convert strings between various character sets encodings. This involves compiling the PHP interpreter which is why I haven’t tested it. Fifth, Can some other process running on the server be causing this? Altogether, we soon will begin creating a test server identical to the original settings and step through the install and try to recreate the invalid character issue. But, if anyone has experienced this invalid character issue or has more information that would benefit us your help would greatly be appreciated.
Invalid Use Of Group Function - Max(`mainid`)+1
I am trying to insert a incremented number into the menu table. I will have an autoincremented number as well as the mainid number. When I tested the code that is below, the above error happened. Can someone please tell me the correct way of doing this? I am getting really frustrated with this. Nothing wants to work for me. require "config.php"; $insert06 = "INSERT INTO `menu` (`mainid`) VALUES (max(`mainid`)+1)"; // error line if (mysql_query ($insert06)) { print "Mainid added."; $query2 = mysql_query("SELECT mainid FROM menu") or die ("Could not query because: ".mysql_error()); while($row2 = mysql_fetch_array($query2)) { echo $row2['mainid']." = Mainid"; } } else { print "<p>Could not add the entry because: <b>" . mysql_error() . "</b>. The query was $insert06.</p>"; } mysql_close();
Group By Returns Invalid Dates
Hi i have one table: ID, User, Score, Date 1, mac, 1234, 2007-12-09 09:01:07 2,kurca, 3232, 2007-12-05 09:43:57 3, mac, 999999, 2007-11-07 09:44:14 I wan to create a high score list which will represent the best scores but not repeating anyone for example: 1, mac, 999999, 2007-11-07 09:44:14 2,kurca, 3232, 2007-12-05 09:43:57 Now i use: SELECT ID, User,MAX(Score) as maxScore,Date FROM `table` GROUP BY User ORDER BY maxScore DESC with this query the score is right but the date is invalid example: 1, mac, 999999,2007-12-09 09:01:07 2,kurca, 3232, 2007-12-05 09:43:57
Table Join Invalid Query
I have this table join below and I am getting an invalid query. How can I put the 15th line ($sql .= " group.id = master.id "; and the 17th line ($sql .= " where ".$currentrow_sql ; together. Is it even possible to do that?
Error:invalid Use Of Group Function
version:mysql 4.0.18 for win hi,all dear:) when execute sql-statement, error raise: update customer,cu_order set customer_point=0 where customer_keyid=order_customerid and DATE_SUB(now(),INTERVAL 1 YEAR)=DATE_FORMAT(max(order_time),"%Y-%m-%d 23:59:59") error:invalid use of group function i beg someone tells me why?how resolve? thx
Valid/Invalid Column Names
I'd like to present tabular data in HTML with the first row being the names of the columns in my database. However, I'd like them to be more presentable; for example 'I.P. Address' instead of 'ip_address'. I was wondering if a name like 'I.P. Address' could ever somehow be a valid column name or not.
Invalid MySQL-Link Resource
I'm getting the error message 'Supplied argument is not a valid MySQL-Link resource' (applies to the 3rd line of code) when the code below executes: $delete_part_no_query = "DELETE FROM prices WHERE part_no == '$part_no_to_delete'"; $delete_part_no_result = mysql_query($delete_part_no_query); $num_delete_part_no_result = mysql_affected_rows($delete_part_no_result); The query works fine and I'm under the impression that 'mysql_affected_rows' should be used to return the number of rows affected by DELETE. However, I keep getting the above error message.
Invalid Distinct Recordset Returned On An Indexed Column
I have a table with about 1.2 million records. I have an index set on a column. For close to two years, this query has worked perfectly fine: SELECT DISTINCT `Mgmt_Area` as thevalue, `Mgmt_Area` as valueid from qcdata ORDER BY thevalue Note, this is a programmatically generated query based on some user selections. This query actually is used to create a listbox. The index has 62 separate values. This query would usually return: Code:
Warning: Mysql_data_seek(): Offset 0 Is Invalid For MySQL Result Index 2
I only posted this on the MySQL forums becouse i believed it had to do with the query. I get the following error: Warning: mysql_data_seek(): Offset 0 is invalid for MySQL result index 2 (or the query data is unbuffered) mysql_data_seek is used in a function (made not by me) which i know 500% that it works for other queries. The query in PHP is this:
CHAR Or INT
i have a 4 Digit empno (Employee Number) field. empno are started from 0001. i manipulate the db though php. Before creating the table in PHPMyAdmin i want to make sure which data type should be used for the empno. should i use the char(4) or int(4) type?
16 Char
Up until I upgraded to mysql5 I could log in with usernames longer than 16 chars by manually altering the mysql.user.User and mysql.db.User fields to 64 chars, so I can auth clients with full email addresses... been doing this for many years. I just now upgraded to mysql 5.0.16 and now client apps (mysql and php5-mysql) seem to truncate the username to 16 chars before submitting it to the mysql server. The mysql.user and mysql.db tables indicate these fields are still 64 chars wide but now, I presume, anything that uses libmysqlclient (not sure which version) like php5-mysql now truncate the username. I have over 2000 usernames longer than 16 chars which no longer work and I am loathe to go back to mysql4 because I have new SQL queries that can take advantage of procedures. Can anyone suggest how I can get back the "old" behaviour of the mysql clients allowing larger than 16 char usernames to be used
Char Vs Varchar
Please check the attached picture before reading my question. Q: I pick this table from a tutorial about char and varchar. My question is Why varchar (4) 'abcd' requir 5 bytes while while it has only 4 bytes? The additioanl byte is used for what ?
INT, ENUM Or CHAR?
I have several fields that are either 1 or 0. What's the best field type for this? Int, tinyint, enum, char(1)? Some of these may be used while searching.
Char Vs Varchar
I know varchar is slower than using char, while char can use up more space, but if space wasn't an issue, would creating database columns with char(255) be better, or more practical than columns of varchar(255)?
Which Is Better VARCHAR(3) Or CHAR(3) ?
I once read an article in Linux Format whereby a technical writer had made performance recommendations on a LAMP environment. One of the points raised was for small columns in a database, that one is perhaps better off to trade a small waste of space for a gain on performance - The recommendation said that when you use VARCHAR(3) that MySQL searches for disk space to take a record that will accept a variable length value up to three characters (he didn't use those words - these were my interpretations). He suggested that by using CHAR(3) - the server would spend less time hunting down where to insert the new record... I have problems explaining this but I'm wondering if someone with a greater dba background then mine could help and answer... Which is better, a VARCHAR on a very small column, or using CHAR instead and loosing out on a little disc?
Column More Than 255 Char
Is it possible to create a column for more than 255 char? I tried the "longtext" in the datatype but its character limit is 255. As i need to insert some paragraphs with few hundred of words, any idea how to solve this limitation?
Special Char
I've done on Macintosh a solution to export data from FileMaker and Import in mysql. I export, by ScriptMaker, a txt file, upload on my server with applescript and then I update the Mysql database with LOAD DATA INFILE. But, the file txt contain special character, and I don't know what can I do to resolve this problem. Mysql when I LOAD the txt file change the character with other strange char. I can't use backslashes in automatic mode because my fields are delimited by " "..
Datetime Vs Char(19)
I have a table that has a datetime field filled from a web server log file. It will always be the same format, ie "YYYY-MM-DD HH:MM:SS". I want to make an index on just the "YYYY-MM-DD" part (ie char(10)), which doesn't seem to work on a datetime field, but if I convert it to a char(19), it does. What am I losing by converting my datetime field to char(19)?
What Char-set To Use In My Case ?
Somewhere there is a column called blogs - type TEXT with a FULLTEXT index. Most entries would be in English, but few might be in any other language. Now, if I make it UTF8, it will be a waste of space to use 3 bytes for each character since most entries, say 99% are in English. What should be my approach here - to somehow enable multiple languages in the column but also to save space..
Format Char
I want change the separate to float. The data return as 4.56 and I want return with 4,56.
DATE Or CHAR
I am just doing the beginning setup for a my first db and one of the tables includes a date field, but the amount of info I put in will vary, so I don't know which field type to use. The reason for the field deals with album release dates, and for most of the items, I do only have the 4 digit number for the year, but on occasion, I also have it down to the day and month, which I would like to include when available. So, I guess if I had any question, it is more about what limitations I have when using the DATE field type. Do I have to have all parts filled in (yyyy-mm-dd) to not have it error or is that just a maxminum? Oh, and I do know about the YEAR type as being available if I did end up just using the 4 digit year, but am still hoping for a solution that will allow for the most information available. Additionally, at this time, I don't have any use for the date info except to be included with other information... I don't plan on having it be sortable or searchable by date.
Enum And Char
for a field to save city name,ENUM and CHAR which is better?(in performance,not care consistent)
Default Char Value
I am submitting a query that writes information into a table, I have two fields setup that do not allow null values, and if the data i am submitting has a null value for that field it should put the default value of N into that field. However it is not doing so and I cannot figure out why.
CHAR Field.
Is there funtion that would determine if a string contains characters other than 0 through 9? The following conditional will work in a select statement: if((if(length( trim(leading '0' from MYVALUE))= 0 ,'0',trim(leading '0' from MYVALUE)) = conv(MYVALUE,10,10)),MYVALUE,NULL) as MYVALUE But this seems cumbersome. Coming from a C background it seems that there should be an easier way.
Exchange Char
i have a dB 100MB some tables whica i do not know contain invalid char like ý (This char must be I) how can exchange this char. with I i want to do this at one time as group any application ?
Japanese Char
I am not able to store Japanese chars in mysql database. what should I do to work this out ?
CHAR Vs. VARCHAR
I have a database with 53 tables most of the fields are VARCHAR 255, I was worry about the space and never about the speed untill i realized with the many users I have now, that the site is really slow, so now as I have dedicated server I want to change all the fields to have VARCHAR(255) to CHAR(255) im worried if that would cause losing data or something, and before I do that, i would really know if that will much speed the site, specially if you know that some tables have more than 250000 records. also I would like to ask how to limit fields like text, or blob because some of these fields I use just to store data with 500 Letters or less. all i know about this is it will consume the web space, is there any other disadvantages?
Char Count
table name is abc.which contain content_id and content are the fields totally 500 record in this table, content hold the paragraph of the article, i want to to whcih record content hold the large paragraph or field character countand also find by desc order of the char count
Performance Char Vs Text?
I wonder if anyone with a better grasp of databases, well MySQL to be precise could advise me on something. In one particular column on a table I need to be able to store up to 500 characters, my question is from a performance point of view would it be better to use a text type for this column or split the column into two and use two char columns? Just a couple of things in relation to my question, I wouldn't actually need to do a search on the column(s) so splitting them in two wouldn't be a problem in that regard and every other column in the table is a fixed format which as I understand would mean improved search performance for the table ( assuming I used two char columns rather than one text column ). Or is the performance difference so small it is not worth worrying about? ( the table will eventfully get reasonably large, hundreds of thousands of rows, maybe over a million )
Char, Varchar, Text, Etc
I'm having trouble deciding how to use char, varchar, tinytext, text, etc. Which one will allow me to use the least amount of space/CPU? If I have small text fields, such as a person's name, which one should I use? What do I specify as the length?
Converting Varchar To Char
I setup a database last fall that worked fine for its intended purpose. Unfortunately, it's purpose has changed slightly of late. I setup a table using varchar tpes almost exclusively (a couple of int types as well). Now, I need to change all those varchar types to char types. Unfortunately, when I try to do an alter table command, MySQL keeps the varchar types (I know this is done for efficiency). Is there a way to "force" the change so the table will keep the char types? I need to export from this table and I need the fixed length fields.
|
TRACKER
