mysql

mySQL: Select WHERE causes error - why?

Hi everybody, I have this query: SELECT `manufacturers`.*, `languages`.*, COUNT(`products`.`id`) AS productcount FROM (`manufacturers`) WHERE `manufacturers`.`flushed` = `0` JOIN `languages` ON `manufacturers`.`lang` = `languages`.`id` LEFT OUTER JOIN `products` ON `products`.`manuf` = `manufacturers`.`mid` GROUP BY manufacturers.i...

Inserting empty string into auto_increment column in MySQL 5

Hi, I've inherited a project which we are trying to migrate to MySQL 5 from MySQL 4.0(!) and from myISAM to InnoDB. Queries are now falling down because they are being constructed using an ADODB connection's ->qstr() on all parameters, including ints. Where no value is provided I end up with: INSERT INTO tablename VALUES ('', 'stuff'.....

how to mysqldump remote db from local machine

Hi folks, I need to do a mysqldump of a database on a remote server, but the server does not have mysqldump installed. I would like to use the mysqldump on my machine to connect to the remote database and do the dump on my machine. I have tried to create an ssh tunnel and then do the dump, but this does not seem to work. I tried: ssh ...

PHP / Zend Framework: Force prepend table name to column name in result array?

I am using Zend_Db_Select currently to retrieve hierarchical data from several joined tables. I need to be able to convert this easily into an array. Short of using a switch statement and listing out all the columns individually in order to sort the data, my thought was that if I could get the table names auto-prepended to the keys in th...

create procedure fails!?

Hi, when trying to create a simple procedure in mysql 5.1.47-community it fails everytime i've tried everything! even simple things like this! DELIMITER // CREATE PROCEDURE two () begin SELECT 1+1; end; // The error is ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server v...

Retrieve articles from array of category ids

I have an array of category ids and want to retrieve articles from my mysql database with these category ids. What is the best method for this? ...

What database design for an enterprise cloud service

I'm planning a database for an enterprise cloud service. The service will be two web applications, one Warehouse management system and one for Invoices. Companies can signup and become a "user" of the service, then they can have their Inventory and Invoice system online. Should I keep all users/companies in the same table or should I h...

Retrieve subcategory ids as an array

I have three columns in my category table: ID, name and parent_id. If the category is parent there is 0 under the parent_id, if it's not parent category id is recorded instead. I would like to get all subcategory ids with one parent category id as an array. How is that possible using php? Can anyone please help? Thanks in advance ...

MySQL NDB Cluster + Hibernate

Does anyone know if you can use mySQL Cluster with Hibernate? ...

Exploring search options for PHP

I have innoDB table using numerous foreign keys, but we just want to look up some basic info out of it. I've done some research but still lost. How can I tell if my host has Sphinx installed already? I don't see it as an option for table storage method (i.e. innodb, myisam). Zend_Search_Lucene, responsive enough for AJAX function...

INSERT..ON DUPLICATE KEY UPDATE - but NOT using the duplicate key to compare.

I am trying to solve a problem I have inherited with poor treatment of different data sources. I have a user table that contains BOTH good and evil users. create table `users`( `user_id` int(13) NOT NULL AUTO_INCREMENT , `email` varchar(255) , `name` varchar(255) , PRIMARY KEY (`user_id`) ); In this table the primary key ...

How do I return the numeric value from a database query in PHP?

Hello, I am looking to retreive a numerical value from the database function adminLevel() { $q = "SELECT userlevel FROM ".TBL_USERS." WHERE id = '$_SESSION[id]'"; return mysql_query($q, $this->connection); } This is the SQL. I then wrote the following php/html: <?php $q = $database->adminLevel(); if ($q > 7) {...

Finding shared list IDs in a MySQL table using bitwise operands

I want to find items in common from the "following_list" column in a table of users: +----+--------------------+-------------------------------------+ | id | name | following_list | +----+--------------------+-------------------------------------+ | 9 | User 1 | 26,6,12,10,21,24,19,16 ...

Easiest way to copy a mysql database from windows to linux?

I dont have perl install on windows. I would like to copy the entire testdb to testdb on my linux VM. What is the easiest way to do that? I can run code on my windows machine to connect to both DB and do sql operations. I am using C#.NET so if i need to write code and its simple then i'm open to that. ...

Procedure in converting int to decimal data type?

I have an int(11) column which is used to store money. I read some of the answers on SO and it seems I just need to update it to be a decimal (19,4) data type. Are there any gotchas I should know about before I actually do the converting? My application is in PHP/Zend and I'm not using an ORM so I doubt I would need to update any sort o...

Are there any common gotchas with using decimal as opposed to double in MySQL?

A friend of mine said that they experienced oddities with using the decimal column to store money formats such as saving 1000.00 which would result in it being stored as 999.99. However I just tested and 1000.00 is stored as 1000.0000 on decimal(19,4) / MySQL 5. Can anyone offer insight into why they may have experienced issues? Perhaps...

How do I create a safe local development environment?

I'm currently doing web development with another developer on a centralized development server. In the past this has worked alright, as we have two separate projects we are working on and rarely conflict. Now, however, we are adding a third (possible) developer into the mix. This is clearly going to create problems with other developers ...

insert ... select with divide operator in select errors?

Hi, the following query CREATE TABLE IF NOT EXISTS XY ( x INT NOT NULL , y FLOAT NULL , PRIMARY KEY(x) ) INSERT INTO XY (x,y) (select 1 as x ,(1/7) as y); errors with Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use ne...

How to translate this 2 queries from Mysql to Postgresql? :

How can I translate this 2 queries in postgresql ? : CREATE TABLE `example` ( `id` int(10) unsigned NOT NULL auto_increment, `from` varchar(255) NOT NULL default '0', `message` text NOT NULL, `lastactivity` timestamp NULL default '0000-00-00 00:00:00', `read` int(10) unsigned NOT NULL, PRIMARY KEY (`id...

Trying to obtain the width or height of a piece of text, but how do I calculate spaces?

for example I have a linkbutton txtIt var metrics1:TextLineMetrics = measureText(txtIt.label); trace(metrics1.width); I use the width and the textbox width to calculate the amount of lines in a textbox. With numLines I can calculate where to place the next item on the y axis. However if a piece of text grabbed from the database has sp...