mysql

Does using magic_quotes() affect the use of mysql_real_escape_string()

If I have magic_quotes switched on and I use mysql_real_escape_string, will the tring be double escaped? Will it cause problems? I assume so based on the get_magic_quotes() function but just seeking confirmation. (PS it's easier to ask this question than test it in my office with all the security we have in place - It takes me 10-15 to...

does mysql stored procedures support data types like array, hash,etc?

i am creating a mysql function which takes in a varchar and change it to a int based on a mapping table. select name, convert_to_code(country) from users where sex = 'male' here, the convert_to_code() function takes in a country name (e.g. Japan, Finland..) and change it to country code which is an integer (e.g. 1001, 2310..) based on...

MySQL efficiency as it relates to the database/table size

I'm building a system using django, Sphinx and MySQL that's very quickly becoming quite large. The database currently has about 2000 rows, and I've written a program that's going to populate it with another 40,000 rows in a couple days. Since the database is live right now, and since I've never had a database with this much information i...

complex sql which runs extremely slow when the query has order by clause

I have following complex query which I need to use. When I run it, it takes 30 to 40 seconds. But if I remove the order by clause, it takes 0.0317 sec to return the result, which is really fast compare to 30 sec or 40. select DISTINCT media.* , username from album as album , album_permission as permission , user a...

php,mysql and Zend lucene

how to index mysql table and search using zend lucene? Pls provide with code and posible with table structure... thanking all... ...

Mysql Server Optimization

Hi Geeks, We are having serious MySQL(InnoDB) performance issues at a moment when we do: (10-20) insertions on TABLE1 (10-20) updates on TABLE2 Note: Both above operations happens within fraction of a second. And this occurs every few (10-15) minutes. And all online users (approx 400-600) doing read operation on join of TABLE1 & TA...

How can i fetch all data from any table of a test DB and DUMP it on the production db in same tablename(structure)?

How can i fetch all data from any table of a test DB and DUMP it on the production db in same tablename(structure)? in Mysql using PHP code? Please help me.. EDIT I need PHP to do this for me using MySQL Queries ...

Mysql date : 0000-00-00 00:00:00

In my query: $cselect = mysql_real_escape_string($_POST['cselect']); --------------- --------------- $sql = sprintf("INSERT INTO content (id, catID, title, abstract, body, status, published, date, description_meta, keywords_meta) VALUES ('', '%s', '%s','%s','%s','%s','%s','%s','', '' )", $cs...

Returning multiple aggregate functions as rows

I need some help formulating a select statement. I need to select the total quantity shipped for each part with a distinct color. So the result should be a row with the color name and the total. Here's my schema: create table s ( sno char(5) not null, sname char(20) not null, status smallint, city char(15), pri...

Preg_match differences?

Hi, i want to ask, what is the meaning or difference between these two line? if( preg_match_all('/\#([א-תÀ-ÿ一-龥а-яa-z0-9\-_]{1,50})/iu', $message, $matches, PREG_PATTERN_ORDER) ) { if( preg_match_all('/\#([а-яa-z0-9\-_\x{4e00}-\x{9fa5}]{1,50})/iu', $message, $matches, PREG_PATTERN_ORDER) ) { and what does the number 3 mean in this l...

Mysql query different group by

Hello i have a products table that contains normal products and configurable product It has a basic stucture of: id name price configurable ('yes', 'no') id_configuration Normal products have configurable no and 0 as id configuration, and configurable products have it set to yes and have the same id_configuration value. The curren...

Mysql: Convert DB from local time to UTC

I need to convert an existing (datetime fields) db from local time ut UTC. The values are stored ad datetimes on a server with time zone CET (+1) (with summertime +2). When selecting data I use UNIX_TIMESTAMP(), which magically compensates for everything, ie, time zone shift and dst (if i've read the docs right). I'm moving the db to a...

Debugging SQL queries step by step using MySQL Proxy

I'm using MySQL proxy and the following Lua script to view all the queries that will be send from my program to MySQL. function read_query( packet ) if string.byte(packet) == proxy.COM_QUERY then print(string.sub(packet, 2)) print() end end Is it somehow possible to pause the script each time a query had been r...

how to link table to table

I am a comsci student and I'm taking up database now. I got a problem in or should I say I dont know how to link table to table. It is not like you'll just use a foreign key and connect it to the primary key. The outcome should be like this: In the table Course there are three fields namely "course_id", "Description" and "subjects". ...

NDbUnit MySQL Assembly Version Conflict

I am trying to use NHiberanate with NDbUnit but I cannot as NDbUnit tried to load MySql.Data version 1.0.10.1 and NHibernate tries to load version 6.2.2.0 and I can only reference one of them. Here is the error I get when i try to run NDbUnit Set Up System.IO.FileLoadException: Could not load file or assembly 'MySql.Data, Version=1.0.1...

Return Total Rows from a table.

while($row=mysql_fetch_array($result)) { $output .= "<Reports>"; $output .= "<ProjectName>"; $output .= $row['ProjectName']; $output .= "</ProjectName>"; $output .= "<ProjectCount>"; $output .= $row=mysql_fetch_array($result) $output .= "</ProjectCount>"; $output .= "</Reports>"; } My SQL Query. SELE...

mysql statement with nested SELECT - how to improve performance

This statement appears inefficient because only one one out of 10 records are selected and only 1 of 100 entries contain comments. What can I do to improve it ? $query = "SELECT A,B,C, (SELECT COUNT(*) FROM comments WHERE comments.nid = header_file.nid) as my_comment_count FROM header_file Where A = 'admin' " edit: ...

MySQL: How to check if a string is a valid DATE, TIME or DATETIME

When I try to put a value into a DATE field which is invalid, MySQL seems to use 0000-00-00 instead. Is there a way I can do this "check" without updating a DATE field? And to do it from for example PHP? Like, is there a way I can query the MySQL server and ask "Hey, is this DATE, TIME or DATETIME valid to you?" Or is there maybe an ev...

Multiple Rails app, single MySQL database

I intend to have multiple Rails apps each for site.com, api.site.com, admin.site.com. All apps will access the same tables from one single MySQL database. Apps and database runs in the same server. Is there any settings in Rails, ActiveRecord or MySQL that I need to be concerned about for above access scenerio? Thanks Running: Rails 2...

How to create HTML tables from MySQL?

Hi Guys, I'm building a part of a system where the user can define "views" from a mysql database. I want some way of generating simple HTML tables/reports from the data, but not just plain output of sql queries- simple joins probably isn't enough. Any ideas? Just to be clear: I know perfectly well how to create a HTML table with PH...