I am coding and ran into a simple problem that I am sure there is a solution too - I just haven't been able to find it. I want to do the following query: UPDATE `users` SET balance = (balance - 10) WHERE id=1 But if the balance will become a negative number I want an error to be returned. Any ideas on if this is possible? Thank you, ...
Hi guys, In the process of fixing a poorly imported database with issues caused by using the wrong database encoding, or something like that. Anyways, coming back to my question, in order to fix this issues I'm using a query of this form: UPDATE table_name SET field_name = replace(field_name,’search_text’,'replace_text’); And t...
I am using the MySQL C API within a C++ application. I have a column in my result set that is of the type MYSQL_TIME (part of mysql.h). Is there a way to convert MYSQL_TIME to a char* or to a C++ string? ...
In my database I currently have two tables that are almost identical except for one field. For a quick explanation, with my project, each year businesses submit to me a list of suppliers that they sale to, and also purchase things from. Since this is done on an annual basis, I have a table called "sales" and one called "purchases". ...
does anyone know where on access 2007 a function where i can access my sql database? i need it to create reports. thanks ...
What is the best way to test the speed of a LAMP based site, without factoring in the user's connection? In other words, I have a CMS and I want to see how long it takes for PHP and MySQL to do all their work. Additionally I do not have shell access to the server, it is in a shared hosting environment. ...
I'm working on a script that sadly I inherited - with no commenting or anything. Argh! For testing purposes I duplicated one of the tables in the database which had an auto-incrementing ID. When the data is saved to the database, though, the ID number just reads "0" -- which is the default for that column. I'm not sure why it's not auto...
My table has data like so products_parent_id | products_quantity 2 5 2 7 2 9 2 4 My SQL statement looks like so (so far): UPDATE ' . TABLE_PRODUCTS . ' SET products_quantity = products_quantity +' . $order['products_quantity'] . ', products_orde...
It's probably the tenth time I'm implementing something like this, and I've never been 100% happy about solutions I came up with. The reason using mysql table instead of a "proper" messaging system is attractive is primarily because most application already use some relational database for other stuff (which tends to be mysql for most o...
Hey guys, I'm trying to make a graph in Rails, for example the avg sales amount per day for each day in a given date range Say I have a products_sold model which has a "sales_price" float attribute. But if a specific day has no sales (e.g none in the model/db), I want to return simply 0. What's the best way in MySQL/Rails to get this ...
I can do it in sybase and I can do it in oracle, but I'm not seeing how to do it in mysql. I've got this: (please restrain yourself from re-formatting my sql, last time somebody did that they changed it so it wasn't the same and made the question meaningless) select table1.id from table1 inner join table2 on (table1.id = table2.i...
Hi Everyone, I have a MySQL Left Join problem. I have three tables which I'm trying to join. A person table: CREATE TABLE person ( id INT NOT NULL AUTO_INCREMENT, type ENUM('student', 'staff', 'guardian') NOT NULL, first_name CHAR(30) NOT NULL, last_name CHAR(30) NOT NULL, gender ENUM('m', 'f') NOT NULL, dob V...
Is there a way to restrict certain tables from the mysqldump command? For example, I'd use the following syntax to dump only table1 and table2: mysqldump -u username -p database table1 table2 > database.sql But is there a similar way to dump all the tables except table1 and table2? I haven't found anything in the mysqldump documentat...
I have a problem where I always need to give simple access to my local account, and don't want to deal with typing in my root password a million times on my local machine. How do I give perms so i can type 'mysql' with my local user and get access to everything? ...
I'm inserting a DateTime into MsSQL using the GetUTCDate() function provided by MsSQL. I need to convert the time in C# to show it as the Unix / MySQL integer, so that it can be eventually manipulated with PHP. I believe the Unix / PHP / MySQL ticks start at 1/1/1970, but I'm not sure how I would convert the equiv MsSql / C# time into ...
Hi, I'm pulling a list of items from one table, on the basis of their being included in another table, like this: select fruit.id, fruit.name from fruit, fruit_rating where fruit_rating.fruit_id=fruit.id group by fruit.name; That works fine -- it basically produces a list of all the fruit that has been rated by someone. But now, I wa...
I'm working on a project that has a MySQL transactional database backing up a web application. The company uses SQL Server for back office and reporting applications. What is the best way to update SQL Server with the data from MySQL? Right now, we are performing a dump of the MySQL data and doing a full restore. This may not be feas...
Why would someone use a group by versus distinct when there are no aggregations done in the query? Also, does someone know the group by versus distinct performance considerations in MySQL and SQL Server. I'm guessing that SQL Server has a better optimizer and they might be close to equivalent there, but in MySQL, I expect a significant...
Out of the following queries, which method would you consider the better one? What are your reasons (code efficiency, better maintainability, less WTFery)... SELECT MIN(`field`) FROM `tbl`; SELECT `field` FROM `tbl` ORDER BY `field` LIMIT 1; ...
I have a stored procedure that is used when a new transaction is inserted. This procedure inserts properly into the transactions table but I also need to update another related table based on inserted values. Based on the Product_ID I need to update PT_Pct_to_Salon in a table called 'Salon' with a value from a table called 'Zen_Product...