mysql

Doctrine 2 configuration with MySQL

I'm busy with Doctrine2, but I can't get it to work. Everything works fine out of the box with sqlite. But when I try to change the database in the sandbox demo to MySQL I'm getting confused. This is what I did. I created a database, changed the connectionOptions array to: $connectionOptions = array( 'driver' => 'pdo_mysql', '...

Get mysql tables in a query

There is any way to get the tables I'm using in a query? The first method I was using was with regular expressions: // result[1] = "SELECT" // result[3] = "All between FROM and WHERE, ex: `users`, ex: `users`, `test` if($result[1] == "SELECT" && !preg_match('/FROM\s*(.*?,.*?)\s*WHERE/i', $query, $res)) { $tables = preg_replace('/`...

no idea why my model returns two different values

This is my view all function in my postcards model, in one controller it returns 4 and the other returns, no idea why, I'm echoing num_rows() to see whats going on. Theres nothing else being passed, its exactly the same except, one view is a front for a facebook and one view is an admin section function view_all(){ $query = $this-...

Most efficient way of checking if a user as activated his/her account?

I want my users to activate their accounts before they are able to login. They are sent an email after registration containing an activation link, something like this: http://www.blabla.com/[email protected]&token=Aisd23uNMAu53932asdDasd82AS Of course, whenever someone logs in, I have to check wether or not that ...

Innodb table analysis tool?

I am a newbie to mysql and trying to understand innodb specifically. I need to understand how exactly innodb is laying out the tables in disk, which index blocks it is putting in cache, size consumption and estimation of the indexes and table records. Is there any tool that can analyze a given set of innodb tables and give me the above ...

SQL where clause with multiple keys in same table

I have a table which has two keys and I cannot break the table up into two. The first key, id is a counter. The second key is parent_id. For example: id, parent_id, text 1, 0, Hello Steve - this is Bill 2, 1, Hi - how are you Bill? 3, 0, Good morning Janice 4, 3, Hello - good morning to you The first record is the parent record of t...

How can I filter this data from my database?

I have a table with a name and type field. The type field contain the musical taste of the person in this way: "Rock; Pop; Metal; Dance" (varchar) I need to generate an excel list with this persons grouped by music taste. For example: One list with everybody in Rock; Metal; Dance Another with everybody in Rock; Metal; Trance Another...

How to Rank in MySQL Using a WHERE clause and UNION ALL

I need some help with a MySQL query. I'm trying to rank participants using a WHERE clause. So, I'd like to rank novices, intermediates, and experienced separately. For example: Rank Name Grade Type ---------------------------------- 1 Bob 98 Novice 2 Jill 88 Novice 3 Jimmy 42 Novice 1 Mark 87 ...

Delete duplicate rows in MySQL table but foreign key relationship exists?

How can I delete duplicate rows from a MySQL table when a foreign key relationship has already been setup up on those rows. Can the duplicates be merged somehow and then foreign key updated with the new value? ...

Using LAST_INSERT_ID() within a multi row insert

Is it possible to use LAST_INSERT_ID() within an insert query? INSERT INTO mytable (col1, col2) VALUES ('val1', null), ('val2', LAST_INSERT_ID()) Trying the above LAST_INSERT_ID() returns 0, Just seeing if i can combine the query. ...

Deployed (ie serverside) mysql db is UTF-8 but local version isn't

At least i think that's the problem. In my staging and production databases (both on the same server) i have a table with a text field that holds html. Some of these have web quotes, which are displayed fine. However, locally i have my development database which is a copy of the staging database (it was copied by taking a dump of the ...

MySQL function to get list of Mondays

What is the most efficient way to get a list of all of the unique Mondays from a date field? When I'm not all that concerned about efficiency, I have done something like: DATE-weekday(DATE) + 1. But now I need to compute this on a large dataset and I don't want my user wishing for a Rubik's cube because it is taking so long. :) Yes, ...

frustrating query - news feed like facebook

im trying to have news feed like facebook. Database Dump http://www.2shared.com/document/RXQ13S-n/exported.html i have feed table, which gets feed for text, image or video. i want to group the latest video and images feed and show them as one row and if someone feed comes after it, then group gets break for that row. the following que...

What is the best way to develop a C# project with a MySQL backend?

So I'm using C# 2010 Express and wondering what the best toolset is for developing a C# project with a MySQL backend? I know what if you use SQL Server Compact edition from within C# it will let you access the DB directly from the IDE. Is there a similar way to integrate the development with a remote MySQL Database? Also, is MySQL a v...

MATCH() AGAINST() & Fulltext index: Possible to have "keywords" field?

Hi, I'm using MATCH() AGAINST() with a fulltext index and I was wondering how I could setup a "keywords" data field. So let's say I have a a fulltext index on the "title" column, which for example might have a row with the value of "AC/DC".. I want to have a field to enter additional keywords like "ACDC" "AC DC" and "AC-DC" Ideally I'd...

Functions save incorrect values if called in real-time; called manually they save correct values. What's going on?!

I have a (php/mysql) system that rewards users for doing things on the site. For the sake of the question, let's say a user gets a rose for every 5 comments they make. The front page has a counter that shows how many roses have been earned today, and how many roses have been earned all-time. The rough logic is below: //Save a single, d...

MySQl query problems

I don't spend a whole lot of time in MySQL, but I have been asked to look into a problem with my church's website. It has been down for quite some time and I am trying to get it back up and running. The original site was done in Mambo 4.5.3, which is an old version. I will upgrade it at some point, but I just want to get it running fo...

MySQL with C#, from a PHP Developer's Standpoint

I understand that with PHP I can use mysql_query($sql); and mysql_fetch_array($result); to fetch some MySQL data and place it into an array. How is this achieved in C# to where I could place my data in say, a datagrid? ...

Limit SQL query result in MySQL

I would like to limit the amount of rows I fetch in MySQL. Can you show me how? ex: 1st query I would like to retrieve only the first 10,000 records 2nd query I would like to retrieve only records from 10,000 - 20,000 etc ...

SQL Sort Order by The Order Specified In the Query

Say I have a query "select * from clauses where id in (0,2,5,1,3)" and I actually want the rows returned in the same order they are specified the where clause. The order of the IDs will change from query to query and there is no pattern to the order. I know it's possible to alter the data model, create temp tables, etc. But trust me ...