mysql

sql delete from start

Is there anyway I can restrict the amount of rows allowed in a table to say 40, and then when 41st is added, the table deletes the first one? ...

mysql changing delimiter

I'm trying to add this function using php myadmin, first off I get on error line 5, which is apparently because you need to change the delimiter from ; to something else so i tried this DELIMITER | CREATE FUNCTION LEVENSHTEIN (s1 VARCHAR(255), s2 VARCHAR(255)) RETURNS INT DETERMINISTIC BEGIN etc.... END DELIMI...

jQuery prepend a auto fetch php file database

Hello, Currently i have a php file which fetch data from mysql to display in website. I'm using input value to send as $_GET parameter to php file to determine the data to show. mysql_query("SELECT * FROM messages WHERE msg_id>'$refID' ORDER BY msg_id DESC"); //$refID is input value So once it load, i'm using this jquery code to d...

how to pass int values in mysql query string?

here my code- $sqlpin = "SELECT pinId FROM tblpin WHERE pinId = '$pin_no', status = 0 "; status is int type. it is showing parse error. ...

PHP - MySQL how to delete an image when a user submits a new image problem.

I'm trying to delete the current image that is already set on the server when a user submits a new image to the server, but I don't know how to do add this to my current code can some one please help me? Here is my PHP & MySQL code. if(isset($_FILES["thumb"]["name"])) { if (file_exists("/images/" . $_FILES["thumb"]["name"])) { ...

PHP mySQL query's and PHP Variables

I'm trying to make an OO Login system for a project I'm working on, and am having trouble with inserting variables into the query strings. In the code below, if I replace "$TBL_NAME" with the actual table name it works. Why isn't $TBL_NAME translating to the value of $TBL_NAME? class UserDB { private $TBL_NAME = "users"; public ...

Does having a longer string in a SQL Like expression allow hinder or help query executing speed?

I have a db query that'll cause a full table scan using a like clause and came upon a question I was curious about... Which of the following should run faster in Mysql or would they both run at the same speed? Benchmarking might answer it in my case, but I'd like to know the why of the answer. The column being filtered contains a couple...

Solr; "rookie" question

I have a SolrPhpClient on my classifieds website, and whenever users wants to add/remove classified the index in Solr gets updated via Php code. So I wonder, does this mean that my Solr index is open for anybody to alter with? Same Q applies to the Solr Admin page. If I set a password for the admin page, does this mean that my classifi...

What Mysql query will return this result?

I have two tables A,B which are related to each other (simplified): A: +-------+---------+ | id | type | +-------+---------+ | 1 | apple | | 2 | orange | | 3 | banana | +-------+---------+ B: +-------+---------+-----------+ | id | a_id | rank | +-------+---------+-----------+ | 1 | 1 ...

Physically cluster a MYSQL table for better performance

I have a 30M row table, which is indexed by ID now. However, a lot of the requests are around a much smaller subset, which is identified by app_user=1 Does it make sense to physically cluster the table according to this? If so, how can I do so? ...

Switching MySql Replication Format

I'm currently using statement-based replication. After upgrading to MySql 5.1, I'm considering using row-based replication. After reading the docs it seems that you can change the format of the master on the fly. Will the slave automatically adapt to whatever type of binary log it is sent? Do I have to make any changes to the slave o...

MySQL Query Help, including a count?

Any ideas why this isn't working in MySQL? SELECT blogentry.*, person.personName, (SELECT * FROM BlogEntryComment Where BlogEntryID = '8') as CommentCount FROM blogentry INNER JOIN person ON blogentry.personID = person.personID WHERE blogentry.deleted = 'N' ORDER BY blogentry.dateAdded DESC ...

Set AUTO_INCREMENT using SqlAlchemy with MySQL on Columns with non-primary keys?

I can't figure out how to set AUTO_INCREMENT on a UNIQUE column using SqlAlchemy 0.6.0 with MySQL 5. I know this can be done in MySQL, but I don't want to have to distribute extra .sql scripts in order to set up the databases for my application. I want SqlAlchemy to build the schema from within Python. As far as I have found so far, th...

How can I implement forum privileges

I've started developing a forum application in PHP on my MVC Framework and I've got to the stage where I assign permissions to members (for example: READ, WRITE, UPDATE, DELETE). Now, I know I can add 5 columns under the user table in my database and set them to 1 | 0, but that to me seems like too much if I want to add other rules, l...

SQL to CodeIgniter Array Missing Data Issue

$query = $this->db->query("SELECT t1.numberofbets, t1.profit, t2.seven_profit, t3.28profit, user.user_id, username, password, email, balance, user.date_added, activation_code, activated FROM user LEFT JOIN (SELECT user_id, SUM(amount_won) AS profit, count(tip_id) AS numberofbets FROM tip GROUP BY user_id) as t1 ON user.user_id = t1.user_...

Variable collation with MySQL stored function?

I want to do something like this in a stored procedure: IF case_sensitive = FALSE THEN SET search_collation = "utf8_unicode_ci"; ELSE SET search_collation = "utf8_bin"; END IF; INSERT INTO TABLE1 (field1, field2) SELECT * FROM TABLE 2 WHERE some_field LIKE '%rarf%' collate search_collation; However, when I do this, I get ERROR 1...

Single Large v/s Multiple Small MySQL tables for storing Options

Hi there, I'm aware of several question on this forum relating to this. But I'm not talking about splitting tables for the same entity (like user for example) Suppose I have a huge options table that stores list options like Gender, Marital Status, and many more domain specific groups with same structure. I plan to capture in a OPTIONS ...

MYSQL accentuated characters é display as %E9

Hi guys, I'm pushing data from as3 to MSQL through a little php script! My problem is that all my accentuated characters are displayed as weird iso characters. Example : é is displayed %E9 Obvisously the collation of my field is set to utf8_general_ci Even when I try to INSERT the data from a simple php script without as3, I get the...

MySQL best usage in Tomcat?

Which one is better way of using MySQL in Tomcat : A) assign a DB connection for user as long as it's session is valid. [OR] B) open connection to DB, on every request come to server and when it's done close that. C) Connection pool. [BEST answer] ...

Is it alright to post my PHP and mySQL Login script for review and improvements?

I would like to post my script here, if I get the okay I will post it, or is there another place that I should post my script and link to it? Please Advise ...