mysql

MYSQL WHERE-IN Subquery Runs Forever

I have a MySQL table. Let's call it Widgets. The Widget table has 3 fields: id, type_id, and name. I want, in one query, to get all the widgets that share a type_id with the Widget named 'doodad'. I've written 2 queries: Give me the type_id of the widget with the name 'doodad'. Give me all widgets with that type_id. This works. Each ...

Wanting to have scrolling comments box pulling from mySQL using ajax

Hi guys, I haven't started on this yet because I'm still pretty new, and I want to be sure there are not any issues with what I'm wanting to accomplish. In it's simplest form, I want to have a div that will display a text quote that is pulled from a mySQL database. Every 5 seconds, the quote will fade out and the next will fade in. ...

Valgrind Possibly Lost - MYSQL

I've recently been running one of my apps through Valgrind but there's a few MYSQL related leaks I can't fix. I put the offending code in the most basic form and tested that; I got the same leaks. Should I just disregard them or am I doing something wrong? Code: #include <stdio.h> #include <stdlib.h> #include <mysql/mysql.h> int main...

Is this a safe/strong input sanitization function?

This is the sanitization function used in a book I recently learned from - Sams Teach Yourself Ajax, JavaScript, and PHP All in One. I've been using it on my own PHP site. Is it safe for real-world usage? function sanitizestring($var) { $var = strip_tags($var); $var = htmlentities($var); $var = stripslashes($var); return mysql_...

Mysql Regex Problem

I want to find only those rows whose column 'col' c doesn't contain characters from a-1. So I don't know how to write script. I've succeed to write script which is opposite to this. Thanks in Advance. select * from tbl_comment c where c.`message` regexp '{a-z}'; I need the script which will be opposite to this. I've tried "not re...

PHP & MySQL dynamic page display problem.

let say I have a post that can be found on page post.php?pid=60 but if the user changes the url to post.php?pid=95 the page is displayed all weird is there a way I can have them redirected to another page that says post dosn't exist or something? If so how would I do this? And is this the best way to handle none existent pages? If not w...

return count 0 with mysql group by

database table like this ============================ = suburb_id | value = 1 | 2 = 1 | 3 = 2 | 4 = 3 | 5 query is SELECT COUNT(suburb_id) AS total, suburb_id FROM suburbs where suburb_id IN (1,2,3,4) GROUP BY suburb_id however, while I run this query, it doesn't give CO...

Does anyone see anything wrong with this? Mysql+PHP Error

I'm running the following line : mysql_query("INSERT INTO tags SET tag = '".$onesearch."', SET date = '".date('d-m-Y')."'") or die(mysql_error()); ...and its dieing saying this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right synt...

how to stop mysqldump from recording as slow queries

Hi, Im recording slow queries longer than 2 seconds. That is running fine except, the daily backups also get recorded in slow log because 1) mysqldump calls select * from xyz (big table); and 2) when inserting the backup into a new db (yesterday's backup, sundays backup, etc). How to prevent mysqldump from logging slow queries? Is there...

Selecting table using case clause in from clause

I have 8 table that contain different specific value for computer peripheral they are glpi_device_ram, glpi_device_hdd, glpi_device_gfxcard, glpi_device_sndcard. Each table has the same designation column in each table that contain device name. i have table glpi_computer_device that contain FK_device that contain id for each 8 table abov...

Getting variable from first table in Left Join

So I have a basic question of how to get a variable from the a table of a outer join statement. mysql_query("SELECT * FROM (cal_events left join cal_cities on cal_events.city_id = cal_cities.id) left join cal_genre on cal_events.genre_id = cal_genre.id WHERE start_day BETWEEN DATE_ADD...

PHP & MySQL display problem.

My script is supposed to find all related posts from the current posts categories which can be one or many categories but I want to stop the script from displaying the current post as a related post. How can I do this? And where do I add it to my script? Here is my MySQL table. CREATE TABLE categories ( id INT UNSIGNED NOT NULL AUTO_I...

How to install FFMPEG in PHP to encode video

I want to know how this FFMPEG to integrate in php. I want to encode video. I got this link earlier to refer http://youtubeclone.wordpress.com/2007/05/26/how-to-convertencode-files-to-flv-using-ffmpeg-php/ but dont know where to point this $ffmpegPath = "/path/to/ffmpeg"; $flvtool2Path = "/path/to/flvtool2"; here we have to give the...

MySQL sharding and primary key performance in InnoDB

I run a lyrics web site and I have a single table that basically keeps an index of songs; essentially primary key with auto increment (song_id), artist, and song title. The lyrics themselves are stored across 10 shards, with each shard based on song_id MOD 10. There are two columns: song_id and song_lyrics. The song_id field is primary ...

Estimating number of read/write queries in mysql

Does Mysql provide any variable through which I can get the number of read and write queries served since server restart? Just want to estimate the Requests per second served by my db. ...

How to return time from IF-clause

Hi, I'm having trouble outputting a time from a conditional select. The aim is to subtract 30min if the time is < 6h. Here is a demo: SELECT TIME(start_time) AS start, TIME(end_time) AS end, TIME_TO_SEC(TIMEDIFF(end_time, start_time)) AS durationSecs, IF( TIMEDIFF(end_time, start_time) >= "06:00:00", SUBTIM...

Creating indexes for 'OR' operator in queries

I have some MySQL queries with conditions like where field1=val1 or field2=val2 and some like where fieldx=valx and fieldy=valy and (field1=val1 or field2=val2) How can I optimize these queries by creating indexes? My intuition is to create separate indexes for field1 and field2 for first query as it is an OR, so a composite index ...

Which dbExpress ServerCharSet do I need for utf8 data in MySQL 5?

We tried ServerCharSet=utf8 and ServerCharSet=UTF8, based on information found in newsgroups - but still, special characters / Umlauts do not appear correctly in the client data. We use Delphi 2009 and the built-in dbExpress driver. Field data is retrieved using AsWideString. ...

mysql string searching algorithm query help

Hi, i have a string table in my mysql/php table. id | str 3 | 2,4,5,6 4 | 7,8,9,14 5 | 3,1,16 if i search with LIKE keyword for example "1" the result is below and its wrong id | str 4 | 7,8,9,14 5 | 3,1,16 But the expected result is id | str 5 | 3,1,16 since "1" is only in the above row but unfort...

How to check whether a string is not null & empty?

I my web app, i have search fiedl where i get some string and a combo box. So, i am sending two arguments to the remote function. I want to check the user input is not null and not empty. So, then i can construct a valid query. public ArrayList findEmployees(String str, int dep)throws ClassNotFoundException, SQLException{ System.o...