mysql

select rows in a one to many situation

Hi everybody! I think that I am stuck with this particular situation: Here are my tables: item_table: id | item 1 : A 2 : B 3 : C attr_table: attr | item_id 1 : 1 1 : 2 2 : 1 2 : 3 3 : 2 3 : 3 I would like to know if it is technically possible to retrieve any item which is associated with attr = 1 and 3. The answer shoul...

Why opensource database like Postgresql and Mysql don't have encrypted stored proc?

Why opensource database like Postgresql and Mysql don't have encrypted stored proc? Is it because of their innate open source philosophy? What are the compelling reasons to encrypt the stored procs? ...

MySQL / Rails Performance: One table, many rows vs. many tables, less rows?

Hi, In my Rails App I've several models dealing with assets (attachments, pictures, logos etc.). I'm using attachment_fu and so far I have 3 different tables for storing the information in my MySQL DB. I'm wondering if it makes a difference in the performance if I used STI and put all the information in just 1 table, using a type colum...

In MySQL stored procedures check if a local variable is null

With the following example stored procedure DECLARE Variable DOUBLE; DECLARE Variable2 DOUBLE; SELECT Something FROM Somewhere INTO Variable; SELECT Something FROM SomewhereElse INTO Variable 2; SELECT (Variable + Variable2); If either Variable or Variable2 are NULL then the final SELECT will return null, what I would like is that i...

backup MySql databases and email them somewhere at a certain time

We're running a CentOS server with a lot of MySql databases atm, what I need is a really easy way for us to back those up. Since many of them are under a couple of meg. Dumping, zipping them up then sending them to a secure Google Apps account sounds like a pretty good idea. So what I need is: a script that will dump and zip the databas...

MySQL Reference Book

Can you point me to a good reference book for MySQL? I'm an experienced database programmer, but I'm new to MySQL, so I need a book where I can look up specific things and get quick (short) answers (preferably with clear examples). It needs to explain MySQL-specific things, not standard SQL. I don't want a wordy book explaining the...

Why can i sort an inline SELECT value but not use it in a WHERE clause?

I have this small SQL query. SELECT a.`id` , a.`title` , a.`date` , ( SELECT MAX( grade ) FROM tests WHERE userid = 41 AND presid = a.`id` ) AS grade FROM `presentations` a WHERE a.`visible` = 1 AND `grade` >= 5 ORDER BY `grade` DESC This gives me the error 1054 - Unknown column 'grade' in 'where clause' But if...

trouble importing csv files to mysql from vbs and access

I am using the following code, based on from previous posts and answers by Remou and Anthony Jones. Dim db: db = "C:\Dokumente und Einstellungen\hom\Anwendungsdaten\BayWotch4\baywotch.db5" Dim exportDir: exportDir = "C:\Dokumente und Einstellungen\hom\Desktop" Dim exportFile: exportFile=NewFileName(exportDir) Dim cn: Set cn = Create...

mysql count into PHP variable

Hi there, Let say that we have the following query: SELECT DISTINCT COUNT(`users_id`) FROM `users_table`; this query will return the number of the users from a table. I need to pass this value to a PHP variable. I'm using this: $sql_result = mysql_query($the_query_from_above) or die(mysql_error()); if($sql_result) { $nr_of_user...

How to extract a matching phrase from data in MYSQL/PHP ?

I am trying to implement searching functionality in my website. I have following type of data. Title Descr ----- ----- World News World news, news from across the world, all world news events covered World Sports World sports news, for all of sports people, sports is a famous world No...

Check to see if an ID exists in a database

When given an ID number, i want to check to see if it exists in the database. Return true if the ID is found and if not, then return false. My knowledge of MySQL is very low, but i am assuming it would be somehting to do with the COUNT(*) function possibly? ...

InnoDB deadlock with read-uncommited! - Java - Glassfish - EJB3 (JPA/Hibernate)

Hello I'm new here and need some help :) It's been several days that i got deadlock issues on a Java application with Glassfish - EJB3 with Mysql InnoDB Config: Mysql InnoDB: Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Application server: Glassfish v2.1 Persistance with EJB3 - JPA - Hibernate To make i...

MySQL/PHP connection error, possible user fabrication

I'm in charge of maintaining a learning management system and this is an issue that I've been dealing with on and off over the past few months. A single student (among thousands) is claiming that his essay submissions are being "eaten" by the website. After form submission he says his essay has been replaced by the following text: ...

counting records from a grouped query with additional criteria

I have a table of reports that has these relevant fields: user_id (int 11) submitted_date (datetime) approved_flag (shortint 1) There are multiple rows per user_id, some with approved_flag = 0 and some with approved_flag = 1 and each with a unique submitted_date. I need to get a count of the approved and unapproved reports. But I on...

mysql transaction in stored procedure (locking/rollback)

does a transaction in a stored procedure do any locking to prevent others from updating tables? also do i need to explicitly put in rollback logic or will the transaction automatically roll back if an error occurs because it never reaches the commit command. ...

MYSQL: How can I create a new row and get the new id without inserting data?

Normally I get a new ID by inserting some data and it creates a new row and returns the new ID. But if I dont want to insert any data I just want to create a new row with a new ID and get the new ID...how can I do that? Thanks. UPDATE: OK Here is my issue. The table I wanted to do this to only has the 1 ID column. Why? I'll explain...

MySQL database up, but some tables not updating.

I have a mysql db with 10 tables. Each table drives a website where users can look at or submit data to that table. Now the more popular tables have stopped displaying new data (checking through phpmyadmin). The smaller tables are still working fine. I don't get any errors when submitting. The data just doesn't show up in the databas...

MySQL - Optimize a Query and Find a rank based on column Sum

I have a high score database for a game that tracks every play in a variety of worlds. What I want to do is find out some statistics on the plays, and then find where each world "ranks" according to each other world (sorted by number of times played). So far I've got all my statistics working fine, however I've run into a problem findin...

SQL - convert data to a date/source/value "grid"

I have data in an MYSQL database that looks like this: Project Date Time A 2009-01-01 15 A 2009-01-02 10 B 2009-01-02 30 A 2009-01-09 15 C 2009-01-07 5 I would like to produce output from this data like this: Date Project A Time Project B Time Project C ...

SQL Server 2005 to MySQL connection

I want to be able to email a report daily from a glpi database in MySQL. I would like to create a SSIS job to pull data from MySQL. How do I do this? ...