I'll try to explain my case as good as i can. I'm making a website where you can find topics by browsing their tags. Nothing strange there. I'm having tricky time with some of the queries though. They might be easy for you, my mind is pretty messed up from doing alot of work :P. I have the tables "topics" and "tags". They are joined usi...
I've written this function before but I can't seem to remember it and it didn't get into version control. Now, more to do with sleep deprivation than anything else, I can't remember how to rebuild it. Here's the idea. I have two tables, "regPrice" and "custPrice", with shared key "itemID." They both have a "price" column and custPrice a...
I have this MySQL query that I'd like to optimize: SELECT min(`topic_id`) FROM `gallery_topics` where `topic_id` > 11 AND `topic_poster` = 5 AND `topic_status` = 0 I wanted to add an index with multiple fields to optimize this query, so I added a key I named previous_by_author with these three keys in this order: topic_id topic_post...
I have 3 tables, message, subject and message_subject_rel. The idea is to have messages that can relate to a lot of subjects and then do a cross subject search. Lets say I have a message: Id: 1, Message: This is a message 2 subjects: Id:1, Subject: Math Id:2, Subject: Science And there's 2 message_subject_rel entries that go: Id:...
CREATE TABLE `clients` ( `client_id` int(11), PRIMARY KEY (`client_id`) ) CREATE TABLE `projects` ( `project_id` int(11) unsigned, `client_id` int(11) unsigned, PRIMARY KEY (`project_id`) ) CREATE TABLE `posts` ( `post_id` int(11) unsigned, `project_id` int(11) unsigned, PRIMARY KEY (`post_id`) ) in my php code,...
I am having a problem with MySQL joins. Table_A: A_id Cost1 A1_id Cost2 1 500 0 200 1 100 1 100 1 50 2 60 1 10 3 50 2 5 0 10 Table_B (Refers B_id: from Table_A A1_id): B_id FName LName 1 X A 2 Y B 3 Z C Table_C (Refers C_id...
Very new to PHP5 and have some problems still. I figured out how to Select with prepared statements now trying to insert/update my code is as follows function input_lab_results($name, $image, $descrip) { $query = "INSERT INTO pat_table (pat_name, pat_image, pat_descrip, pat_doctor, pat_resident, pat_create, pat_modify) VALUES (?, ?, ...
I have the code below (I've included what I believe are all relevant sections): private String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = ? AND VAL_@ = ?;"; public bool read(string id) { level = -1; MySqlCommand m = new MySqlCommand(readCommand); m.Parameters.Add(new MySqlParameter("", val1)); m.Parameters.Add(...
Hello, Recently I changed a bunch of columns to utf8_general_ci (the default UTF-8 collation) but when attempting to change a particular column, I received the MySQL error: Column 'node_content' cannot be part of FULLTEXT index In looking through docs, it appears that MySQL has a problem with FULLTEXT indexes on some multi-byte chars...
Hi, I new to the MySQL syntax. I have created a procedure and ran it, but it is showing some syntax probem, can you help me? My procedure is as: **DELIMITER $$ CREATE PROCEDURE TestAdd( in mODE varchar(10), in Id int, in AttName varchar(10), in AttValues Varchar(10) ) IF EXISTS (SELECT * FROM AttTable WHERE id=Id) THE...
I have to work with an analysis tool that measures the Web Service calls to a server per hour. These measurments are inserted in a database. The following is a snippet of such a measurement: mysql> SELECT * FROM sample s LIMIT 4; +---------+------+-------+ | service | hour | calls | +---------+------+-------+ | WS04 | 04 ...
My table: ID NAME COST PAR P_val S_val 1 X 5 0 1 0 1 y 5 0 2 0 1 z 5 0 0 5 2 XY 4 0 4 4 I need to update the PAR field with the SUM(S_val), grouped by ID: For ID 1 PAR should be SUM(SVAL) WHERE ID=1 For ID 2 PAR should be SUM(SVAL) WHERE ID=2 Expected ouput: ...
mysql: get all tables in database that have a column called xyz ...
Sometime soon we will need to make schema changes to our production database. We need to minimize downtime for this effort, however, the ALTER TABLE statements are going to run for quite a while. Our largest tables have 150 million records, largest table file is 50G. All tables are InnoDB, and it was set up as one big data file (instead ...
I have read-access to a MySQL database and am trying to connect to it via the MySql ODBC 5.1 driver. I'm getting an authorization failure (401) from the server. The administrator set up my access as follows: mysql> grant select, create temporary tables on theDatabase.* to 'adrian' identified by 'password'; I am successful in connecting...
Hello, I am writing a backend application which needs to be able to send multiple SQL commands to a MySQL server. MySQL >= 5.x support multiple statements, but unfortunately we are interfacing with MySQL 4.x. I am trying to find a way (hint: regex) to split SQL statements by their semicolon, but it should ignore semicolons in single...
I want to generate my database schema with Ant. I am using hbm2ddl task. I am using Hibernate with JNDI. My hibernate.cfg.xml looks like: <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-config...
I'm on a mac server. From my home directory, I can get to mysql on the command line. But apps I install (I've tried phpMyAdmin and then Wordpress) can't connect to mysql@localhost. Suggestions on troubleshooting the problem? Also, how can I tell what port mysql is running on? ...
I have a site, e.g. site.com I would like users to be able to access it in their locale at site.com/somecity This is similar to craigslist, but they do it with subdomains e.g. sfbay.craigslist.org Using Apache HTTP server. MySql for DB. If you can provide a brief explanation and perhaps links to more thorough discussions, I would be qui...
I have a database that has two tables "locations" and "classes". I am familiar setting up some sort of parent/child situation with my database tables, but this one has thrown me for a loop. This relationship works exactly like you'd expect. The "locations" have multiple "classes". But some of the "classes" are at multiple "locations"...