mysql

MySQL: Selecting One Record When Others Have Same Data

I have a table of cities that all share the same area code: 367 01451 Harvard Worcester Massachusetts MA 978 Eastern 368 01452 Hubbardston Worcester Massachusetts MA 978 Eastern 369 01453 Leominster Worcester Massachusetts MA 978 Eastern The table has multiple area codes, all with multiple cities. What I'd like to do is only ...

MySQL: How many minutes ago was DB updated?

I need to keep a field in a data-base and update it with a time somehow, then later I need to check that time to see if it was over 30 minutes ago or not, and if not, how minutes left until 30? I am going to be doing this with PHP+MySql can anyone tell me the simplest way to do this? Thanks!! ...

mySQL Select visits and display all by month?

Hi guys, I'm punching way above my weight for mySQL Queries and syntax right now. I could do with some help achieving the following:- I have a metrics/analytics table with country of visit in a row. I have the following query:- SELECT `country`, COUNT(`ip`) AS `cViews` FROM `metrics` WHERE `projID` = 'projID' AND `country` != 'XX' AND...

MySQL DELAY_KEY_WRITE TAble Value

Does anyone know how to find the DELAY_KEY_WRITE value for a specific table? It's a table option for the Create Table syntax. The table was created a while ago by someone else and i need to know if it has DELAY_KEY_WRITE enabled. Thanks ...

PHP: form select box doesn't write changed selection to DB

Okay, so I have this form that is set to preload a DB record for editing if you add a ?edit=1 to the url, it will load record #1 for editing into the form. I have a box that is like this- <select class="field select addr"> <option value="no"<?php if($row['has_amenities'] == "no") {echo ' selected=\"selected\"'; } ?>>No</option> <o...

MySQL connection reuse problem

I am trying to reuse mysql connection. Hence I have a global variable in databasemanager.php class that returns a connection. The problem is somehow on one particular page mysql is executing prior query as well. Looks like there is some leftover query in connection object that gets executed if same connection is being reused . Is it...

php login script - remember me

Can anyone see anything wrong with this login script: public function login($username, $pass, $remember) { // check username and password with db // else throw exception $connect = new connect(); $conn = $connect->login_connect(); // check username and password $result = $conn->query("select * from login w...

MySQL, Given a list, selecting the missing rows from a Table

This has been driving me crazy for the past few minutes I have a table, lets say table_alphabet with three columns. letter(pri) col1 col2 a 24 55 b 45 45 c 23 44 ... y 33 55 z 45 22 Now in my application I have a list ('a', 'b', 'lol', 'cats', 'z', 'foo...

I'm trying to make a forum

So I have these tables: Topics id, title, date Posts id, title, text, date, user, topic_id How should I structure it so that the first post, the topics text, is on top of all other posts in the topic? Sort them by date? Or is there a smarter way? ...

how do you ensure database Interoperability

I'm starting a new opensource project (for real estate) and wanted to focus on using MySQL, but would also like to ensure it works in PostgreSQL. What is the best way to doing this without having to continually test in both environments? I'm assuming the db schema is close to the same, but there could be some differences on the SQL scr...

Crystal Reports XI and MySQL Stored Procedure with Parameters

I am having a problem with a Crystal Report that displays data from a MySQL table. I am currently gathering the data directly from the table, however when the users try to input parameters, problems arise such as: null values for parameters returning errors parameters not working as specified I then created a stored procedure to re...

How can I copy a locked mySQL table?

I am trying to rotate out an innoDB table which has a high number of transaction, so I need to lock all the tables involved. With the tables locked I cannot use RENAME to move data around using auxiliary tables. And if I do an INSERT, then I have to worry about the integrity of the ids. Any suggestions? Is this even possible ...

MYSQL How can I make sure row does not get UPDATED more than once?

I have mutliple workers SELECTing and UPDATing row. id status 10 new 11 new 12 old 13 old Worker selects a 'new' row and updates its status to 'old'. What if two workers select same row at the same time? I mean worker1 selects a new row, and before worker one updates its status, worker2 selects the same row? Should I SELECT and UP...

Best way to denormalize data in Django?

I'm developing a simple web app, and it makes a lot of sense to store some denormalized data. Imagine a blogging platform that keeps track of Comments, and the BlogEntry model has a "CommentCount" field that I'd like to keep up to date. One way of doing this would be to use Django signals. Another way of doing this would be to put h...

PHP/MySQL - an array filter for bots

Hello, I'm making a hit counter. I have a database and I store the IP and $_SERVER['HTTP_USER_AGENT']; of the visitors. Now I need to add a filter, so I can put away the hits, that are made by bots. I found out, that many bots usually keep some common words in the $_SERVER['HTTP_USER_AGENT']; , so I's like to make and array of words, th...

What are the sqlite equivalents of MySQL's INTERVAL and UTC_TIMESTAMP?

What are the sqlite equivalents of INTERVAL and UTC_TIMESTAMP> For example, imagine you were "porting" the following SQL from MySQL to sqlite: SELECT mumble FROM blah WHERE blah.heart_beat_time > utc_timestamp() - INTERVAL 600 SECOND; ...

Filemaker Pro 10: How to get the unique ID of last insert on mySQL tables (ODBC)

I have a filemaker script that inserts a new entry on several imported mySQL tables. I need to get the unique id of these entries as they are created (before or after, either way) so I can refer to them later in the script. This is easy to do in SQL with LAST_INSERT_ID(), but I can't seem to find how in filemaker. What I have tried that...

Create and import mysql database on shared host in php

I'm trying to write php script that would: connect to mysql create database create user and password add user to database import prepared sql file to database The thing is that it'll be on shared host (and I'm trying to make it universal and work with different hosts). I guess database and db user will be prefixed with my account nam...

What is mysql actually doing when the main thread state is "making checkpoint"?

I've noticed that mysql (5.0.60) often freezes for up to minutes at a time under load, during which time the server is completely non-responsive. I've been able to isolate this to only happening when innotop shows the main thread state as "making checkpoint". What is the server actually doing at this point? ...

Considering move to PostgreSQL, feedback requested...

We are considering using PostgreSQL 8.4 on RHEL for some upcoming projects. We've been heavy users of MySQL since 3.23 (now on 5.1). The database servers are behind a load balanced cluster of web/app servers. Our usage is mostly business related web apps (user accounts with lots of emails / contacts / stats / projects / plans / task...