mysql

I cant see SQL Queries in MySQL Stored Procedure!

I have created numbers of stored procedures in MySQL through remote access. And those procedures worked well. After a few days when I connected to Database through remote access. All stored procedures were alive but while was trying to call those procedures, I realized that SQL queries inside procedures had gone. Anyone knows the reason...

Conditional sorting in MySQL?

I have "tasks" table with 3 fields: date priority (0,1,2) done (0,1) What I am trying to achieve is with the whole table sorted by done flag, tasks that are not done should be sorted by priority, while tasks that are done should be sorted by date: Select * from tasks order by done asc If done=0 additionally order by priority desc I...

How to find the previous and next record using a single query in MySQL?

Hello, I have a database, and I want to find out the previous and next record ordered by ID, using a single query. I tried to do a union but that does not work. :( SELECT * FROM table WHERE `id` > 1556 LIMIT 1 UNION SELECT * FROM table WHERE `id` <1556 ORDER BY `product_id` LIMIT 1 Any ideas? Thanks a lot. ...

Form input correction & database query- Codeigniter

Hey guys. I'm re-creating a registration for my website using codeigniter. On my previous page I did it in simple php and HTML. Now that I'm trying to recreate it in codeigniter I seem to be running into difficulties. The following code is a function that is called to validate an email address that the user puts in. It is supposed to q...

Deleting a row in MySQL

I have a table: NACHRICHT_ID | VERFASSER_USERNAME | BETREFF | TEXT | DATUM | EMPAENGER_ID ------------------------------------------------------------------------------ | | | | 2009-07-01| 1 | h | hfgh | hfgh | 23:15:10 | 31 --------------------------...

Using PHP date to display weekly dates

I'm not really sure what to call this. But basically I want to be able to have a user click on "Popular Stories from This Week", and it will take them to a page that will have other stuff but mainly the dates. For example, July 10-17. I'm currently using this code: $seven_days_ago = date('j') - 7; $time_span = date('F ' . $se...

MySQL: Creating table with FK error (errno 150)

I've tried searching on this error and nothing I've found helps me, so I apologize in advance if this is a duplicate and I'm just too dumb to find it. I've created a model with MySQL Workbench and am now attempting to install it to a mysql server. Using File > Export > Forward Engineer SQL CREATE Script... it outputs a nice big file fo...

Hierarchical Data in MySQL

I've got a sort of tree like thing going on in my MySQL database. I have a database that has categories, and each category has a subcat. I'm keeping all the categories in one table, so the columns are like this: *categories table* id | name | parent_id 1 | Toys | 0 2 | Dolls | 1 3 | Bikes | 1 Each item in my database is assigned...

Query to be used in the conditions, when space in between the value

Hi! I am using JQuery and CakePHP for my application. I have a table Attributes(id,form_id,label,type) In this table, I have an entry like 1 1 First name Text 2 1 Lastname Text I have written a CakePhp query to retrieve the id given the form_id and the label $attri = $this->Attribute->find('all', array( 'fields'=> array('Attri...

RoR segfaults when accessing MySQL database

I'm running Ruby on Rails on a Solaris 10 server. I just reinstalled Ruby today to get things to work correctly with my MySQL system so I could add a new application I've been working on. I also updated my gems and the system. MySQL is the only thing in this system that hasn't been updated lately, that's at version 5.1.25. When I run th...

mysql connection

Im using this code for mysql connection $con = mysql_connect("localhost:/var/lib/mysql/mysql.sock", "abc" , "xyz"); if (!$con) { die('Could not connect: '); } mysql_select_db("database_name", $con); Im including this file on the top of every php file that im using for parsing and then storing the data in database.Now when the orig...

Is it really helpful to store content in flat-file than a database for better google/yahoo/bing searches?

I just came across few articles, while selecting a wiki for my personal site. I am confused, as i am setting a personal wiki for my personal projects, i think a flat file system is good, even to maintain revisions of the design documents, design decisions, and comments/feedbacks from peers. But the internet gives a mixed bag of response...

Mysql query: retrieve current date query

In mysql database i have this column called: Name: Date Type: datetime I have few values in that column: 2009-01-05 01:23:35 2009-03-08 11:58:11 2009-07-06 10:09:03 How do I retrieve current date? I am using php. in php: <?php $today = date('Y-m-d');?> How to write a mysql query to retrieve all today date data? Should i chang...

Mysql B+ Tree implementation

Mysql uses B+ tree for implementing indexes. Lets say my primary index is of type (name, age, city). Can someone throw some light how Mysql implements a B+ tree on these kinds of keys ? My hunch is that it treats the whole tuple as a key and it treats it like a partial order. Eg: (John, 24, New Jersey) < (John, 24, New York) < (John, ...

DB design : members table separate or all in one table ?

I want to create a table of friends with personal information and log on details. What better to separate the members table to 2 tables , one contain minimal details , second with Other details. or remain in one table ? i have a lot of tables that contain the foreign key of the member. ...

What characters have to be escaped to prevent (My)SQL injections?

Hi, I'm using MySQL API's function mysql_real_escape_string() Based on the documentation, it escapes the following characters: \0 \n \r \ ' " \Z Now, I looked into OWASP.org's ESAPI security library and in the Python port it had the following code (http://code.google.com/p/owasp-esapi-python/source/browse/esapi/codecs/mysql.py): ...

Problem creating a reference table in mysql using php

Hi all. I am creating a two MySQL tables in PHP, using the code as given below: $sql = "CREATE TABLE qotwMember ( MemberId NOT NULL PRIMARY KEY, Name varchar(255), Passwork varchar(255), emailId varchar(255), )"; $sql = "CREATE TABLE qotwQuestion1111 ( QuestionId NOT NULL AUTO_INCREMENT, Question varchar(5000), MemberId varchar(255) ...

MySQL automated back up on Win Server

What is the best method for automating the task of backing up a MySQL DB on a Windows 2003 Server? ...

retrieve last updated column in mysql

Hi, I have a MySQL query that goes as follows (using Zend_Db): $sql = $handle->quoteInto("UPDATE board SET rank=rank+1 WHERE post_id=?", $postid); $handle->query($sql); (Rank isn't an auto-incrementing PK). I would like to now retrieve the value of rank without preforming another query. I've tried $handle->lastInsertId(); but it doe...

SQL help: select the last 3 comments for EACH student?

I have two tables to store student data for a grade-school classroom: Behavior_Log has the columns student_id, comments, date Student_Roster has the columns student_id, firstname, lastname The database is used to store daily comments about student behavior, and sometimes the teacher makes multiple comments about a student in a given da...