mysql

How Can I SELECT Similar Rows in Two Different Tables in MySQL (Is It Possible?)

I've got two tables and I'd like to grab all of the schools where there is a similarly spelled school or the same school. For example: my_table_a: School             Olde School         New School     Other, C.S. School   Main School Too Cool for School my_table_b: School Old School New ES     Other School Main School  Hardkn...

How to subtract the same amounts to all values in a column?

I was wondering if I can subtract the same value (offset) to all values of a specific column in my table. For example I have: Col1 ------ 34 35 36 I want to make it: Col1 ------ 24 25 26 What's the SQL code for doing it ? ...

Trying to use a SQL function in MySQL

I'm trying to use this script (which extracts the domain from a URL): CREATE FUNCTION [dbo].[parseURL] (@strURL varchar(1000)) RETURNS varchar(1000) AS BEGIN IF CHARINDEX('http://',@strURL) > 0 OR CHARINDEX('https://',@strURL) > 0 SELECT @strURL = REPLACE(@strURL,'https://','') SELECT @strURL = REPLACE(@strURL,'http://','') SELECT @str...

Install script MySQL syntax error "You have an error in your SQL syntax; [...] "

I have an install script on my website, and when i run it, it gives me thsi error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE active_guests ( ip varchar(15) collate latin1_general_' at line 2 Here is the code where it seems to b...

php mysql sort by date (newest)

Right now im sorting by each articles auto_increment id with the query below mysql_query("SELECT * FROM articles ORDER BY id DESC"); I wanna know how to sort by a date field i made, that stores the current date via, strtotime() it should query the dates from newest to oldest. Current code $alist = mysql_query("SELECT * FROM artic...

sqlachemy, says decimal is not defined?

Trying to make a column of type decimal: Column('cost', DECIMAL) Erorr, name 'DECIMAL' is not defined. SqlAlch seems to support decimal, am I missing an import? BTW, how do I also create a longtext column? I'm using mysql. ...

Mac ports Mysql install

I completed my install on Leopard with Mac Ports. I also installed Mysqld via Mac Ports for use with python. I set the password for mysql on mysql start. Everything seemed to be fine except when I invoke mysql-start from the command line now I get this: mysql-start ***Password: Starting MySQL . SUCCESS! demetrius-fords-macbook-pro-17:~ ...

setting up mysql on Mac

I first installed Macports so it would be easier. Then installed mysql5,mysql5-server and py26-mysql. Everything went ok. When I typed: which mysql5 it returns `/opt/local/bin/mysql5 But when i try to enter the server: mysql5, an error is generated: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/opt/local/var...

MySQL trigger to update value in another table

What i want to is when updating/inserting to table x, increment a value in table y, by 1 example below, using a select statement, which i cant use as im selecting from the table im trying to update DELIMITER $$ create trigger occupancy after insert on tbl_attendence for each row begin set @course_id = new.course_id; set @attend...

Store Information In MySQL Database with Python

I am working on a huge project. I have been working on it for a while now, and decided to "up" the security on the way the software handles data. I already know how to encrypt and decrypt the data strings using DES encryption, but what I am not sure about is where to put that encrypted data. I would like to store everything in a MySQL da...

Django MySql setup

I set up Mysql5, mysql5-server and py26-mysql using Macports. I then started the mysql server and was able to start the prompt with mysql5 In my settings.py i changed database_engine to "mysql" and put "dev.db" in database_name. I left the username and password blank as the database doesnt exist yet. When I ran python manage.py syncdb...

Using SQL to find the total number times a product has been bought in one query

Hey all, I'm trying to find the most optimal way to return a recordset containing a list of all products that have been bought by customers and how many of each have been sold. Table ORDERS is laid out like this: CustomerID ProductID (etc) 1 10 1 10 1 11 2 10 4 9 This...

Strategy to handle large datasets in a heavily inserted into table

I have a web application that has a MySql database with a device_status table that looks something like this... deviceid | ... various status cols ... | created This table gets inserted into many times a day (2000+ per device per day (estimated to have 100+ devices by the end of the year)) Basically this table gets a record when jus...

link Redirect from php

Dear all I have database of website link, it list out in main file ,when i try to click that link it get to redirect on that database link. my code is test.php <?php // getting from database echo '<li onclick=\"window.location='.$result->website.'\"> <a href="#">'.$result->option.'</a></li>'; ?> The Main.h...

How to connect MySQL to Java program.

I ve installed MySQL (last update). I need to code, that ll create & establish a connection with SQL DB & manage the DB(using SELECT, INSERT, CREATE). I did everything but, I am not able to create connection. I've also installed the MySQL/J connector, I just extracted the .zip pack in a folder & added the folder path in Variables). Ca...

java.sql.SQLException: Incorrect string value: '\xF3\xBE\x8D\x81'

I am getting the following exception while trying to save some Tweets, Caused by: java.sql.SQLException: Incorrect string value: '\xF3\xBE\x8D\x81' for column 'twtText' at row 1 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) ...

Disabling previously searched term suggestion in a search bar

I was following this tutorial: http://www.marcofolio.net/webdesign/a_fancy_apple.com-style_search_suggestion.html And checking out the demo here: http://qpoit.com/marcofolio_demo/apple_search/ I realized something. If you take a particular search term, like iPhone for example, and type that into the demo search bar and hit the return ...

JavaScript Avatar

Hello, I'm trying to create a javascript avatar. E.G with Head Items, body items etc... I have a PHP script that queries the database and retrieves the current items the avatar/user is wearing, it's response is in a serialised array. The item/avatar images are in E.G: http://blahblahblahBlah.com/graphic/itemtype/id.png Thank you in adv...

MySQL (exploding/matching array)

Question1: MySQL table id | array 1 | 1,2,3 2 | 2 3 | 2,3 4 | 4,5,6 $_GET['id'] = 2; $a = mysql_query("SELECT * FROM `table` WHERE `array` ??? '$_GET[id]'"); In this step, I want to run through the entire array and see if it matches with the $_GET['id'], so it should output: ids: 1,2,3 Question2: MySQL table id | array 1 | ...

how to use the character " (double quote) in RMySQL?

I am toying with RMySQL and trying to import a testing database using the following code: dbSendQuery(db, statement = "LOAD DATA LOCAL INFILE '~/R/survey/pet.csv' INTO TABLE pet FIELDS TERMINATED BY '\t' ENCLOSED BY '@' IGNORE 1 LINES") ...