mysql-query

MySQL Syntax and 'OR' Performace

This MySQL query works just fine SELECT o.id FROM descriptions_programs d, titles_programs t, programs o WHERE (d.object_id=o.id AND MATCH (d.text) AGAINST ('+china' IN BOOLEAN MODE) AND d.current=1) AND (t.object_id=o.id AND MATCH (t.text) AGAINST ('+china' IN BOOLEAN MODE) AND t.current=1) But if I replace...

Find overlapping (date/time) rows within one table

I have a table which stores in each row a meeting with start date/time and end date/time. meetingID int meetingStart datetime meetingEnd datetime Desired output: For each pair of overlapping rows I would like to output meetingID, meetingStart, meetingID, meetingEnd Whats the most efficient way to perform such a query in mysql? ...

When and How to use Multiple MySQL Queries with PHP (PDO)

What are the benefits of using multiple MySQL queries in a statement, other than to minimize code. How do you execute, retrieve, and display the results of multiple MySQL queries sent in one statement using PHP (and preferably PDO). ...

How to use one query for two tables representing a poll with question and multiple answers?

Hi, I have two tables poll and poll_answers, which represent a poll and the options to choose from to answer a question from the poll. EX: DO I suck at SQL? yes yes, you do change craft and the co-responding tables: poll pollID pollQuestion 1 | DO I suck at Sql? poll_answers pollAnswerID pollAnswerText pollID 1 | yes | 1 2 ...

How to edit values in a returned mysql result resource and forward edited result resource

Here's what I'm trying to do ... Im using Flash to call an AMFPHP service that queries my database and returns the result resource. Before returning the result to the Flash movie I need to edit some of the values in the result resource. How can i iterate through the rows of the result, change some values and 'repackage' the resource t...

How to prevent, counter or compensate for, an empty/NULL field (MySQL) returning an empty set...

I'm learning PHP and MySQL to get a hang of server-side scripting and for personal interest/development. The first 'program,' beyond 'hello world' seems to be writing ones' own blog. This is a little beyond me yet and so thought I'd start with a contacts-database type. So far so good, but (and there's always the but!) I'm trying to form...

mysql query, reuse columnnames in query

I have a mysql query, something like this: SELECT users*100 as totalusers, totalusers*90 AS totalsalerys FROM table As you can see I want to reuse the totalusers when calculating totalsalerys so I son't have to write the same code again. That dosesn't seem to work in mysql, is there another easy way to do this? My query is just an ex...

MySQL set storing

I have a couple of tables in a web application I'm coding in PHP, and I would like to know if this would be good pratice. CREATE TABLE `products`( `product_id` int NOT NULL auto_increment, `name` varchar(255) NOT NULL, `variations` varchar(255) default NULL, PRIMARY KEY (`product_id`) ) CREATE TABLE `variations`( `variation_...

Incrementing in MySQL

I try to increment a field in mysql but it seems that it is not really working for some reasons ... That is the query I run in mysql query: UPDATE profil_perso SET profile_views = profile_views +1 WHERE id = 30537 In the status bar it says : 1 row affected by the last command. No resultset returned. But it didn't seemed to work. At f...

ordering a table in Mysql, according to another, but without seeing repetitive rows of the first table

In MySql, I have two tables, A and B. A has as columns A.id, B has as columns B.id and B.aid. or each row of A I have many rows of B. And the value of B.aid=A.id of course. Now I need to get a list of the values in A, but I need to order them, according to B. In particular if I have two rows in A: a1 and a2. Each will have a series...

What's wrong with this MySQL statement: DECLARE @ID INT

DECLARE @ID INT ; This statement parses fine with MS SQL Server, but gives me You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @ID INT' at line 1 Does anyone have any idea about the reason? ...

Simple MySQL-query takes ages

We are running a stats site for a bunch of games, but after the log has tipped over 100 megs, things are starting to slow down, so we need to optimize our queries. However, we've found out that what we thought was a simple query, takes about 1.5 secs: SELECT handle, ( SELECT COUNT(*) FROM kills WHERE killer=p.handle AN...

Learning resources to transerfer my MS SQL Server knowledge into MySQL

I'm an experienced MS SQL Server developer But with my first MySQL project I got problems with very simple issues So can anyone suggest good resources helping me transfer my MS SQL Server knowledge into MySQL I'm not looking for a complete reference guide for MySQL I'm looking for something targeted for experienced SQL developers EDI...

Pass result of mysql_fetch_object() to a function does not work

I have the following problem: public function row2Partner($row){ echo $row->PartnerID; } public function main(){ $query = "SELECT PartnerID, PartnerName FROM Partner"; $result = mysql_query($query); $this->row2Partner(mysql_fetch_object($result)); } This gives me the error in row2Partner(): Trying to get property of non-objec...

insert multiple rows via a php array into mysql

I'm passing a large dataset into a mysql table via php using insert commands and I'm wondering if its possible to insert approximately 1000 rows at a time via a query other than appending each value on the end of an mile long string and then executing it. I am using the codeigniter framework so its functions are also available to me. ...

MySQL Query Selecting records from a database where a value equals one of two different things

I have to get records from my MySQL DB where: sentto = "$username" OR sentto = "everyone" How would I put this in a MySQL query? I tried a few things, but they don't seem to be working: mysql_query("SELECT * FROM pmessages WHERE status='unread' AND sentto='$username' || sentto='everyo...

Optimizing a PHP page: MySQL bottleneck

I have a page that is taking 37 seconds to load. While it is loading it pegs MySQL's CPU usage through the roof. I did not write the code for this page and it is rather convoluted so the reason for the bottleneck is not readily apparent to me. I profiled it (using kcachegrind) and find that the bulk of the time on the page is spent doin...

MySQL IN() for two value/array?

I'm having trouble finding a better way to search MySQL for a pair of values in a table. I have the value pairs in an array, and would like to duplicate the IN() function, but for more than 1 value. For example purposed; I have the following 3 pairs: foo,1 boo,2 goo,3 The current solution puts me at: SELECT * FROM [table] WHERE (co...

Mysql: setting a variable within the select

Given my two db tables aliases and subscriber have entries like this: aliases.username = '5551234567' aliases.contact = 'sip:[email protected]' subscriber.username = 'a_sip_username' I'd like to select only the matching rows that have subscriber.username within the aliases.contact field. This was my first attem...

Why is mysql ignoring the 'obvious' key to use in this simple join query?

Hello. I have what I'd thought would be a simple query, but it takes 'forever'. I'm not great with SQL optimizations, so I thought I could ask you guys. Here's the query, with EXPLAIN: EXPLAIN SELECT * FROM `firms_firmphonenumber` INNER JOIN `firms_location` ON ( `firms_firmphonenumber`.`location_id` = `firms_location`....