mysql

Updating an attribute in XML with MySQL

I have a chunk of XML stored as a string in a MySQL database, and need to update one of the attributes using a query. Given the following string: <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <town> <road name="Main" direction="north"/> </town> I would need to change it to update the attribute direction to a differe...

join 2 tables in mysql, return rows that don't relate

I have 2 tables: table 1: questions (id, question, date) table 2: answers (id, question_id, answer, date) a question can have more than 1 answer, some questions don't have answers. I want to output only unanswered questions a query like "SELECT * FROM questions,answers WHERE questions.id!=answers.question_id group by questions.id" do...

How to bulid triggers for 3 table

Hi, I have problem with MySql, i try write triggers for table but my knowlage abut it is nearly none. What i want to do? I want update some of record in table A when someone put something in table B. But to do that first i need count row with value 1 and -1 from table B. This is my 3 table CREATE TABLE IF NOT EXISTS `wp_comment_vote`...

mysql fetch sum php

Hi, sql column - trans_value contain both positive and negative value amount. so i'm trying to figure out how do i set a sum of positive value and negative value aside, so that i can calculate the how much sum of positive and how much is sum of negative. so in the end, i can minus both, positive - negative. edit, i forgot to mention ...

MySQL RENAME TABLE behavior when 'replicate_do_table' is set under replication

I have a MySQL master machine, and several slaves. One of the slaves is only replicating two tables. I've set this up in my.cnf using: replicate-do-table = db.table_old replicate-do-table = db.table_new Now, I understand only queries effecting table_old and table_new will be reflected on this server. That's what I want. However, now...

Calculate non-standard auto-increment automatically

The table I am working with does not have a standard auto-increment field to use as a primary key, so I need to come up with a way to automatically calculate the value that should be used in the field. My first thought was to create a trigger to happen AFTER INSERT, however, as far as I can tell, there's no easy way to reference the row...

JDBC Connect and Disconnect: Am i doing it right?

Hi I just faced the following exception : Exception Occured while Connecting : com.mysql.jdbc.CommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket. This is usually caused by a limit on the number of sockets imposed by the operating syst...

Mysql order by comma-delimited column value

Hi Guys I would like to know if it's possible to order by a comma-delimited set returned by a subquery? I have the following: SELECT navigation.id, documents.template_id FROM navigation INNER JOIN documents ON navigation.document_id = documents.id AND FIND_IN_SET(navigation.id,(SELECT numeric_lineage ...

Numeric types and row size in MySQL

I have a user table structured like this: id MEDIUMINT(7), username VARCHAR(15) Would it technically be faster if I changed it to this instead: id MEDIUMINT(5), username VARCHAR(15) I'm confused because even though the total row length in terms of characters and digits would be shorter, I assume the number of bytes used would b...

SQL: Find and Replace with SQL?

I have a MySQL InnoDB database. I have a column my in 'article' table called url that needs to be updated. Stored in article.url = /blog/2010/article-name /blog/1998/the-article-name /blog/... I need to change /blog/ to /news/. (E.g. now article.url = '/news/...') What is the SQL needed to replace "/blog/" with "/news/" in the art...

How To clone an existing wordpress site into a subdirectory on same server?

For example: I have a Wordpress site (blog) on www.xxxxxx.com I want to upgrade some plugins and WP version of site but before it I want to check all things on a mirror version. How to make a mirror backup site of currently running site on same server like www.xxxxxx.com/testing-site/? Whatever I will do on mirror site, should not ef...

Mysql is taking .7 seconds to "copy into tmp table". What is the best way to cache this kind of query?

I have a group-by query that is very fast when it comes to indexing, joining, sending the results etc. Unfortunately, mysql spends 99.6% of its time "copying to tmp table" when I profile it. I am at a loss as to how I can get mysql to perform better on its own. This group by query essentially finds the number of entities for the top 20 ...

XAMPP phpmyadmin mysql user manual?

This is actually 2 questions... Background: After using the command line in MySQL for my database introductory classes (very enjoyable), I had been required to use a DBA tool and downloaded XAMPP as identified below. The transition to a GUI would be ok if I understood the interface for creating database tables... Q1.I have searched the ...

MySQL - Change the stored procedure definer

I have a around one hundred stored routines in my MySQL database with with most of them have 'root' as the definer. I have another mysql account named 'abc', how to change the definer of all routines to 'abc'. Is it possible to do the same if I have access to MySQL server only as 'abc' user and not as 'root' ...

Is there a framework for data access in PHP?

I'm a .net developer by trade but I'm looking to expand to PHP. I really like what I've seen PHP can do. However I have been somewhat spoiled with data access in .net (such as LINQ) and how easy it is to make strongly type datasets. Are there such ways to do the same (or similar ) thinks for data access in PHP? A framework I'm unawar...

Multiple ANDS in a PHP mysql query?

I'm attempting to use multiple ANDs in this query below, and it messes up the password every time when I attempt to use my login feature. code below. // this is my problem, right here $result = mysql_query("SELECT username, password, FirstName FROM members WHERE username='$myusername' ...

Check for specific errors in MySQL queries with PHP

Hello, Is there a way that I can check for specific errors in MySQL with PHP? For example, I want to run a query that inserts 1000 last names into a database table. I know that I can use mysql_error() to check for errors, but I want to check specifically for a "duplicate value" error. Is there anyway to do that? Thanks! ...

Showing the most populous products in a table?

How would I show, for example, the 10 most populous products in a table? I have a sales table for example, that contains sales for 20 beers, 10 nachos, 5 peanuts, 2 hotdogs and 4 breakfasts. Is there a way to automatically calculate and see which is the most popular, and show them in descending order? edit: My table is as follows Ta...

SQL query ignoring where clause?

I have this query SELECT theMonth, sum( Sales ) AS sumSales, sum( Saleswotax ) AS sumSaleswotax, sum( Purchases ) AS sumPurchases, sum( Purchaseswotax ) AS sumPurchaseswotax FROM (SELECT date_format( saledate, '%M' ) AS theMonth, sales.cost AS Sales, ROUND( sales.cost * 0.85, 2...

adding a where clause from a separate table

I have a sales table, with fields product and cost of type varchar and decimal. I also have a purchases table, with fields product and cost, also of type varchar and decimal. lastly, I have a products table, with fields name, category and cost, of type varchar, varchar and decimal. I am doing the following query to get the NET income ...