mysql

Include table name in SQL query

What I need to do is get a table name in result from a union query with 4 selects from different tables. I need to get the id and the table name for further processing. For example i have table1, table2, table3 table4 and have a query: SELECT id from table1,blablabla UNION SELECT id from table2,blablabla UNION SELECT id from table3,bla...

MySQL Bulk Insert of Geometry fields

I have a mysql database that I'm trying to populate from a text file. The contents of my file look like (as just some examples. there are thousands of rows) 1:GeomFromText('Polygon(0 0, 1 1, 2 2, 0 0)') 2:GeomFromText('Polygon(0 0, 1 2, 2 2, 0 0)') In my schema, the first field is an integer and the second is GEOMETRY I try to load t...

What is an efficient way to check a word in a paragraph against the DB?

I have a table called keywords. It's simple, two columns - id and keyword. This table is always growing, we're always adding additional content to it. Now, we have a user-submitted 2-3 sentence paragraph. We want to check the words, in this paragraph, against all the keywords in the keywords table. What is the most efficient way...

COUNT(*) from multiple tables in MySQL

How do I go about selecting COUNT(*)s from multiple tables in MySQL? Such as: SELECT COUNT(*) AS table1Count FROM table1 WHERE someCondition JOIN?? SELECT COUNT(*) AS table2Count FROM table2 WHERE someCondition CROSS JOIN? subqueries? SELECT COUNT(*) AS table3Count FROM table3 WHERE someCondition Edit: The goal is to return this: ...

Mysql select with find_in_set with two stringlist

I got in my table this 2 rows: 1, 'Halo: Reach', 2010, ''fps','sci-fi'', '"The best game of the year".', 'Microsoft', 'Bungie', 'XBOX 360', 9.5, 'http://wuwb.com/wp/wp-content/uploads/2009/06/halo_reach.thumbnail.jpg', '', '' 2, 'FIFA 11', 2010, 'sport,soccer', '"The best soccer game ever"', 'EA', 'EA', 'PC, XBOX 360, PS3', 10, 'http:/...

User Login & Sessions :Safe or Not

DB table: login_info -------------------------------------- | login | passwd | company | -------------------------------------- |company1 | passmd5 | company1 | -------------------------------------- |company2 | passmd5 | company2 | -------------------------------------- |company3 | passmd5 | company3 | -----...

Is Regex for Form Validation if I use the following?

I know there is no harm in adding it either way but I'm curious... If I was to use htmlentities(); with ENT_QUOTES and then mysql_real_escape_string(); the variable before entering it into the Database, then just use html_entity_decode(); along with stripslashes(); to display the information... Would this still be safe and secure? ...

How to make a query which gets and lists data from the last entry ?

How do you make a query which gets data and lists it from the last entry in order of the ID adding it onto the code shown below, Example which needs add on, $data_table = "posts"; $per_page = 8; $start = $_GET['start']; $query = mysql_query("SELECT * FROM $data_table LIMIT $start,$per_pag...

mysql number of rows per hour

I have a table where each row has a timestamp. Is there a query I can run that can count the number of rows per hour. Outputting a row for each hour with a field for the number of rows encompassed? e.g id timestamp 10 2010-09-19 21:05:05 11 2010-09-19 22:05:30 12 2010-09-19 23:05:05 13 2010-09-19 23:05:05 number of rows | hour 1 ...

Connecting to mysql in ruby produces wrong number of arguments error (4 of 0) - how to debug?

I'm trying to connect to a hosted MySQL database from my Ruby script. require "rubygems" require "mysql" con = Mysql::new('host', 'user', 'pass', 'database') Running this results in: ArgumentError: wrong number of arguments (4 for 0) method initialize in untitled at line 4 method new in untitled at line 4 at top level ...

PHP/MySQL Delete image from database

Hi, I have a php script that handles the admin section/creation of pages for my site. All of the data is saved into a database table called 'isadmin'. Within this script I have an image upload form which adds images to a seperate database, 'isgallery' and then displays them back in the script/admin section. Now this all works great, but...

Is there a way to use a variable in MySql Specific comments?

I am using mysql specific code that was generated by one of mysql's GUI programs. The following comment /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER='root'@'localhost' has a hardcoded superuser in it. Is there a way that I can declare and use a variable in this mysql specific code. I can't seem to find anything on MySQL's site. ...

Display items by location. Mysql Select.

I have two MySQL tables, "locations" and items": locations `id` `name` `address` `latitude` `longitude` Now I use a MySQL SELECT that allows a user to enter in their latitude and longitude and it will sort the locations by distance. That works perfect. Now I have a list of items: items `id` `location` `title` `description` `disp...

CakePHP database query - am I overcomplicating things?

So, I need to search a real estate database for all homes belonging to realtors who are part of the same real estate agency as the current realtor. I'm currently doing this something like this: $agency_data = $this->Realtor->find('all',array( 'conditions'=> array(business_name'=>$realtor_settings['Realtor']['business_name']...

SELECT MAX() function in MySQL

I'm trying to figure out a problem I'm having with a SELECT MAX() command. I want to gather the highest value in the 'BidAmount' column, but I only want selected entries to be scanned for the highest value. For example, I want the highest 'BidAmount' to be returned where another column 'UserID' = 6. I want to know the highest 'BidAmoun...

Create mysql trigger via PHP?

I'm executing the following in PHP5.3: $sql = " CREATE TRIGGER `_photo_fulltext_insert` AFTER INSERT ON `photo` FOR EACH ROW INSERT INTO `_photo_fulltext` SELECT * FROM `photo` WHERE `id`=NEW.`id`; CREATE TRIGGER `_photo_fulltext_delete` AFTER DELETE ON `photo` FOR EACH ROW DELETE FROM `_photo_fulltext` WHERE `id`=OLD.`id`; DELIMITER |...

How to escape and unescape strings with jQuery and mySQL.

I have a field in my database that has a freetext area, and uses quotes for many things. On my website, that field is shown in a textarea, but I can't simply put the value between the textarea tags. I need to be able to set it with javascript. I use jQuery to do this, via: jQuery('#notes').val('{NOTES}'); Since the text can have sing...

mysql query help?

( SELECT u.username, u.picture, m.id, m.user_note, m.reply_id, m.reply_name, m.dt FROM relationships r JOIN notes m ON m.user_id = r.leader JOIN user u ON r.leader = u.user_id WHERE r.listener ='2' ) UNION ALL ( SELECT u.username, u.picture, m.id, m.user_note, m.reply_id, m.reply_name, m.dt FROM notes m JOIN user u ...

Inserting records into MySQL DB

Im using the follow script to insert records into my DB: $sql = "INSERT INTO fotetweets VALUES('$tweetid','$dp', '', '$username','$tag', '$twittercontent', '$twittertimestamp', '')"; mysql_query($sql); However what if $twittercontent contains the ' char, I think it will fail. Correct? If so how can I deal with it correctly? ...

mysql my.cnf for a 50gb of RAM server

Hello, I am having load problems on my database server (dual quad core 50 gb ram) load average: 4.26, 6.78, 11.25 here is the current my.cnf, can i optimize it? [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user ...