[Fatal error: Call to undefined function pg_escape_string()]

*Returns from long absence*

Just wanted to reply in case someone has the same issue later.

I'm sure the problem here is one of parameter types. Just cast your param to text and it'll work.

 

 

[Select statement question]

You'll need to do a left join:

 

SELECT chn.name AS chain, dlr.name AS dealer, ....
FROM dealers dlr
LEFT JOIN dealer_chain_lookup cl ON dlr.dealer_id = cl.dealer_id
LEFT JOIN dealer chn ON cl.chain_dealer_id = chn.dealer_id
ORDER BY 1, 2

 

[Having trouble altering table to insert foreign keys]

Sounds like your integrity is already broken. Fix it by adding the corresponding rows to table2 or remove the offending rows from table1.

[order by values from different table]

Move the

COUNT( rating.game_id ) / SUM( rating.rating_value )

out of the order by clause and put it in the select clause. It will still be broken but you should be able to figure out what you are doing wrong.

[Large set of data in small chunks, SELECT inquiries only, MySQL or SQLite?]

That's 25 million rows for something that can be calculated fairly cheaply if you've got lat/lon for each location.  What is the typical use case?  I know much more about PostGIS, but MySQL has spatial extensions that will should do all of this for you.

[table data with updated timestamp]

Use a staging table to import the csv and only update the ones that don't match.

[XAMPP for PostgreSQL]

XAMPP has the Postgres drivers baked in, so all you've really got to do is install Postgres. But there is a package called BitNami that has pg instead of mysql.

 

http://bitnami.org/stack/wappstack

[Invoice numbers]

Easiest way would be to use the keyword DEFAULT for the invoice number.

 

INSERT INTO invoices (invoicenumber, invoice_time, customer_id)

VALUES (DEFAULT, current_timestamp, 222);

 

Otherwise you can use nextval('invoice_seq') where invoice_seq is the name of your sequence.

 

[Refferential Integrity Issue: Parent Table FK not setting on INSERT]

Well it looks like you've got a circular reference. Do both tables refer to each other? If so that wont work.

 

Why would you have a disclaimer_id in the user table?

[weighted probability]

If you add more than 10 options, swap out the sqrt(10) with sqrt(n).

[weighted probability]

SELECT round(pow(1 + rand() * (sqrt(10) - 1), 2)) rnum

 

[[SOLVED] syntax on multiple join problem?]

Well just because you've made it harder doesn't mean you've normalized it

 

So I'm going to guess these are job interests a person may have. So what do you do when someone comes along with 4 job interests? or 5 or 6? Do you keep adding tables?

 

Instead do something like this:

CREATE TABLE person (
  person_id  INT NOT NULL PRIMARY KEY,
  first_name VARCHAR(30),
  last_name VARCHAR(30),
);

CREATE TABLE job_interests(
  interest_id INT NOT NULL PRIMARY KEY,
  job_description VARCHAR(80) NOT NULL
);


CREATE TABLE person_job_interests (
  person_id  INT NOT NULL,
  interest_id INT NOT NULL,
  preference_no INT2,
  CONSTRAINT person_job_interests_pk PRIMARY KEY (person_id, interest_id)
);

 

Also "english" appears to be a poor choice for a column name here. Use something like 'job_category', 'label' or 'description'.

[Sort String Column as Integer]

Right, and you'll have to write a user defined function that removes all non-numeric characters and returns a number. I'm not sure which database you are using, but if it has regex functions it should be fairly straight forward.

[Comparing Records by Dates that are Stored in a Single Table]

Yeah, well 1 and 3 weren't in the table on 8/31/2009. I assumed you would have a continuum here in your data. If you've got a fencepost then you'll have to add some logic to handle that.

[Comparing Records by Dates that are Stored in a Single Table]

This isn't the greatest design. Always try to keep the start and end times for an event in a single row.

This would be ideal for a full outer join but I'm pretty sure MySQL still doesn't support them.

 

SELECT CASE WHEN yesterday.id IS NULL AND tomorrow.id IS NULL THEN 
  concat(today.id, ' was added ', today.date, ' and removed ', tomorrow.date)
  WHEN yesterday.id IS NULL THEN concat(today.id, ' was added ', today.date)
  WHEN tomorrow.id IS NULL THEN concat(today.id, ' was removed ', tomorrow.date)
  ELSE 'Artacus freaking rocks'
END AS status_update
FROM example today 
LEFT JOIN example yesterday ON today.id = yesterday.id
  AND today.date = yesterday.date + INTERVAL 1 DAY
LEFT JOIN example tomorrow ON today.id = tomorrow.id
  AND today.date = tomorrow.date - INTERVAL 1 DAY
WHERE yesterday.id IS NULL OR tomorrow.id IS NULL

[Complex search Query]

You need to use a left join when joining languages and specialty or you wont get any records that don't have a specialty and language.

[[SOLVED] plus/minus a value]

PFM's approach will cause a full table scan. Which will be an issue if you've got a large table.

 

Corbin's approach will perform better, but you don't necessarily need to preprocess in php.

 

BETWEEN ($id - 5) AND ($id + 5)

[SELECT * FROM WHERE?]

Was there a question in here?

 

Where is your problem? Doing the search, joining the tables, displaying the results?

[[SOLVED] PHP+XML+MYSQL - Loop through rows]

This is more a PHP question. Your variables are not going to be visible from inside the function unless you use global $var...

 

I'd just take the sql out of the function.

[Comparing Records by Dates that are Stored in a Single Table]

I'm not sure what you mean by added and removed. I'm guessing what you really need are min and max dates for an id.

 

SELECT id, MIN(date) AS added_on, 
  -- Not sure if you need the last day it WAS in your result
  -- or the first day it WAS NOT
  MAX(date) + 1 AS removed_on 
FROM example
GROUP BY id

[[SOLVED] Loading data from a csv file]

No, you won't be able to do that with copy.

 

Is this a one time thing or is it something you need to process on a continual basis?

 

If it's a one time thing you can copy/reorder the columns in Excel. You have several options for continual process.

One would be to copy to a staging table and then transform into your regular table. There is pgloader. It will allow you to do transforms before outputting to copy.

http://www.postgresql.org/docs/8.3/static/pgcrypto.html

 

Another option would be to use an ETL tool. There are loads of them out there.

 

 

[[SOLVED] Specifying date range in query using DATE_FORMAT.]

Don't do that. You're forcing your db to go through every row converting the date to a string in order to compare with a string.

 

Instead, convert your strings to dates. Then you only only have to do the conversion twice and better yet, the db can use indexes.

[[SOLVED] Loading data from a csv file]

http://www.postgresql.org/docs/8.3/static/sql-copy.html

[editing a view]

Am I close?

 

You got it. Oh and just comment out the first line to test when you are making views.

 

--CREATE OR REPLACE VIEW im_project_types AS
SELECT project_status_id, CASE WHEN id = 96 THEN 'ThisIsMyLabel'
ELSE originalLabel END AS originalLabel
FROM im_project_types;

[MySQL Syntax for MSSQL Query]

MySQL doesn't have windowing functions so you'll have to scrap that approach.

 

But just select the entityID and max(pollingID) in a subquery and join that max pollingID to get the correct product line and version.