Cleaning Rows In The Database From Strange Characters...
I have emails in my customers table. At the end of some of the email addresses I have a strange character:?
email@email.com plus two square looking characters
How can I trim my emails so that they are cleaner?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Strange Characters In Mysql Database
I'm currently updating a site and in one of the fields is the content, but in the content I'm noticing strange characters such as: Quote: “This is a quote� These are Mr Plum’s ideas... The thing is, I need to get rid of them, or replace them (on mass) with the proper character. Is there a way to do this without finding every record that has one of these special characters and then replacing it?
Replace Strange Characters
When I made a back-up on my old database and did restore it on a another server then all å ä ö in all tables where replaced with some strange characters. How can I use Replace() to replace all these characters in the database (all tabels at the same time)
Weird / Strange Characters
I recently switched servers, and when i dumped all of my database info, some of the fields that contained single and double quote characters turned into:—. (things like this). Does anyone know of an easy way to fix this. Possibly without re importing the data.
Cleaning Database Field Script "O'Connell"
I have fields like these in my database: O'Connell I would like to write a sql statement that would eliminate backslashes from the first and lastname fields. How can I do this?
Strange Problem With Connecting To Database
I've installed new server and transferred databases from my home computer to this server. I have no problems with connecting to server's database from my home computer (with MySQL Query Browser), but I can't connect to database from PHP script, placed on same server. I get this error: Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'login'@'host' (using password: YES) in /home/user/script.php on line 20 Could not connect: Access denied for user 'login'@'host' (using password: YES) PHP code is: $conn = mysql_connect("server's ip:3306", "login", "pass") or die('Could not connect: ' . mysql_error()); mysql_select_db("database", $conn);
Strange MySql Database Table
I got a very strange MySql database table from my client. Inside the table there is a columne with datatype char(1), but the default value is '000'. I would really want to know how to create this column.
Strange Behavior Of Properties Section Of Database
We recently moved our company to a new building, and servers along with it. Although our mysql database is still on the same server, ever since the move, I have a lot of difficulty (impossibility) getting the properties portion of the table to click open so I can see the fields. I need to get in there to add new fields and so forth. In some cases, I cannot even browse the tables to view the contents. Can someone explain what the problem could be, and how to fix?
Limit Number Of Rows Or Characters Returned In A Text Field
I understand you can limit the rows of a recordset, but can you somehow tell a query to limit the rows (or characters) returned within just a text field? I have a page that returns abstracts of many articles and I want to truncate the description for each to just 200 characters. Is there a way to accomplish this with MySQL?
Help Cleaning Code?
I'm not too familiar with MySQL code, I was hoping someone could help clean this code to make it importable in PHPMyAdmin, in it's current form, it produces an extreme amount of errors when I attempt to import it in PHPMyAdmin. — MySQL dump 10.9 – — Host: localhost Database: meneame — —————————————————— — Server version 4.1.11-Debian_4sarge2 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; – — Table structure for table `blogs` – DROP TABLE IF EXISTS `blogs`; CREATE TABLE `blogs` ( `blog_id` int(20) NOT NULL auto_increment, `blog_key` varchar(35) collate utf8_spanish_ci default NULL, `blog_type` enum(’normal’,'blog’) collate utf8_spanish_ci NOT NULL default ‘normal’, `blog_rss` varchar(64) collate utf8_spanish_ci NOT NULL default ‘’, `blog_rss2` varchar(64) collate utf8_spanish_ci NOT NULL default ‘’, `blog_atom` varchar(64) collate utf8_spanish_ci NOT NULL default ‘’, `blog_url` varchar(64) collate utf8_spanish_ci default NULL, PRIMARY KEY (`blog_id`), UNIQUE KEY `key` (`blog_key`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `categories` – DROP TABLE IF EXISTS `categories`; CREATE TABLE `categories` ( `category__auto_id` int(11) NOT NULL auto_increment, `category_lang` varchar(4) collate utf8_spanish_ci NOT NULL default ‘es’, `category_id` int(11) NOT NULL default ‘0′, `category_parent` int(11) NOT NULL default ‘0′, `category_name` varchar(64) collate utf8_spanish_ci NOT NULL default ‘’, PRIMARY KEY (`category__auto_id`), UNIQUE KEY `category_lang` (`category_lang`,`category_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `comments` – DROP TABLE IF EXISTS `comments`; CREATE TABLE `comments` ( `comment_id` int(20) NOT NULL auto_increment, `comment_randkey` int(11) NOT NULL default ‘0′, `comment_parent` int(20) default ‘0′, `comment_link_id` int(20) NOT NULL default ‘0′, `comment_user_id` int(20) NOT NULL default ‘0′, `comment_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `comment_karma` smallint(6) NOT NULL default ‘0′, `comment_nick` varchar(32) collate utf8_spanish_ci default NULL, `comment_content` text collate utf8_spanish_ci NOT NULL, PRIMARY KEY (`comment_id`), UNIQUE KEY `comments_randkey` (`comment_randkey`,`comment_link_id`,`comment_user_id`), KEY `comment_link_id_2` (`comment_link_id`,`comment_date`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `friends` – DROP TABLE IF EXISTS `friends`; CREATE TABLE `friends` ( `friend_id` int(11) NOT NULL auto_increment, `friend_from` bigint(20) NOT NULL default ‘0′, `friend_to` bigint(20) NOT NULL default ‘0′, PRIMARY KEY (`friend_id`), UNIQUE KEY `friends_from_to` (`friend_from`,`friend_to`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `languages` – DROP TABLE IF EXISTS `languages`; CREATE TABLE `languages` ( `language_id` int(11) NOT NULL auto_increment, `language_name` varchar(64) collate utf8_spanish_ci NOT NULL default ‘’, PRIMARY KEY (`language_id`), UNIQUE KEY `language_name` (`language_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `links` – DROP TABLE IF EXISTS `links`; CREATE TABLE `links` ( `link_id` int(20) NOT NULL auto_increment, `link_author` int(20) NOT NULL default ‘0′, `link_blog` int(20) default ‘0′, `link_status` enum(’discard’,'queued’,'published’,'abuse’,'duplicated’) collate utf8_spanish_ci NOT NULL default ‘discard’, `link_randkey` int(20) NOT NULL default ‘0′, `link_votes` int(20) NOT NULL default ‘0′, `link_karma` decimal(10,2) NOT NULL default ‘0.00′, `link_modified` timestamp NOT NULL default CURRENT_TIMESTAMP, `link_date` timestamp NOT NULL default ‘0000-00-00 00:00:00′, `link_published_date` timestamp NOT NULL default ‘0000-00-00 00:00:00′, `link_category` int(11) NOT NULL default ‘0′, `link_lang` int(11) NOT NULL default ‘1′, `link_url` varchar(200) collate utf8_spanish_ci NOT NULL default ‘’, `link_url_title` text collate utf8_spanish_ci, `link_title` text collate utf8_spanish_ci NOT NULL, `link_content` text collate utf8_spanish_ci NOT NULL, `link_tags` text collate utf8_spanish_ci, PRIMARY KEY (`link_id`), KEY `link_author` (`link_author`), KEY `link_url` (`link_url`), KEY `link_date` (`link_date`), KEY `link_published_date` (`link_published_date`), FULLTEXT KEY `link_url_2` (`link_url`,`link_url_title`,`link_title`,`link_content`,`link_tags`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `trackbacks` – DROP TABLE IF EXISTS `trackbacks`; CREATE TABLE `trackbacks` ( `trackback_id` int(10) unsigned NOT NULL auto_increment, `trackback_link_id` int(11) NOT NULL default ‘0′, `trackback_user_id` int(11) NOT NULL default ‘0′, `trackback_type` enum(’in’,'out’) collate utf8_spanish_ci NOT NULL default ‘in’, `trackback_status` enum(’ok’,'pendent’,'error’) collate utf8_spanish_ci NOT NULL default ‘pendent’, `trackback_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `trackback_date` timestamp NULL default NULL, `trackback_url` varchar(200) collate utf8_spanish_ci NOT NULL default ‘’, `trackback_title` text collate utf8_spanish_ci, `trackback_content` text collate utf8_spanish_ci, PRIMARY KEY (`trackback_id`), UNIQUE KEY `trackback_link_id_2` (`trackback_link_id`,`trackback_type`,`trackback_url`), KEY `trackback_link_id` (`trackback_link_id`), KEY `trackback_url` (`trackback_url`), KEY `trackback_date` (`trackback_date`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `users` – DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `user_id` int(20) NOT NULL auto_increment, `user_login` varchar(32) collate utf8_spanish_ci NOT NULL default ‘’, `user_level` enum(’normal’,’special’,'blogger’,'admin’,'god’) collate utf8_spanish_ci NOT NULL default ‘normal’, `user_modification` timestamp NOT NULL default CURRENT_TIMESTAMP, `user_date` timestamp NOT NULL default ‘0000-00-00 00:00:00′, `user_pass` varchar(64) collate utf8_spanish_ci NOT NULL default ‘’, `user_email` varchar(128) collate utf8_spanish_ci NOT NULL default ‘’, `user_names` varchar(128) collate utf8_spanish_ci NOT NULL default ‘’, `user_lang` int(11) NOT NULL default ‘1′, `user_karma` decimal(10,2) default ‘10.00′, `user_url` varchar(128) collate utf8_spanish_ci NOT NULL default ‘’, PRIMARY KEY (`user_id`), UNIQUE KEY `user_login` (`user_login`), KEY `user_email` (`user_email`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `votes` – DROP TABLE IF EXISTS `votes`; CREATE TABLE `votes` ( `vote_id` int(20) NOT NULL auto_increment, `vote_type` enum(’links’,'comments’) collate utf8_spanish_ci NOT NULL default ‘links’, `vote_date` timestamp NOT NULL default CURRENT_TIMESTAMP, `vote_link_id` int(20) NOT NULL default ‘0′, `vote_user_id` int(20) NOT NULL default ‘0′, `vote_value` smallint(11) NOT NULL default ‘1′, `vote_ip` varchar(64) collate utf8_spanish_ci default NULL, PRIMARY KEY (`vote_id`), KEY `user_id` (`vote_user_id`), KEY `link_id` (`vote_link_id`), KEY `vote_type` (`vote_type`,`vote_link_id`,`vote_user_id`,`vote_ip`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci PACK_KEYS=0; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
Cleaning Data
I'm about to inherit a MySQL instance full of tables themselves full of data which has plenty of holes. Is there an accepted standard in cleaning it up?
Cleaning Up MySQL Records In A Table
I transfered a mysql database to a new mysql server using a little software... Thing is, it put some garbage characters at the end of every record and it messes up when I try to echo the result in PhP.I'm looking for a way to remove the unwanted characters (or non-alphanumeric characters) in each record.Is there an easy way to do this ? I'm also using phpmyadmin, if it can be usefull.
Cleaning Up MySQL Connections To Avoid 1040
Well, I'm trying to run PHP $result = mysql_query("SHOW FULL PROCESSLIST"); while ($row=mysql_fetch_array($result)) { $process_id=$row["Id"]; if ($row["Time"] > 200 ) { $sql="KILL $process_id"; mysql_query($sql); } } To clean up my connections, as I'm getting a 1040 error "too many connections". Of course, I can't run this until I can actually connect, unless there is a way around somehow. I don't have any admin rights, I just have a web-based "php my-admin" module to run the db. Every page people access opens a mysql connection, and then it is closed with PHP mysql_close($connection); . Would putting in PHP <?php mysql_close($connection); $NASI_connection = null;?>
Database With Accented Characters
I have a database with a lot of accented characters. Is there a way that I can run searches in plain text that will pull matches with similar string? ie if I'm running the search "Belanger", the row "Blanger" will be returned.
Unwanted Characters In Database
Suddenly several unwanted characters, like –, are appearing in my mysql driven sites. First it started showing up in my Wordpress blog (http://www.joycebabu.com/blog/steganography-hide-your-files-within-files.html). Now it is showing up at other database stored entries too (http://www.prokerala.com/general/recipes/snacks/vanilla-cookies.html). Can someone plz tell me what is causing the problem? Also how can I replace them? Can I perform a simple search replace using phpMyAdmin?
Weird Characters In Mysql Database
I'm having some problems with one of my mysql databases. In my data there's some weird characters that's looking terrible when viewed in firefox. IE seems to ignore this character so it doesn't show up. Now to the question, does anyone know how to remove these "characters" from my data? I've attached an image which shows my problem since it's quite hard to explain with words.
Importing Database And Special Characters
The database .sql file is quite large (74 Mb) and I'm using the command line 'source filename.sql' method to import it on my local mysql server. It works, except that somehow the content of my text fields is altered at places where special characters are encoded i.e. Les souverains ayant régné sur le Zollernberg ont utilisé divers titres durant la période précédant le règne becomes Les souverains ayant régné sur le Zollernberg ont utilisé divers titres durant la période précédant le règne Why is this change occuring and what can I do to avoid it when I use export/import tables ?
Inserting Special Characters Into MySQL Database
I am trying to add products to one of my clients' online shops and in the past it's all gone fine. I have a PHP script which handles a CSV file upload, digests the rows and inserts the data into a number of tables. The upload I did today seemed fine until I realised that certain products have appeared with a ? in the name or description where there shouldn't be. Checking in the CSV, I find that the client has added a few hundred new products whose names include 'Hahnemühle Albrecht Dürer' (the product supplier) and other products whose names &/or descriptions include the micron symbol 'µ'. As you've probably guessed, the characters which are being replaced are ü and µ. FYI, the site is still using MySQL 3.23.58 - I know it's old but we've just persuaded the client into a new .NET and MSSQL site re-design starting in a couple of weeks and we'll be dicthing that server so upgrading isn't an option. I guess that I would be doing a search and replace for these characters in PHP (by ascii code?) and then inserting into the database. Then doing the reverse when displaying the product on the page?
Strange DB Error
I have a strange error from MySQL: Could not execute SELECT en FROM translation WHERE id = 229. Error: 1033 :: Table './dbname/translation' was created with a different version of MySQL and cannot be read. I just got this error like that, without doing nothing. And the bad news is that the entire DB is wracked. What can I do?
Strange Character ...
We were trying to save https://oururl in mysql database. But got https: / /oururl We don't know why "/" be changed to " /", any suggestion?
Strange Insert
i hava a basic insert sql: insert into test values(123.34, 321.32, 678.34,32.8); Here is table structure: CREATE TABLE `test` ( `data_hba1c` float(3,2) unsigned NULL, `data_albumiini_kreatiini` float(4,3) unsigned default NULL, `data_mikroalbuminuria` float(3,2) unsigned default NULL, `data_sldlkol` float(2,1) unsigned default NULL ) ENGINE=InnoDB; MySql changes inserted values in database to: data_hba1c -> 9.99 data_albumiini_kreatiini -> 9.999 data_mikroalbuminuria -> 9.99 data_sldlkol -> 9.9 Strange but true. Any one else had the same problem?
Strange Connection
I'm using MySQL Control Center to connect to a MySQL server which I have configured and started on a remote computer. I have already succeeded connecting and started working on the "test" database. I already had 1 table there, and when I tried to return all its rows, it suddenly could not connect and retrieve the data set (error 2003)! I then disconnected from the database and tried to re-connect, but then I recieved again error 2003! Then I connected to the remote computer (using SSH) and restarted the MySQL server. After that, I was able to connect to the "test" database. I created a new table, added a few fields and saved it. Then I tried to return all the rows of the new table, and once again got error 2003!!! I tried executing the SQL query "SELECT * FROM <table-name>", but got the same results. I don't understand what's happening here! Why can I define tables, but not filling them with data? Why, after disconnecting from the database, can't I connect again?
Strange Float
I have a field, with a FLOAT(31,30) as field type, whenever I insert 5.254 the value changes to 5.254000186920166015625000000000 Where do these numbers after the .254 come from, I would logically think it would fill it up with zero's or nothing .. but this is a real problem since quite some calculations depend on these numbers and there is no room for errors in the needed calculations, and Yes, we need calculations up to 30 numbers after the .
Strange Join
I'm hoping someone can help me here.. I have 2 tables.. car (id, driver, navigator, backup) person (id, name, age, gender) The driver, navigator, and backup fields are FK's for person ids. What I want is: 'driver', joe, 27, M 'navigator', sue, 29, F 'backup', jack, 32, M Is there a way to do this in a single sql statement?
Strange Query
I have the following simple query: select * from person where username='admin' and status='A'. When executed in the person table it executes fine but returns no recordset. In a similar table (user with a username and status field) it executes as expected and returns a result. The problem seems to be the status field (is it a "reserved" word. The only difference between the two tables is that in the user table - username is unique. Does any one have any idea why this behaviour is exhibited?
Adding Rows To My Database
I am writing a php username/password script. I have been able to insert one row into my database, but whenever I attempt to add another user nothing happens (not even an overwrite). The POST array contains the values $Name and $Pass. How do I fix this? //local variables $username="Bobby"; $password="glove"; $database="martians"; $link="mysql.net"; //connect to db mysql_connect($link, $username, $password); mysql_select_db($database) or die("Unable to select database"); //sql query mysql_query("INSERT INTO user (userName, password) VALUES('$Name', '$Pass')"); //close db mysql_close();
Selecting A Certain Rows From A Database
if i wanted to select a certain range of rows from a database,is it possible?For example,i have a column in my database which is also a primary key and i wanted to select rows from where the primary key column is 21 - 40,would this sql statement be valid? SELECT * FROM tbl1 WHERE id=21 TO id=40;
Insert Rows Into Database
I need to insert some rows into a database, but without duplicates. I can find some SQL commands to insert the rows as long as there isn't a row like it, but I am only concerned about one column. So if I had a table with an id, first name, and last name. I would not want to insert the row 6,Jim,Smith, if 5,Bob,Smith already existed. I don't want two people with the same last name, regardless what their first name and id is. Is there an easy way to do this?
Strange Cartesian Product
I have a strange cartesian product error. I am doing a query from a db I did not create. I can see the tables in it and have a search query that searches in 2 tables. If I search for "name LIKE %' OR model LIKE '%670%' " I get the cartesian product error. However, if I search for "name LIKE %' " by itself I get an empty query set, 0 rows returned. And model LIKE '%670%' pdoduces an expected query of 7 rows. This makes absolutely no sense to me. If it does not match anything in the name column, how the heck is it creating a cartesian product result??? Furthermore, if I change the query to something that is contained in the name column, then I don't get the error. This is driving me nuts. To compund the matter further there is a third column involved which is doing the same thing. But I am currently testing this with just the two columns to narrow things down a bit.
Strange Problem With Primary Key
I am unable to add records in tut_db table, its stopped on id 255. Whenever I tried to add a new record I received the message "#1062 - Duplicate entry 饗' for key 1". I wonder why I got this message, it is an auto increment key. Following is the structure of table PHP `id` tinyint(5) unsigned NOT NULL auto_increment, `s_cat_id` int(4) NOT NULL default Ɔ', `title` varchar(255) collate latin1_general_ci NOT NULL default '', `url` varchar(255) collate latin1_general_ci NOT NULL default '', `des` text collate latin1_general_ci NOT NULL, `date` date NOT NULL default 񟍰-00-00', PRIMARY KEY (`id`), UNIQUE KEY `url` (`url`)
Sha1 Strange Values
I'm trying to use Sha1() to store a password value in our software. The software is using MyODBC 3.51.10. MySQL version 4.1 Statement is simply: update mytable set mypass = sha1('thepassword') where myuser = 1 For some reason, when one particular user saves a password, it stores a value that is exactly half of what it should be. It literally stores the first 20 characters of the value. If I store the same password, I get the full value. The password is stored in a varbinary column. Used to be in a char column. Both had the same problem (I changed the column type thinking that would help. Any ideas? I can't find any reason he would get a different value for the same password using the same software, database, server, etc. We're both running Windows XP. I'm almost certain there is something unique about his machine, but I can't imagine what it could be!
Strange LEFT JOIN
I tried a command (from phpMyAdmin): SELECT * FROM dttips t left join dtbets b on t.datum=b.datum and t.userid=b.userid where t.tip not like '0000%' and t.datum like '2004%' and (b.sazka='000' or isnull(b.sazka)) and t.userid='pepa' I've got 30 records in table t, 18 records in table b (with right key), but always get only 18 records !!! I would expect 30 lines (with LEFT JOIN) - 18 regular and 12 with 'b' table NULL 'tail'!!!
Strange Codes Have Appeared! =o(
we all know what access databases are! ;oP So I converted it to a MYSQL database. I only deal with the webside of the site as opposed to the content, although the guy who does this reported that there were some strange characters which had cropped up (not sure if this was related to the Access changeover or not). For example some of the ones we have found so far (first is what it should be, then the code which the database has converted it to): ' IS CONVERTED TO ’ -- IS CONVERTED TO — - IS CONVERTED TO • At the moment i just have a replace in my asp code when displaying these characters, but he says that more are appearing!? Should this be happening (as to me it seems like all these characters are used in SQL statements) or is there a problem somewhere along the line?
Strange Error On A Mysql_query
When I run the following query through PHPMYADMIN I have no problem and return the proper results. When I run a script calling mysql_query, I recieve the following errror: Warning: Supplied argument is not a valid MySQL result resource on line 58. To troubleshoot the situation: I echo the $sql that is being processed. Then I copy what is echoed, and paste it into PHPMyAdmin, and run it, and have no problems. Nontheless, I continue to recieve the error message when I try to run the PHP script and use the query. I'm using the following SELECT query: Code: SELECT IfNULL (a.article_date_added, p.photo_article_date_added) AS date_added, IFNULL (a.article_type, p.photo_article_type) AS type, IFNULL (a.article_title, p.photo_article_title) AS title, IFNULL (a.article_deck, p.photo_article_deck) AS deck, IFNULL (a.article_author, p.photo_article_author) AS author, IFNULL (a.article_date_long, p.photo_article_date_long) AS date_long FROM _dummy AS D LEFT JOIN article AS a ON ( D.num =0 AND a.article_section_id =11 ) LEFT JOIN photo_article AS p ON ( D.num =1 AND p.photo_article_section_id =11 ) WHERE D.num < 2 AND IFNULL(a.article_date_added, p.photo_article_date_added) IS NOT NULL AND IFNULL(a.article_title, p.photo_article_title) IS NOT NULL AND IFNULL(a.article_type, p.photo_article_type) IS NOT NULL AND IFNULL(a.article_deck, p.photo_article_deck) IS NOT NULL AND IFNULL(a.article_author, p.photo_article_author) IS NOT NULL AND IFNULL(a.article_date_long, p.photo_article_date_long) IS NOT NULL ORDER BY date_added DESC
Strange Query Result
I have a column (tinyint) that has either 1s or 0s in it. When I run this query counting either 1s or 0s it counts all of the rows, not just the ones with 1s or 0s as stated in the query: SELECT COUNT( cb_paiddues = '0' ) from jos_comprofiler or Code: SELECT COUNT( cb_paiddues = '1' ) from jos_comprofile
Trouble With Accessing Database -- Too Many Rows?
The database is used for an online game. One of the tables keeps track of all the lands in the game. There are 7 continents, each with a different set number of regions (239 in total). Each region has 625 sectors, and each sector contains 25 lands. The total number of rows in the database is 3,734,375. I recently populated the table with the necessary rows. The table comes out to over 93MB. I've already modified the structure of the table to maximize the space usage. I had no problems accesses my database before populating the world. However, now that I have, I can't access the database as a whole. I can send queries to select certain tables (such as the user table), and even check out the individual table structures in phpMyAdmin. But it locks up every time I try to connect to the database directly (ie selecting the database using phpMyAdmin or connecting through the game). I suspect it's because of the huge amount of data in the database. I tried to send a query to delete all the records in the land table, but it doesn't go through and just locks up.
Database Table Limited To 127 Rows
I created a csv file with 271 rows of information, then imported it into my database (via phpMyAdmin), only to discover that my table gets hung up on the number 127. In other words, it lists 126 rows, followed by "127" in the numerical key before it simply stops. I deleted my primary key and refilled my table. This time, all the rows imported successfully, but the numerical key is still flawed. The numerals 1-126 display OK, after which every additional row is numbered 127. I've had similar problems, where my table was apparently truncated because phpMyAdmin thought my table should only have X number of rows when it actually has twice that many. But I haven't figured this one out yet. When I browse my table, I see another problem: There are columns of question marks (?) before my first table column (the numerical/primary key). Except in the first column, the question marks are occasionally replaced by the little pencil icon you click to edit a row. In other words, most rows can not be edited because they lack the pencil icon. I normally created my tables in a spreadsheet, which I save in a csv format. But I created this csv file manually, so I wonder if that's where the problem is. This is what row 126 in my csv file looks like: 126,"ita","<p>Italy became a nation-state (etc.)</p> <p>A democratic republic (etc.)</p>"," ",Italy<br /> Everything after is not meant to display, and the <br /> is just a legacy of the Dreamweaver file I created this table on. I can't create this file with a spreadsheet, because the cells in my spreadsheet program only hold a limited amount of text, and some of these articles are quite long.
SSH: Select Database And Update Rows
I'm trying to update a few rows in my database via SSH. I have multiple databases on my server and I need to know what I'm doing wrong. I login via SSH and enter $: mysql -u root -p I want to select the database and then run my update command, but when I type. > select [db_name] It just jumps to a new line without any prompt. Can anyone tell me really quick when they do to select a database?
Random For Displaying Rows From Database
I did query and get all of the rows in my table test . Each row in my database include fields like this : id , ask , a , b , c , d , answer . All are 7 fields . When display on a page i want it to be displayed in inorder , in random the with the field ID and in the row the 4 fields a,b,c,d are all displayed in an inorder too . Telling this is so hard to imaging so i am trying to put an example here . This is a projest of mine , doing the work of a test with a question and 4 choies for ppl to choose the answer . Code:
Omitting Characters Between Characters?
This is what I wrote: select f.faqdesk_id , c.categories_id , f.faqdesk_question , c.categories_name , f.faqdesk_answer_short from faqdesk_description as f join faqdesk_to_categories as f2c on f.faqdesk_id = f2c.faqdesk_id join faqdesk_categories_description as c on f2c.categories_id = c.categories_id It works just fine, but the column: f.faqdesk_answer_short has characters I want to omit. For example, the results have html formatting. <H1>Hello World</H1><br><b>How is your day?</b> I want to perform the search without viewing the html. Is there a way to omit the brackets & anything between them?
How Big ( Disk Size ) Would This Database Be: 4.2 Billion Rows
I have a database in MySQL version 5.0.19 that I'm making. Its structure is: CREATE TABLE `big` ( `keyval` int(10) unsigned zerofill NOT NULL, `name` varchar(20) default NULL, `avail` enum('y','n') default NULL, PRIMARY KEY (`keyval`), KEY `keyval` (`keyval`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The 'keyval' will be filled with every value from 00000000 to 4294967295 ( 4.3 billion rows ), and assume worse case of 20 characters in each 'name' element and 'y' or 'n' always set in the 'avail' colmn.
Strange 'unknown Column' Error
PHP $sql = "SELECT COUNT(*) FROM klanten WHERE username = '$_POST[newid]'"; $result = mysql_query($sql); if (!$result) { error('A database error occurred in processing your '. 'submission.If this error persists, please '. 'contact xxx.' . mysql_error()); and I swear there is a column called username. Still, I get an error saying that column username is not there. Anyone has an idea what might be wrong ? I just added the column. Does the server need time to refresh or sth ?
Strange Select Command, Is That Normal?
I am using addslashes before submitting a string to mysql. Means, O'REILLY is getting submitted as O'REILLY. Now I was using a select command. PHP $lname = addslashes(htmlspecialchars($_POST['lname'])); "SELECT * FROM $mytable WHERE lastname = '$lname'" Problem is that, it's not working. So I made a function, and after much hit and try, this is what I found. PHP function kgbaddslashes($string) { $string = str_replace("'", "\'", $string); return $string; } and then PHP $lname = addslashes(htmlspecialchars($_POST['lname'])); $lname = kgbaddslashes($lname); "SELECT * FROM $mytable WHERE lastname = '$lname'" Now, this is working, but I am wondering where's the problem. I mean, why I had to add so many slashes to make it work?
Strange Problem With ORDER Clause
I am having a problem with a site I manage. The site has News Releases stored in a MySQL database. The news releases are displayed on the site, and ordered by DATE DESC which has been working fine for months and months up until today. Now, for some reason, the news releases are displaying a different order. They seem to be ORDERED by ID rather than DATE, even though the code hasn't changed. There doesn't seem to be any explanation as to why this occured and the code was not changed whatsoever. I use the same News Release system on a bunch of websites, without problems. I've checked the code, nothing has changed; I've checked the database, and it seems to be fine. I'm not even sure where to look now. Any ideas? Anyone been through this? Here is the code I use to draw the News Releases out: PHP <?php include 'inc/db.inc.php' // The basic SELECT statement $select = 'SELECT DISTINCT id, ndate, ntitle, nbody' $from = ' FROM news' $where = ' WHERE 1=1' $sort = ' ORDER BY ndate DESC' $nyear = $_POST['nyear']; if ($nyear != '') { // A year has been chosen $where .= " AND ndate LIKE '%$nyear%'"; } $searchtext = $_POST['searchtext']; if ($searchtext != '') { // Some search text was specified $where .= " AND nbody LIKE '%$searchtext%' OR ntitle LIKE '%$searchtext%'"; } $press = @mysql_query($select . $from . $where . $sort); if (!$press) { echo '</table>' exit('<p>Error retrieving news releases from database!<br />'. 'Error: ' . mysql_error() . '</p>'); } while ($row = mysql_fetch_array($press)) { echo "<tr valign='top'> "; $id = $row['id']; $ndate = htmlspecialchars($row['ndate']); $ntitle = htmlspecialchars($row['ntitle']); echo "<td width=ྖpx' style='color:#333;padding:7px;border-bottom: 1px solid #ccc;'>$ndate</td> "; echo "<td style='color:#333;padding:7px;border-bottom: 1px solid #ccc;'><a href='viewnews.php?id=$id'>$ntitle</a></td> "; echo "</tr> "; } if (mysql_num_rows($press) < 1) { echo '<p style="padding: 20px 0px 0px 0px;">No results found.' } ?>
Strange Access Denied Problem
Yesterday I signed up for another dedicated linux server with the hosting company I use and I am having problems getting MySQL 4.1.3 working on it. I've tried just about everything I can think of to login via the MySQL client but get a variation of the following error every time: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) I put skip-grant-tables in to my.cnf and restarted the mysqld service and logged in to the client as root no problem. I manually cleaned up the User table by resetting all the passwords using: UPDATE mysql.user SET Password=PASSWORD('mynewpass'); FLUSH PRIVILEGES; and granting all priveleges to all users. My User table now looks like this: +-----------------------+------------+------------------+ | host | user | password | +-----------------------+------------+------------------+ | localhost | root | *EAA2B914CE13E7B | | srv01.mysite.net | root | *EAA2B914CE13E7B | | localhost | wpphpadmin | *EAA2B914CE13E7B | | localhost | myuser | *EAA2B914CE13E7B | | localhost.localdomain | myuser | *EAA2B914CE13E7B | | 127.0.0.1 | myuser | *EAA2B914CE13E7B | +-----------------------+------------+------------------+ I've cut off the priveleges for readability but they are all set to Y apart from Grant_Priv which only root has. When I remove the skip-grant-tables from the my.cnf file and restart the service I get the following errors when I try to login: mysql -u root -p Enter password: mynewpass ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) mysql -h 127.0.0.1 -u root -p Enter password: mynewpass ERROR 1045 (28000): Access denied for user 'root'@'localhost.localdomain' (using password: YES) mysql -h srv01.mysite.net -u root -p Enter password: mynewpass ERROR 1045 (28000): Access denied for user 'root'@'srv01.mysite.net' (using password: YES) mysql -h localhost -u root -p Enter password: mynewpass ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) mysql -u wpphpadmin -p Enter password: mynewpass ERROR 1045 (28000): Access denied for user 'wpphpadmin'@'localhost' (using password: YES) etc. The same goes for the myuser user as well. None of them seem to work. My setup is: Redhat Linux 2.4.21-4.0.1 Apache 2.0.46 PHP 4.3.2 MySQL 4.1.3 Ensim WEBppliance Pro 4.0.0-14
Querying From 2 Tables With Strange Results
What I want is to run a query that select the complete.a2 field and echos the value, where dn_records.number='5553331111' Whats happening is all the values for complete.a2 are echoing back, even if dn_records.number isn't equal to '5553331111' I am getting same results using the below php script as I am usnig the MySQL Query Browser. <?php mysql_connect( 'localhost', 'user', 'pass' ) or die( "Error! Could not connect to database: " . mysql_error() ); mysql_select_db( 'customers' ) or die( "Error! Could not select the database: " . mysql_error() ); $result = mysql_query("select complete.a2, dn_records.number from complete, dn_records where dn_records.number='5553331111'") or die(mysql_error()); while($row = mysql_fetch_array( $result )) { echo "<tr><td>"; echo $row["a2"]; echo "</td></tr>"; } ?>
Strange Access Denied Message
Maybe i am going completely insane or i just can't see an error, but everytime i try to log in to a database i get an error message like this: Access denied for user: 'root@%' to database 'myDB'. What confuses me about it is the "%". All the data concerning the server, the password are correct but i get this strange message.
|
TRACKER
