Assume a table titled 'transactions' Typical query: select * from transactions where ip=INET_ATON('127.0.0.1'); I want the ability to do a regex search for all ip's with a particular first octet (i.e. 127) but I can't quite figure out the syntax. Thanks! ...
Hi, I have a text file with a long list of terms (approx 800) sorted alphabetically in the format: aword bword cword ... I would like to use this file to create a new MySQL table where each term is a field, all with the property VARCHAR(5). Would be best if the fields were inserted in the order in which they appear in the file as I ...
Given these two tables: Foo (id, name) -- PK = id Bar (fooId, value) -- PK = composite, fooId + value -- value is a non-negative int How can I find all the Foo.names where there is no corresponding Bar,value above 0? eg: Foo id name 1 a 2 b 3 c Bar fooid value 1 0 1 1 2 ...
hi all, i have defined a stored procedure (let's call it proc_create_node(parent INT)) in mysql which does an insertion. when i call it from the mysql cli, doing the following works just fine: CALL proc_create_node(12); SELECT LAST_INSERT_ID(); and i get the last inserted id: +------------------+ | LAST_INSERT_ID() | +-------------...
I'm running MySQL 5.x on my local Windows box and, using MySQL administrator, I can't connect to the databases I created using the root account. The error I get is: MySQL Error number 1045 Access denied for user 'root'@'localhost' (using password: YES) I can't recall changing root account credentials, but I know I tried to give...
After reading through many of the questions here about DB schema migration and versions, I've come up with a scheme to safely update DB schema during our update process. The basic idea is that during an update, we export the database to file, drop and re-create all tables, and then re-import everything. Nothing too fancy or risky there...
My Windows client application (written in C#) works with the remote MySQL database deployed on *NIX server. I'm going to add the ability to store files on the server. I was never fond of storing files in DB, so the files will be stored in the file system. The application will need to upload, download, and delete files from server. Any ...
I am trying to query a mysql table which contains strings of numbers (i.e. '1,2,3,4,5'). How do I search to see if it has '1' but not '11' bearing in mind if it is '9,10' '9%' doesnt work?? Fixed! (field like '10' OR field like '%,10,%' OR field like '%,10' OR field like '10,%') ...
In my database, on a particular table there are several cloumns but the only 2 of interest right now are stone_id and upcharge_title. Individually they can each have duplicates, but they should never have a duplicate of BOTH of them having the same value. For example stone_id can have duplicates as long as for each duplicate upsharge ti...
Hi, I know that deadlocks occur inevitably when using transactions in Innodb and that they are harmless if they are treated properly by the application code - "just try it again", as the manual says. So I was wondering - how do you detect deadlocks? Does a deadlock issue some special mysql error number? I am using PHP's mysqli extensio...
I'm possibly teaching an undergraduate PHP/MySQL course in the Fall and was wondering if anyone has any recommendations for good textbooks. The books have to be geared towards a beginner/medium level (I'm assuming/hoping my students will have C++ and MsSQL classes prior to mine). Topics I want to cover in PHP: Variables/Constants Con...
I have a MySQL table called bb_posts used by a bbPress forum. It has an autoincrement field called topid_id and another field called topic_poster. I'm trying to write a function that finds the "next post by the same author". So, for instance, say the user is on a particular page that displays topic 123. If you do a SQL query: SELECT * ...
Hi everyone, I'm wondering if there's some sort of runtime mechanism that would observe the queries that are running against my database server; record how many queries of each "type" are running; look at the performance of these queries; then, based on this runtime data, suggest what indexes need to be added/removed. I'm working aga...
I regularly have lists such as "To-do" and "Done-works". It would be useful if it worked something like: mysql add-to-column-in-table "Blog corrected" mysql read-column-in-table-where-match-blog "Blog" Can I use MySQL for the job? Are you using it for day-to-day things? [Clarification] I need to organize my things to tables...
Problem Users from other IPs on the (Windows XP) LAN suddenly cannot connect to my local MySQL server. Background I've set up MySQL on my local Windows computer so that other computers on the network have access to the root account. I've added each IP as a host for root. Up to some weeks ago, things worked flawlessly and I could conne...
Hello, I want to write an application that runs entirely locally on one machine - there is no need for connection to the internet or to any external machines. I was thinking that it would be a good idea to use a web browser as the platform for this application so that I would not have to mess around with lots of UI stuff - I could just...
I am finding it difficult to use MySQL with Python in my windows system. I am currently using Python 2.6. I have tried to compile MySQL-python-1.2.3b1 (which is supposed to work for Python 2.6 ?) source code using the provided setup scripts. The setup script runs and it doesn't report any error but it doesn't generate _mysql module. I...
The actual question is a little more complex than that, so here goes. I have a website which reviews games. Ratings/reviews are posted for each game, and so I have a MySQL database to handle it all. Thing is, I'd really like a page that showed what score (out of 10) meant what, and to illustrate it would have the game that was last rev...
Hi there, I'm using PHP and PHPMyAdmin to create a small profile site. I'm giving members an ID number, based on which is the biggest number currently in the database, +1 I did 25 tests before I got the PHP script where I wanted it to be. I then deleted those 25 entries using PHPMyAdmin. But now, when my PHP code does this: function...
Example data: 1,000,000 rows Structure: id (int), randomText(char 10), flag(boolean) index creates for id, randomText and flag This is mysql command: update table_NAME set flag = ( select flag from ( select @randomText:=randomText as 'randomText', '1' as flag from table_NAME where flag=0 limit 1 ...