mysql

Mysql: ERROR 1005 (HY000): Can't create table 'receitascakephp.recipes' (errno: 150)

CREATE TABLE `users` ( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `username` VARCHAR(75) NOT NULL, `password` VARCHAR(75) NOT NULL, `image` VARCHAR(255) ); CREATE TABLE `recipes` ( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `method` TEXT NOT NULL, ...

Multiple WHERE with LIMIT MySQL

Using PHP, is it possible to select multiple rows from one table, in a MySQL database, with different WHERE clauses having a specific LIMIT for each WHERE clause. For example: SELECT * FROM the_table WHERE color = 'blue' LIMIT 5 OR color = 'red' LIMIT 10 I know the above statement does not work. But is there a way to do this with a s...

Does the order of the fields in the WHERE clause affect the performance?

for example: if there are two WHERE clause： WHERE stringA="value" AND intB=1. ...

Different ORDER BY for each SELECT in a UNION with MySQL

Using PHP and MySQL, is there a way to use a different ORDER BY for each of the SELECT statements in a UNION? SELECT * FROM the_table WHERE color = 'blue' ORDER BY price ASC LIMIT 5 UNION ALL SELECT * FROM the_table WHERE color = 'red' ORDER BY RAND() LIMIT 10 The above statement does not work. It seems you can only do an ORDER BY on ...

How to select this in mysql ?

the field value equal to zero OR it is NULL ? ...

Transaction safe insertion of node in nested set?

I am storing hierarchical data in mysql in the form of a nested set. myTable id, title, lft, rgt I use the following series of sql statements to insert a new node: SELECT @myLeft := lft FROM myTable WHERE ID = $id; UPDATE myTable SET rgt = rgt + 2 WHERE rgt > @myLeft; UPDATE myTable SET lft = lft + 2 WHERE lft > @myL...

Adjacency List Model Select by Path

I'm trying to select a node in my heirarchical system. I have the "/Path/To/Some/Node" (In exactly that form) and I am trying to figure out how I can get the children of "Node". Naturally "Node" is pseudo-unique, in that it is the only child called "Node" inside of Some, but there could be another "Node" inside of "Path" so you obvious...

Problems writing a module for phpBMS

I am trying to write a small, simple module for phpBMS. I am basically modifying and simplifying one that exists, and have gotten to the stage of creating the table and pages in phpBMS itself and making the module installable. I am now trying to create the php page that will allow for new records to be inserted into the table via phpBM...

Counterintuitive results from setting the connection character set

I have nearly completed the task of overhauling my web app to be properly "UTF-8 aware". I have found, though, that if I set the connection character set to utf8 using mysqli_set_charset, the result is that output appears incorrectly (indeed it appears as though the page's character encoding had been misidentified), whereas if I do not s...

What is the easiest way to visualize transactional data utilizing using a data dump like MySQL, CSV etc etc

Essentially I want to be able to feed it data and see fancy charts and graphs displaying information out of the data based on x amount of transactions etc etc and/or SQL statements etc. It does not matter if it is client based or web based. Open source would be great but commercial is fine also. Thanks ...

Modifying a PHP page to show a different form

I am trying to modify a php module, which shows a form on a webpage to be different. The original module code is here, and instead of showing menus, I just want to show form fields with names. Here is the code I have so far: http://tinyurl.com/2cjdfuh (redirects to nomorepasting.com) which is essentially just setting up the database ta...

Rails 3 Mysql Problems

Trying to start a new Rails 3 beta 4 app with mysql.... Running OS X Snow Leopard. WIth previous versions of Rails I have no problem with MySQL. But now when I start the Rails 3 app I get the following error when I click "About Your Application Environment" on the Rails index.html startup screen: undefined method `init' for Mysql:Class ...

Selecting large amount of rows from database table with MySQL

I have a table with attributes like this: likes(id, message, ip, time) id is the primary key and is auto-incrementing. Whet I want to do is select 30 rows from this table based on an id that I provide. I can build a random array of all IDs I want to grab from the table, now I just need to go about doing that. My usual SQL query would...

Left Join duplicates

I have several tables I need to query in order to get all the rows for a certain user. The tables basically look like this contact ======= id_contact PK firstName lastName ... contact_phone =============== id_contact_phone, PK id_contact, FK id_phone_type, FK phone ... phone_type ============ id_phone_type PK phone_type .... And the...

Starting MySQL Routines and Scripting

I have been using MySQL for a while, but only with basic queries and some joins. I want to start using Stored Procedures but I am having trouble finding a good resource to help me with what I want to know. The main thing I want to know is how to check the value of any field in the entire database before returning anything. For example: ...

mysql:show all value of three fields

hi all...i have a question... i have a DB..i'm just want to show all value of 3 fields from DB,they are Line,Model,and NIK... i want it order by insp_datetime field...and descending..limit 0,30.. i've been try like this: SELECT * FROM Inspection_report WHERE NIK='".$NIK."' AND Line='".$Line."' AND Model='".$Model."' ORDER BY Insp_datet...

Compare DateTime values by Min and not by Day in Coldfusion CFQUERY

This is the query that I have. <cfquery name="qryname" datasource="dsn"> UPDATE ticketlist SET status = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="Expired"> WHERE expdatetime < <cfqueryparam value = "#yourDate#" cfsqltype = "CF_SQL_DATE" maxLength = "19"> </cfquery> It is able to give me all the results who's expdatetime is l...

MySQL - How can this query be optimised?

The following query works, but its very slow for 10 records (2 seconds). The profiling says its creating a tmp table, but I'm not sure why. Basically, I'm joining the current user, to the acl groups, to get all groups they are in, then joining the groups to the companies, to get all companies they are in, then joining the companies to t...

Using MySql, can I sort a column but have 0 come last?

I want to sort by an column of ints ascending, but I want 0 to come last. Is there anyway to do this in MySql? ...

Mysql query parameter issue in Perl

Got a little problem in my code, the query works fine if I manually put values in. But failed if I use my variable. The code shows below my $get_meter_id = $dbh->prepare("SELECT * from t_readings where meter_serial = '21001652' AND ..."); $get_meter_id->execute() or die "Couldn't execute statement: ".$get_meter_id->errstr; my $meter_reg...