mysql

Two versions of mysql on one Mac server, need to get data from one to other

My sysadmin patches my Mac server, and suddenly MySQL is pointing at a version 5 install. Prior to this, it was pointing to a version 4 install. My data is stranded in the version 4. Somehow whenever I run the mysql command I end up in the version 5. I need to do a mysqldump of the version 4 stuff to put it in v5. I'm not sure how to:...

MySQL / PHP problem with " and '

Hi I made a simple news system with comments using PHP and MySQL, and it worked great on my local Apache server, both on my Fedora 10 machine, and my Windows 7 one. Now I have a problem though, I uploaded it to a web host and it keeps returning all the ' and " as \' and \". I believe this is either the web host who by automatic adds them...

MySQL multiple right joins

mysql> select * from product; +------------+---------------+ | product_id | name | +------------+---------------+ | 1 | Car | | 2 | House | | 3 | Cat | | 4 | Blank Product | +------------+---------------+ 4 rows in set (0.00 sec) mysql> select * from tag; +----...

cleaning datasources

Hi I'm project managing a development that's pulling data from all kinds of data sources (SQL MySQL, Filemaker, excel) before installing into a new database structure with a record base through 10 years. Obviously I need to clean all this before exporting, and am wondering if there are any apps that can simplify this process for me, or ...

MySQL : When stored procedure parameter name is the same as table column name

Hello, Let's say a have a stored procedure SetCustomerName which has an input parameter Name, and I have a table customers with column Name. So inside my stored procedure I want to set customer's name. If I write UPDATE customers SET Name = Name; this is incorrect and I see 2 other ways: UPDATE customers SET Name = `Name`; UPDATE cu...

Grouping Data by Year -- Return 0 for no results

My Current MySQL query: SELECT MAX( s.con_grade ) AS max, YEAR( u.sent_date ) AS year FROM pdb_usersectionmap u LEFT JOIN pdb_sections s ON u.section_id = s.id WHERE u.user_id =21 AND YEAR( u.sent_date ) > '2004-01-01' GROUP BY YEAR( u.sent_date ) ORDER BY u.sent_date ASC (The year and user_id are generated dynamically in PHP) I'm tr...

SQL ORDER BY the 'IN' value list

I have a simple SQL query (in PostgreSQL 8.3) that grabs a bunch of comments. I've composed a bunch of ids before-hand and that gets fed into the WHERE IN clause like so... SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4)) This returns the comments in a natural order which in my case is the ids like 1,2,3,4. What I'm wan...

Is this the right place to call the RSS building function?

Is this the right place to call the function that builds the RSS? its for a reddit type of site. function save() { /* Here we do either a create or update operation depending on the value of the id field. Zero means create, non-zero update */ if(!get_magic_quot...

mysql vs PDO

Hello, I'm fairly new to php and have built a medium sized website using standard mysql database calls. However, I have recently learned about PDO and I am hoping to find out from the community if it is worth switching from mysql over to PDO. For security I have been using mysql_real_escape_string. Info about the site: I'm using a m...

MySQL vs SQLite + UNIQUE Indexes

For reasons that are irrelevant to this question I'll need to run several SQLite databases instead of the more common MySQL for some of my projects, I would like to know how SQLite compares to MySQL in terms of speed and performance regarding disk I/O (the database will be hosted in a USB 2.0 pen drive). I've read the Database Speed Com...

MySQL on duplicate key update

If I have query like this, how can I refer to values I have already given in update statement, so that I don't need to insert same data to query again? Example I would like to update col1 value with 'xxx', but now I need to enter 'xxx' again in duplicate statement. Is there anyway to refer those values in duplicate statement? INSERT INT...

Is there the upper limit of the number of rollback in MySQL5.1 with InnoDB?

I do data management with InnoDB of MySQL5.1 and use transaction when I perform INSERT, UPDATE for plural tables. I sometimes brought myself to the incidentally, but will there be the method that how much data number knows whether rollback of protectionism is possible when I carry out rollback of protectionism on a transaction start poi...

MySQL : When stored procedure parameter name is the same as table column name [continue]

Hello, Let's say a have a stored procedure SetCustomerName which has an input parameter Name, and I have a table customers with column Name. So inside my stored procedure I want to set customer's name. If I write UPDATE customers SET Name = Name; this is incorrect and I have to write (for example) UPDATE customers SET `Name` = Name;...

Why do I get an "This version of MySQL doesn't yet support '...'" error?

I have a query like following: delimiter $$ DROP TRIGGER IF EXISTS TR_SCIN_BANK_UPD$$ CREATE TRIGGER TR_SCIN_BANK_UPD AFTER UPDATE ON SCIN_BANK FOR EACH ROW BEGIN IF OLD.BANK_NAME != NEW.BANK_NAME THEN INSERT into SCIN_BANK_LOG SET BANK_ID=OLD.BANK_ID, BANK_NAME=OLD.BANK_NAME, LAST_UPD_USER_ID=OLD.LAST_UPD_USER_ID, LAST_UPD_TS=now...

Unite two MySQL queries with a UNION or programmatically

I've got two MySQL queries that both insert data into a table. Both have the following format: CREATE TABLE IF NOT EXISTS `data` ( `id` BIGINT NOT NULL AUTO_INCREMENT UNIQUE, PRIMARY KEY (`id`) ) SELECT `field1`, `field2` WHERE `active` = 1 The only differences between the two queries are how field1 and field2 are determined, and some...

MySQL/PHP Query

I have 3 groups of fields (each group consists of 2 fields) that I have to check against some condition. I don't check each field, but some combination, for example: group priceEurBus, priceLocalBus group priceEurAvio, priceLocalAvio group priceEurSelf, priceLocalSelf My example (formatted for legibility) how can this be improved? ...

Problems with mixing form and mysql database query

I have the following form, which I have reduced as much as I can, without being sure where the problem is coming from. I am trying to insert the form values into a database. However, when trying to use the form below, it spits out: Query was empty twice. The STATUS table exists, as do the fields deleted and notice. The table is curren...

MySQL connections from client programs persists.

In a college where I'm employed, we are currently testing (dry-run) the new enrollment system I have developed. The application has several modules (accounting, cashiering, course scheduling, admission, advising, grading) installed in different offices. We are using MySQL Server 5.1 and the front end was developed using VB.Net. The sys...

Simulating MySql's password() encryption using .NET or MS SQL

Hi guys, I'm updating an old ASP/MySql webapp to ASP.NET/MS SQL. We would like to keep the logins from the old website working in the new app. Unfortunately the passwords were stored in the MySql DB using MySql's password() function. Is it possible to simulate MySql's password() function in either .NET or MS SQL? Any help/links ...

Options to Replicate Microsoft SQL Server Database to MySQL/PostgreSQL on Linux

I need to replicate data from Microsoft SQL Server to MySQL or PostgreSQL. The data includes images stored in BLOB columns. Could you please comment on your experiences with the following strategies and suggest others I may have missing? custom script written in Java using JDBC linux odbc driver with perl script Setup my own windows ...