mysql

MySQL, Union ALL and LIMIT

Hey, Let's say I want to perform this query: (SELECT a FROM t1 WHERE a=10 AND B=1) UNION ALL (SELECT a FROM t2 WHERE a=11 AND B=2) UNION ALL (SELECT a FROM t3 WHERE a=12 AND B=3) ORDER BY a LIMIT 1000; Is MySQL smart enough to skip "t3" if 550 results are available in "t1" and 450 in "t2"? I'm looking at MySQL docs (http://...

A PHP MySQL Error which I just cannot work out - help please

Hi guys - extreme novice here playing with PHP/MySQL trying to teach myself online but can't figure this out for the life of me... I have been following a PHP tutorial at http://www.keithjbrown.co.uk/vworks/php/php_p5.php This page is at tasmanianracing.com/horses.php I'm getting the following mysql error: You have an error in you...

MySQLdump define the path

I'm trying to backup a MySQL database: $ mysqldump -u ruby -p idm_production > /home/username/idm_backup_2010_10_23.sql Enter password: mysqldump: mysqldump: Couldn't execute 'show fields from arguments': Can't create/write to file '/tmp/#sql_721_0.MYI' (Errcode: 13) (1) However I don't seem to have permission to write the the /tm...

How do I print 5 items to this users profile

http://u.neighborrow.com/users/view/7343 I want to take a query that pulls most recent 5 items with a photo and print them horizontally above the tabs and below the "online-brary" header ...

How to calculate percentage of "equal or better" in mysql DB?

Dear All, Figuring out a title for this question was hard, but the following is harder for me. I hope anyone can help. I have a simple MySQL database table. This table is filled with records containing an ID and the number of week-visitors. It has records of 2 year of about 200+ websites. To summarize, I want to be able to know two th...

SELECT 2010-10-24 09:02:46 post as 'Oct 24, 2010 at 9:02AM'

How can I convert my date field from mysql from 2010-10-24 09:02:46 to post on my site as 'Oct 24, 2010 at 9:02AM' (SELECT TIME_FORMAT(`dPostTime`, '%b %e %l:%i %p')) as post_time This won't post the date, just the time. Thanks so much! ...

Best way to debug MySQL connections that are being closed on me after ~39 minutes?

I have hibernate 3.3, c3p0, MySql 5.1, and Spring. The MySQL connections in my service calls are consistently being closed after ~39 minutes. The natural running time of my service call is on the order of ~5 hours. I've tried changing various c3p0 config, etc, to avoid the 39 minute cap. No luck. Is there a more direct, systematic wa...

Printing a single field below

Suppose I do a select like this: select company, location, acnum, bills from table Just how do i output data using php so that the company and location remain at the top and every bill is printed and the acnum is displayed at the bottom. I'm using mysql_fetch_assoc and have gone mad trying. I can go tru every record but cant get one ...

MySQL query to match everything in array

I have a complex query I've been wrestling with for 2 days and can't seem to get it to run. I have two querys - The first query works fine but I can't figure out the second What am I doing doing wrong? CREATE TABLE `recipes` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `cat` int(5) NOT...

Database strategy for over 10,000 instances of an ERP

Hi , I have a moderately large rails ERP application having around 80 tables . I use MySQL . I recently received a client requirement which needs me to deploy the same application for roughly 10,000 offices(an instance for each office). The central office(superadmin office) must be able to view details from all these offices . After tal...

PHP Constant open mysql connection safety?

I have a website where at the top of each page I include a page that opens a mysql_connect connection, so I don't have to open up the connection each time I need to modify or insert stuff into the database. Is this safe, or should I open and close each connection once I'm done using it? require_once("../include/mysqlconn.php"); $sql = ...

Installing MYSQL and passenger gems, getting no definitions for documentation

I'm running Mac OSX 10.6.4 and setting up rails for the first time. I've been following a bunch of guides to get set up, specifically these two here and here. Also, I know this question has been asked before, but nothing has helped me and my circumstances are a bit different. First, let me give you my setup. I've installed Xcode, ...

Any short code for this in php?

I have a picture uplaod tool which reloads the page (which contains a form) whenever a picture is chosen. I have this code to "remember" the drop list options selected, so basically I am creating options using php: $posted_type=$row['9_type']; //From mysql db $types = array('Clothes', 'Bags', 'Others'); $category_table .= "<select nam...

Parsing XML with php and save it to mysql.

Hi Guys, I have been asked to parse a simple file which is stored as an XML file, the data is to be then save it to a mysql. However I have absolutely no clue what to do and after looking online all the examples given seem either too complicated for my problem or not the right solution. The XML file looks like this games_scores.xml <g...

Mysql - very weird behavior: query works locally, but not on a remote server for some people

I have a very weird thing that we cannot understand or fix. The short version: Some very simple SELECT-queries in MySQL hangs the whole application/program who made the query. However, its not consistent. If I have the EXACT same MySQL version locally, with the exact same table structure and the EXACT same data, the query works fine. bu...

Getting last post user id (MySQL)

I have a little problem - i would can get with this query topics posts count and last post id, but i can't figure out, how to get right user id. I get first post (lowest id) user id but i want lastest post... I have tried adding "ORDER BY id DESC" but this will not help. Any ideas how to do it? SELECT COUNT(`id`) AS `count`, M...

New MySQL install on OS X 10.6 (snow leopard) PHP socket issues

I just installed mysql-5.1.51-osx10.6-x86_64 and I'm having issues. Also installed MySQL.prefPane and MySQLStartupItem.pkg. Lastly I added MySQL to $PATH $echo $PATH $/usr/local/mysql/bin now when if I: $ sudo /Library/StartupItems/MySQLCOM/MySQLCOM start $ Password: $ mysql $ERROR 2002 (HY000): Can't connect to local MySQL serv...

MongoDB as a cache for frequent joins and queries from MySQL

Im thinking about doing the following and need suggestions if it makes sense to approach it this way. Basically since I am able to do queries in MongoDB and MongoDb is wicked fast at these since the hotspots of the data are cached in memory. I was thinking of storing data I would normally do a join from in mysql in mongoDB. While I am us...

HTML5 Video not auto-playing

Hello, I'm requesting this chunk of html from a mysql database: <p>Hello.<br><video src="video/hi.mp4" width=100% height=100% autoplay="autoplay"></video> It displays, but won't autoplay. On a static HTML page it goes off without a hitch. The request comes from an ajax call for a matching keyword that is being typed into a input bar. ...

mysql problem with union/join used in conjunction

I have these two tables: Table Author: ID (INTEGER), author_name (VARCHAR), first_name (VARCHAR), last_name (VARCHAR), preferred_name (VARCHAR) Table CoAuthored: ID (INTEGER) author1ID (INTEGER), author2ID (INTEGER), paper_ID (INTEGER) // (ID of the co-authored paper referenced by this link) As you can see, both tables have a colu...