mysql

mysql_real_escape_string() completely removes string

I'm escaping all the string parameters I receive from a php form like this: $usr_name = mysql_real_escape_string($_REQUEST['usr_name']); to avoid a few problems with SQL Injection. But when I my string back from the function, I end up with nothing. Also I keep getting this weird warning in my PHP log: PHP Warning: mysql_real_escap...

How to store special characters (', ", &, etc) in mysql database, so they can be searchable?

Im having a small problem with storage of special characters like quotes, double quotes and ampersands. I put every POST request through mysql_real_escape_string(), and when I add a string like "That '70s Show" it gets stored as "That '70s Show" in the mysql DB. When I echo it out, it works fine... but when I try to run a % $string ...

a job to delete rows older than 3 months in mysql database

Hi, We use mysql server as a centralized logging system and I want to have a job to delete\clean the table entries that are more than 3 months old regularly. What is the best way to do this? Thanks in advance. -hinling ...

mysql order by select -- possible?

Here is my table setup: mysql> describe a; +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | x | int(11) | YES | | NULL ...

PHP: MYSQL COUNT query with linked tables

I am looking for a cleaner way to do this. My code works, but I know it can be better. I have three tables: one with a list of Category Groups, One with a list of categories that are linked to category groups, and one with a list of news stories that are linked to the categories. I need to loop through all of the names of the Category G...

Using Prepared Statements and Connection Pooling together in MySQL

Presently, for each query, a Prepared Statement is created and reused. I am not using any connection pool. C3P0 is a widely recommended library for the same. But, as a PreparedStatement is tied to a connection. In the pooled environment, the connections are returned to the pool, effectively making PreparedStatement unusable. Am I corr...

ejb sql [select * from where A & 1]

Hello, i will use a query like this in ejb ql: Select * from whe A&1; But i have the following error: line 1, column 171: unexpected char [&]. How can i use this operator to check the bits in a row? ...

Calling MySQL exe using PHP exec doesn't work

I have an extremely simple script with PHP exec, calling mysql command: $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'mypass'; $db = 'job'; $file ='job_create.sql'; $mySQLDir='"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql"'; if ($dbpass != '') { $cmd = $mySQLDir.' -h '.$dbhost.' --user='.$dbuser.' -...

Refresh a PHP page for every predefined seconds

I would like to load/refresh a particular page for every 10 secs to view updated data's fetched from Database. I used META for doing it <META HTTP-EQUIV=Refresh CONTENT='10; URL=livedata.php'> But still i agree we also do this by using : Javascript to load a div by settimeout Ajax dynamic refresh Would be great ...

i hav php5.2.9 +mysql5.1+apache2.2.6 installed on win xp .iam not able get php connect with mysql .

i have php5.2.9 +mysql5.1+apache2.2.6 installed on win xp. I'm not able get php connect with mysql. here is the code.` <? php $link=mysql_connect('localhost','root','godismine'); if(!$link) echo "fail"; else echo "success"; mysql_close(); ?> i don't think there is any error in code. phpinfo is showing mysql support so it can...

Suggestions on retrieving related data across databases with different logins?

I have an array of user ids in a query from Database A, Table A (AA). I have the main user database in Database B, Table A (BA). For each user id returned in my result array from AA, I want to retrieve the first and last name of that user id from BA. Different user accounts control each database. Unfortunately each login cannot have ...

PHP MySQL syntax for inserting date,time

Hi, I am trying to insert to date,time fields using a php script but I am getting a syntax error. Can someone please tell me, where I am doing the mistake. Thanks fellows INSERT INTO calendar(event,from,to,day) VALUES ('".$_REQUEST['event']."', '".$_REQUEST['from_time']."', '".$_REQUEST['to_time']."', '".$_REQUEST['date_event']."') ...

MySQL primary key column type for large tables

I'm working on the project with relatively big DB table(700K rows). Mistake, I've made while designing DB schema. When rows were increasing, I had to increase ID's column type bigint(x). Now it is bigint(44). I afraid of setting high x value because I thought that it can significantly slows down performance. Maybe I'm wrong.. Please h...

Find Rows whose date range contain a given date

Hello, I am looking at the following problem: My group table contains two fields: ArrivalDate and DepartureDate. Now I want to find all groups who are present at a given date. Eg. the SQL query is passed a date, and I want to find every group where this date falls into the range ArrivalDate to DepartureDate. Thanks a lot! Martin ...

MySQL: Fulltext indexing previous records?

Hi All, I added a FULLTEXT index on three of my columns, after I already had a bunch of records...does that mean the FULLTEXT won't index them and will only index records inserted after the FULLTEXT index was added? If so, anyway to reindex the whole database? ...

How do I add more than one row with Zend_Db?

I have an array with information which looks more or less like this: $data[] = array('content'=>'asd'); $data[] = array('content'=>'asdf'); And I want to add both entries into the Database. $db->insert('table', $data); does not add both entries. What am I doing wrong? Do I have to use Zend_ Db_Table? $data = array('content'=>'asdf...

MySQL Fulltext Searching ideographic (Asian) characters

I have a database full of Asian-character filled records (Chinese, Japanese, and Korean) alongside those with Latin-character filled records (English, Français, you name it), and I want to perform fulltext searches on them. MySQL http://dev.mysql.com/doc/refman/5.1/en/fulltext-restrictions.html%29">says: Ideographic languages such a...

mysql timestamp only on create

I use timestamp on mysql 5.x (with PHP) to remember event times. During development I had to update the table with a query that changes something in all columns. The timestamp was then reset to current time. How can I make timestamp change only on inserts and not on updates or replace ? ...

PHP/MySQL Update code

I'm trying to call a row update from php to an mysql database. It fails out but when i try to call an insert new row formated just the same it works. $result = mysql_query("INSERT INTO auth (username, password, studycode, description, server) VALUES ('$username', '$password', '$studycode', '$description', '$server')"); but this code f...

What's the best way to implement a "soft" save or modification workflow?

I'm working on a MVC based web app on LAMP that needs some records to be modified only under the approval of a "superior" user. (The normal user can submit changes but they get applied only after this approval) There are is only a table in which this would have to take place, say "events": EVENTS - id - name VARCHAR - start_date DAT...