mysql-query

mysql query occasionally returns nothing

Hi, We have a function used within our PHP/MySQL application which returns basic configuration information, it contains a simple select query and looks like this: public function getConfigurationValue($field) { $res = mysql_query("SELECT `cfg_value` FROM `ls_config` WHERE `cfg_name` = '".mysql_real_escape_string($field)."'"); $...

How to sort MySQL results based on a specific foreign value?

Here are my DB tables: Field - ID, name Order - Order ID, name, etc. OrderField - Order ID - Field ID - Value For example, a Field name may be "First Name," and the OrderField value may be "James." Using a MySQL query only, how can I efficiently sort Orders based on the OrderFields? To be a little more specific: Say I want...

mysql_num_rows() argument not valid

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/ango/public_html/ficha/findUser.php on line 201 This is the code: $query = mysql_query("SELECT ango_turnos.planilla.fecha_turno, ango_turnos.planilla.cod_estado, ango_personas.dominio_interno.* FROM ango_turnos.planilla RIGHT OUTER JO...

MYSQL Query multiple id to name resolution between two tables

I have 2 tables like this: [games] gameid hometeamid awayteamid score and [teams] teamid teamname How would i create a query to output something like: [home team][away team][score] 49ers chargers 28-17 You see, i need to resolve 2 team names with 2 team ids within the same table and output just the names. Thanks in advance for...

Using MySQLdump for sub-set of database migration

I've been using both mysql and mysqldump to teach myself how to get data out of one database, but I consider myself a moderate MySQL newbie, just for the record. What I'd like to do is get a sub-set of one database into a brand new database on a different server, so I need to create both the db/table creation sql as well as populating...

Any way to improve this slow query?

Its particular query pops up in the slow query log all the time for me. Any way to improve its efficiency? SELECT mov_id, mov_title, GROUP_CONCAT(DISTINCT genres.genre_name) as all_genres, mov_desc, mov_added, mov_thumb, mov_hits, mov_numvotes, mov_totalvote, mov_imdb, mov_release, ...

MySQL create function won't return sets

I can't get this work with MySQL 5.x. This function should return all relevent rows from the select-statement: CREATE FUNCTION getuids(root INT) RETURNS SET OF INT AS $$ BEGIN RETURN SELECT uid FROM pages WHERE deleted = 0 END $$ LANGUAGE 'SQL'; Any idea what I made wrong? ...

Problem in mysql when ordering by more than one column using ORDER BY FIELD

I have a MySQL query where I sort by field like this: "... WHERE (patterns.id IN($idsJoin)) $where ORDER BY FIELD($idsJoin2) LIMIT 0 , $numLines"; where $idsJoin2 is something like this: my $idsJoin = join(',',@ids); my $idsJoin2="patterns.id, ".$idsJoin; and "@ids it's an array with numbers I want to order...

Can I make this MySQL staement with complex COUNTs more efficient?

I have a table of all Major League Baseball games that is structured like this: **Game** id (int) home_team_id (int) away_team_id (int) home_score (int) away_score (int) date (date) I also have table named "ticket" that has tickets sold to different games: **Ticket** id (int) game_id (int) price (float) time_sold (datetime) I'd lik...

Count line breaks in a field and order by

I have a field in a table recipes that has been inserted using mysql_real_escape_string, I want to count the number of line breaks in that field and order the records using this number. p.s. the field is called Ingredients. Thanks everyone ...

MySQL Insert in table if it doesn't exist already ¿Am I doing it right?

While building my application with relational tables I ran into the following problem : I have the following table, for this example named "valores": ----------------------- | id | value | ----------------------- | 1 | Unique VAL | | 2 | Unique VAL2 | ----------------------- ID = AUTOINCREMENT VALUE = UNIQUE What I...

SQL Query: Incrementing by two instead of 1

Hello all, I am trying to update a field in a table by increasing its integer value by 1. Here is what I am using: function updateViews($id){ $sql = "UPDATE tweets SET tweet_views = tweet_views + 1 WHERE tweet_key = '$id'"; $result = mysql_query($sql) or die("DB Error : ". mysql_error()); return $result; } However, I find its inc...

MySQL - get elements from a link table but ONLY those elements

I have a problem selecting from a link table and filtering out superfluous results. Publications belong to packages by means of the package_publications table. For instance, I know the ids of my publications to be 11 and 47. I want to return a package that has ONLY those publications in it. Now if I do a join and do something like whe...

About the RoR Update DB Record

I would like to use the ruby on rails and mysql to write a simple test program but, in this time, i can not update the database record. because i use the "mid" to replaced the "id" column in this case, what can i do to work to update the record? Problem: Mysql::Error: Unknown column 'id' in 'where clause': UPDATE `messages` SET `messag...

Is there a simple way to convert MySQL data into Title Case?

I have a MySQL table where all the data in one column was entered in UPPERCASE, but I need to convert in to Title Case, with recognition of "small words" akin to the Daring Fireball Title Case script. I found this excellent solution for transforming strings to lowercase, but the Title Case function seems to have been left out of my vers...

my mysql index is not having any effect

I have the following query: mysql> explain SELECT Exhibition.venue_id, Exhibition.name, Exhibition.slug, Venue.name, Venue.slug, Venue.location_id, Venue.id, Exhibition.id FROM exhibitions AS Exhibition LEFT JOIN venues AS Venue ON (Exhibition.venue_id = Venue.id) LEFT JOIN temperatures AS Temperature ON (Temperature.ref = Exhibition.id...

Evaluation of multiples 'IN' Expressions in 'WHERE' clauses in mysql

Updating by @Cesar's request. Hope I understood what you want, if not, please revert. Quassnoi. If I make an SQL query like this: SELECT * FROM TABLE_NAME WHERE b IN (2, 7) AND c IN (3, 9), can I assume that MySQL will match only pairs from elements with same number in each list? That is, (2, 3), (7, 9), ...? For example, suppose we h...

SQL with alphabet - thinking like a dictionary

I want to write a query that will match up a string from one table with the closest alphabetical match of a string fragment within another table. Given: There are 2 tables, table 1 has a record with string "gumby". Table 2 has letters that start words: "g", "ga", "gam", "go", "ha", "hi". Problem: Because I don't have a "gu" entry o...

Optimizing MySQL Queries: Is it always possible to optimize a query so that it doesn't use "ALL"

According to the MySQL documentation regarding Optimizing Queries With Explain: * ALL: A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that ...

How can I optimise this MySQL query?

I am using the following MySQL query in a PHP script on a database that contains over 300,000,000 (yes, three hundred million) rows. I know that it is extremely resource intensive and it takes ages to run this one query. Does anyone know how I can either optimise the query or get the information in another way that's quicker? I need to ...