mysql

SQL - why would this not work?

The following script allows you to create the database and populate it with necessary data for my question: # HeidiSQL Dump # # -------------------------------------------------------- # Host: 127.0.0.1 # Database: blueskylearning # Server version: 5.1.22-rc-community # Server OS: Win32 # Ta...

MySQL remote access on local network giving authentication error

I can access my MySQL server on my remote Ubuntu 10.04 machine downstairs (local network) just fine through SSH, and when I sudo in to MySQL monitor using the MySQL password, I can modify any of the tables. However, I am on my Windows 7 box upstairs, and when I try to use a conventional SQL GUI application, I consistently get a: 1045 -...

Searching for similar groupings; including diff and score (ie. Similar Recipes)

I'm trying to find the best way to determine how similar a group of items (in this example; ingredients in a guacamole recipe) is to all groups of items (recipes in a table; linked to another table of ingredients). For instance; I have the following guacamole recipe: 3 Avocados 1 Vine-Ripened Tomatoes 1 Red Onion 3 Jalapenos 1 Sea Salt...

Subqueries with Doctrine_RawSql

Is it possible to have subqueries in the select field of Doctrine_RawSql? $q->select('{t.*}, {i.*}, {e.*}, {f.*}, {f2.*}'); $q->addSelect("(SELECT f.id FROM Following f WHERE f.follower_id = ? AND f.following_id = t.owner_id) AS following"); $q->addSelect("(SELECT COUNT(c.id) FROM PostComments c WHERE c.post_id = t.id) AS num_comments")...

MYSQL: Using GROUP BY with string literals

I have the following table with these columns: shortName, fullName, ChangelistCount Is there a way to group them by a string literal within their fullName? The fullname represents file directories, so I would like to display results for certain parent folders instead of the individual files. I tried something along the lines of: GRO...

invitation chains

The site what.cd uses a type of invitation chain whereby if I invite a friend to the service and they break the rules and loose their account I also loose my account as does the person who invited me and so on. What is the best way to keep track of this kind of invitation inheritance, Just a table cell linking the user to the user who ...

DB schema for a booking system of fitness class

I need a schema for fitness class. The booking system needs to store max-number of students it can take, number of students who booked to join the class, students ids, datetime etc. A student table needs to store classes which he/she booked. But this may not need if I store students ids in class tables. I am hoping to get some good i...

PHP: Converting utf8 chars from mysql db

I have a longtext in my db where i have some special chars like à How i can convert it to "à"? I've tried using utf8_encode and _decode but it seems not work. Document charset is utf8, and longtext field too. ...

Which performs better in a MySQL where clause: YEAR() vs BETWEEN?

I need to find all records created in a given year from a MySQL database. Is there any way that one of the following would be slower than the other? WHERE create_date BETWEEN '2009-01-01 00:00:00' AND '2009-12-31 23:59:59' or WHERE YEAR(create_date) = '2009' ...

Correct way of processing user input strings in SQL and PHP ?

I have designed a comment system. This is what I am basically doing. $story=$_POST['story']; $story=mysql_real_escape_string($story); $query = "INSERT INTO `comment` VALUES('$story')"; Now the problem is when i store the comment all the " are replaced by \" and all the ' are replaced by \'. So when I display the comments back these \ ...

mySQL outer join

I have 2 tables for which I need to run a query on Table1 has 2 fields: l_id, and name Table2 also has 2 fields: l_id, and b_id I need to run a query to get the "name" and "l_id" for all the entries in table1 that do not have an entry in table2 for a given b_id. Hope this makes some sense ...

Optimizing use of database queries

Is it faster to do a single database query that finds all the results you need for a page, then do several loops through it with if/else statements to extract what you want to show in each section, or to do several database queries selecting the exact data you require and then just loop through each one separately? For instance: @compl...

MySQL: Add constraint if not exists

In my create script for my database create script looking something like this: CREATE TABLE IF NOT EXISTS `rabbits` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `main_page_id` INT UNSIGNED COMMENT 'What page is the main one', PRIMARY KEY (`id`), KEY `main_page_id` (`main_page_id`) ) ENGI...

SQL error #1064

Trying to enter a value into a mediumblob field in a mySQL table but I'm getting: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 40' at line 3 INSERT INTO 'Picture' VALUES (efvr) WHERE id = 40 What's wrong with this statem...

Why does InnoDB do a full scan for COUNT(*)?

In a table with 5 millions rows, a SELECT count(*) FROM table would be instant in MyISAM but would take several seconds in InnoDB. Why is this that way? Why haven't they optimise count in InnoDB like MyISAM? Thanks. ...

MYSQL - Selecting existing users who have not made an entry in the last 3 months, but have made an entry within the last 6 months.

After several hours of searching I'm finally giving in and asking for help. I've found something that gets me close, but doesn't seem to be working properly. Basically looking for users who uses us between 3-6 months ago, but haven't in the last 3 months. For the sake of the example lets say I have two tables named event(id, clientFK, ...

Converting a php array to SQL array

Hey, I want something like the following: $arrayOfValues = array(1,2,3,4); $sqlArray = mysql_convertToSqlArray($arrayOfValues); which then will return what in SQL would be: (1,2,3,4) but in php would be the string "(1,2,3,4)" ...

With SQL, how do I calculate what percentage of rows have a certain value?

I'm using PHP/MySQL to make a website that displays race results. I want to display a statistic of the 10 hardest races ie the races that most people DNF. I'm doing it like this: select raceid,race.name,race.location,race.date,count(result.raceid) as dnfs from result inner join race on result.raceid=race.id where result.place=0 grou...

How to create a Google Reader ??

I need to create a web tool like Google Reader for my college project. I have 2 question about it: 1) How Google Reader track the read and unread posts ? 2) Google Reader save every post in the db or load the feeds at the moment ? ...

mysql unknown column...

Hi there I am using this query SELECT COUNT(DISTINCT to_number) AS errors FROM sent_txts WHERE msg_link_id = 0 AND msg_sent_datetime BETWEEN '2010-08-09 07:00:00' AND '2010-09-07 11:59:59' HAVING to_number IN(SELECT mobile FROM action_6_members WHERE mobile = to_number) However I am getting Unknown column 'tada_prod.sent_txts.t...