mysql-query

mysql concat with trim

I've got a table which holds a bunch of addresses in cells labelled address | city. I am attempting to merge the complete address into the common 'address, city' format. Occasionally, in my database, I will have one of the location cells empty. Therefore, I do a IFNULL in my concat line, but I end up with a leading or trailing ','. I ...

mysql query excluding a set of data but includindg certain items from the set if item contains value in a specific column

I need to exclude a product line from a database so I do pline != prodctline but certain products in that product line I do not want to be excluded. For instance: here is simlplistic version of my database: partname, pline, option (3 columns) i want to exclude all results from a certain product line unless they have X option value. s...

MySQL - Please help with optimization, I am not sure how

Hi I would really appreciate it if some of you could help optimize my tables, as they currently take extremely long to execute because of the following issues: Main table: game { game_id [PRIMARY KEY] *team_id *stadium_id home_score opponent_score date tour half_home_score half_opp_score attendance ref...

MySQL table -> Can you return the same row multiple times, in the same query??

SELECT * FROM menu WHERE item_id = 1 OR item_id = 2 OR item_id = 3; The above statement returns 3 rows. But the statement below only returns 2 rows. SELECT * FROM menu WHERE item_id = 1 OR item_id = 1 OR item_id = 2; And I understand why like, but is there a way to force item_id 1 to be returned twice??? Example of what I want to b...

Insert 'even numbered' rows into an existing table in mysql

I have a table: +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | fooID | int(11) | NO | PRI | NULL | auto_increment | | fooDetails | varcha...

Can I use MySQL to join two tables based on relationships with a third table?

Let's say you have three tables named Item, Event, and Seat, constructed as such: Item Id (int) Event_id (int) Section (int) Event Id (int) Venue_id (int) Configuration (int) Seat Id (int) Venue_id (int) Configuration (int) Section (int) Face_value (int) I'm trying to construct a MySQL query that pulls all entries from the Item table...

GROUP BY ID range?

Given a data set like this; +-----+---------------------+--------+ | id | date | result | +-----+---------------------+--------+ | 121 | 2009-07-11 13:23:24 | -1 | | 122 | 2009-07-11 13:23:24 | -1 | | 123 | 2009-07-11 13:23:24 | -1 | | 124 | 2009-07-11 13:23:24 | -1 | | 125 | 2009-07-11 13:23:24 | ...

MySQL query error.

What's wrong with this MySQL query? $name = mysqli_real_escape_string($db,$_POST['name']); $email = mysqli_real_escape_string($db,$_POST['email']); $comment = mysqli_real_escape_string($db,$_POST['content']); $dt = date("F j, Y, g:i a"); $sql = mysqli_query($db, "INSERT INTO `tbl_contact` (`id`, `name`, `email`, `comment`, `date`) VALU...

MySQL full text search across multiple tables

I have a series of tables that contain data I want to full text search. I've tried combining the tables with UNION, but the result loses its fulltext index so can't be fulltext searched. I don't think that putting the data into a temp table is the way to go. Is there someway that I can fulltext search these tables efficiently? Thanks in...

mysql left join, how to write such query?

I have first: select url from urls where site = '$value' order by url second: select id, widget_name from widgets where display_urls LIKE 'urls.url' and is_father=1 order by id Third: select id, widget_name from widgets where display_urls RLIKE 'urls.url' and is_father=0 order by id Fourth: select id, widget_name, father_widg...

MySQL LIKE IN() ?

My current query looks like this: SELECT * FROM fiberbox f WHERE f.fiberBox LIKE '%1740 %' OR f.fiberBox LIKE '%1938 %' OR f.fiberBox LIKE '%1940 %' I did some looking around and can't find anything similar to a LIKE IN() - I envision it working like this: SELECT * FROM fiberbox f WHERE f.fiberbox LIKE IN('%140 %', '%1938 %', '%1940 ...

Mysql Query - Query to show Itens that are from Actual date to the Max of 15 days ago

Hello This is a bit "confused" question for those who don't now advanced SQL... However. I want your help to do a query. Its simple but I'm not sure how to do it. I want the query to do something like this. I want her to SELECT * FROM table1 WHERE date.... The date condition its only to show items that have a date to the MAX of 15...

Single Equals in MYSQL

Hi, I was wondering why MYSQL uses a single equals sign in conditional statements instead of the more typical two equals signs. Is there a technical/historical reason for this? Thanks. ...

MySQL Query: Displaying counts for many groups with many records

I need to produce a large HTML table with quarterly counts for several (around 50) different groups (jurisdictions). I have a MySQL table with around 16,000 rows which have 'jurisdiction_id' and 'quarter' fields. Unfortunately my client doesn't want the resulting report to be paginated. How would I construct a good MySQL query from which...

MySQL select "accumulated" column

I'm not sure what to call this besides an "accumulated" column. I have a MySQL table with a column that looks like +---+ |val| +---+ | 1 | | 4 | | 6 | | 3 | | 2 | | 5 | +---+ I want to do a query so that I get this column along with another column which is the sum of all the rows from this column so far. In other words, the select wo...

mysql_fetch_assoc() returning unexpected value

I am working on a project where I use mysql_fetch_assoc to return an associative array. However, I am puzzled as to why it would return TRUE I have looked at the PHP manual page, and the only boolean value it should be returning is FALSE, and then only on a fail. Although I am using a custom set of abstraction classes, it is basically...

Optimizing my mysql query to use index for sorting

I have a composite index based on 3 columns, two of which are constrained in my query and the 3rd is in order by clause yet mysql doesn't use index for sorting. explain select * from videos where public_private='public' and approved='yes' order by number_of_views desc; +----+-------------+--------+------+------------------------------...

Sum amount of overlapping datetime ranges in MySQL

I have a table of events, each with a StartTime and EndTime (as type DateTime) in a MySQL Table. I'm trying to output the sum of overlapping times and the number of events that overlapped. What is the most efficient / simple way to perform this query in MySQL? CREATE TABLE IF NOT EXISTS `events` ( `EventID` int(10) unsigned NOT NUL...

SQL join multiple values from column into one cell

There are a ton of sql join q's already but I didn't see my answer so here goes . . . I am working with WPDB (Wordpress database)/EZSql/MySQL 5.0. Trying to achieve the 'simple' desired output below has not proven to be easy. Current output MemberID MemberName FruitName -------------- --------------------- --...

MySQL - Need help counting id's that correspond to certain in other tables

Hi, I have on table named players, then other tables named tries, conversions, penalties, dropgoals. I need to get the player_id from players, then count the following: number of tries for player_id in tries number of conversions for player_id in conversions number of penalties for player_id in penalties number of dropgoals for player...