mysql

Mysqli query only works on localhost, not webserver

Hello. I have changed some of my old queries to the Mysqli framework to improve performance. Everything works fine on localhost but when i upload it to the webserver it outputs nothing. After connecting I check for errors and there are none. I also checked the php modules installed and mysqli is enabled. I am certain that it creates a ...

How to select all options from a drop list in php / mysql

Thanks to stackoverflow.com's frienly experts I've managed to create my first php + mysql application. The code searches a mysql database for last names and cities. The choices are made through two drop lists like these: Choose city: All cities Liverpool Manchester Choose last name: All last names Lennon Gallagher The code would retur...

How do I write this MySQL Query? (datetime)

Suppose I have a DateTime field called "time_before" I want to insert a datetime that is 1 hour before the now() time. INSERT INTO mytable(time_before) VALUES(now()-3600 seconds)...something like this, right? ...

Best way to sort MySql results based on two columns and show results using pagination?

I want to sort results set first based on one column and then based on second column. I know how to do it on server side. And then I want to show these results with pagination feature. Question: would it be better to do it on client side via ajax in jQuery? I'm using Zend Framework. Would Zend_Paginator module be useful in this scenario...

Convert a MySQL database from latin to UTF-8

I am converting a website from ISO to UTF-8, so I need to convert the MySQL database too. On the Internet, I read various solutions, I don't know wich one to choose. Do I really need to convert my varchar columns to binary, then to UTF-8 like that: ALTER TABLE t MODIFY col BINARY(150); ALTER TABLE t MODIFY col CHAR(150) CHARACTER SET ...

Two n x m relationships with the same table in mysql

I want to create a database in which there's an n x m relationship between the table drug and the table article and an n x m relationship between the table target and the table article. I get the error: Cannot delete or update a parent row: a foreign key constraint fails What do I have to change in my code? DROP TABLE IF EXISTS `textmi...

detection of 'flush tables with read lock' in php

I would like to know from my application if a myisam table can accept writes (i.e. not locked). If an exception is thrown, everything is fine as I can catch this and log the failed statement to a file. However, if a 'flush tables with read lock' command has been issued (possibly for backup), the query I send will pretty much hang out f...

how to store "2010-03-26 10:13:04 Etc/GMT" mysql?

Hi All, I would like to store "2010-03-26 10:13:04 Etc/GMT" value in column of type datetime. when i try to insert it i got exception SQLException: 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 '10:13:04 Etc/GMT', at line 1 how to insert data tim...

I am using following PHP code for trigger creation but always get error, please help me to resolve it.

I am using following PHP code for trigger creation but always get error, please help me to resolve it. $link = mysql_connect('localhost','root','rainserver'); mysql_select_db('information_schema'); echo $trgquery = "DELIMITER $$ DROP TRIGGER `update_data` $$ CREATE TRIGGER `update_data` AFTER UPDATE on `jos_menu` FOR...

Return order of MySQL SHOW COLUMNS

Hey guys. Simple one this, but one I can't seem to find any information on so here goes. I need to find the columns in a specific table, which is no problem.... SHOW COLUMNS FROM tablename LIKE '%ColumnPrefix%'; But I need to know what order they will be returned, preferable by choosing to order the results ascending alphabetically. ...

How do you handle descriptive database table names and their effect on foreign key names?

Hello, I am working on a database schema, and am trying to make some decisions about table names. I like at least somewhat descriptive names, but then when I use suggested foreign key naming conventions, the result seems to get ridiculous. Consider this example: Suppose I have table session_subject_mark_item_info And it has a forei...

Sql query problem

I have the below sql query that will update the the values from a form to the database $sql= "update leads set category='$Category', type='$stype', contactName='$ContactName', email='$Email', phone='$Phone', altphone='$PhoneAlt', mobile='$Mobile', fax='$Fax', address='$Address'...

Help with SQL Query

With regards to the following statement: Select * From explorer.booking_record booking_record_ Inner Join explorer.client client_ On booking_record_.labelno = client_.labelno Inner Join explorer.tour_hotel tour_hotel_ On tour_hotel_.tourcode = booking_record_.tourrefcode Inner Join explorer.hotelrecord hotelrecord_ O...

PHP: How To Integrate HTML Purifier To Fileter User Submitted Data?

I have this script that collects data from users and I wanted to check their data for malicious code like XSS and SQL injections by using HTML Purifier http://htmlpurifier.org/ but how do I add it to my php form submission script? Here is my HTML purifier code require_once '../../htmlpurifier/library/HTMLPurifier.auto.php'; $config ...

in mysql table update 'a' record with 'b' and 'b' with 'a'

IN my table there are two field one is name and other is gender I want to fire query so that every male is update with female and vice a versa. I don't want to use procedure ,trigger or function.I have to do this only with simple query. ...

Replacement relevance sorting for MySQL fulltext in InnoDB tables?

I am using MySQL InnoDB and want to do fulltext style searches on certain columns. What is the best way of sorting the results in order of relevance? I am trying to do something like: SELECT columns, (LENGTH(column_name) - LENGTH(REPLACE(column_name, '%searchterm%', ''))) AS score FROM table WHERE column_name LIKE '%searchterm%' ORD...

Buffer Overrun using Mysql Connector c++

Hi all, I'm having a few issues executing sql statements in c++ using the connector. Any hints or ideas are welcome. NB: Mysql Connector c++ 1.05 I have these defined in the class header: sql::Driver *driver; sql::Connection *con; sql::Statement *stmt; sql::ResultSet *res; sql::PreparedStatement *prep_stmt; And the...

MySQL range date overlap check

This table is used to store sessions CREATE TABLE session ( id int(11) NOT NULL AUTO_INCREMENT , start_date date , end_date date ); INSERT INTO session (start_date, end_date) VALUES ("2010-01-01", "2010-01-10") , ("2010-01-20", "2010-01-30") , ("2010-02-01", "2010-02-15") ; We don't want to have conflict between ranges Let's sa...

MySql difference between two timestamps in days?

How can I get the difference between two timestamps in days? Should I be using a datetime column for this? I switched my column to datetime. Simple subtraction doesn't seem to give me a result in days. mysql> SELECT NOW(), last_confirmation_attempt, NOW() - last_confirmation_attempt AS diff FROM DateClubs HAVING diff IS NOT NULL ; +-...

MySQL Exception - Fatal Error Encountered During Data Read

I am working on a C# console program that grabs large numbers of records from a table, runs them through a medical grouper, and then updates each of the records. It uses MySQL Connector/NET 5.2.7. The way it works is that I grab chunks of data at a time (i.e. 20,000 rows) using SQL_BUFFER_RESULT to avoid locks. Each record is run through...