mysql

MySQL trigger loop

Hey all I am going through the pain stacking process of sorting out someone else code. So I am decided to recreate a new database to sit alongside the old one then to use triggers to transfer data between both tables. Now I have an issue with a it looping IE A trigger on each table to update the other. Once one updates it should upda...

MySQL db Audit Trail Trigger

I need to track changes (audit trail) in certain tables in a MySql Db. I am trying to implement the solution suggested here. I have an AuditLog Table with the following columns: AuditLogID, TableName, RowPK, FieldName, OldValue, NewValue, TimeStamp. The mysql stored procedure is the following (this executes fine, and creates the proce...

How to include many "sub"-queries in a SQL statement to generate file paths for images?

Greetings, Using this Query to extra data to CSV file from customized OS Commerce mySQL db. SELECT products_id, max( if( products_extra_fields_id = '1', products_extra_fields_value, 0 ) ) AS band_avail, max( if( products_extra_fields_id = '2', products_extra_fields_value, 0 ) ) AS 1_gal_avail, max( if( products_extra_fields_id = '3', ...

Is there a way to combine these 3 tables? (mysql)

I'm creating a "flagged" func that flags users, postings, and comments. I am currently using three tables: "flagged_users" "flagged_postings" and "flagged_comments". And in each table, there's: flagged_id, user_id/posting_id/comment_id, reason. Is there a way to combine into just one table? or is 3 tables the best? ...

mysql join default value

I've been trying to use the IsNull() function to ensure that there is a value for a field. $result = mysql_query(" SELECT crawled.id,IsNull(sranking.score,0) as Score,crawled.url,crawled.title,crawled.blurb FROM crawled LEFT JOIN sranking ON crawled.id = sranking.sid WHERE crawled.body LIKE '%".$term."%' ORDER BY Score DESC LIMIT " ...

Mysql Algorithm for Determining Closest Colour Match

I'm attempting to create a true mosaic application. At the moment I have one mosaic image, ie the one the mosaic is based on and about 4000 images from my iPhoto library that act as the image library. I have already done my research and analysed the mosaic image. I've converted it into 64x64 slices each of 8 pixels. I've calculated the a...

How can I determine when an InnoDB table was last changed?

I've had success in the past storing the (heavily) processed results of a database query in memcached, using the last update time of the underlying tables(s) as part of the cache key. For MyISAM tables, that last changed time is available in SHOW TABLE STATUS. Unfortunately, that's usually NULL for InnoDB tables. In MySQL 4.1, the cti...

MySQL concat question

Hi guys! I am somewhat mystified by concat and joins in MySQL 4. Here's where I am having an issue. I have two tables... person id, fname, lname, city, state, zip capital state, city I am needing to generate all states and the number of persons (from the person table) from each. Something like .... AK | 5 AL | 7 AZ | 0 etc et...

mysql query optimization

I would need some help on how to optimize the query. select * from transaction where id < 7500001 order by id desc limit 16 when i do an explain plan on this - the type is "range" and rows is "7500000" According to the some online reference's this is explained as, it took the query 7,500,000 rows to scan and get the data. Is there an...

mySQL ORDER BY ASC works, DESC does not...

I've "integrated" SMF into Wordpress by querying the forum for a list of the most recent videos from a specific forum board and displaying them on the Wordpress homepage. However when I add the ORDER BY clause, the query (which I tested on other parts of the same page successfully), breaks. To add to the mix, I am using the Auto Embed ...

Hibernate deletion issue

I'm trying to write a Java app that imports a data file. The process is as follows Create Transaction Delete all rows from datatable Load data file into datatable Commit OR Rollback if any errors were encountered. The data loaded in step 3 is mostly the same as the data deleted in step3. The deletion is performed using the followin...

Allow number to start with ZERO when stored in mysql integer field

I need to store phone numbers starting with 0 but whenever i try to store this in MySql table the starting ZERO is removed because no number start with Zero actually how i can solve this problem please do i need to change the field type from Integer to another type please help ...

Is it possible a trigger on a select statement with MySQL?

Hi. I know that triggers can be used on insert, update and delete, but what about a trigger (or sort of) on a select statement. I want to use a trigger to insert data on a table B when it is selected an existent record on a table A, it could be possible?. Thanks in advance. ...

When selecting an amount of rows, how can I select a percentage of a percentage?

Fairly simple question: Say I want to use 30 percent of the rows in the table of my MySQL table. However I also have a user input where they can select a percentage of that percentage For example: $_GET['percentage']% of 30% so we say that $_GET['percentage'] = 30 How would I select 30% (or $_GET['percentage']) of 30% to use in a wh...

System date capturing in to data base

i create a registration form in my jsp project.mysql be the database.i want to insert system date automatically in datebase in the registration time. ...

Using query to change table mapping

I have a table mytable( id, key, value). I realize that key is generating a lot of data redundancy since my key is a string. (my keys are really long, but repetititve) How do I build a separate table out that has (key, keyID) and then alternate my table to be mytable( id, keyID, value) and keyTable(keyID, key) ? ...

US Bank Routing Number and BIC/SWIFT

I've been having a hard time finding more information to this question, and since this site is visited by a lot of people from the United States, you guys might know/find the answer more easily. Banks in europe each have a SWIFT Number, while US Banks use Routing Numbers. This leads to following questions: Does each bank in the US als...

How to use AS keyword in MySql ?

In the below SP i will be getting result in One single column. How can i name the column of the output ? DELIMITER $$ DROP PROCEDURE IF EXISTS `InsGen` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `InsGen` ( in_db varchar(20), in_table varchar(20), in_ColumnName varchar(20), in_ColumnValue varchar(20) ) BEGIN declare Whrs varcha...

MySQL Grouping of data

I want to group my data by Age and by gender: like this sample data: Age: 1 Male: 2 Female: 3 Age 1 Total: 5 Age: 2 Male: 6 Female: 3 Age 2 Total: 9 How can I group the data according to age and count all the male and females in that age from mysql database? ...

2 IP are stored for a visitor : PROXY ?

Hello, on my database i've decided to store IP of the visitors who answoers to polls. It's all working, but there is only 2 cases where not only 1 IP is stored, but there is 2 SAME ip for the same visitor MySQL output (i replaced 2 numbers by XX) 10.188.XX.129, 10.188.XX.129 Here's the script to recieve the IP of the visitor : ...