mysql-query

Need help with correct SQL

So I'm a noobie, starter, beginner - throw all of the above at me. I'm trying to create a query that will search through a database and return blah blah blah. The problem is, it isn't quite working. Here's an example query - As you can see, above other things, I am trying to return results from someone with the last name Johnson SEL...

Highly filtered search based on multiple conditions

I have two tables in a restaurant search done in php. All the information about the restaurant type, facilities, cuisines are in entered to Table2 -'stack' with reference to their restuarant id in the table 1. How do i run a query so that i can get all the restaurant that serve chineese and also serves dinner and also have parking? This...

Why is my SQL UPDATE statement not working in a loop?

The update statement in example is not working all the time even though the where clause is true. The database is MYSQL innodb. Would that cause some sort of locking ?? This is so weird. <?php $query = 'SELECT id FROM TABLE1'; $result = db_query($query); while($row = db_fetch_array($result)) { //do some process...

Optimize an SQL query to get count of specific answers

I'm creating a survey with 10 questions. All questions have 5 possible answers with values from 1-5. The data is stored in a database with one row per user. There is a column for the answer to every question. To make bar graphs for the answers to every question, I currently retrieve the count of rows where the value of a specific colu...

Would any configuration or INNODB principle cause the following MYSQL update to not necessarilly perform all updates?

<?php $query = 'SELECT id FROM transaction_table'; $result = db_query($query); while($row = db_fetch_array($result)) { //do some processing db_query('UPDATE transaction_table SET updated = "1" WHERE id = "%s"',$row['id']); } ?> Every time this script run, it only updates a few random rows (8-25 on ...

php and mysql help, working with multiple database

Hi, I need to work on multiple database, and below is my current code. Following code seems to be very very slow and having issues with multiple connection while I view the mysql connection list. So can anyone let me what's wrong with the code and help me to correct the issues and faster performance. $dbHost="localhost"; $dbUser="use...

Need help with correct SQL query

So I asked a similar question yesterday, and got a great response - I'm hoping for the same luck this time around. What I'm trying to create is paged results for a MySQL query. I'm taking advantage of LIMIT. Here is my query. SELECT BookingInfo.ClinicID, BookingInfo.BookingDate, BookingInfo.BookingTime, BookingInfo.Status, Pati...

Multiple Ranks in one table

Hi, I need the following, Can anyone please help me do it. Rank Cust_Type Cust_Name Revenue 1 Top A 10000 2 Top B 9000 3 Top C 8000 1 Bottom X 5000 2 Bottom Y 6000 3 Bottom Z 7000 I need separate rank...

MySQL:Extracting a sub graph?

I have the adjacency list of a large graph stored in a table. v1 | v2 1 | 2 1 | 3 1 | 4 2 | 5 3 | 5 3 | 6 4 | 7 7 | 9 5 | 10 I am trying to extract the graph 2-hops away from say vertex 1 so that it returns all the edges from the above list except (7,9) and (5,10). I managed this: SELECT * FROM stub_graph WHERE v1...

MySQL: Join two tables

Hello, I'm having problems with design correct SQL query. I'm tired like a hell today, been working over 12 horus (deadline soon) and cannot find issue... Tables: buddies | userid | buddyid users | id | username Now, what I'd like to do: Query table buddies for all user friends (when ID = userid OR ID = buddyid). Having no problems ...

how to execute the batch query in mysql for update column values.

For example i have a table ACCOUNT WITH TWO COLUMNS, ID AND A_NUMBER. AND I WANT TO UPDATE THE A_NUMBER VALUES IN A SINGLE QUERY WITH DIFFRENT VALUES. "UPDATE ACCOUNT SET A_NUMBER="0135" WHERE ID=131; UPDATE ACCOUNT SET A_NUMBER="0145" WHERE ID=132; UPDATE ACCOUNT SET A_NUMBER="0155" WHERE ID=133; UPDATE ACCOUNT SET A_NUMBER="0160" WHERE...

Using mysql Regexp in a select statement not the where clause

So what i want to do is use a Regex to select only certain parts of data from a column Example : SELECT URL REGEXP 'http://' from websitelist --(website list is a list of URL's) If I run that against the table it returns 1 foreach row in which 'htt://' was found, what I want is to return the string that matches the regexp ...

Approximately how long should it take to delete 10m records from an MySQL InnoDB table with 30m records?

I am deleting approximately 1/3 of the records in a table using the query: DELETE FROM `abc` LIMIT 10680000; The query appears in the processlist with the state "updating". There are 30m records in total. The table has 5 columns and two indexes, and when dumped to SQL the file about 9GB. This is the only database and table in MySQL. ...

how to declare variable in trigger and use it with mysql ??

what is the error ? DELIMITER $$ CREATE TRIGGER Task_insert_trig AFTER INSERT ON task FOR EACH ROW begin declare userID int; Set userID =(select userID from assigned_task where Atk_Task_Id = new.Tsk_Id and Atk_Project_Id = new.Tsk_Project_Id); insert into dashboard_event set Dsh_Project_Id = new.Tsk_Project_Id, Dsh_Actor = user...

Custom query in drupal views - now can't sort.

The view with the custom query is being displayed in the right sidebar titled "Most Downloaded" here: http://tf2huds.com. The view with the query generated by views is right below it. To put in the custom query I'm using this code in the views.module file: <?php function views_views_pre_execute(&$view) { if($view->name=="hud_downloa...

MySQL get rows, but for any matching get the latest version

I'm developing a CMS, and implementing versioning by adding a new entry to the database with the current timestamp. My table is set up as follows: id | page | section | timestamp | content "Page" is the page being accessed, which is either the path to the page ($page_name below), or '/' (to indicate 'global' fields). "Section" is t...

How to select records who match criteria defined in a join table?

I have this three tables: products TABLE: id:integer name:string features TABLE: id:integer name:string features_products TABLE: product_id:integer feature_id:integer The features_products TABLE tells me which features have each product. For example: product_id feature_id 1 3 1 5 3 4 tells me the ...

mysql select query pattern, get duplicates just once , regex, pattern

I have a database with 3 columns (item_number, seller_id, type) . I'm getting the columns content using while loop. $sql = msql_query("SELECT * FROM item_detals WHERE type='classifiedTitlePrice'"); while($row = mysql_fetch_array($sql)) { $item_number = $row['item_number']; $mcat = $row['mcat']; } However the problem is that a...

MySQL order by rand() grouped by day

Is it possible to get random items inside the same day? For example: +----+---------------------+ | id | md | +----+---------------------+ | 1 | 2010-06-27 11:26:01 | | 2 | 2010-06-27 11:28:20 | | 3 | 2010-06-27 11:29:46 | | 4 | 2010-06-27 11:30:50 | | 5 | 2010-06-27 12:20:56 | | 6 | 2010-06-...

Easier way to select MAX() based on conditions?

Hi, Given the following table format... id | date | value ___|____________|______ 11 | 2010-01-01 | 50 11 | 2010-01-02 | 100 12 | 2010-01-01 | 150 12 | 2010-01-02 | 200 ... I need to select the id that corresponds to the maximum value on a day that I specify. The only way I've figured out how to do this so far is using a sub-qu...