mysql

How to send email from MySQL 5.1

I'm trying to send an email to several recipients when a new row is inserted into a table. The list of recipients varies. I would like to be able to set this list using a select statement. I also have installed Navicat which allows me to send email notifications but only to a predetermined set of people. Thanks. ...

Mysql Connection, one or many?

I'm writing a script in python which basically queries WMI and updates the information in a mysql database. One of those "write something you need" to learn to program exercises. In case something breaks in the middle of the script, for example, the remote computer turns off, it's separated out into functions. Query Some WMI data Upda...

mysql 5.0 indexes - Unique vs Non Unique

What is the difference between mysql unique and non-unique index in terms of performance? Let us say I want to make an index on a combo of 2 columns, and the combination is unique, but I create a non-unique index. Will that have any significant effect on the performance or the memory mysql uses? Same question, is there is difference be...

SQL GROUP BY/COUNT even if no results

I am attempting to get the information from one table (games) and count the entries in another table (tickets) that correspond to each entry in the first. I want each entry in the first table to be returned even if there aren't any entries in the second. My query is as follows: SELECT g.*, count(*) FROM games g, tickets t WHERE (t.g...

asp mysql variables testing problem

Hi everyone. I have a problem that just started happening after I reinstalled my website's server. In the past I could do do this: Code: <% set msgSet = conn.execute("select * from base_scroller where scroller_num = 1" %> check if it's not empty or anything else Code: <% if msgSet("scroller_name") <> "" then %> and if it is i...

Add a time-field formated like MM:SS (MySQL)

I want to add a new time-field to a an existing MySQL-table that is formated like this "MM:SS". The field is supposed to hold duration-data. What is the correct MySQL syntax to do this? Couldn't find anything that covers this on the MySQL-site. ...

mysqli prepared statements and REPLACE INTO

I have to following code: http://www.nomorepasting.com/getpaste.php?pasteid=22987 If PHPSESSID is not already in the table the REPLACE INTO query works just fine, however if PHPSESSID exists the call to execute succeeds but sqlstate is set to 'HY000' which isn't very helpful and $_mysqli_session_write->errno and $_mysqli_session_write-...

MySQL autogenerate statistics fram data

Hi! I have a Table containig Dates, Distances, and TimeForDistance... Is there any way to Calculate the average per month velocity using MySQL Statements? Any Help would really be appreciated! ...

MySQL failover using MySQL Proxy

Hi, I'm looking at implementing a failover mechanism for a MySQL database using MySQL Proxy (http://forge.mysql.com/wiki/MySQL_Proxy) My proposed setup is as follows: One active MySQL server and one passive MySQL with two way replication enabled between the both of them. MySQL Proxy is the failover that sits in between these two server...

Table comment length in mysql

According to MySQL manual, table comments are limited to 60 characters. I'm designing the schema in MySQL Workbench, which does not enforce this limit, so I end up with writing more than 60 symbols quite often, and this causes the SQL script to fail. To tell the truth I would be quite happy with table comments being internal to my schema...

Why does MySQL not use an index on a int field that's being used as a boolean?

select * from myTable where myInt will not show any possible_keys when explaining the query even though there is an index on myInt field. Edit: The index in question is not unique. ...

Why 'delete from table' takes a long time when 'truncate table' takes 0 time?

(I've tried this in MySql) I believe they're semantically equivalent. Why not identify this trivial case and speed it up? ...

PHP ignoring mysql_connect requests

I'm new to PHP and have installed on Linux to boot (also a newbie). Anyway, PHP is working... <? $myVar = "test"; echo($myVar); ?> ... works just fine. But... <? $dbhost = "localhost"; $dbuser = "myuser"; $dbpass = "mypass"; $dbname = "mydb"; echo($dbhost . "-" . $dbuser . "-" . $dbpass . "-" . $dbname); $conn = mysql_connect($db...

Hosting: why does the number of MySQL databases matter?

Ok, maybe I'm missing something here but I'm looking at various PHP hosting options and I see things like "10 MySQL databases", or 25 or even unlimited. Now I've worked on sites with an Oracle backend that have 10,000+ concurrent users and we've had... one database. The idea of a database is, of course, that you can store whatever you ...

SELECT FOR UPDATE for locked queries

I'm using MySql 5.x and in my environment, I have a table with the name CALLS. Table CALLS has a column status which takes an enum {inprogress, completed}. I want reads/updates of the table to be row-locked, so: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET AUTOCOMMIT = 0; SELECT amount from CALLS where callId=1213 FOR UPDATE; COM...

How to do it in mysql: If id=idold THEN UPDATE status=1

Hi I would like to compare two tables and then update if some logic is true, In pseudo code: SELECT * FROM users, usersold IF users.id=usersold.id THEN UPDATE users.status=1; Is there a way to do it in mysql? ...

How to set a list of integers while preparing SQL queries in Java

I have a query like this - select * from tbl where ... and colname in (2,3,4) When I prepare the query (... ' colname in (?)' ) using a PreparedStatement in Java, what setter method should I call to set these integers ? The integers are available in an int array and the size varies. If it matters, the database is MySQL and the column i...

2 Column Mysql Date Range Search in PHP

Hello.. MySQL column > sdate, edate ( its 2 column). sdate is start date for project starting and edate is end date for project ending. so i need to make search between them.. <strong>Search</strong><br /> <form method="post" action="search.php"> Start Report Date : <input type="text" name="sdate" /> End Report Date : <input type...

SQL query or sub-query?

I've got a table of student information in MySQL that looks like this (simplified): | age : int | city : text | name : text | ----------------------------------------------------- | | | | I wish to select all student names and ages within a given city, and also, per student, ho...

Converting an Oracle merge query into mysql mySQL query

MERGE INTO PAGEEDITCONTROL A USING (SELECT '1585' AS PAGEID ,'admin' AS EDITUSER ,sysdate AS EDITDATE FROM DUAL) B ON (A.PAGEID = B.PAGEID) WHEN MATCHED THEN UPDATE SET A.EDITUSER = B.EDITUSER ,A.EDITDATE = B.EDITDATE WHEN NOT MATCHED THEN INSERT ( ...