mysql

MySQL INSERT data does not get stored in proper db, only temporary?

I'm having trouble with MySQL or Python and can't seem to isolate the problem. INSERTs only seem to last the run of the script and are not stored in the database. I have this script: import MySQLdb db = MySQLdb.connect(host="localhost", user="user", passwd="password", db="example") dbcursor = db.cursor() dbcursor.execute("select * fro...

Cache data in PHP SESSION, or query from db each time?

Is it "better" (more efficient, faster, more secure, etc) to (A) cache data that is used on every page load in the $_SESSION array (but still querying a table for a flag to reload the data fresh), or (B) to load it from the database each time? I'm using the cache method (A), but I'm worried that with hundreds of users, memory could beco...

SELECT SQL query help

I'm trying to figure out a little bit unusual select query. First of all, what I have is an id of a single row in the table. I want to select a number of rows from the table (let's the say 10) with the single row with the id I know being in the table being in the middle of the rowset. So it will select let's say 4 rows before the id I k...

MySql copy value from one row to another

This should be pretty basic but i can't figure it out. Have a table 'values' entity|attribute|value frank - shirt - red frank - hat - green sam - shirt - blue sam - hat - orange how do i set all hats color's to be the same as the shirt color, of the same person to give frank a red hat and sam a blue hat. ...

Need to COUNT in LEFT JOIN using MYSQL

Hi all SELECT * FROM `tbl_group_join` tgj LEFT JOIN tbl_groups tg ON tg.group_id = tgj.group_id LEFT JOIN tbl_registeration tr ON tr.mem_id = tgj.mem1_id WHERE tgj.group_id =24 LIMIT 0 , 30 Above query is working right. And it produces two rows as O/P. Exactly this is what I want. Now in addition I need the count of ROWS. I did it...

How to get id of the last updated row in mysql

Hi friends, I just want to get id of the last updated row in the mysql using PHP. Thanks in advance... ...

MySQL - Finding out Balancing Figure From 2 Tables

I have 2 tables like this: Stock Table product_id bigint(20) qty float Sales Table product_id bigint(20) qty float Sample Data Stock Table product_id---qty 1---10 2---11 3---20 4---50 1---10 3---10 Sales Table product_id---qty 1---2 2---5 3---20 4---40 1---7 I want the following Output after running the Query product_id---qty ...

format integer to string

I have an integer field in a table and I want to make a query to format the integer value of this field in an char or double field with a especific format. For example, if my value in the table is 123456 I want to format it as "###.###" what means the result should be like this: 123.456 I've done this using CONCAT function, but the res...

select privilege on more than one table

How do I grant select privilege on more than one table in a single statement? mysql> grant select on dbName.crw_changes to sink; Query OK, 0 rows affected (0.02 sec) mysql> grant select on dbName.crw_changes, dbName.bs_services to sink; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your ...

MySQL does not use effectively use the index for ORDER BY queries

I have a simple Message table, with 2 indexes: mysql> show keys from Message; +---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | ...

How can I search mysql for a single date?

I have a timestamp and I want to search for a single date but I can't figure out how to do this. SELECT something from mytable WHERE timestamp = 'desiredDate' If I include the hours mins and seconds in a range I can get it but there has to be a way to tell mysql that you want everything for a single day. Can someone please help? Thank...

Database directory disappeared

One of my clients complained that his database disappeared without any reason. I guess he must have dropped the database by mistake. But I just want to make sure that this can not happen automatically. The ibdata and iblog files are there but the directory (for e.g. company) is missing. ...

Deleting from a table results in a Cardinality violation

I have a method that deletes a row form a database using the sql adapter provided the the zend framework. class Table extends Zend_Db_Table_Abstract { ... $where = $this->getAdapter()->quoteInto( 'ModID=? AND URL=?', array((int)$mid, $url->toString()) ); $this->delete($where); The issue is that this ...

ASP.NET DropDownList Issue

[...] public DataSet ReturnPromoMagazinesDs() { MySql.Data.MySqlClient.MySqlConnection mysqlConnection = new MySql.Data.MySqlClient.MySqlConnection(this.connectionString); MySql.Data.MySqlClient.MySqlCommand mysqlCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT id, magazine_name FROM `magazines`", mysqlConnection); ...

using rsync to backup mysql

hi, I use the following rsync command to backup my mysql data to a machine within the LAN network. It works as expected. rsync -avz /mysql/ root:[email protected]:: /root/testme/ I just want to make sure that this is the correct way to use rsync. I will also like to know if the 5 minute crontab entry for this will work. ...

Is this a stored procedure?

IS this bit of code in PHP/mysql considered a stored procedure? $sql = 'SELECT username FROM user WHERE username = ? AND passwordHash = ?'; $result = $db->query($sql, array($_POST['username'], $passwordHash)); ...

MYSQL INSERT SELECT problem

Hey all, i have a little difficulty in understanding how to do some INSERT SELECT. For instance i have two tables. TABLE : users id | name | gender 1 | John | m 2 | Mary | f TABLE : website fid | url | id 1 | www.desilva.biz | 2 2 | gidhelp.com | 4 Now let's say i want to add anoth...

MySQL Storing "Generic" Data (Can be very long or very short)

ok i am developing a quiz ... the question's options ... so i am wondering what can i use for options that can both be very long (eg. Long paragraph of text/code) or very short (eg. boolean) if i use whether i use VARCHAR(65535) or TEXT i only use the space i need right, L chars + 1 or 2 bytes vs L chars + 2 bytes? any speed differences...

Trying to create category wise filtering in the view where i list stuff and getting an error in php?

I have a simple crud application where i am listing certain information . i need to create a filter on the basis of category but am getting an error. I get the error: Notice: 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 'mysql_real_escape_string()' ...

How do I specify the MySQL sock file location for thinking sphinx?

My socket file is located here: /var/run/mysqld/mysqld.sock When I do: rake thinking_sphinx:start I get: rake aborted! Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) I want to tell thinking_sphinx where my socket file is. How is this possible? This issue appeared after performing a hard reboot of my sl...