mysql-query

MySQL -- Getting distinct rows in a view.

OK, here goes. I am trying to make a view that shows this: +---------+---------+-------+--------+------------+---------------------+---------------+-------------------+-------------+ | post_id | status | title | body | ip_address | time_stamp | category_name | sub_category_name | post_type | +---------+---------+-------+--...

how to get latest entry of a item when item have multiple rows?

i have an table tbl_exp id| exp_id|qnty| last_update 1 | 12 | 10|2010-05-18 19:34:29 2 | 13 | 50|2010-05-19 19:34:29 3 | 12 | 50|2010-05-19 19:34:29 4 | 15 | 50|2010-05-18 19:34:29 5 | 18 | 50|2010-05-20 19:34:29 6 | 13 | 70|2010-05-20 19:34:29 now i need only latest entry of each exp_id id| exp...

MySQL help: Find Rank of a particular user on the number of its occurance in another table.

I have two tables 'users' and 'invites' Fields of users - id, name, email, ... Fields of invites- email, invitedby, status My Problems: Find the rank list of users on 'invites' table where the top user will be the one who has the most number of (status='confirmed') invites. Find the rank of a particular user say id=15 where it stand...

Trying to reduce mysql query, why is 'ref' NULL?

Why does lean_users show NULL in the ref column? This causes my query to use a temporary table and a filesort later (when I've added more joins)... 14:45:21 (60) > EXPLAIN select * from users u inner join lean_users lu on u.id = lu.user_id; +----+-------------+-------+--------+---------------+---------+---------+----------------+------...

MySQL Simple query gives "Query was empty". Transaction help needed I think.

Hi, I'm trying to do a simple transaction in MySQL delimiter go start transaction; BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND ROLLBACK; INSERT INTO jext_categories (Name) VALUES ('asdfas'); INSERT INTO jext_categories (Name) VALUES ('asdfas2'); END; commit; SELECT * FROM jext_c...

can we use join inside join in mysql?

i have 3 tables structure is below tbl_login login_id | login_name 1 | keshav tbl_role role_id | login_id( refer to tbl_login.login_id) 1 | 1 tbl_stuff stuff_id | role_id( refer to tbl_role.role_id) 1 | 1 i need data in follow format stuff_id | login_name 1 | keshav how to use JOIN to ret...

How to use Insert .. select, with conditional vars from insert

I have two separate tables both with user id columns uid. I want to take a value from all users in one table and insert it into the correct row for the correct user in the other table. INSERT INTO users2 (picture) SELECT pv.value FROM profile_values as pv, users2 as u WHERE pv.uid = u.uid AND pv.fid = 31 AND users2.u...

Updating fields in one table that are not empty

I got this SQL: UPDATE users2 SET picture = 'sites/site2/files/pictures/' + picture; WHERE picture NOT IS NULL And the only thing I get are that all picture fields get the value '0'. ...

PHP MySQL Insert Data

Hello, Im trying to insert data into a table in MySQL. I found/modified some code from w3Schools and still couldn't get it working. Heres what I have so far: <?php $rusername=$_POST['username']; $rname=$_POST['name']; $remail=$_POST['emailadr']; $rpassword=$_POST['pass']; $rconfirmpassword=$_POST['cpass']; if ($rpassword==$rconfirmpa...

SQL Query to select upcoming events with a start and end date

I need to display upcoming events from a database. The problem is when I use the query I'm currently using any events with a start day that has passed will show up lower on the list of upcoming events regardless of the fact that they are current My table (yaml): columns: title: type: string(255) notnull: true de...

MySQL Full-Text Search Across Multiple Tables - Quick/Long Solution?

Hello all, I have been doing a bit of research on full-text searches as we realized a series of LIKE statements are terrible. My first find was MySQL full-text searches. I tried to implement this and it worked on one table, failed when I was trying to join multiple tables, and so I consulted stackoverflow's articles (look at the end for...

Table alias -- Unkown column in field list

Hi all, I have a sql query which is executing a LEFT JOIN on 2 tables in which some of the columns are ambiguous. I can prefix the joined tables but when I try to prefix one of the columns from the table in the FROM clause, it tells me Unknown column. I even tried giving that table an alias like so ...From points AS p and using "p" to...

Complicated MySQL query?

I have two tables as follows: I have a RatingsTable that contains a ratingname and a bit whether it is a positive or negative rating: RatingsTable ---------------------- ratingname ispositive ---------------------- Good 1 Bad 0 Fun 1 Boring 0 And I have a FeedbackTable that contains feedback on things: t...

MySQL "OR MATCH" hangs (very slow) on multiple tables

After learning how to do MySQL Full-Text search, the recommended solution for multiple tables was OR MATCH and then do the other database call. You can see that in my query below. When I do this, it just gets stuck in a "busy" state, and I can't access the MySQL database. SELECT a.`product_id`, a.`name`, a.`slug`, a.`description`, b....

Cake bake undefined function mysql_query on easyphp

Hi, when i try to use the shell to build models cake bake M i get this error: Fatal error: Call to undefined function mysql_query() in C:\Program Files\EasyPH P-5.3.2\www\cake\cake\libs\model\datasources\dbo\dbo_mysql.php on line 588 on phpinfo(); mysql extension is enabled, i'm using mysql driver running if(!function_...

mysql query help, take total sum from a table, and based on discount value on another table calculate the gross_total

hi I have a table called invoices: `si_invoices` ( `id` int(10) , `biller_id` int(10) , `customer_id` int(10) , `type_id` int(10) , `inv_tax_id` int(10) , `date` date , `unreg_customer` tinyint(1) , `discount` decimal(10,2) , `discount_type` tinyint(1) ) each invoice has items that are stored in invoice_items tabl...

Query for find Nth maximum value in Mysql

How do I write a query to find the Nth maximum value in MySQL? ...

Searching 2 fields at the same time

I have a table of first and last names firstname lastname --------- --------- Joe Robertson Sally Robert Jim Green Sandra Jordan I'm trying to search this table based on an input that consists of the full name. For example: input: Joe Robert I thought about using SELECT * FROM tablename WHERE ...

which is better, creating a materialized view or a new table?

I have some demanding mysql queries that have to select same frequently updated datasets from 5-7 mysql tables. 'Select' operation would be a bit more than CUD. I am thinking of creating a table or materialized view to gather all demanding columns from other tables, so as to reduce overall query times to different tables and thus incre...

Any disadvantages to this tagging approach

I have a table of tags ID tag --- ------ 1 tagt 2 tagb 3 tagz 4 tagn In my items table, I'm using those tags in serialized format, comma delimited ID field1 tags ---- ------ ----- 1 value1 tagt,tagb 2 value2 tagb 3 value3 tagb,tagn 4 ...