mysql-query

Mysql multi-table / multi-entity related content query

I've got the following tables: magazine_tags news_tags page_tags content_tags faq_tags They all have exactly the same structure: uid_local - id of the entity uid_foreign - id of the tag Each one of them maps to a Model in PHP. I need to get related content based on multiple tags. Say I'm viewing an "faq" tagged with 1 and 4, I ne...

MySQL Select query - remove unwanted rows

I have a table with the following (I've sorted it by date): ID prodId date 16 532 2015-08-17 19 535 2014-08-18 18 534 2011-08-17 27 48 2010-08-26 26 1541 2010-08-25 25 1541 2010-08-21 24 1540 2010-08-20 21 48 2010-08-19 20 48 2010-08-18 17 53...

MySQL query results used in LIKE

I am trying to write a query to pull all the rows that contain a username from a large list of usernames in a field. For example, the table contains a column called 'Worklog' which contains comments made by users and their username. I need to search that field for all user names that are contained in a list I have. I have tried a few d...

Appending one sql query with another.

Hi, I have a datareader that binds a sql select (with 10 columns from table1) , i want to append another with 5 different cols from table2 to this first sql select, i can't do UNION as it has different number of columns, (one query has 10, another returns 5 cols). Is there any other way of implementing this, via mysql? Also i need to ...

Duplicate removal in UNION

Hi, I have this condition in mysql query SELECT col1,col2,col3,col4 FROM table1 -> this I select produces 5 rows (R1,R2,R3,R4,R5) where (C1,c2,c3 are null) UNION SELECT col1,col2,null,null FROM table2; -> this II select produces 2 rows (R4,R5) where (C1,c2,c3 are not null) Is there any way to remove the duplicate and show the entry ...

MySql: Ordering results by number of matches in a space-delimited value column

I have a MySQL database table that has a "word" column with space-delimited values. For example: one two three four five So given the record above, I want to find other records that contain one or more of those space-delimited values, and order the results by most number of relevant matches. How can I go about achieving this? For e...

Edit text in columns

Just wondering if someone can assist me with the following issue. I have a database in MySQL and I'm using PHPMyAdmin to interact with the database. So within the table example_table_name I have two columns product_item_short and product_item_long which have URL's in them. Now there is 3000 rows of data and as an example the URL in eac...

MySQL Case in Select Statement with LIKE operator

Is it possible to combine the CASE statement and the LIKE operator in a MySQL SELECT statement? For Example, I am trying to query a database that stores data in a single column in either one of two formats (this is awful and hurts my head, but I cant change the data so it is what it is.). So sometimes the column numbers would have data ...

Single MySQL query which checks another table for rows

I have a Users table and a Payments table. I need a query which lists the users who DO NOT have a record in the payments table where the field PaymentCompleted=1. These are the columns in the tables (simplified): Users: UserID, UserName Payments: PaymentID, UserID, PaymentCompleted The query should select the field UserName. ...

php mysql forum app...how to make sticky threads?

Hi I am working on a mysql/php forum app as part of a corporate intranet system. My problem is that while listing all threads under a particular forum, there could be more than one "sticky threads" which should show on top and the rest should after them sorted by date in descending order. Just need to get an idea about the sql query to u...

MySQL query to use data from 2 tables

I have a Users table and a Payments table. I need a query which lists the users who have a record in the payments table where PaymentStatus=1. It must also only list users with a certain GroupID. The GroupID will be passed by a variable, but we can just call it 5 for simplicity. The timestamp of the record (TimeCompleted) must also be ...

SQL query for nth most recent update

I have a revision table and I need to be able the nth most recent update. I have a query that gives me the most recent revision record of enitity, but I need one for the nth most recent revision record. revisions --+---------+--------+----------+------- id|entity_id|contents|revisor_fk|revised The query must take input 0 to n. If th...

mysql switch to alternative table depending on conditions

Hi all, this post is a long one sorry for that but the problem is complex too. Using mysql and php, I can solve my problem with 3 sql requests, but not in 1, nor in 2. So that's why I post here : does any of you know a way to do it in less than 3 strokes ? (sorry for my english) First let me explain the context : it's about t...

How we can assign a value in mysql query

Hi How we can assign a value in the querywhat i need is assume that i write a query for output result using match against i am getting a bunch of result like http://www.flipkart.com/account.php http://www.thomas.com/account.phphttp://www.flipkart.com/account22.phphttp://www.flipkart.com/account45.phphttp://www.thomas.com/account22.phpht...

MySQL JOIN & Split String

I want to JOIN two table. Have no problem with that. I have problem with quite different thing. Here's my code: SELECT * FROM `table1` JOIN `table2` ON `table1.`field`=`table2`.`field` ... The main issue is that table1.field is a string, comma-separated. Is there any good and fast way to split it? Update I found a function by Federi...

Updating AUTO_INCREMENT value of all tables in a MySQL database

Hello, It is possbile set/reset the AUTO_INCREMENT value of a MySQL table via ALTER TABLE some_table AUTO_INCREMENT = 1000 However I need to set the AUTO_INCREMENTupon its existing value (to fix M-M replication), something like: ALTER TABLE some_table SET AUTO_INCREMENT = AUTO_INCREMENT + 1 which is not working Well actually, I woul...

mysql string searching algorithm query help

Hi, i have a string table in my mysql/php table. id | str 3 | 2,4,5,6 4 | 7,8,9,14 5 | 3,1,16 if i search with LIKE keyword for example "1" the result is below and its wrong id | str 4 | 7,8,9,14 5 | 3,1,16 But the expected result is id | str 5 | 3,1,16 since "1" is only in the above row but unfort...

adding fulltext query to existing query

I am struggling to work out the syntax for joining a normal multi table query with single table fulltext. I can't get my head around the syntax This is the existing syntax which works fine: SELECT * FROM products,categories,sub_categories WHERE products.approved = 1 AND products.main_category = categories.id AND products.sub_cate...

SQL Nearest Date

I need to get a date such as '2010-04-27' as a string in php and find the nearest 5 dates in a table. The date in the table are saved as a date type. ...

using php variable in mysql LIKE

Hi! I want to write a mysql query something like this: select * from books where title like '$title_'; The $title is a php variable. when i run the above query, it throws an error saying '$title_ variable not found' How can I achieve this? Thanks.. ...