mysql

Database MySql design - varchar length for utf8 fields :: 1. password 2. username 3.email

Hi, Most of the times I define varchar(255) length auto. But now I thinking how much varchar length should be best to define for utf8 fields: password username email If this fields should be define less than varchar 255, how much performance it will improve? Thanks ...

MySQL Update query with left join and group by

I am trying to create an update query and making little progress in getting the right syntax. The following query is working: SELECT t.Index1, t.Index2, COUNT( m.EventType ) FROM Table t LEFT JOIN MEvents m ON (m.Index1 = t.Index1 AND m.Index2 = t.Index2 AND (m.EventType = 'A' OR m.EventType = 'B') ...

MySQL Inserting into locked aliased table

I am trying to insert data into a InnoDB MySQL table which is locked using an alias and I cannot for the life of me get it to work! The following works: LOCK TABLES Problems p1 WRITE, Problems p2 WRITE, Server READ; SELECT * FROM Problems p1; UNLOCK TABLES; But try and do an insert and it doesn't work (it claims there is a syntax err...

HOw do I delete record in a table by keeping certain datas??

my site has lots of incoming searches which is stored in a database to show recent queries into my website. due to high search queries my database is getting bigger in size. so what I want is I need to keep only recent queries in database say 10 records. this keeps my database small and queries will be faster. I am able to store incomin...

php mysql error beginner

Part I - Solved. Hi, I'm trying to print some values on the screen from a table but I having a problem, I don't know much about string, vector and array but I think that my problem is related to them. I'm getting this on the screen Fatal error: Cannot use [] for reading ... My code $sql="SELECT * FROM $tbl_name"; $result=mysql_que...

mysql showing null values for group by statements

I'm doing: select sum(clicks), date from stats group by date ...however when the sum is null for some date, the whole row is discarded, I want to see: | null | some_date |, how to do so? ...

White Label Ecommerce app. Shared or Individual dbs

Currently I'm working with an in house white label cms that we resell to multiple clients and it all runs from the same box/db. I'm just looking at converting this to have an ecommerce version that we'll run alongside it. I'm wondering whether there will be an issue keeping all the products/categories/orders in one db or whether it wou...

What is the cleanest way to sort "describe table" query results ?

Hello everyone, I'm working on "describe table" output to show a list of fields and their types, i want my primary keys to be at top of the list.. I think there's no way to sort describe's results using SQL (something like 'order by') rather than sorting it in PHP. what do you think guys ? thanks ...

Select in MySQL based on two tables

I have two tables. diseases ----------------------------- | ID | NAME | ----------------------------- | 1 | Disease 1 | | 2 | Disease 2 | | 3 | Disease 3 | diseases_symptoms ----------------------------- | DISEASE_ID | SYMPTOM_ID | ----------------------------- | 1 | 1 ...

phpexcel write excel and save ?

code: <?php /** PHPExcel */ require_once '../Classes/PHPExcel.php'; /** PHPExcel_IOFactory */ require_once '../Classes/PHPExcel/IOFactory.php'; // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Set properties $objPHPExcel->getProperties()->setCreator("Maarten Balliauw") ...

How to make MySQL utilize available system resources, or find "the real problem"?

This is a MySQL 5.0.26 server, running on SuSE Enterprise 10. This may be a Serverfault question. The web user interface that uses these particular queries (below) is showing sometimes 30+, even up to 120+ seconds at the worst, to generate the pages involved. On development, when the queries are run alone, they take up to 20 seconds ...

Best datastructure for this relationship...

I have a question about database 'style'. I need a method of storing user accounts. Some users "own" other user accounts (sub-accounts). However not all user accounts are owned, just some. Is it best to represent this using a table structure like so... TABLE accounts ( ID ownerID -> ID name ) ...even though there will be som...

alter mysqldump file before import

Hi-- I have a mysqldump file created from an earlier version of a product that can't be imported into a new version of the product, since the db structure has changed slightly (mainly altering a column that was NOT NULL DEFAULT 0 to UNIQUE KEY DEFAULT NULL). If I just import the old dump file, it will error out since the column that ...

Zend Framework - Problem with Database Table Recursive Cascading Deletes

My situation may be a bit abnormal, but I have foreign keys defined in my MySQL database, while enforcing referential integrity in the Zend_Db_Table classes. The tables use the InnoDB storage engine. When deleting a record, the Zend Framework will properly identify immediate children via the $_referenceMap in the table model and delet...

Problem with mysql_query() ;Says resource expected .

I have this php file. The lines marked as bold are showing up the error :"mysql_query() expecets parameter 2 to be a resources. Well, the similar syntax on the line on which I have commented 'No error??' is working just fine. function checkAnswer($answerEntered,$quesId) { //This functions checks whether answer to question havi...

Update MySQl table onDrop?

Hi all. I am writing a PHP/MySQL application (using CodeIgniter) that uses some jQuery functionality for dragging table rows. I have a table in which the user can drag rows to the desired order (kind of a queue for which I need to preserve the rank of each row). I've been trying to figure out how to (and whether I should) update the data...

How to MySQL Trigger to update a field whenever any record is touched?

I haver zero experience with triggers in MySQL. Learnt them in school (Oracle environment). I have a table which already has a datestamp column for insert date. I want to update a Record_Touched field whenever any record is touched in this table. What is the best efficient way of doing it? Thank you... ...

Doing sum's if certain conditions are true

I am trying to build a query that does a sum if a certain parameter is set. For example: SELECT SUM(IF(<condition>,field,field)) AS total_value ...which is working correctly. But i have more than one condition in the IF(), like: SELECT SUM(IF(<condition> <condition>,field,field)) AS total_value ..which is not working, could you h...

how i can make a sort system depend on Character

i mean i have songs table ok?? i want to make some things like that a | b | c | d click on a select from the database the songs that start by a Character how i can do that by the mysql??? ...

Change One Cell's Data in mysql

how can i change the data in only one cell of a mysql table. i have problem with UPDATE because it makes all the parameters in a column change but i want only one changed.how? ...