mysql

Keeping track of views for content in mysql

Im wondering if I should bother changing my system. Currently when a video is viewed, it runs a query that does UPDATE table SET hits=hits+1 WHERE id = $id. very simple, except they appear in the slow query log all the time, sometimes with 3-4 seconds of query_time. What if I INSERT every view as a new row into a memory table, and ...

Database design question

I'm currently developing a website for a hotel. And one of the things I'm about to implement is worker->superior relationship. What is the best way to do so in MySQL? Here is what I mean: a chef's superior is a head chef, head chef's superior is shift manager, shift manager's superior is general manager. In the employee table, I could m...

Python MySQL - SELECTs work but not DELETEs?

Hey -- I'm new to Python and Python's MySQL adapter. I'm not sure if I'm missing something obvious here: db = MySQLdb.connect(# db details omitted) cursor = self.db.cursor() # WORKS cursor.execute("SELECT site_id FROM users WHERE username=%s", (username)) record = cursor.fetchone() # DOES NOT SEEM TO WORK cursor.execute("DELETE FROM ...

Error while implementing SQL Tree

Hi I'm getting the error while executing following code in MySQL : BEGIN DECLARE right_most_sibling INTEGER; SET right_most_sibling = (SELECT rgt FROM Personnel WHERE emp = 'Albert'); UPDATE Personnel SET lft = CASE WHEN lft > right_most_sibling THEN lft + 2 ELSE lft END, rgt = CASE WHEN rgt >= right_most_sibling THEN rgt + 2 ELSE rgt ...

implementing UNIQUE across linked tables in MySQL

a USER is a PERSON and a PERSON has a COMPANY - user -> person is one-to-one, person -> company is many-to-one. person_id is FK in USER table. company_id is FK in PERSON table. A PERSON may not be a USER, but a USER is always a PERSON. If company_id was in user table, I could create a unique key based on username and company_id, but i...

MySQL - How to run a SELECT query that orders results by a score -- such as upvotes - downvotes.

I have two tables -- an article table and a vote table. Users can either vote up or vote down articles of their choice (similar to Reddit). The fields I have in the vote table are: article_id user_id vote The value for the vote field can either be 0 or 1... (0 if they vote down the article, 1 if they vote up). What I'm trying to d...

Which javascript library for mysql access can you recommend?

I am using JavaScript standalone, not running in a browser. I want to connect to a mysql server running on the same host. Is there a JavaScript library for this? Edit: I had a look at Jaxer. It has mysql functionality, but it is baked into the framework. ...

Dynamic Query based on mulitple GET variables (Refine Search Form)

So im trying to find a script that can start me off with creating an 'refine search' panel which will be a form, hopefully something close to what Ebay have got at the moment. Whilst I know this is going to be a form and using the GET method, I cant find a script that can parse a url for multiple params and update the query all dynamica...

PHP/MySQL COUNT doesn't seem to work as it should

I'm pulling my hair out for this, what is wrong with this query: SELECT COUNT(id), * FROM location WHERE country = '$country' AND LCASE(namenodiacritics) LIKE LCASE('%$locname%') ORDER BY name ASC Am I allowed to COUNT(id) and * in a single query? I keep getting this error: You have an error in your SQL syntax; ...

BASH MySQL Query to Comma Separated File

I have the following bash script: #!/bin/sh MYSQLHOST="mysql.remote-host.com" MYSQLDB="mysqldb" MYSQLTABLE="table" MYSQLUSER="user" MYSQLPASS="pass" MYSQLDUMP="Report/report.csv" LOG="Report/report.log" MYSQLOPTS="--user=${MYSQLUSER} --password=${MYSQLPASS} --host=${MYSQLHOST} ${MYSQLDB}" echo "Report Begin: $(date)" echo "MySQL Du...

How can I make this backlink checker regex better, its missing some sites

<? $mysite = ('websiteurl');?> <? echo $mysite; ?> Links not found <? $time_limit = 3600; set_time_limit($time_limit); include_once("myconnect.php"); $sql0="select * from trade where 1 "; $sql0=$sql0." order by a1 asc"; $query=mysql_query($sql0); $cnt=1; while ( ($rs_query=mysql_fetch_array($query)) ) { if($cnt%2<...

Limiting mySQL data as 5 column 2 entries per column 1 entry.

Hey there, I'm new to mySQL, so my poor knowledge of the languages might be precluding me from searching for the result I need. I am building a simple application in CodeIgniter that will access the Twitter API and return a table of status updates for a certain number of various users. I would like to limit this to showing 5 updates per...

Importing MS ACCESS DB to mySql?

I'm working on a project atm, and I need to import data that is stored in a MS ACCESS database to mySql. For mySql I'm using phpMyAdmin on a Ubuntu machine, I have another Windows Machine where I can access the Access DB from, In MS Access 2003 I can't find an option to convert the data to mySql? Can this be done? ...

Simple Database Design Question

Hi, I am developing web application that powered php and mysql. I have table named users, in this table i store some information such as username, first and lastname, telephone number etc.. In my application user can enter some OPTIONAL information such as e-mail newsletter options, company name(if works..) or web site url. All of these ...

MySQL Default Value Issue

Hello All, In MySQL, the default value for a Integer Field is 0. During insertion the value for that field is NULL and the NULL gets inserted. I do some arithmetic manipulation like addition, multipilcation, division using that field's value. This results in error as NULL got inserted. How to resolve this problem? Don't the DB support d...

MySQL fulltext search for html entities

Hi! I have to hack a content management system to support fulltext search for a language that contains special characters. These are stored in the database as html entities. Out of the box, the CMS doesn't support it. The bug was reported long time ago, but apparently it has no priority. I'm stick to this CMS, the customer is awaiting m...

create search option for local webpages by tagging a page

Hello, My actual question is how would I go about creating a autocomplete dropdown off tags that will refer to a particular page, like on this site. I already have a autocomlete dropdown and it looks in the db field for a word starting with something% (wildcard),but that is just one word What if you would have like 5 tags, comma sepe...

How to keep database connect alive?

I'm using ajax, and quite often if not all the time, the first request is timed out. In fact, if I delay for several minutes before making a new request, I always have this issue. But the subsequent requests are all OK. So I'm guessing that the first time used a database connect that is dead. I'm using MySQL. Any good solution? ...

How to store Richtextbox Foramtted COntents in MYSql DB ' Direclty ' using VB.Net

I have one Field in MYSQL DB. Name is ' Field1 '. I can store the Richtextbox text(it contains Bold, Italic, Etc). using the code ' Richtextbox1.rtf ' But when i am trying to display using 'Richtextbox1.rtf=dt.rows(0).item(0).tostring ', It shows 'File Format is not Valid '. What is the Solution. ...

problem full text search in mysql

I want to search a text 'law' in the Mysql colume 'content(colume_name)' thats values is('this is law') I am using this query SELECT * FROM stuff WHERE match(content) against('law' in boolean mode) But this query returns 0 rows Thanks ...