mysql

Insert using linq templates not returning the id - MySQL

I'm using the latest subsonic dll and the latest linq templates from github. The db i'm inserting into is MySQL. Id column on table is primary key auto increment. Versions: Subsonic.Core.dll - 3.0.0.3 - (November 18, 2009 Merged pulls from Github). LinqTemplates - July 29, 2009. MySQL.Data.CF.dll - 6.1.2.0. The row is inserted but th...

Rails/MySQL: Knowing a version number for a table, or the entire db schema

As an API endpoint, I need to provide a table schema with a version number so that the mobile developers I am working with can cache the schema until it changes again. I am automating this process, which complicates the "versioning" idea. At first I thought that I could use the latest migration number as the version # but it occurred ...

Problems with character encodings in LAMP app - UTF-8 or not?

I'm still learning the ropes with PHP & MySQL and I know I'm doing something wrong here with how character sets are set up, but can't quite figure out from reading here and on the web what I should do. I have a standard LAMP installation with PHP 5, MySQL 5. I set everything up with the defaults. When some of my users input comments to ...

Using EXISTS with MySQL

I have this simple query that works on all other database systems, but fails with MySQL: UPDATE points p SET p.userid = 5224 WHERE p.userid = 2532 AND NOT EXISTS ( SELECT 1 FROM points q WHERE q.userid = 5224 AND q.game = p.game ) I get the following error message: #1093 - You can't specify target table 'p' for up...

AJAX - timed mySQL queries (please wait screen)

I need to make an AJAX page which queries the database on page load and then every 5-10 seconds after that. In the meantime I will display some kind of waiting page (maybe with a animated gif to keep my customers entertained :) ) I am working with paypals IPN so its for while I am waiting for the transaction to clear.. most of the tim...

What datatype should I use for my column? [MySQL]

I am working on a content management system at work. I will have a column to hold the body of our users' content (HTML) and I'm not too sure what column type to use. I don't want to arbitrarily assign a maximum length but I don't know if using a LONGTEXT or BLOB field is overkill. Maybe someone could give me a tip or point me in the dir...

PHP Multiple Dropdown Box Form Submit To MySQL with OR

Hello, This question is similar to http://stackoverflow.com/questions/944158/php-multiple-dropdown-box-form-submit-to-mysql, but with a twist. Let's assume we have an HTML multi-select, which gets submitted to a PHP backend. How do you elegantly create a mysql request with an OR condition on the values of a multi-select? For instance...

PHP PDO MYSQL JOIN

I have a table called cms_page in which each page has an ID. I want to pull all the information from this table and all the information from cms_page_part table where page_id is equal to the ID i send to the page...confusing i know but here is my attempt: require '../../config.php'; $conn = new PDO(DB_DSN, DB_USER, DB_PASS); $id = (in...

running mysql_fix_privilege_tables kills my root password

I am learning mysql and ran into a problem with 'mysql.proc' missing when trying to create a stored procedure. mysql version 5.1.41. I read that running the script 'mysql_fix_privilege_tables' is supposed to fix the previous error. I am getting a strange side effect... my root password is no longer good anymore when I run this script ...

Finding end/start date in MySQL

Look at this MySQL table: +------------+-----------+ | date_key | stude_key | +------------+-----------+ | 2005-09-01 | COM | | 2005-09-02 | COM | | 2005-09-06 | COM | | 2005-09-07 | COM | | 2005-09-08 | COM | | 2005-09-09 | COM | | 2005-09-12 | COM | | 2005-09-01 | YUM | | 2005-...

PHP MySql Insert n rows at once?

How can I optimize this code? $items[0] = "Item-0"; $items[1] = "Item-1"; $items[2] = "Item-2"; $items[3] = "Item-3"; ... $items[n] = "Item-n"; foreach($items as $item) { mysql_query("INSERT INTO mytable (item) VALUES ('$item')"); } The array is just sample and the key point I like to know is how can I insert n items without quer...

generate many rows with mysql

Hi: In postgres there is a built in function generate_series() which can generate rows with number. is there a function in mysql does same thing? ...

Simulating a railway network

Hello, I wanted to simulate a railway network where there would be stations, trains and routes connecting each station. So how do you think I should attempt to model this?? I thought of using graphs taking station as vertex and edges as routes. Also since there will be tens of trains and stations and hence hundreds of routes so should I...

How to get the dependency of jobs in SQL Server?

Hi, How can we find the job dependency in SQL server. Therotically basing on the success of first one job the other job begins. Thanks a lot in advance!! ...

php while loop variable for every third div

Is their a way in a while loop to assign a variable to a class in a div, for every third item in a while loop. I am using the blueprint structure and the third div is at the end and i need to attacht a "last" class name to every third div so 3rd div 6th div 9th div and so on? /* LOOP THROUGH SHOEDATA TABLE */ $results = mysql_query("SE...

Grouping records by day in PHP/MySQL

This is a common issue that I can't find an elegant way to handle. Database contains 5000 records. I need to show these records on a page, but they need to be sorted and grouped by day. 10/11/2009 record3456 record456 record456 10/12/2009 record345234 record3456 10/13/2009 10/14/2009 record81 record8324 record983 record83...

Is there a way to search 2 or more fields at the same time?

I am looking into creating something that does this: SELECT * FROM (`tm_accounts`) WHERE CONCAT(`last_name`, `first_name`) LIKE '%o%' This, of course, doesn't work. I just want you to see the idea behind what I'm trying to emulate. last_name and first_name are two separate fields in the accounts table ...

Is there a less hacky way to do this in MySQL?

I need to get the last inserted product by a user. This is what I've came up with $query = 'SELECT id FROM products WHERE user_id = ? ORDER BY id DESC LIMIT 1'; It should work because the id is auto increment. To me though, it feels hacky. Is there a better wa...

MySQL query to replace spaces in a column with underscores

I have a MySQL database table 'photos' with a column 'filename'. I need to replace the spaces in the filename column values with underscores. Is it possible with a single/multiple query? If so how? ...

Usng enum in drupal

I have a mysql table id,name,gender,age religion( enum('HIN','CHR','MUS') ,category(enum('IND','AMR','SPA') where last 2 are enum datatype and my code in drupal was $sql="SELECT * FROM {emp} WHERE age=".$age." and religion=".$rel." and category=".$categ; $result=db_query_range($sql,0,10); while($data=db_fetch_object($result)) ...