mysql

SQL ORDER by `no` with NULLs at the end

Hi! I have got a MySql query that orders me results by no column (int, can be null). Simple example: SELECT * FROM table ORDER BY no ASC I would like to get a resultset sorted like 1, 2, 3, 10, 52, 66, NULL, NULL, NULL but I get NULL, NULL, NULL, 1, 2, 3, 10, 52, 66 Is it possible with SQL query ? ...

Is there a better way to assign permissions to temporary tables in MySQL?

Our users log into the production database as a fairly low-level user, with SELECT granted at the database level, and INSERT/UPDATE/DELETE granted on the specific tables they need access to. They also have permissions to create temporary tables (we need them for some of the more complicated queries). The problem is that whilst they can ...

Mysql Selection

Hi good people once again While busy traversing, iterating and manipulating my arrays from a mysql database i have stumbled on a problem, which i know you will be able to help me with - fellow geeks. I have a COLUMN the contains city names and some cities appear several times on the COLUMN. I'm using the following query to retrieve th...

Change MySQL innodb_buffer_pool_size at runtime?

This may be a dumb question, but is it possible to change the MySQL configuration options such as innodb_buffer_pool_size at runtime? Or, equivalently, is there a way to reload MySQL without closing existing connections or refusing new connections (like you can do with Apache)? Linux_32 (2.6.26) + MySQL 5.0.24a ...

mysql query which selects from another mysql query

i am migrating from ms access database to mysql database with java frontend so that my application can be used in linux as well . in ms access here's what i would do go to create query . write a select statement . call give the name of the query as query1. when double clicking on query1 you would get the result of select statement in ...

Query To Get Consecutive Events If They Are Close Enough

This is my table structure: monResults: id Int(10) monDateTime Datetime() typeId int(10) I need a query to get all monResults that have consecutive monDateTimes that are less than 1 minute apart and have the same typeId. I want to be able to get X number of these events. Mostly I don't know how to add the condition to make consec...

Evaluate a string in a MySQL stored procedure

I have a stored procedure that I would like to pass a string to, to be evaluated in the query. I will be building a string in php, based on query parameters that exist. So, my string might be col1 = val1 or col1 = val1 AND col2 = val2 AND col3 = val3, ect. Then in my stored procedure, I would do something like this: SET @s = CONCAT('SELE...

MySQL structure help for joins ( large tables)

Hello I currently have 2 tables that are used for a select query with a simple join. The first table houses around 6-9 million rows, and this gets used as the join. The primary table is anywhere from 1mil to 300mil rows. However, I notice when I join above 10mil rows on the primary table the select query goes from instant to very slow (...

How can I see raw bytes stored in a MySQL column?

I have a MySQL table properly set to the UTF-8 character set. I suspect some data inserted into one of my columns has been double encoded. I am expecting to see a non-breaking space character (UTF-8 0xC2A0), but what I get when selecting this column out of this table is four octets (0xC3A2 0xC2A0). That's what I would expect to see if...

Aggregate function returning first row data for column not in the group by

I have a table relationship that looks like the following: barn ------ PK barn_id <other columns> stable --------- PK stable_id FK barn_id stable_number stable_contents timestamp So whenever the contents of a stable change I just put in a new row with the corresponding barn_id and stable_number with new stable_contents and a current ...

Can mysql-database save strings casesensitive, like UNIX?

Is it possible to store "Filename" and "filename" in a mysql-database? ...

display mysql newline in HTML

Certain fields in our mysql db appear to contain newline characters so that if I SELECT on them something like the following will be returned for a single SQL call: Life to be sure is nothing much to lose But young men think it is and we were young If I want to preserve the line breaks when displaying this field on a webpage, is the ...

PDO and PDOStatement, magical assignments

Hi there, I have the following "test-class" made on the fly: http://sumoin.pastebin.com/ff744ea4 No fine-tuning or something else, just a brief testing pdo class. And I have the test.php: http://sumoin.pastebin.com/d70dcb4ec The funny thing is: The PDOStatement object is never returned directly and I it never gets assigned to $this->...

Best practice: Import mySQL file in PHP; split queries

I have a situation where I have to update a web site on a shared hosting provider. The site has a CMS. Uploading the CMS's files is pretty straightforward using FTP. I also have to import a big database file (Around 2-3 MB uncompressed). Mysql is closed for access from the outside, so I have to upload a file using FTP, and start a PHP s...

Ajax - php - mysql - checkbox

I am trying to create a dynamic system with check boxes for an web based game that I am trying to make using Ajax, PHP and MYsql Is there a possible way to detect whenever a checkbox is selected/unselected, pull up information from the database and display information based on user's selection dynamically on the website without refresh...

Duplicate a row depending on some column value ?

Say i have a table 'users', with 'user_id', 'name' and 'multiplier'. If I do a basic select, I get one row for each user: SELECT * FROM users What I would like to do now, is to get mutiplier rows for each user. So if I have say two users in my table which those values (1, 'user1', 1) (2, 'user2', 6) What I would like to get is a s...

Good PHP & MySQL book that shows how to build a rating system?

Looking for a good PHP & MySQL book that will show me how to build a rating system the correct way. ...

Optimal key for MySQL / InnoDB table with multi-part WHERE

Given a (simplified) table CREATE TABLE `transactions` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `CREATION_DT` datetime DEFAULT NULL, `STATUS_IND` int(11) DEFAULT NULL, `COMPANY_ID` bigint(20) DEFAULT NULL, `AMOUNT` bigint(20) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FKE7E81F1ED170D4C9` (`COMPANY_ID`), KEY `RPTIDX_CREATI...

PHP Cannot escape my string properly

I'm using mysql_real_escape_string to escape my content but I am receiving an error in a SQL INSERTION QUERY for having a single-quote unescaped. How can I resolve this? $content = mysql_real_escape_string("'content'",$conn); The error message I am receiving is: You have an error in your sql syntax near 'content My SQL Query ENDS U...

Select the X "closest" ids ?

Hello, Let's say you have a table with a integer primary key named 'table_id' Is it possible in a single query to extract the row with a specific id AND the X row before it AND the X row after it ? For exemple, if your ids are (1,2,8,12,16,120,250,354), X is 2 and the id you have is 16, the select should return rows with ids 8,12,16,1...