mysql

Python mysql with variables

I am having a hard time using the MySQLdb module to insert information into my database. I need to insert 6 variables into the table. cursor.execute (""" INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation) VALUES (var1, var2, var3, var4, var5, var6) ...

MySQL function that alerts if a new row is inserted.

Is there a function in MySQL that we can use in a client application that will automatically sends alerts every time a new row is inserted in a table? ...

Suggestions for dealing with time offsets in mysql

Distilling this project down to the simplest of terms; Users click a button, a record is made with a timestamp of NOW(). NOW() of course equals the time on the server of record creation. I need to show them stats based on their timezone, not mine. What is the best method for dealign with time zone offsets in MySql? Is there a spe...

PHP converting date format

Duplicate Managing date formats differences between PHP and MySQL PHP/MySQL: Convert from YYYY-MM-DD to DD Month, YYYY? Format DATETIME column using PHP after printing date formatting in php Dear All, I have a PHP page where i wil be displaying some data from Mysql db. I have 2 dates to display on this page.In my d...

MySQL: dot (".") in database name

Hello, does MySQL allows to create database which has dot (".") in its name? I'm using MySQl 5.1.22. Thanks ...

Reusing computed columns in mysql

How can I reuse a computed column in SQL in MySQL? Say: my query is something like: - SELECT CONVERT_TZ( if(timestamp_start > last_update, timestamp_start, last_update), 'GMT', user.timezone ) as time_usr_tz from shecdule inner join user on shecdule.user_id = user.user_id where CONVERT_TZ...

Calendar in Zend Framework

Hello, I looking to create a custom calender with Zend Framework, I am hoping that it will be able to list all the days of the month for the coming years and then have a different bg color on the date if there is an event on this. I am however struggling to create this firstly because it needs to go into the layout view, rather than an...

Database Error

I have a site that gets just about 100 people everyday but I got this error message when log in as a user: Warning: mysqli::mysqli() [mysqli.mysqli]: (42000/1203): User mexautos_Juan already has more than 'max_user_connections' active connections in /home/mexautos/public_html/kiubbo/data/model.php on line 26 Warning: mysqli::query() [m...

Pass result of mysql_fetch_object() to a function does not work

I have the following problem: public function row2Partner($row){ echo $row->PartnerID; } public function main(){ $query = "SELECT PartnerID, PartnerName FROM Partner"; $result = mysql_query($query); $this->row2Partner(mysql_fetch_object($result)); } This gives me the error in row2Partner(): Trying to get property of non-objec...

How to get total occurrence of a value within its own query?

The reason why I am asking this is due to legacy code, so bare with me Lets say we have this query SELECT * FROM table And this result from it. id | user_id ------------ 1 | 1 ------------ 2 | 1 ------------ 3 | 2 ------------ 4 | 1 How could I get the count of how often a user_id appears as another field (without some major S...

Recordset Iterating

I want to iterate through records returned from a MySQL database using Perl, but only ten records at a time. The reason is that the server component can only handle 10 items per request. For example: If the query returned 35 records then I have to send the data in 4 requests: Request # # of Records -------- ...

MySQL Subquery Returns more than one row

I am executing this query SELECT voterfile_county.Name,voterfile_precienct.PREC_ID, voterfile_precienct.Name ,COUNT((SELECT voterfile_voter.ID FROM voterfile_voter JOIN voterfile_household WHERE voterfile_voter.House_ID = voterfile_household.ID and voterfile_household.Precnum = voterfile_precienct.PREC_ID)) as Voter...

Can I have an inner SELECT inside of an SQL UPDATE?

I have a database like where: Table foo has columns id and name Table bar has columns id and foo_id I have an incoming HTTP query with a foo.name, I'd like to insert a row into bar with bar.foo_id set appropriately. So, for example: > SELECT * FROM foo; id name ------ ------- 1 "Andrey" (1 row) > SELECT * FROM bar; (0 rows)...

Comparing two date ranges when one range has a range of starting dates.

I've got the next problem up from this one: http://stackoverflow.com/questions/143552/comparing-date-ranges The solution to comparing two ranges is the query: SELECT * FROM periods WHERE NOT (range_start > @check_period_end OR range_end < @check_period_start) I have the added problem. I am allowing people to enter a ran...

MySQL ON DUPLICATE KEY - last insert id?

I have the following query: INSERT INTO table (a) VALUES (0) ON DUPLICATE KEY UPDATE a=1 I want the ID of either the insert or the update. Usually I run a second query in order to get this as I believe insert_id() only returns the 'inserted' ID and not the updated ID. Is there a way to INSERT/UPDATE and retrieve the ID of the row wi...

Named pipes versus TCP for JDBC-MySQL in Windows

I've been having numerous connection problems between my Java (JPA+Hibernate+CommonsDBCP) app connecting to MySQL. I've done the research, tweaked all the settings with validation queries, timeouts, tests before X, etc. This path led me to another StackOverflow question comparing DBCP and C3PO. From the responses, I've decided to defini...

Using MySQL triggers to log all table changes to a secondary table

I have a table: CREATE TABLE `data_table` ( `data_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `field1` INT NOT NULL , `field2` INT NOT NULL , `field3` INT NOT NULL ) ENGINE = MYISAM ; I would log to log any chances to field1, 2, or 3 to: CREATE TABLE `data_tracking` ( `tracking_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `data_id...

Requiring SSL for the database connection for MediaWiki/Apache/PHP/MySQL with OpenSSL

Hi, For a school project, I have installed MediaWiki on my local machine, and am required to have any database connection to the local MySQL database use SSL. I am unsure of how to connect all the dots. Here's what I have done so far: I have installed OpenSSL, and created a self-signed certificate, and associated keys. phpinfo() sho...

How to implement locking across a server farm?

Are there well-known best practices for synchronizing tasks across a server farm? For example if I have a forum based website running on a server farm, and there are two moderators trying to do some action which requires writing to multiple tables in the database, and the requests of those moderators are being handled by different server...

insert multiple rows via a php array into mysql

I'm passing a large dataset into a mysql table via php using insert commands and I'm wondering if its possible to insert approximately 1000 rows at a time via a query other than appending each value on the end of an mile long string and then executing it. I am using the codeigniter framework so its functions are also available to me. ...