mysql

Cityname to geographical coordinates PHP script???

Hello guys, i need to develop a php script who ask for a city name and return a simple echo with the geographical coordinates of the city. Input data: Cityname OR Cityname, Country I know there is a free database called GeoNames were you can download a database who contains this information, but i really dont know how to export this da...

How to quickly prune large tables?

I currently have a MySQL table of about 20 million rows, and I'm in an urgent need to prune it. I'd like to remove every row whose updateTime (timestamp of insertion) was more than one month ago. I have not personally performed any alterations of the table's order, so the data should be in the order in which it was inserted, and there is...

How to get all the MySQL binlog event info using T-SQL

I am researching the possibility to log all the changes made to a MySQL database including DDL statements that may occur and use that information so it can be synchronized with a remote database. The application itself is written in C# so the best synchronization technology that I have seen so far to be available is Microsoft Sync Frame...

How to write the MySQL DATE_ADD() in "generic" SQL

I am writing my queries in 'standard' / 'generic' SQL so that it runs on any database (MySQL, Postgre etc). I'd like to use DATE_ADD() of MySQL. In Postgre, it would be done with something like '2001-01-01'::timestamp + '1 year'::interval;. Is there a more 'general' way of writing it so that it runs on both MySQL and Postgre (or other...

Performance of querrying across two mysql databases on the same server?

Is there any performance hit from querying over two (or more) databases on the same MySQL server, compared to if those databases had been merged into one? Background, I have inherited maintenance over a web application that splits its data into three different databases that runs on the same server, one for content, one for users and gr...

Using Cursor in a Loop of a stored procedure

So as to use cursors dynamically using MySQL is it possible to declare a cursor in a loop of a stored procedure? I've tried and got an error: increment: LOOP DECLARE cur1 CURSOR FOR SELECT person_id, publication_id FROM p_publication WHERE person_id = new_count; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; REPEAT ...

disabled textbox displaying value [php]

i have a book form in which there is an ID field. i'd like the [disabled] textbox to display the ID from mysql when the user tries to fill the form. it should check the database for the last id and accordingly display the next id. for example, if the last id entered is 8, then it should display to the new user- 9. how would i pre-gener...

CRUD Class for PHP (Simple but will Allow Relational Data)

I am building a database of products and manufacturers. I already have the database layout done. I am looking for a simple CRUD class that will let me setup Manufacturers and Products and create the records quickly and easily. I have looked at ORM's but they all seem to be over kill for what I am looking for. Thanks ...

Use single or mulitple mysql users for SaaS PHP/mysql application with multi-tenant architecture

We are building a multiuser app that has one database per customer. All customer database structures are identical. Right now we are generating a new mysql user (per client) that only has privileges to work on its own database. e.g. mysql user1 has rights on dbase1.* (database1.alltables), mysql user2 has rights on dbase2.*. We are no...

MySQL insert to multiple tables (relational)

tbl_product Name | Creator | UID | Salerank tbl_price Supplier | Price | UID I want to insert a product and then insert multiple prices into a seperate table. How is it possible to ensure that both tables had the same UID ideally an auto increment field? I will be using PHP alongside MySQL. Thanks, J ...

MySQL Stored procedure: search for a variable number of strings

I need a some stored procedure that called like: search('foo bar') makes a search similar to: SELECT FROM A, B WHERE A.B_ID = B.ID AND (A.f1 LIKE '%foo%' OR A.f2 LIKE '%foo%' OR B.f3 LIKE '%foo%') AND (A.f1 LIKE '%bar%' OR A.f2 LIKE '%bar%' OR B.f3 LIKE '%bar%') And I have some doubts and questions: I can't pass an array to the p...

SQL: Group counts in individual result rows

When I run query number 1: SELECT id, name, COUNT(name) AS count FROM markers WHERE state like 'OR' group by named I get the following results: id name count 14 Alsea 2 76 Applegate Valley 1 3 Ashland 9 64 Beaver 1 26 Bend 1 10 Carlton ...

SQL Query always uses filesort in order by clause

Hello all I am trying to optimize a sql query which is using order by clause. When I use EXPLAIN the query always displays "using filesort". I am applying this query for a group discussion forum where there are tags attached to posts by users. Here are the 3 tables I am using: users, user_tag, tags user_tag is the association mapping ...

MySQL Help, Duplicate entries using where clause

I have two tables in my database: test1 which looks like: ID pubmed_ID ------------------- 1 22 2 22 test2 looks like: antigen pubmed_ID ------------------ Hello 22 Bye 22 when i use the following select statment: select * from test1, test2 where test1.pubmed_ID = test2.pubmed_ID; I get: ID pubmed_I...

can I use mysql with .net application ....or its only supposed to be used with php

Can mysql be with .net applications ....or its only supposed to be used with php.If yes can it support large applications ...

When converting a mysql time in php I always get 16:00:20, regardless of the mysql time.

I am having trouble with converting time, when I use the date() function on a mysql timestamp, it only returns 16:00. Am I using the date() function wrong? PHP: date_default_timezone_set('America/Los_Angeles'); $date = date('H:i:s A'); echo "System: $date <br />"; $dbc = @mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); $q...

JavaScript to trigger PHP

Is it possible to have an input text box such that when a person types something and presses submit or enter, I can trigger PHP to submit that data to a database without leaving the page (hence the JavaScript)? ...

What is wrong with this mysql create table query?

I have a database in my production server and it works fine... What i did is took a dump of that DB and executed in my local system.. All the other tables are created except one table... So i manually inserted it, CREATE TABLE `contact` ( `contactId` int(11) NOT NULL AUTO_INCREMENT, `cRefId` int(20) DEFAULT '0', `contactFirstName`...

Encrypting a value using MySQL's AES_ECRYPT function, then passing it in a URL string, using PHP

Hi There, I need to encrypt a string using MySQL's AES_ENCRYPT function, then attach that encrypted string to the end of a URL, such that it can then be decrypted and used by a PHP script on the other end. Basically, I am encrypting the string (using MySQL's AES_ENCRYPT), I am then using PHP's rawurlencode() function to make it "URL sa...

Calculating shortest path between two "locations" via php from a mysql db

Hi, I'm trying to figure out the best method of getting/displaying the shortest distance between multiple locations. To best explain this think of it as a map and use the following below. All of the distances and paths from each location would be already put in a mysql database. Locations(Letter -> Locations it can reach. and distance/...