mysql

Help with two SQL Queries

Hi, I’m having some difficulties with two queries. I can get some of the information but my problem lies in the some special conditions I just can’t seem to get right in the SQL statement. The first query is in natural language defined: I’m going to find all the candidates who has acquired two qualifications but during the same time p...

MySQL slow query

SELECT items.item_id, items.category_id, items.title, items.description, items.quality, items.type, items.status, items.price, items.posted, items.modified, zip_code.state_prefix, zip_code.city, books.isbn13, books.isbn10, books.authors, books.publisher FROM ( ( items LEFT JOIN bookitems ON items.item_id = bookitems.ite...

In what way does Wordpress rewrite page URLs?

Hey Recently I'm interested in post's structure of Wordpress. They use a table named (wp_posts) and in this table they saved 3 related fields such as : post_title post_name guid It's clear that they save title of each story in post_title field , and slugs in post_name , and full url of a post in guild filed . But where the hell...

How to check the existance of multiple IDs with a single query

Hey there I'm trying to find a way to check if some IDs are already in the DB, if an ID is already in the DB I'd naturally try to avoid processing the row it represents Right now I'm doing a single query to check for the ID, but I think this is too expensive in time because if I'm checking 20 id's the script is taking up to 30 seconds ...

Query using SUM

I need to fix this query so that it will give me the part number, part description and on hand value for each part in the SG class. This is what I have so far. but it just gives me the total value of all the items in the SG class. How can I have it where it will give me the total based on the description? SELECT PART_NUM, DESCRIPTION, ...

Help with query

I'm trying to make a query that looks at a single table to see if a student is in a team called CMHT and in a medic team - if they are I don't want to see the result. I only want see the record if they're only in CMHT or medic, not both. Would the right direction be using sub query to filter it out? I've done a search on NOT IN but h...

Why is recordset result being returned in this way for Python database query?

I have searched high and low for an answer to why query results returned in this format and how to convert to a list. data = cursor.fetchall() When I print data, it results in: (('car',), ('boat',), ('plane',), ('truck',)) I want to have the results in a list as ["car", "boat", "plane", "truck"] ...

Implicit cast from now() to a date field

Hello, I have an issue with MySQL 5.1. A datetime data type isn't implicitly casted to match a date column. SELECT * FROM my_table WHERE my_date_field = NOW() This request doesn't return any rows using MySQL 5.1, but works well with version 5.0. If we use CURDATE() instead of NOW() it works both in MySQL 5.0 and MySQL 5.1. If the ca...

Limit a user to view only associated records in rails

I have an application with three Models (Profile -> SubModel -> SubSubModel) chained together with has many relationships. I am trying to limit a user, after logging in, to only retrieving records that are associated with their Profile. I am very new to rails and this is what I had been trying in the Profile model has_many :submodels, :...

MYSQL Query using SUM

How can I make this select the 7500 or greater from the ON_HAND VALUE column? " List the part number part descrtiption and onhand value for each part whose on hand value is at least $7,500. Assign ON_HAND_VALUES as the computed column." This is what I have so far, I tried subquerys IN statements but I cant figure it out. SELECT PART_...

PHP MySQL database problem

Code 1: <?php class dbConnect { var $dbHost = 'localhost', $dbUser = 'root', $dbPass = '', $dbName = 'input_oop', $dbTable = 'users'; function __construct() { $dbc = mysql_connect($this->dbHost,$this->dbUser,$this->dbPass) or die ("Cannot connect to MySQL : " . mysql_error()); mysql_select_db($this->dbName) or die ("Datab...

MySQL: difference of two result sets

How can I get the set difference of two result sets? Say I have a result set (just one column in each): result1: 'a' 'b' 'c' result2: 'b' 'c' I want to minus what is in result1 by result2: result1 - result2 such that it equals: difference of result1 - result2: 'a' ...

MYSQL update query php

I am trying to use an update query to change values held in a database, the new values I am trying to store are held in php variables. I'm not sure what it is I'm doing wrong. mysql_query("UPDATE user SET status='full' WHERE user_id = '$user_id'")or die(mysql_error()); here is the error message Duplicate entry '91317691' fo...

Best way to handle Many-to-Many relationships in PHP MySQL

I am looking for the best way to handle a database of many-to-many relationships in PHP and MySQL. Right now I have 2 tables: Users (id, user_name, first_name, last_name) Connections (id_1, id_2) In the User table id is auto incremented on add and user_name is unique, but can be changed. Unfortunately, I don't have control over the us...

Am I correctly extracting JPEG binary data from this mysqldump?

I have a very old .sql backup of a vbulletin site that I ran around 8 years ago. I am trying to see the file attachments that are stored in the DB. The script below extracts them all and is verified to be JPEG by hex dumping and checking the SOI (start of image) and EOI (end of image) bytes (FFD8 and FFD9, respectively) according to the ...

mysql innodb max size of transaction

Using mysql 5.1.41 and innodb I'm doing some data import, but can't use load data infile, so I'm manually issuing insert statements. I found that it's much faster to disable auto commit and issue say, 100 insert statements and then commit, instead of the implicit commit after each insert. It got me thinking, what limits are there to ho...

Mysql - NOW() function calling

Hello, I'm using php for making queries for mysql. Here is one: UPDATE `subscribers` SET `curDate` = NOW() WHERE `e_mail` = "$resEmail" curDate - DateTime type. The problem is that after this query curDate of given email is 0000-00-00 00:00:00 What's wrong? ...

How do I select one record per group and left join with another table?

I have one table with two location IDs (pointa and pointb) and how far apart they are. Multiple pairs can be the same distance apart. Example data: pointa pointb distance 1 2 250 3 4 250 4 5 250 ..... 6 8 500 10 12 500 13 17 500 I want to select one pair from...

Get data from table from a list of strings on MySQL

Hello, How can I query data from a table available on a list of strings? I only want to get the data from the list of strings. Example: Table ID Name 1 Big 2 Small 3 Extra 4 Orange 5 Drink List of Strings: Big Small Extra Thanks! ...

Checking if any of a list of values falls within a table of ranges

I'm looking to check whether any of a list of integers fall in a list of ranges. The ranges are defined in a table defined something like: # Extra Type Field Default Null Key 0 int(11) rangeid 0 NO PRI 1 int(11) max 0 NO MUL 2 int(11) min 0 ...