mysql

Escaping field names in PDO statements

I am currently building a query where both the field and value parts possibly consist of user inputted data. The problem is escaping the fieldnames. I'm using prepared statements in order to properly escape and quote the values but when escaping the fieldnames i run into trouble. mysql_real_escape_string requires a mysql connection r...

Making sure a url parameter is not present, using regex

I need 2 regular expressions that I will use in MySQL OK if one of the url parameters equals something (e.g page_id=5) I came up with this: ^https?:.*[?&]page_id=5([#&].*)?$ OK if a certain parameter is not present in the url (e.g do not match [?&]page_id=) This is the one I need help with. This functionality is part of a bigger pro...

Software licensing question using ProgreSQL or MySQL

Can I use PostgreSQL or MySQL to write an application for my intranet for my company? Will I be violating any licensing if the software is not for distribution and only used within my company? Thank you ...

MySQL index creation is slow (on EC2)

I have a fairly simple table requestparams ( requestid varchar(64) NOT NULL, requestString text, ) ENGINE=MyISAM; After populating the table with "LOAD DATA", I am changing the schema and making "requestid" the primary key. There are 11 million rows in this table and the data size is less than 2GB (size of the MYD file.) The ind...

No duplicates in SQL query

I'm doing a select in MySQL with an inner join: SELECT DISTINCT tblcaritem.caritemid, tblcar.icarid FROM tblcaritem INNER JOIN tblprivatecar ON tblcaritem.partid = tblprivatecar.partid INNER JOIN tblcar ON tblcaritem.carid = tblcar.carid WHERE tblcaritem.userid=72; Sometimes I get duplicates of tblcaritem.caritemid in the result. ...

Simple Javascript MYSQL checking function

Hi friends, I'm not very skilled in web developing by the moment but i've managed to create a web application with PHP that queries a MYSQL Database and shows data in HTML. I would like to check if the table has changed using a timestamp field that I already created and that updates on every insertion. Comparing two timestamps (the pre...

Transaction with two sql insert

I have two sql insert to do (say for examples in tables A and B), they are in a transaction because I want the database to remain consistent, that is, a tuple in A must have references in B. In the second insert I need the id that comes from the first, but I don't get this id until I make a commit on the transaction. So I'm stuck. I do...

Zend_Db: Filter results

Hey there, does anyone know the best practice to filter all the results I'm getting from any Zend_Db class. It's because I've got a whole database with timestamps as INTs and I want to change the fields into Mysql's TIMESTAMP without having to change too much code. It would be nice if I could apply a global database filter using strto...

Have you used any databases only hosting service?

Are there any database only hosting services? I need a MySQL only hosting service but I couldn't find any. All mysql hostings are part of a package e.g. PHP + MySQL. I need it for development purposes. Having my PHP files somewhere else I can remotely connect to this mysql server and once an application is ready I can put that on a se...

Apache -> MySQL multiple connections vs one connection

I've been thinking, why does Apache start a new connection to the MySQL server for each page request? Why doesn't it just keep ONE connection open at all times and send all sql queries through that one connection (obviously with client id attached to each req)? It cuts down on the handshake time overhead, and a couple of other advantag...

Zend Framework normal query to Zend_Db_Table

Here is my query: "SELECT * FROM `posts` WHERE MATCH(title, text) AGAINST('".$word."' IN BOOLEAN MODE)"; How can I do this with Zend_Db_Table, I mean($this->select()...) Best Regards! ...

How to connect to multiple databases in a single PHP page?

Possible Duplicate: How do you connect to multiple MySQL databases on a single webpage? If I want to connect to one db do some query, and then later do another query from another DB. How do I do it? Do I just mysql_pconnect("host:3306", "user", "password") or die(mysql_error()); mysql_select_db("Test") or die(mysql_error());...

How to read data from an XML file and store it into database (MySQL)?

I need to get data from an XML file and store it into a MySQL Database. I am thinking of using a SAX Parser for parsing the data but I am not sure of how to store data efficiently into database, I am thinking of few technologies like JDBC and Hibernate but I wanted to ask about what would be the efficient way of doing it? Note: Here pro...

Best database design for a "sensor system"

I'm doing a schoolwork and.. I have to do a vehicle tracker system. I thought of these three designs. What do you think? My database schemas (png at ImageShack ~99KB) http://img390.imageshack.us/img390/3781/des.png Opinions? ...

What language to use - simple form + MySQL + admin page

Hi everyone, Apologies if this is not the right place to ask but here goes... What should I use to create a simple web application for our website? I'm the IT guy for a small non-profit. On our website we have a page with a large form in which users can fill out information on an application form. The information gets sent to a MySQL ...

Java and SQL : return null or throw exception?

This is another debated subject, but this time i am searching only for the simple and documented answers. The scenario : Let's assume the following method: public static Hashtable<Long, Dog> getSomeDogs(String colName, String colValue) { Hashtable<Long, Dog> result = new Hashtable<Long, Dog>(); StringBuffer sql = null; Dog dog...

Is there a procedure required for updating mysql column types?

I'm trying to optimize my mysql tables (MyISAM) and I changed some column types with phpmyadmin, mostly from int to smallint, medium int, etc. After I did that mysql really bogged down and the server load went really high. I did this on a live site, which I figured was the problem, but even after putting the site into maintenance mode an...

Database: SQL Pagination ?

I'm using MySQL. I want to limit the amount of rows fetched from the database. I have a SQL statement that returns 10,000 records which are all relevant to the search. SELECT colA, colB, colC FROM xyzTable WHERE ... ORDER BY colA I know how to use the TOP statement to return the TOP x number of rows fetched but how do I fetch rows r...

Three potential db designs

I've been considering three similar database designs, but haven't come up with a very strong for or against regarding any of them. One huge table for content content: int id, enum type, int parent_id, varchar title, text body, text data This would assign each row a type (news, blog, etc), and have fields for common/standard/searchab...

Mysql Adding column and Foreign keys

In mysql, can I add a column and foreign key in the same statement? And what is the proper syntax for adding the fk? Here is my SQL: ALTER TABLE database.table ADD COLUMN columnname INT DEFAULT(1), FOREIGN KEY (fk_name) REFERENCES reftable(refcolumn) ON DELETE CASCADE; ...and the accompanying error message: You have an error i...