mysql

How to "escape" a whole String - an SQL query

When I add a relative path to an SQL query as a String all the \ get removed. I am trying to add the String ("pics\\"+onlyFile) and as you can see I have escaped the \ character so I don't understand why it is being removed. onlyFile is a variable containing a file name. The value of the "src" variable (the one I am discussing) just be...

How do you change the default collation of MySql for rake db:create:all in rails?

In my rails app I am running rake db:create:all in order to create the databases and I am prompted that the collations do not match. Is there a way to change the default MySQL collation? If not what is the best way around this? Thanks ...

How can "set timestamp" be a slow query?

My slow query log is full of entries like the following # Query_time: 1.016361 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 SET timestamp=1273826821; COMMIT; I guess the set timestamp command is issued by replication but I don't understand how set timestamp can take over a second. Any ideas? ...

Encoding problem wirh JDBC and MySQL

I'm grabbing data from RSS-channels, sanitize it and save in the database. I use java, tidy, MySQL and JDBC. Steps: I grab RSS-records. It's OK. I sanitize html with tidy. Here is one transformation. Tidy automatically converts strings like "So it’s unlikely" to "So it’s unlikely". I save this string to the table MySQL scheme ...

Mysql many to many problem (leaderborad/scoreboard)

Hi all! I'm working on a small project in regards of the upcoming World Cup. I'm building a roster/leaderboard/scoredboard based on groups with national teams. The idea is to have information on all upcoming matches within the group or in the knockout phase (scores, time of the match, match stats etc.). Currently I'm stuck with the DB in...

Parameterizing file name in MYSQL LOAD DATA INFILE

Hi Is there a way to dynamically specify a file name in the LOAD DATA INFILE? Can it be parameterized like for instance (syntax may be incorrect) LOAD DATA INFILE '$filename'? ...

How can I make PHP scripts timeout gracefully while waiting for long-running MySQL queries?

I have a PHP site which runs quite a lot of database queries. With certain combinations of parameters, these queries can end up running for a long time, triggering an ugly timeout message. I want to replace this with a nice timeout message themed according to the rest of my site style. Anticipating the usual answers to this kind of ques...

GROUP BY a date, with ordering by date.

Take this simple query: SELECT DATE_FORMAT(someDate, '%y-%m-%d') as formattedDay FROM someTable GROUP BY formatterDay This will select rows from a table with only 1 row per date. How do I ensure that the row selected per date is the earliest for that date, without doing an ordered subquery in the FROM? Cheers ...

Best way to add a column in mysql query

Can any one please let me know that, I need to add a column dynamically when executing mysql query Table: Table1 -------------------------- col1 col2 col3 -------------------------- Test OK Test3 Test OK Test5 Test OK Test6 from the above example i need to introduce "col2" as new column and ...

mysql_real_escape_string is storing the data in the database along with backslashes

When i am using mysql_real_escape_string on my unescaped strings the data in the database is storing with the backslashes which should not happen. I have magic_quotes_gpc OFF not sure why this is happening. Any idea ? Is there any setting in the mysql database which needs to be modified. I am not using addslashes any where in the code...

need some thing weird to do in MySQL

I am trying to get the elements from the database where the status begins from either w or c.. i tried this query but the page gets blank.. $test1=mysql_query("select * from railways where status like('w%') || like('c%')"); how is this query written in that case??? ...

How to get a list of MySQL views ?

I'm looking for a way to list all views in a database. Initially I found and tried an answer on the MySQL forums: SELECT table_name FROM information_schema.views WHERE information_schema.views.table_schema LIKE 'view%'; How ever this doesn't work, returning an empty set. (I know they're in there!) These also fail: mysql> use inform...

MySQL data integrity?

In various PostgreSQL vs. MySQL comparisons I've seen many mentions of problems with data integrity in MySQL. Is this currently still an issue? Particularly for web applications using MySQL, are there tricks to making sure data integrity is maintained? Or with new versions is this true 'out of the box' with no additional configuration r...

MySQL VARCHAR like Data type with MIN and MAX character lengths

Looking for a data type that is similar to VARCHAR as to the values I would like, but is there a way to have a MIN/MAX character length? VARCHAR(6,10) This would have a minimum of 6 characters and a maximum of 10 characters. ...

Increase and decrease row value by 1 in MySQL

Hi I have a MySQL database table "points" the user can click a button and a point should be removed from their account, the button they pressed has an ID of another user, therefore their account must increase by one. I have it working in jQuery and checked the varibles/posts in Firebug, and it does send the correct data, such as: useri...

PHP News Feed Database & Design

I'm designing a News Feed system using PHP/MySQL similar to facebook's. I have asked a similar question before but now I've changed the design and I'm looking for feedback. Example News: User_A commented on User_B's new album. "Hey man nice pictures!" User_B added a new Photo to [his/her] profile. [show photo thu...

Send comma delimited CSV through SFTP?

I am collecting registration information on my site and need to figure out how to pass all data stored in the MySQL DB (or just portions of it) as a CSV file through an SFTP so our partners can access the information. The pages are built using PHP. I literally have no idea how to do this and am hoping somebody has experience doing so. ...

MySQL developer here -- Nesting with select * finicky in Oracle 10g?

I'm writing a simple diagnostic query then attempting to execute it in the Oracle 10g SQL Scratchpad. EDIT: It will not be used in code. I'm nesting a simple "Select *" and it's giving me errors. In the SQL Scratchpad for Oracle 10g Enterprise Manager Console, this statement runs fine. SELECT * FROM v$session sess, v$sql sql WHER...

adodb .FIND question

i am using excel to connect to a mysql database i am doing this: rs.Find "rowid='105'" If Not rs.EOF Then cn.Execute "delete from batchinfo where rowid='105'" and it works well however, i need to be able to match data on multiple columns for example like this: rs. find "rowid='105'" and "something='sometext'" and "somethingelse='m...

invalid use of rs.filter property?

i am connecting to mysql using adodb from excel i am doing: dpath = Range("B2").Text atime = Trim(Range("B3").Text) rtime = Trim(Range("B4").Text) lcalib = Trim(Range("B5").Text) aname = Trim(Range("B6").Text) rname = Trim(Range("B7").Text) bstate = Trim(Range("B8").Text) rs.Filter "datapath=dpath and analystname=aname and reportname...