mysql

MySQL: How to select all rows from a table EXCEPT the last one

Hey! I have a table with N rows, and I wanna select N-1 rows. Suggestions on how to do this in one query, if it's possible..? ...

Automagically expanding a Python list with formatted output

Does anyone know if there's a way to automatically expand a list in Python, separated by commas? I'm writing some Python code that uses the MySQLdb library, and I'm trying to dynamically update a list of rows in a MySQL database with certain key values. For instance, in the code below, I'd like to have the numeric values in the record_...

MySQL -- create two indexes, only one appears in SHOW CREATE TABLE output

In have a many-to-many linking table and I'm trying to set up two foreign keys on it. I run these two statements: ALTER TABLE address_list_memberships ADD CONSTRAINT fk_address_list_memberships_address_id FOREIGN KEY index_address_id (address_id) REFERENCES addresses (id); ALTER TABLE address_list_memberships ADD CONSTRAINT fk_address_...

MySQL Multi-Table Join

I need to modify the following MySQL statement to include information from a third table... but I suck at joins. select ss.*, se.name as engine, ss.last_run_at + interval ss.refresh_frequency day as next_run_at, se.logo_name from searches ss join search_engines se on ss.engine_id = se.id where ss.user_id='.$user_id.' group by ss...

Help with SQL query

Hi to all I'm stuck in trying to solve this problem for a days. I even give up for a while, and since i'm a PHP newbie, i need help. This is my problem: I have a query that selects all appropriate record in a table 'hotels' and then for each hotel looks for booked room of certain type in table 'booked_rooms' and all of that for certai...

Need help joining table

I have a function that exports a table to CSV and in the query I set which fields will export. Here is the query: SELECT lname, fname, email, address1, address2, city, state, zip, venue_id, dtelephone, etelephone, tshirt FROM volunteers_2009 The field venue_id is the the id of the venue which is referred to in another table (venues)...

MySQL code fails to display category name (WordPress database)

Why does this code fail to display the category name "Apples" using the current WordPress taxonomy system? The Category names are stored in the $wpdb->terms table (wp_terms). <?php $ra_category_id = 3; $ra_category = $wpdb->get_results("SELECT name FROM $wpdb->terms WHERE term_id = '3'"); $ra_category_name = $ra_category->name; ?...

How is this MySQL query vulnerable to SQL injection?

In a comment on a previous question, someone said that the following sql statement opens me up to sql injection: select ss.*, se.name as engine, ss.last_run_at + interval ss.refresh_frequency day as next_run_at, se.logo_name from searches ss join search_engines se on ss.engine_id = se.id where ss.user_id='.$user_id.' group by...

Problem compiling MySQLdb for Python 2.6 on Win32

I'm using Django and Python 2.6, and I want to grow my application using a MySQL backend. Problem is that there isn't a win32 package for MySQLdb on Python 2.6. Now I'm no hacker, but I thought I might compile it myself using MSVC++9 Express. But I run into a problem that the compiler quickly can't find config_win.h, which I assume is...

Object Oriented MySQL Statements, PHP

So far i have got the code below which works lovely when trying an update, delete or select statement. However I run into problems when I try to use an insert. If someone could point me in the correct direction i would be grateful. private function escape($value) { if(get_magic_quotes_gpc()) $value = stripslashes($value); ...

Marking latest news as ... "new"

On some websites or forums I usually go to, entries that I havn't read yet are marked as "new". I would like to implement this feature on a website I have developed (with news being posted from time to time) -- php/mySQL. How is this usually done ? Does it require using a cookie remembering the last visit date/time ? Or the last viewed p...

Performing Interactive queries in MySql (mainly from the GUI)

I mainly use the MySQL GUI tools. This allows me to easily see the results in a table as well as to quick edits and bookmark frequently run queries. This suits my needs far better than the command line. I remember when I used to do this on Oracle DBs years ago I could put variables in the query itself, so that when running the query I g...

How to test for an empty SQL result in ASP

I am running a query from ASP using a MySQL database, I want to create a variable (ssResult) based on the result with a person's name (fullname), if the record does not exist I want to assign the text 'N/A' to the variable, code below, I currently use a function getOther for my database connections which passes the column name "fullname"...

Mysql efficient "select id else insert" query

I have a mysql table consisting of: CREATE TABLE `url_list` ( `id` int(10) unsigned NOT NULL auto_increment, `crc32` int(10) unsigned NOT NULL, `url` varchar(512) NOT NULL, PRIMARY KEY (`id`), KEY `crc32` (`crc32`) ); When inserting data into a related table I need to lookup the primary key from this table, and using the cr...

Is ENCODE() and DECODE() the "best" way to handle an application password field in MySQL?

I am developing on a LAMP(erl) stack and know of several ways to store obscured passwords. I'd like to hear from those who feel they have a best practice, given MySQL 4.1.1 and Perl 5.8, and the reasons why it's the best. One option I have read about, using the MySQL ENCODE() and DECODE() functions, sounds pretty good to me... your tho...

mysql stored procedure question

I have a table with a credit and debit column. I need to get the highest balance out of that, and I think a stored procedure is the way to do it, but I have no idea how. I need to start with the first row, add the debits, subtract the credits and store the value A. Second row is A+debit-credit=B; A = max(A,B) Repeat last step till the...

In SQL how to compare date values?

Using MySQL syntax and having a table with a row like: mydate DATETIME NULL, Is there a way to do something like: ... WHERE mydate<='2008-11-25'; I'm trying but not really getting it to work. ...

Resources for developing a C++ MySQL application for Linux

I'm going to be developing a C++ application that uses a MySQL database. I've written similar apps in Java using JDBC, as well as the Spring Framework. Are there equivalent libraries for C++? What have you had the most success with? ...

mysqli->error: Is it for the last query only, or for the last error from the query group?

Hi All, I am new to mysqli, and trying to confirm that if I so something like the below, the errno will be set to the last error, if any, and not the error of the last query. Is this a decent practice or should I be checking for the error in between every query? Thanks! $mysqli->autocommit(FALSE); $mysqli->query("INSERT INTO ........

Algorithm for generating a random number

I'm looking to generate a random number and issue it to a table in a database for a particular user_id. The catch is, the same number can't be used twice. There's a million ways to do this, but I'm hoping someone very keen on algorithms has a clever way of solving the problem in an elegant solution in that the following criteria is met: ...