mysql

GPS radius search with Php 5 and MySQL

I have a PlaceID | PlaceName | Lat | Lon as my database structure. Say I have my current latitude and longitude as $lat = "37.331862"; $lon = "-122.029937"; I want to find places that are in a 100 feet radius. Might allow the user to choose. ...

MySql Error 1064 - Created using MySQL WorkBench

I created this using MySQL WorkBench CREATE TABLE IF NOT EXISTS `bakasura_new`.`cities` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR(255) NOT NULL COMMENT 'City Name' , `short_name` VARCHAR(255) NOT NULL COMMENT 'Short Name' , `country_id` INT(11) UNSIGNED NOT NULL , PRIMARY KEY (`id`) , INDEX `fk_cities...

Attentance management using MySQL Procedure how to do?

Hi Friends, I need to develop a SQL Procedure,i am having 2 table one,with following details userid h1 h2 h3 h4 h5 h6 h7 date and another table with following details useid per why i need a procedure,because, the data to the first table is of follows userid h1 h2 h3 h4 h5 h6 h7 date 1 p p p p p p p 2010-10-10 while data is updat...

php + mysql, order by name + starting at specific id

MySQL: id | name | ------------ 1 | Joe | 2 | Craig | 3 | Shawn | 4 | Ryan | 5 | Seth | PHP: $a = mysql_query("SELECT * FROM table_name ORDER BY name DESC"); what I want to do though is, I want to start at id: 3, so it should output: 3,4,5,1,2 ...

Show all rows in MySQL that contain the same value

Hi there, I have a MySQL database: ID | Name 1 | Bob 2 | James 3 | Jack 4 | Bob 5 | James How would I return a list of all the columns where the same name appears more than once, eg, I'd like to return this: 1 | Bob 2 | James 4 | Bob 5 | James I've written a count query: SELECT Name, COUNT(Name) AS NumOccurrences FROM ...

Why do i have a mapping exception?

Hi! Right now i want to make a small java application, in order to learn the hibernate framework. But it gives me the org.hibernate.MappingException: Repeated column in mapping for entity: model.Book column: author (should be mapped with insert="false" update="false"). And if I delete the author column mapping from entities.hbm.xml, the...

how to permanently activate/set the global event_scheduler to 1 in mysql

Hey guys, I have added a event to my mySQL db and it works fine, but the thing that is bothering me is that every now and then I have to set the mysql global variable to 1 so that my event is active.I log in as root user and have complete privileges (I use it for practice purpose) Every time I log in to my mysql server I have to exec...

MYSQL query for searching through ALL the fields?

As dumb as it sounds, is there a way to do something like this: select row_id from mytable where * like '%searched_text%'; By * here I mean "all the fields" in the table, instead of me specifying them one by one... ...

mysql fulltext MATCH,AGAINST returning 0 results

I am trying to follow: http://dev.mysql.com/doc/refman/4.1/en/fulltext-natural-language.html in an attempt to improve search queries, both in speed and the ability to order by score. However when using this SQL ("skitt" is used as a search term just so I can try match Skittles). SELECT id,name,description,price,image, MATCH...

MySql PHP tree view

I am building a web application for an affiliate program and on the users table I have this structure: Based on this MySql table I want every user to be able to see his tree, so I need this query: The user to see who recruited him and who was recruited by him and I don't want the query to show other users that are recruited by the...

SQL query for multiple tag inclusion

I´m net very good in explaining this. But i do my best: I've made a tagging tool for people. Some people contains more then 1 tag, and i need to get those but dont know how to write the SQL query for multiple tag inclusion. I know you can't write WHERE conditions like this: LOWER( peoples_tags.tag_id ) = 'outside' AND LOWER( peoples_ta...

How can I get column types width MySQLdb?

Hello, how can I get text description for column types with MySQLdb? I know that cursor.description contains number which represents type of column and also there is module FIELD_TYPE.* with int constants like FIELD_TYPE.ENUM=247 For example how can I get name of column type if I know that it is '3'? ...

mysql join count

I have two table Table Leave ID | TYPE 1 Annual 2 Sick 3 Unpaid 4 Marriage Table LeaveData IDLEAVEDATA | LEAVETYPE* 1 1 2 1 3 2 4 2 LEAVETYPE is foreign key (refer to ID in table leave) How do i count the occurence of ID in table Leave...

Only two people on a certain page at a time?

I'm not really sure how I would implement this along with a fair amount of security. I want to be able to know if someone or two people are on based on a mysql row. These two people are totally anonymous. I do have an xmpp server set up but I'm not sure how that would integrate into the mysql and I already have a separate login system fo...

mysql dropping all indexes from table

I have a mysql database that runs for some time now with many changes on it. Lately i looked over it and i noticed that in some cases i have doubled index on the same field. Some Indexes are missing, And mainly there is a huge mess in all the indexes. I wants to drop all indexes from a table. Later on i have a prepared script that will ...

Use multiple columns as unique identifier for mysql

I have a mysql table with the following columns: group_id game_id user_id message last_update I want to make it so that no two rows can exist where the value of group_id for row x is equal to the value of group_id for row y AND the value of user_id for row x is also equal to the value of user_id for row y. So, for example, let's say ...

CodeIgniter Working with multiple Databases

Hello, I am trying to list all the mysql databases and their respective tables, I am currently using this, but can anybody recommend if there is any better way. $q = $this->db->query('SHOW DATABASES'); $databases = $q->result_array(); foreach($databases as $db) { $this->db->query('USE '. $db['Database']); $q = $this->db->query('S...

should the following sql query data normalization work?

CREATE TABLE IF NOT EXISTS `mydb`.`MATCHES` ( `idMatch` INT NOT NULL , `idChampionship` INT NOT NULL , `idWinningTeam` INT NOT NULL , `idWLoosingTeam` INT NOT NULL , `date` TIMESTAMP NULL DEFAULT NULL , `goalsWinningTeam` INT NULL DEFAULT -1 , `goalsLoosingTeam` INT NULL DEFAULT -1 , `played` CHAR NULL DEFAULT 'Y' ...

Find time difference in minutes with php or mysql

I have two data inputs where you can enter start time and finish time. for example start_time 13:00 finish_time 14:40 The entry will be always the format of HH:MM. I'd like to find the time difference, in this case 100 minutes. What is the best way to do it? ...

Why returns the first element of an array only?

$count =0; $result1 = mysql_query("SELECT fwid FROM sbsw WHERE fword = '".$searchText."'"); while ($result2= mysql_fetch_array($result1)) { $result3 = mysql_query("SELECT fsyn FROM wrsyn WHERE fwid = '".$result2[$count]."'"); $result4= mysql_fetch_array($result3); print $result4[$count].'<br>'; $count++; } mysql_free_result($result1)...