mysql-query

Can this query be done without a loop?

So, basically, I have a MySQL table called 'topics' and another one called 'replies', for example. In table 'topics', there's a field called 'relforum' which relates this topic to a forum section. And in the table 'replies', there's a field called 'reltopic', which relates the reply to a topic. Both tables have an id field, auto_incremen...

mysql query to find sum of different names

I use similar queries (10) as following queries (modified) to find sum SELECT sum(amount) AS amount FROM `students` WHERE sex='M' && name in ('salil', 'anil', 'gaikwad') ...and: SELECT sum(amount) AS amount FROM `students` WHERE sex='M' && name in ('salil1', 'anil1', 'gaikwad1') i want to make a single query of ...

Retrieve 2 last posts for each category.

Hello, Lets say I have 2 tables: blog_posts and categories. Each blog post belongs to only ONE category, so there is basically a foreign key between the 2 tables here. I would like to retrieve the 2 lasts posts from each category, is it possible to achieve this in a single request? GROUP BY would group everything and leave me with only...

MySQL: Joining a table on a field that could be NULL

I have two tables (Releases and Formats) and a field that is linked to both of them - bar_code. The problem I am having is with this select statement: SELECT Release.name, Release.default_upc, Artist.name, Artist.url_name, Format.* FROM releases AS Release, artists AS Artist, formats AS Format WHERE R...

Truncate all tables in a MySQL database in one command?

Is there a query (command) to truncate all the tables in a database in one operation? I want to know if I can do this with one single query. ...

MySQL select multiple values from column

Here is the table ID WHO FRUIT 1 Adam Apple 2 Adam Lemon 3 Eve Apple 4 Adam Grape 5 God Papaya 6 Eve Melon How do I get all persons who have apple and lemon: in this case, so that I get the result Adam? Furthermore, I want all persons who have apple and lemon or melon, so I would get Adam ...

How to write this challenging SQL (MySQL) command?

This is the scenario: I am developing a website which is similar to stackoverflow.com. After an asker/seeker posts his question, other users can post their answers to the question. A user can post more than one answer to the same question, but usually only the latest answer will be displayed. User can give comments on an answer, if comme...

MYSQL Select query to return difference in row data

Hi, I have an example table as below that I'm trying to do analyse upon, to learn using PHP and MYSQL for producing statistical results; TableID | RNGResult | sessionseq | sessionid | Date | Scriptname 1 | 1 | 1 | 1 2 | 7 | 2 | 1 3 | 2 | 3 | 1 4 | 12 | 4...

Problem with advanced distinct SQL query

Ok this one is realy tricky :D i have a this table bills_products: - bill_id - product_id - action - | 1 | 4 | add | | 1 | 5 | add | | 2 | 4 | remove | | 2 | 1 | add | | 3 | 4 | add | as you can see product with the id 4 was added at bill 1 then re...

How to fetch only certain rows from an MySql database table

I have a php array of "primary key" values, which correspond to just a few rows of a very long table of data, saved on a MySql database. How can I fetch with just one query only those rows. Is it possible or will I need to make one query per primary key? eg: SELECT * FROM table1 WHERE key = 8573 Thanks, Patrick ...

start and stop element in mysql

Is there a way to setup an start or stop element in mysql ? I want to to select every element starting with id = 20 and stops when level < 3 the first time. ...

How to insert space after each character in a string MySQL

Hi, I have a character string and for reporting/alignment purpose I need to insert a space after each character. Unfortunately I will have to do it in a SQL or I can write format function. e.g. "abcd123" to be converted it to "a b c d 1 2 3 ". Since it's for a considerable number of rows I was wondering how optimized it will be to pa...

How Do I Select the Entire Record When Using MAX() With GROUP BY

Using MYSQL I would like to refactor the following SELECT statement to return the entire record containing the newest invoice_date: > SELECT id, invoice, invoice_date FROM invoice_items WHERE lot = 1047 id invoice_id invoice_date ----------------------------------- 3235 1047 2009-12-15 11:40:00 3295 1047 2009-...

MySQL/PHP: How to list results in groups but limit the DB queries required

I have a database as follows: --------------------------------------------------------------- | module_name | category | content | --------------------------------------------------------------- | module1 | category1 | content | | module2 | category1 | c...

Shorter REGEXP for MySQL query

I want to do a MySQL query to get the following effect: table_column [varchar] ----------------------- 1|5|7 25 55|12 5 3&5 5|11 I want a reliable way to get all the values where 5 is the complete value. So, for example, if I do a REGEXP query for the number 5 on the upper table I would like to get all ro...

Zend: How to use 'not equal to' in WHERE clause?

I am using following zend code to select all data from a table where verified=1 and it is working for me. $table = $this->getDbTable(); $select = $table->select(); $select->where('verified = 1'); $rows = $table->fetchAll($select); No I want to select all data from that table where verified is not equal to '1'. I have tried the followi...

How to write this MySQL update statement?

How to write this MySQL update statement: table1 identity table2 memberid, username, email Some values in identity of table1 are email, some are username, how to replace the values in identity of table1 with the corresponding value of memberid of table2? ...

Why does mysql_num_rows($result) return 1 even if $result returns empty result set?

Why does mysql_num_rows($result) return 1 even if $result returns empty result set? $resut=mysql_query("select * from tablename where column1='$memberid' and (TIME_TO_SEC(TIMEDIFF(NOW(),when_submit))/60<2)")or die(mysql_error()); $count=mysql_num_rows($result); when I echo $count, I get 1 . ...

mysql query with random and desc

this is a mysql table num wt a 24 e 22 c 11 d 24 b 13 f 12 how can i create a mysql query which will display with descending order of weights and give random sorting to num with same weight . thus the select query can have two valid results a 24 d 24 e 22 b 13 ...

One php recordset instead of 7 ? Extra filtering during presentation.

I have a table about a program guide with 4 columns: idguide, idday, starthour, program. I would like to present it on the screen per day. Must i call 7 times its recordset, like this SELECT * FROM tblguide WHERE idday = 1 ORDER BY start ASC This works but causes bigger workload; is there another solution where i only have one recordse...