mysql

MySQL query case insensitive

in my table , I have firstname and last name , few names are upper case ,( ABRAHAM ) few names are lower case ,(abraham) few names are character starting with ucword (Abraham) So when i am doing the where condition using REGEXP '^[abc]' ,am not getting proper records, How to change the names to lower case and use SELECT QUERY , ...

Should I add total count in a the main relational table

I'm doing something different but this is an easier to understand example. Think of the votes here. I add these votes to a separate table and log information about them like by who, when and so on. Would you also add a field to the main table that simply counts the number of votes or is this bad practice. ...

Optimizing join query to fetch data from A with condition on B sorted by B

I have an item-to-item similarity matrix set up with these tables: items (id, ...) (Primary key `id`) similarities (item1_id, item2_id, similarity) (Index on `item1_id` and `item2_id`) The similarities tables contains pairs of ids with a similarity index, i.e: item1_id item2_id similarity 1 2 0.3143 2 3 ...

Selecting a column that is also a keyword in MySQL

For some reason, the developers at a new company I'm working for decided to name their columns "ignore" and "exists". Now when I run MySQL queries with those words in the where clause, I get a syntax error; however, I can't seem to figure out how to reference those columns without running into an error. I tried setting them as strings, b...

Using a form to update data in MySQL

Having trouble getting my form to UPDATE records in my database even after searching the web and viewing the other answers on stack-overflow. Here is my current NON functioning code: if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) { session_start(); $tablename = $_SESSION['MM_Username']; $amount=$_POST['amount'];...

sql script error on varchar and exit under mysql

This sql script gives me error at the VARCHAR and EXIT;. I try to run it under MySQL console. May I know how to solve this? CREATE TABLE Solution ( SolutionKey INT NOT NULL, /* PRIMARY KEY (SolutionKey), */ FeatureKey VARCHAR(100), /* +++ Foreign key missing here */ Value VARCHAR(4000) ); CREATE TABLE InactiveContext ( Cas...

MySQL table setup counting a fields daily value changes

I have a table which at the moment just consists of an id, name, and number field. I would like to be able to record how much number field changes daily so that I may graph it later(ex. 10/01 5 , 10/02 20 etc). How do I set up the table in such a way that it will keep up with the daily changes? ...

Form Variable in PDO Query

A Submit button for a form located on my page triggers the code below, but I am unsure of how to save the numeric value of a textbox named 'amount' into a php variable which I can use in the below PDO query. I am unsure of the syntax. Thanks in advance! if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) { $amo...

Mysql benchamrking GUI tool

Hi I need to optimize an application which is already there for a long time. Optimization will include move inline queries from php pages to "stored procedures", get rid of sub queries and convert them to "joins" etc etc. I guess the best way is to use benchmarking tools for this purpose, but is there any GUI based tool available which ...

mysql multi word search with one query

I'm wanting to do an ANDing or ORing in my mysql query, but the LIMIT needs to be the same regardless. eg. SELECTION * FROM table WHERE text LIKE %word1 OR word2 OR word3% LIMIT 10 I'd like this to work the same way it does on twitter, but I'm finding no help from google :( ...

How to load data to db from csv using LOAD DATA INFILE in drupal

I have tried the below code for laoding the inline file in drupal db $sql = "LOAD DATA INFILE '".db_escape_string($this->file_name). "' INTO TABLE `".$this->table_name. "` FIELDS TERMINATED BY '".db_escape_string($this->field_separate_char). "' OPTIONALLY ENCLOSED BY '".db_escape_string($this->field_enclose_c...

How could I optimise this MySQL query?

I have a table that stores a pupil_id, a category and an effective date (amongst other things). The dates can be past, present or future. I need a query that will extract a pupil's current status from the table. The following query works: SELECT * FROM pupil_status WHERE (status_pupil_id, status_date) IN ( SELECT status_pupil_id,...

MySQL query display problem.

How can I display the users friends correctly on the users and friends side using my MySQL tables? My current code screws this up. Users Friends Table CREATE TABLE users_friends ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED UNSIGNED NOT NULL, friend_id INT UNSIGNED NOT NULL, friendship_status TINYINT(1) UNSIGNED NOT N...

Mysql Master Master Replication

Server1: xxx.xxx.xxx.xxx server-id=1 binlog-do-db=sample master-host = xxx.xxx.xxx.xxx master-user = someuser master-password = somepassword master-connect-retry = 60 relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index Server 2: xxx.xxx.xxx.xxx server-id=2 binlog-do-db = sample...

(Fluent)NHibernate and PostgreSQL 9

We currently have a FNH - MySQL 5.1 DAL in our .Net LOB app. Some recent tests we did showed that PostgreSQL does perform faster than MySQL, and we believe the table partitioning feature in PostgreSQL will be very important with a table in our database we foresee to grow into 100 million rows and more (in production). Has anyone upgrad...

Shall I add one or count to get booked number from DB?

I have the following db for a fitness club calendar and booking system. I get omc_courses details which joined to omc_date,omc_week etc. Now I also want to display total booked numbers for each course. I thought two ways to do it and I'd like to know your opinions which way is better or if there are any better ways to do it. My metho...

mysql order by integer and varchar

I got a field with sizes(VARCHAR). The sizes can have int and string values, e.g. (1, 2, 3-4, X, XL, M, ...). When i use the normal order by function of mysql the output is the following: 1,10,11,2, 44-46, L, M, S, XL, XXL The values that does not contain a number are sorted as i want to do it. (I know that the values are sorted as a str...

PHP/ MySQL: Query not processing (syntax error near x on line 1)

Hey... so I attempted to run the query below and it just won't do it. I've checked: The number of fields in the form matches the number of fields in the table. The number of session variables matches those numbers too. That the actual query contains the data to be input with an echo. That the table name is correct. That queries work wi...

Problem searching MySQL table using MATCH AGAINST

Hi there, I have a MySQL table containing event data. On this table, I have a FULLTEXT index, incorporating event_title,event_summary,event_details of types varchar,text,text respectively. Examples of titles include: "Connections Count", "First Aid", "Health & Safety". I can search the table as follows: SELECT * FROM events WHERE MA...

sql query to show the name of table in a database

i use "SHOW TABLES STATUS" but what i got only number of table in database ? ...