mysql

mysql binary comparison doesnt use index

I have the following query: EXPLAIN EXTENDED SELECT * FROM ( `photo_data` ) LEFT JOIN `deleted_photos` ON `deleted_photos`.`photo_id` = `photo_data`.`photo_id` WHERE photo_data.photo_id = 'UKNn' AND `deleted_photos`.`photo_id` IS NULL I unfortunately have to use binary to compare this photo_id (which is being handed to me from a diffe...

mysql count total last week from timestamp

id date 1 1288006344 2 1288010391 3 1288010752 4 1288011379 5 1288013258 6 1288014043 7 1288014555 8 1288015611 9 1288019119 10 1288020490 11 1288023483 12 1288029300 13 1288031668 14 1288032090 How to count total last 7 days? Thank you ! ...

How do i prevent tampering using an admin flag?

Hello, In my users table I have a true/false boolean flag which signifies whether the logged on user is Admin or not. Basically, even Admin and other users has access to database because it's a small college website. How do i ensure that general users don't modify the flag? Is storing admin flag a correct way of doing? If i keep passwor...

MySQL storing view data over time, how to reference

Tables: videoID (vidID, vidName, vidURL) etc. viewCount (vidID, views, timestamp) The main goal of this database is to calculate the slope between the most recent view and the previous one. Lets say I automatically store the view data in the database once everyday. This means all the view data for all videos is in one big table call...

Users table - one table or two ?

i wanna have a Users details stored in the database.. with columns like firstname, last name, username, password, email, cellphone number, activation codes, gender, birthday, occupation, and a few other more. is it good to store all of these on the same table or should i split it between two users and profile ? ...

How can I avoid a sub-query?

This is my table: ID KEY VALUE 1 alpha 100 2 alpha 500 3 alpha 22 4 beta 60 5 beta 10 I'm trying to retrieve a list of all KEY-s with their latest values (where ID is in its maximum): ID KEY VALUE 3 alpha 22 5 beta 10 In MySQL I'm using this query, which is not effective: SELECT temp.* F...

How can I retrieve address book contacts using the Zend Framework?

Hi, I want to add contact fetch (gmail,AOL,hotmail,yahoo) script in my social networking site using zend-framework. Any one can tell me how to do this ? ...

Exporting entire table structure, but data ONLY from certain tables via phpMyAdmin

I have access to phpMyAdmin, but NOT the MySQL command line. I need to recreate the table structure elsewhere along with certain tables that need to be 'pre-populated'. I can, of course, export the empty table structure and then export the tables one-by-one, but is there a better way to export a bunch of table data together? ...

php script to load contents of a file into mysql database

hello, i have written a php script to insert the contents of a text file into mysql database and the content of my file a.txt is \n is used for a new line i want it to be loaded into the database including the special character \n as it is... But it is loading only " is used for a new line" kindly help me out... than...

mysql: delete data not working

i want to delete some data use: $dbc=mysql_connect(_SRV,_ACCID,_PWD) or die(_ERROR15.": ".mysql_error()); $db=mysql_select_db("qdbase",$dbc) or die(_ERROR17.": ".mysql_error()); switch(postVar('action')) { case 'changedata': changedata(postVar('id'),postVar('chlotno'),postVar('chrange'),postVar('chS'),postVa...

Mysql: Use REGEX to extract string

I would like to have a mysql query like this: select <second word in text> word, count(*) from table group by word; All the regex examples in mysql are used to query if the text matches the expression, but not to extract text out of an expression. Is there such a syntax? ...

Taking a MySQL snapshot and recreating the database?

If a snapshot of the database is taken, can it be copied over to another drive and re-created? ...

MYSQL Slow Query

How can I view mysql slow_query_log to see which query is taking too much time? ...

create a windows 2000 batch file that executes a mysql stored procedure

I want to create a batch file that I can run from the desktop. This batch file should execute a stored procedure in mysql. This is what i have so far, the batch file logs into the server but it doesnt execute the stored procedure <---- start: run_stored_proc.bat -----> cd\ cd Program Files cd mysql cd mysql server 5.1 cd bin mysql.e...

Recursively check the parents of a child in a database.

I'm working on a CMS system that receives urls like this: /parent1/parent2/child/ Now it's easy to check only the child but in my opinion you should also check if the parents are correct and in the right order. The problem is that I'm unsure on how to do this. I'm using mysql. this is how that table would look: CREATE TABLE IF NO...

Sort a Query Based on Omitting A Word

if I have the following result sets: id, name 1, The Popper 2, Brittany 3, Fledgler 4, The Nightingale How do I sort them by name so that it disregards the "The" word and sorts the as follows: id, name 1, Britanny 2, Fledler 3, The Nightingale 4, The Popper Thanks in advance. I am using MySQL ...

Unreasonable difference between MySQL dump and load times

I'm dumping a database into a file (piping the dump through gzip) and it takes about 2 minutes to create a 300MB dump.sql.gz file. When loading the file back into MySQL (the dump begins with dropping the tables), the load takes around 30 minutes!! What can be the reason for such a huge difference in times? Are there any general tips fo...

MySQL foreign key to another foreign key

Hi. The idea is quite simple: i have three (or more) tables - master_tbl (id, something, somethingelse) - tbl2 (id, ....) - tbl3 (id, ....) Now what i want is a foreign key relationship, such as tbl3.id would point to tbl2.id and tbl2.id would point to master_tbl.id - all foreign keys are ON UPDATE CASCADE and ON DELETE CASCADE. What ...

Mysql - Can you check for both a blank string and 0 in one condition?

I want to check in mysql if a column is either blank, ie '', or 0. Is there a way to do this with one condition? Like WHERE order_id > '' or WHERE order_id != '' Would either of these work, or is there a different solution? ...

MyIsam vs NDB On Inserts

Hi, I'm considering ways to increase performance of our persistence layer. We want to achieve as many inserts per second as we can and we don't need any transactions at the moment. What I want to know is what MySQL engine faster: standard MyIsam or single-node NDB cluster which will be accessed via ClusterJPA/ClusterJ? P.S. This guy ha...