mysql

MYSQL join a table which its name is stored as a field value on another table

Hello, I am trying to build a dynamic query in MYSQL to join a table which its name is stored as a field value on another table like this : SELECT * FROM CATEGORIES INNER JOIN CATEGORISATIONS ON CATEGORISATIONS.id = CATEGORIES.fk_categorisation INNER JOIN [CATEGORISATIONS.nom_table] LV_REGIONS ON LV_REGIONS.id = CATEGORIES.valeur Any...

How to get database structure in MySQL via query

Is it possible to somehow get structure of MySQL database, or just some table with simple query? Or is there another way, how can I do it? ...

Is there a faster way to get a random record from a MySQL table through ActiveRecord?

In a Rails application, I'm using this to get a random row from a MySQL table: contact = Contact.find(:all, :limit => 1, :order => 'RAND()')[0] Even though the table only has about 20,000 rows it takes a couple seconds. Does anyone know of a faster way? Update Also tried just running the SQL in the console... SELECT * FROM `conta...

Example of a Multi Condition Delete with Zend framework

Can someone give me an example of how I would delete a row in mysql with Zend framework when I have two conditions? i.e: (trying to do this) "DELETE FROM messages WHERE message_id = 1 AND user_id = 2" My code (that is failing miserably looks like this) // is this our message? $condition = array( 'message_id = ' =>...

Best DB (MySQL) structure: Articles which contain favored tags

Hello! I've built a news site: - The articles are shown on the front page ordered by date. The newest one first. - The news are in the table "news" with the fields "id", "title", "text" and some other ones. - All articles are tagged with 1-5 relevant tags. - The tags are in the table "tags" with the fields "id", "tag", "article" and...

PHP multiple MySQL connection though classes/objects?

Here's the code I'm trying to make work: <?php class database { var $connection; function database($host,$username,$password,$database){ $this->connection = mysql_connect($host, $username, $password); mysql_select_db($database,$this->connection); } function query($query){ $query = mysql_query($query,$this...

How do I connect to MySQL 5.1 in Visual Studio 2010?

Does any one know how to connect to MySQL 5.1 with Visual Studio 2010? I have already tried the MySQL Connector/ODBC route and it got me really nasty results. The table rows were all listed as a view in the views section and nothing at all was listed in the tables or procedures folder. ...

MYSQL case sensitive search for utf8_bin field

I created a table and set the collation to utf8 in order to be able to add a unique index to a field. Now I need to do case insensitive searches, but when I performed some queries with the collate keyword and I got: mysql> select * from page where pageTitle="Something" Collate utf8_general_ci; ERROR 1253 (42000): COLLATION 'utf8_gener...

How to calculate the smallest period of time between consecutive events?

Lets say that I have a table with a timestamp column full of records and I want to calculate the smallest time difference between two consecutive records using only one query. Maybe a table like... CREATE TABLE `temperatures` ( `temperature` double, `time` timestamp DEFAULT CURRENT_TIMESTAMP ); ...

PHP value won't exceed 127

Here's part of the code on my form: <br><input type="checkbox" checked="yes" name="country[]" value="1" />Asia/Pacific Region <br><input type="checkbox" checked="yes" name="country[]" value="2" />Europe <br><input type="checkbox" checked="yes" name="country[]" value="3" />Andorra ... <br><input type="checkbox" checked="yes" name="countr...

How do you store result row count in nested SELECT?

Hi, I have a MySQL query where I have a nested SELECT that returns an array to the parent: SELECT ... FROM ... WHERE ... IN (SELECT .... etc) I would like to store the number of returned results (row count) from the nested SELECT, but doing something like IN (SELECT count(...), columnA) does not work, as the IN expects just one resul...

MySQL with 2 LEFT JOINs on the same table

Hi everyone. I'm trying to run this query: SELECT Destaque.destaque, Noticia.id, Noticia.antetitulo, Noticia.titulo, Noticia.lead, Noticia.legenda, Noticia.publicacao, Seccao.descricao, Album.pasta, Foto.ficheiro, Foto.descricao, Cronista.nome, Cronista.profissao, Cronista.ficheiro, AudioFile.*, AudioCollection.*, Video...

SQL SHA1 inside WHERE

Hello, In my program, we store a user's IP address in a record. When we display a list of records to a user, we don't want to give away the other user's IP, so we SHA1 hash it. Then, when the user clicks on a record, it goes to a URL like this: http://www.example.com/allrecordsbyipaddress.php?ipaddress=SHA1HASHOFTHEIPADDRESS Now, I ne...

SubSonic, SQL Server and MySQL problems table names [ ClienteEndereco Clienteendereco ]

I was making tests with SubSonic with SQL Server and everything was fine. I decided to install MySQL and made the same tests, but now I have a little problem My table name is ClienteEndereco in SQL Server but when i make change in my web.config to work with MYSQL and recompile the program the table name comes Clienteendereco SQL Serve...

How do you COUNT outside of the WHERE statement in MYSQL

Hi, I have a table - something like: A|B 1|1 1|2 1|3 2|1 2|3 2|4 2|5 3|5 My query returns the distinct values in A if they coincide with a value in B of 1 or 2 - so A 1 2 I am trying to also return the original count of the 1's and 2's in column A - to get something like A|Count 1|3 2|4 Is there a simple way to get this count p...

Resetting a MySQL Field value without user execution.

I need to reset a MySQL Field value automatically at midnight. It is a specific column in a specific row in a table. I know how to do this in PHP but I do not know how to execute the PHP Script at midnight without someone having to do it themselves. Do you have any viable solutions? Edit:-------------------- Preferably without using Cro...

What is the best way to update (or replace) an entire database table on a live machine?

I'm being given a data source weekly that I'm going to parse and put into a database. The data will not change much from week to week, but I should be updating the database on a regular basis. Besides this weekly update, the data is static. For now rebuilding the entire database isn't a problem, but eventually this database will be li...

MySQL Query, Join, and Myself, or how I always take the hard way through.

I'm creating a small forum. Attempting to run SElECT... JOIN... query too pick up information on the individual posts, plus the last reply (if any). As part of my desire to do everything the hard way, this covers five tables (only columns revelant to this issue are being stated) commentInfo referenceID | referenceType | authorID | ...

MySQL Query Logging in CakePHP

Hello, I wanted to know if there is way to log the mysql queries in CakePHP being executed when we use the find method on the models, I know that rails database queries, so does Cake do the same, if so how can I enable it or use it? Shiv ...

Parse an XML string in MySQL

Hi, all. I have a task of parsing a simple XML-formatted string in a MySQL stored procedure. XML looks like this (testing purposes only): <parent> <child>Example 1</child> <child>Example 2</child> </parent> What I need MySQL to do is to produce a result set one row per match. My stored proc code looks like this: DECLARE xmlD...