mysql

Most efficient way to count all rows in a table but select only one

Currently I'm running these two queries: SELECT COUNT(*) FROM `mytable` SELECT * FROM `mytable` WHERE `id`=123 I'm wondering what format will be the most efficient. Does the order the queries are executed make a difference? Is there a single query that will do what I want? ...

Looping Over Result Sets in MySQL (Resolved: Using Cursors)

I am trying to write a stored procedure in MySQL which will perform a somewhat simple select query, and then loop over the results in order to decide whether to perform additional queries, data transformations, or discard the data altogether. Effectively, I want to implement this: $result = mysql_query("SELECT something FROM somewhere ...

phpMyAdmin is having a show plugins error on startup

My WAMP phpMyAdmin instance is now throwing a SHOW PLUGINS error on startup. It has never done this in the past, but now does this and will not start. Any ideas how to resolve this issue? I contacted support but haven't gotten a response back yet. ...

Checking querystring values in PHP

http://localhost/?area=characters&amp;name=Michal+Stroganof $result = mysql_query("SELECT * from players WHERE name = '$_GET[name]'"); while ($row = mysql_fetch_assoc($result)) { echo "Name: " .$row['name']. "<br>"; echo "Level: " .$row['level']. "<br>"; } This is all code of my characters.php If the get variable "name" ...

mysql delete every other rows except the rows number in a given list

so basically here's what i want to do: i have an account table, i have a list of acct_id: (3, 24, 515, 6326, 17), assuming i have about 100,000 accounts in the table, what's the most effective way to delete all the other rows besides the one with the account_id in my given list? i came up with something like: delete from account where ...

Is there any MySQL driver for C/C++ which is not GPLed?

I need to open a connection to a MySQL database from a commerical distributed closed source program. I know that Ruby has rewritten the mysql driver in pure ruby and that PHP 5.3 also comes with its own non GPL driver. But what is with C/C++? Is there any way to do this without risking my code becoming GPL infected? I did my last res...

How do I check if date occurs today?

Here's what I have. SELECT CASE WHEN DATE_FORMAT(a.lastLogin, 'W') = DATE_FORMAT(NOW(), 'W') THEN 'Today' ELSE DATE_FORMAT(a.lastLogin, 'W') END AS lastlogin FROM authors a I am pretty sure there is a much simpler way to do this that I'm missing. ...

retrieve available day from date rante

I think I'm pretty close on this query, but can't seem to crack it, and I'm not sure if I've got the most efficient approach. I am trying to find a day where a user is not booked from a range of dates where they are booked. Think staff scheduling. I need to find who is available to work on Tuesday, and is working on other days this w...

update columns values with column of another table based on condition.

I have two tables... table1 ( id, item, price ) values: id | item | price ------------- 10 | book | 20 20 | copy | 30 30 | pen | 10 ....table2 ( id, item, price) values: id | item | price ------------- 10 | book | 20 20 | book | 30 Now I want to: update table1 set table1.Price = table2.price where table1.id = table2.i...

When creating a CSV file, do you need to escape certain characters?

I am generating a CSV file from Ruby. The problem is a column string will contain double quotes, single quotes. How can I escape these things? "Mary had a little so called \"lamb\"","34","none" "something is not \"right\"","23","none" Each column is enclosed in double quotes followed by comma (and no space), and written into a file. ...

How do I disable/enable MySQL replication monitoring?

How do I disable/enable MySQL replication monitoring? I would like to be able to run a command line to disable monitoring a service. The GUI is to slow to do this task. Every time I have to run a database maintenance its several clicks for a single host. I would love to be able too run a single command to disable replication monitoring f...

how can google find me if I am inside a mysql table?

I am creating a classifieds website. Im storing all ads in mysql database, in different tables. Is it possible to find these ads somehow, from googles search engine? Is it possible to create meta information about each ad so that google finds them? How does major companies do this? I have thought about auto-generating a html-page fo...

MySQL 6 release date?

Anyone know when the MySQL 6 release date is? I've seen a ton of alpha/beta for it but have not seen an actual release date for 6.0 Looks like back in April, MySQL announced the release of ALPHA version 6.0.11. In that release back in April 2009 they stated that this would be the last ALPHA release and the next release in Sept 2009 wou...

MySQL: Has anyone used the TokuDB storage engine?

Has anyone used the TokuDB storage engine for MySQL? The product web site claims to have a 50x performance increase over other MySQL storage engines (e.g. Innodb, MyISAM, etc). Here are the performance claims http://tokutek.com/downloads/tokudb-performance-brief.pdf Is this true? Any personal experiences with this storage engine in us...

Help with manage.py syncdb

Hello there, I am a newbie learning Python/Django... Am using the following tutorial located at: http://bit.ly/eIdT Created a mysite database in MySQL 5 running on Snow Leopard. Edited the settings.py file to look like this: DATABASE_ENGINE = 'mysql' DATABASE_NAME = 'mysite' DATABASE_USER = 'root' ...

Is there any harm choosing a large value for varchar in MySQL?

I'm about to add a new column to my table with 500,000 existing rows. Is there any harm in choosing a large value for the varchar? How exactly are varchars allocated for existing rows? Does it take up a lot of disk space? How about memory effects during run time? I'm looking for MySQL specific behavior details not general software desi...

Is it Proper a way to select count(*) from two different tables

i want count(*) from two different tables and a value from third table like this: table A: select count(*) from TABLE_A where grp_id = 1 table B: select count(*) from TABLE_B where grp_id = 1 table C: select totalcount from TABLE_C where grp_id = 1 and AND UserID = 1 so , i framed this query: select ifnull((select count(*) from TAB...

No matter what I do the same value is being stored in MySQL table field...

I have a MySQL table, with the field ('ad_id'). I store variables in different fields with PHP. One of these fields, the 'ad_id' field as mentioned above, stores the same exact nr over and over again, no matter what the "REAL" name is in the PHP file. Example: $ad_id= 12345; When trying to store this, the number 11111 is stored. ALWAY...

Getting table name in a trigger in MySQL

Hi guys, is it possible to get table name in a MySQL trigger ? i use trigger to log insert actions on some tables in MySQl and i need the name of the table where the trigger is placed at. Thanx before :) ...

How to group by and order correctly

Hi, I have a problem ordering my results correctly when using the group by. It seems to show the first entry in the database instead of the most recent in the group. Example: id(autoincrement) | name 1 | anne 2 | james 3 | anne 4 | brad As you can see I have ...