The query I'm running is as follows, however I'm getting this error: #1054 - Unknown column 'guaranteed_postcode' in 'IN/ALL/ANY subquery' SELECT `users`.`first_name`, `users`.`last_name`, `users`.`email`, SUBSTRING(`locations`.`raw`,-6,4) AS `guaranteed_postcode` FROM `users` LEFT OUTER JOIN `locations` ON `users`.`id` = `location...
Hi, The error mysql could not fetch schema table status occurs when I try backup or list database tables using MySql Administrator. I did some research on MySql Bugs (id=28140) and also MySql Forums (http://forums.mysql.com/read.php?34,169966,170024#msg-170024). Their solution talks about McFee. Actually I rent a Windows 2003 VPS host...
This is admittedly similar to (but not a duplicate of) http://stackoverflow.com/questions/737275/pros-cons-of-full-text-search-engine-lucene-sphinx-postgresql-full-text-search, however what I am looking for are specific, supported, recommendations from the benefit of experience with more than one of the available systems (there seems to ...
I have some NAS storage to do backups to with my dedicated hosting provider. I setup automatic daily backups with WHM to backup the databases and accounts. The server is hosting one site. When it does the backup, it does a mysql dump, and essentially takes the site down for the whole time it is doing the backup because nobody can connect...
I need to alter my existing JOIN query below to also include the data from users.image correlating to the UserID of the post maker. Something like: users.image WHERE users.UserID = posts.userid I am not very good with join queries yet. How would I do this? Existing Query: $result = mysql_query("SELECT posts.* FROM listen JOIN pos...
I am using PHP and MySQL. In my program there is a select query involving joins. When I run it on localhost it's working fine but when I upload it on my server and try to execute it then it generates the following error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_...
Hello, I'm working with mySQL, and I'm in a situation where I need to select data from one table that matches an ID at any level in parent -> child data hierarchy in the other table. Further more, I would like to resolve this with a well written SQL query, rather than a recursive function in my PHP code, as this feature will be used qu...
I'm using MySQL 5.0.67 on RHEL5 and basing my configuration on my-huge.cnf. I can't find anything in the MySQL manual for the row 'skip-locking' which appears in the config file. Should this be replaced with 'skip_external_locking' or should I just remove the row entirely as that is now a default. MySQL Manual for skip-external-lockin...
Hello, I have a problem defining the best way to replicate and synchronize some information that I have in different mysql servers. Let me explain: Actual Scenario: Server A -Table A1 (read/write) Operations -Table A2 (read/write) Operations Server B -Table B1 (write) Operations Desired Scenario: Server A -Table A1 (read/write) Op...
What's wrong with this query: SELECT co.*, mod.COUNT(*) as moduleCount, vid.COUNT(*) as vidCount FROM courses as co, modules as mod, videos as vid WHERE mod.course_id=co.id AND vid.course_id=co.id ORDER BY co.id DESC In other words, how can I do it so with every record returned from 'courses', there's an additional column called ...
I have a table which holds information on television programs and I want to order it by Seasons and then by episodes. Heres a basic view of what I have +---+--------+---------+ |id | Season | Episode | +---+--------+---------+ | 1 | 1 | 1 | +---+--------+---------+ | 1 | 1 | 2 | +---+--------+---------+ | 1 | 2 ...
Couldn't find any good information on how to do this so I thought I'd add it here. How do I grab the selected data from a multiple choice drop down html form using php and submit into a database. I need a seperate row for each choice. I'd be happy just knowing how to grab the data and put it into an array. ...
Hi all, Let's say I had the following table: id num_votes total_rating ------------------------------- 1 20 30 2 40 13 3 15 25 I want to join the SUM of all ids, let's say, onto the entire table so it looks like: id num_votes total_rating sum ----------------------------------...
I have a table with 4 things I want... the Name, Price, QTY, and a specific Date There are lots of Entries per date: Name Price Date Twin Private $25 06/02/09 Double $35 06/02/09 Single $20 06/02/09 Twin Private $25 06/03/09 Double $35 06/03/09 Single $20 06/03/09 Twin Private ...
Say one were looking at transitioning a codebase from using the basic PHP mysql_* functions to a mysqli implementation. Can connections to the same database be used side-by-side in both interfaces, or is there some way in which they can interfere with one another? ...
I'm trying to run a report on our database. We want to know the new registrations per industry per month. I've written this query: SELECT COUNT(j.jobseeker_id) as new_registrations, i.description as industry_name, MONTHNAME(j.created_at) FROM tb_jobseeker as j, tb_industry as i WHERE YEAR(j.created_at) = 2009 AND i.indu...
How can I insert huge BLOBs into a MySQL database (InnoDB)? Fields of type LONGBLOB support data sizes of up to 4 GB according to the MySQL manual. But how does data of such a huge size get into the database? I tried to use INSERT INTO table (bindata) VALUES ( LOAD_FILE('c:/tmp/hugefile') ); which fails if the size of hugefile is bi...
TL;DR: I've supplied a patch for a bug I found and I've got 0 feedback on it. I'm wondering if it's a bug at all. This is not a rant. Please read this and if you may be affected by it check the fix. I have found and reported this MySQLdb bug some weeks ago (edit: 6 weeks ago), sent a patch, posted it on a couple of ORM's forums, mailed ...
EXPLAIN SELECT * FROM content_link link STRAIGHT_JOIN content ON link.content_id = content.id WHERE link.content_id = 1 LIMIT 10; +----+-------------+---------+-------+---------------+------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+...
mysql> explain SELECT p.id ID, p.job_desc_title Title, p.url URL, substr(p.posting_date, 1, 10) Date, -> p.job_city_name City, -> p.job_state_name State, -> b.screen_name Name, b.type Type,f.name Company,IF...