mysql

MySQL - How to use subquery into IN statement by value

The question is to get table column data and use it as a value list for IN function; For this example I created 2 tables: movies and genres Table "movies" contains 3 columns: id, name and genre. Table "genres" contains 2 columns: id and name. +- movies-+ | |- movie_id - int(11) - AUTO_INCREMENT - PRIMARY | |- movie_nam...

Wordpress post sync / publish to production

I'm using wordpress as a simple backend CMS for a flash site. Posts are queried and displayed in list format on the frontend. I want to be able to make post edits or add new posts on my staging server, and have a quick and easy way to publish changes to production without having to do a full mysql db dump / import. I've looked around ...

while loop to update dates in DB only updates first row?

I have no idea why this won't work. function query($sql) { $this->result = @mysql_query($sql, $this->conn); return($this->result != false); } function convert() { $this->db->open(); $sql_update = ""; $this->db->query("SELECT * FROM ACCOUNTS "); $str = ''; while ($row = $this->db->fetchassoc()...

Generating Strong Unique User ID's w/PHP & MySQL

Ahoy Stack Overflow! This be mai first post... I'm attempting to identify users with a salted unique public key. Algorithm - Should I use uniqid(), sha256, sha512, something else? All hashes will be salted. NIST recommended SHA256, but I like recommendations from geeks/scientists like me. Generation - Does hash(SALT + AUTO_INCREME...

Help with a specific MySQL query: how to get the smallest value from a group of several values.

I have a table 'foo' with a timestamp field 'bar'. How do I get only the oldest timestamp for a query like: SELECT foo.bar from foo? I tried doing something like: SELECT MIN(foo.bar) from foo but it failed with this error ERROR 1140 (42000) at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if...

MySQL data modeling: Multiple user types / Activity / Following

I have a project in which there is a site with multiple user types. And I am having trouble wrapping my head around the best practice for this. I want to be able to get activity from (nodes) you follow regardless of node type. Pretend the node types are: User: Organization: An organization will be an entity that can act as a user. ...

mySQL query - show most popular item

I need to find the most popular occurrence of an item grouped by date and display the total of all items along with the name of this item. Is something like this possible in a single query? note: If they are all of equal occurrence (see last 3 rows in Insert) than I can just show at random or the first or last occurrence (whichever is e...

MYSQL - Rows to Columns

Hi, I tried to search posts, but I only found solutions for SQL Server/Access. I need a solution to MYSQL (5.X). I have a table with 3 columns: hostid, itemname, itemvalue. If I do a select, it will return 1 A 10 1 B 3 2 A 9 2 C 40 How do I query the database to return something like A B C 1 10 3 2 9 40...

mysql stored procedure editor

hi all, i want to know what is the best GUI or editor for working with stored procedures in mysql. i used to use toad but it is full of bugs specially in a network environment. ...

PHP, MySQL | Windows vs Linux

Well the title does the explaination. Which one is faster PHP/MySQL on Linux or on Windows. Question 1 I know that MySQL is slower on Windows, because i tried to run a C++ program on Windows to access MySQL, it took a year every time it had to open a connection. When i ported the exact copy into the linux enviornment it was lightning f...

What is the correct/safest way to escape input in a forum?

I am creating a forum software using php and mysql backend, and want to know what is the most secure way to escape user input for forum posts. I know about htmlentities() and strip_tags() and htmlspecialchars() and mysql_real_escape_string(), and even javascript's escape() but I don't know which to use and where. What would be the safe...

mySQL MATCH across multiple tables

Hi, I have a set of 4 tables that I want to search across, each has a full text index, and here is my query; SELECT categories.name AS category, categories.id AS category_id, host_types.name AS host_type, host_types.id AS host_type_id, hosts.name AS host, hosts.id AS host_id, products.name as name, products.id AS ...

Having trouble allowing people to BUMP a unique entry in table...

So, my table has a bunch of codes in it and I don't want more than one of the same code in the table so I have it as "UNIQUE." But, at the same time, I want them to be able to bump their code once every hour. function some_more_custom_content() { $output="<BR>"; ob_start(); if ($_REQUEST['code'] != "") { $code = $_REQUEST['code']...

MySQL query help.

SELECT username, (SUM(rating)/count(*)) as TheAverage, count(*) as TheCount FROM ratings WHERE month ='Aug' AND TheCount > 1 GROUP BY username ORDER BY TheAverage DESC, TheCount DESC I know that's really close (I think) but it's saying 'TheCount' doesn't exsist in the WHERE clause and the ORDER clause. The table is: id, username,...

Illegal mix of collations error in MySql

Just got this answer from a previous question and it works a treat! SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount FROM ratings WHERE month='Aug' GROUP BY username HAVING TheCount > 4 ORDER BY TheAverage DESC, TheCount DESC But when I stick this extra bit in it gives this error: Documentation #1267 - ...

Performance question sql

I'm making a forum. And I'm wondering if i should store the number of replies in the topic table or count the posts of the topic? How much slower will it be if i use sql and count them? Lets say i have a billion posts. Will it be much slower? Im not planning on being that big but what if? How much slower would i be compared to stroing t...

How do i know how much space i need for mysql database

Let's say I'm importing multiple csv files into mysql. How can I find out how much space I need for this database (and as far as i noticed it doesn't seem to be the same size like csv files size)? ...

MySQL - Find rows matching all rows from joined table

Hi, Table 1: Tracks Table 2: Wordlist Table 3: N:M Track has Words (trackwords) Find all tracks which have all the words. currently the query looks like: SELECT DISTINCT t.id FROM track as t Left Join trackwords as tw ON t.id=tw.trackid Left Join wordlist as wl on wl.id=tw.wordid WHERE wl.trackusecount>0 group by t.id HAVING SUM(...

MySql Says Column can't be null for a column that is not null! [using named parameters]

I am trying to execute an INSERT INTO query using .Net through the MySql/.NEt connector. The query makes uses of Parameters. It is rather simple: INSERT INTO post ( ID, content, post_url, blogID, title, addedOn, updatedDate, commentsFeedURL, active, viewCount, commentCount, languageID, authorName, postDate, posRating, negRating, adul...

Where did I go wrong with this unicode field in MySQL?

I have a table with a field which contains strings in my MySQL database. The MySQL version is 5.0.51a. The default character set for the table is 'utf8'. Many of the strings have unicode characters such as \xae and \u21222 (registered symbol and trademark symbol respectively). For example, suppose I have a row with a field this v...