Search Results

Search found 68715 results on 2749 pages for 'mysql data'.

Page 97/2749 | < Previous Page | 93 94 95 96 97 98 99 100 101 102 103 104  | Next Page >

• Escape characters in MySQL, in Ruby

  - by Swards

  I have a couple escaped characters in user-entered fields that I can't figure out. I know they are the "smart" single and double quotes, but I don't know how to search for them in mysql. The characters in ruby, when output from Ruby look like \222, \223, \224 etc irb> "\222".length => 1 So - do you know how to search for these in mysql? When I look in mysql, they look like '?'. I'd like to find all records that have this character in the text field. I tried mysql> select id from table where field LIKE '%\222%' but that did not work. Some more information - after doing a mysqldump, this is how one of the characters is represented - '\\xE2\\x80\\x99'. It's the smart single quote. Ultimately, I'm building an RTF file and the characters are coming out completely wrong, so I'm trying to replace them with 'dumb' quotes for now. I was able to do a gsub(/\222\, "'"). Thanks.

  Read the article

• How to verify if two tables have exactly the same data?

  - by SiLent SoNG

  Basically we have one table (original table) and it is backed up into another table (backup table); thus the two tables have exactly the same schema. At the beginning both tables (original table and backup table) contains exactly the same set of data. After sometime for some reason I need to verify whether dataset in the original table has changed or not. In order to do this I have to compare the dataset in the original table against the backup table. Let's say the original table has the following schema: `create table LemmasMapping ( lemma1 int, lemma2 int, index ix_lemma1 using btree (lemma1), index ix_lemma2 using btree (lemma2) )` How could I achieve the dataset comparision? Update: the table does not have a primary key. It simply stores mappings between two ids.

  Read the article

• Database (MySQL) structuring: pros and cons of multiple tables

  - by Gideon

  I am collecting data and storing it MySQL, for: 75 variables 55 countries Each year I have, at this stage since I am building this tool created a single table, of variables / countries (storing 1 year worth of data). Next year (and for several years after that) a new set of data will be input for each country. There are therefore 3 variables in controlling data returned to a user reviewing all collected data. The general form of any query would be: Show me these specifics variables, for these specific countries, for these specific years. (Show me average age and weight, for USA and Canada, for 2012 and 2009, for example) My question is, it seems that I have two options for arranging this data: -Multiple tables where I create a table of country / variable for each year data is collected - Single table and simply add a column (field) for the year that data relates to. As far as I can tell I could make these database calls with either sructure, but is one more powerful / efficient / quicker, and why? Thanks for your consideration. It's a PDO / PHP interface if that is relevent.

  Read the article

• How can I fake sql data while preserving statements without commenting my server-side code?

  - by Fedor

  I have to use hardcoded values for certain fields because at this moment we don't have access to the real data. When we do get access, I don't want to go through a lot of work uncommenting. Is it possible to keep this statement the way it is, except use '25' as the alias for ratecode? IF(special.ratecode IS NULL, br.ratecode, special.ratecode) AS ratecode, I have about 8 or so IF statements similar to this and I'm just too lazy ( even with vim ) to re-append while commenting out each if statement line by line. I would have to do this: $sql = 'SELECT u.*,'; // IF ( special.ratecode IS NULL, br.ratecode, special.ratecode) AS ratecode $sql.= '25 AS ratecode';

  Read the article

• PHP & MySQL storing multiple values in a database.

  - by R.I.P.coalMINERS

  I'm basically a front end designer and new to PHP and MySQL but I want a user to be able to store multiple names and there meanings in a MySQL database table named names using PHP I will dynamically create form fields with JQuery every time a user clicks on a link so a user can enter 1 to 1,000,000 different names and there meanings which will be stored in a table called names. All I want to know is how can I store multiple names and there meanings in a database and then display them back to the user for them to edit or delete using PHP MySQL? I created the MySQL table already. CREATE TABLE names ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, userID INT NOT NULL, name VARCHAR(255) NOT NULL, meaning VARCHAR(255) NOT NULL, PRIMARY KEY (id) ); Here is the HTML. <form method="post" action="index.php"> <ul> <li><label for="name">Name: </label><input type="text" name="name" id="name" /></li> <li><label for="meaning">Meaning: </label><input type="text" name="meaning" id="meaning" /></li> <li><input type="submit" name="submit" value="Save" /> <input type="hidden" name="submit" value="true" /></li> </ul> </form>

  Read the article

• MySQL Multiple Table Join

  - by hitman001

  I have a 3 tables that I'm trying to join and get distinct results. CREATE TABLE `car` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB mysql> select * from car; +----+-------+ | id | name | +----+-------+ | 1 | acura | +----+-------+ CREATE TABLE `tires` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `tire_desc` varchar(255) DEFAULT NULL, `car_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `new_fk_constraint` (`car_id`), CONSTRAINT `new_fk_constraint` FOREIGN KEY (`car_id`) REFERENCES `car` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB mysql> select * from tires; +----+-------------+--------+ | id | tire_desc | car_id | +----+-------------+--------+ | 1 | front_right | 1 | | 2 | front_left | 1 | +----+-------------+--------+ CREATE TABLE `lights` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `lights_desc` varchar(255) NOT NULL, `car_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `new1_fk_constraint` (`car_id`), CONSTRAINT `new1_fk_constraint` FOREIGN KEY (`car_id`) REFERENCES `car` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB mysql> select * from lights; +----+-------------+--------+ | id | lights_desc | car_id | +----+-------------+--------+ | 1 | right_light | 1 | | 2 | left_light | 1 | +----+-------------+--------+ Here is my query. mysql> SELECT name, group_concat(tire_desc), group_concat(lights_desc) FROM car left join tires on car.id = tires.car_id left join lights on car.id = car_id group by car.id; +-------+-----------------------------------------------+-----------------------------------------------+ | name | group_concat(tire_desc) | group_concat(lights_desc) | +-------+-----------------------------------------------+-----------------------------------------------+ | acura | front_right,front_right,front_left,front_left | right_light,left_light,right_light,left_light | +-------+-----------------------------------------------+-----------------------------------------------+ I get duplicate entires and this is what I would like to get. +-------+-----------------------------------------------+--------------------------------+ | name | group_concat(tire_desc) | group_concat(lights_desc) | +-------+-----------------------------------------------+--------------------------------+ | acura | front_right,front_left | right_light,left_light | +-------+-----------------------------------------------+--------------------------------+ I cannot use distinct in group_concat because I might have legitimate duplicates which I would like to keep. Is there any way to do this query using joins and not using inner selects like the statement below? SELECT name, (select group_concat(tire_desc) from tires where car.id = tires.car_id), (select group_concat(lights_desc) from lights where car.id = lights.car_id) FROM car Also, if I will use inner selects, will there be any performance issues over joins?

  Read the article

• Migrate from Oracle to MySQL

  - by Cassy

  Hi together. We ran into serious performance problems with our Oracle database and we would like to try to migrate to a MySQL-based database (either MySQL directly or, more preferrable, Infobright). The thing is, we need to let the old and the new system overlap for at least some weeks if not months, before we actually know, if all features of the new database match our needs. So, here is our situation: The Oracle database consists of multiple tables with each millions of rows. During the day, there are literally thousands of statements, which we cannot stop for migration. Every morning, new data is imported into the Oracle database, replacing some thousands of rows. Copying this process is not a problem, so we could, in theory, import in both databases in parallel. But, and here lies the challenge, for this to work, we need to have an export from the Oracle database with a consistent state from one day. (We cannot export some tables on Monday and some others on Tuesday, etc.) This means, that at least the export should be finished in less than one day. Our first thought was to dump the schema, but I wasn't able to find a tool to import an Oracle dump file into mysql. Exporting tables in CSV files might work, but I'm afraid it could take too long. So my question now is: What should I do? Is there any tool to import Oracle dump files into MySQL? Does anybody have any experience with such a large-scale migration? Thanks in advance, Cassy PS: Please, don't suggest performance optimization techniques for Oracle, we already tried a lot :-)

  Read the article

• Unable to connect to mysql through JDBC connector through Tomcat or externally.

  - by iftrue

  I've installed a stock mysql 5.5 installation, and while I can connect to the mysql service via the mysql command, and the service seems to be running, I cannot connect to it through spring+tomcat or from an external jdbc connector. I'm using the following URL: jdbc:mysql://myserver.com:myport/mydb with proper username/password, but I receive the following message: server.com: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. the driver has not received any packets from the server. and tomcat throws: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) Which seems to be the same issue as if I try to connect externally.

  Read the article

• How to proxy to different named databases on the same server using MySQL Proxy?

  - by cclark

  I would like to have two databases on my MySQL server: DEV_DB_A DEV_DB_B However, in order to keep everyone's scripts, Query Browser settings and anything else from changing when we switch from using on DB to another I'd like to have everyone connect to DEV_DB and then use something like MySQL Proxy running a lua script which knows the currently active DB is DEV_DB_A and routes queries to there. If we restore a fresh version of the DB to DEV_DB_B or make some changes (e.g. partition a table) we can easily switch to DEV_DB_B by changing one Lua script instead of updating references everywhere. I had hoped I might be able to symlink inside of the mysql data directory but that didn't work so it seems like MySQL Proxy is a reasonable approach. Being new to Lua and MySQL Proxy I'm wondering if anyone else has approached the problem this way and how it worked.

  Read the article

• how can I get back /usr/local/bin/mysql on Snow Leopard?

  - by Ole Media

  From terminal and trying to uninstall macports, I ran a command, rm -rf / macports...., that erased bunch of stuff. I feel ashamed about this, not realizing the space after /. Since then, mysql is running but I cannot execute any of the mysql commands because it is not under /usr/local/bin/ I went ahead an reinstall mysql but without luck. What steps do you recommend on doing in order to be able to run mysql, mysqlduml, mysqladmin, from terminal? I can access databases from phpmyadmin, so mysql is running, don't ask me how.

  Read the article

• EC2 Amazon Linux AMI MySQL CPU @ 62% When Idle?

  - by Jeff

  I am running MySQL on an Amazon Linux AMI. There is nothing connected to it. There are no connections and no other applications running that use MySQL. It is completely idle, but yet, top is reporting that mysql is using 62% of the CPU? Why is this happening and how do I fix it? Cpu(s): 0.2%us, 0.2%sy, 0.0%ni, 97.8%id, 0.0%wa, 0.0%hi, 0.0%si, 1.7%st Mem: 1738504k total, 390708k used, 1347796k free, 56888k buffers Swap: 917500k total, 0k used, 917500k free, 229804k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2959 mysql 20 0 466m 39m 5244 S 62.2 2.3 4:00.67 mysqld 1 root 20 0 19252 1504 1212 S 0.0 0.1 0:00.20 init 2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd There are no connections... mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 5 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-----------+------+---------+------+-------+------------------+

  Read the article

• MySQL Connect in 4 Days - Sessions From Users and Customers

  - by Bertrand Matthelié

  72 1024x768 Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Cambria","serif";} Let’s review today the conference sessions where users and customers will describe their use of MySQL as well as best practices. Remember you can plan your schedule with Schedule Builder. Saturday, 11.30 am, Room Golden Gate 7: MySQL and Hadoop—Chris Schneider, Ning.com Saturday, 1.00 pm, Room Golden Gate 7: Thriving in a MySQL Replicated World—Ed Presz and Andrew Yee, Ticketmaster Saturday, 1.00 pm, Room Golden Gate 8: Rick’s RoTs (Rules of Thumb)—Rick James, Yahoo! Saturday, 2.30 pm, Room Golden Gate 3: Scaling Pinterest—Yashwanth Nelapati and Evrhet Milam, Pinterest Saturday, 4.00 pm, Room Golden Gate 3: MySQL Pool Scanner: An Automated Service for Host Management—Levi Junkert, Facebook Sunday, 10.15 am, Room Golden Gate 3: Big Data Is a Big Scam (Most of the Time)—Daniel Austin, PayPal Sunday, 11.45 am, Room Golden Gate 3: MySQL at Twitter: Development and Deployment—Jeremy Cole and Davi Arnaut, Twitter Sunday, 1.15 pm, Room Golden Gate 3: CERN’s MySQL-as-a-Service Deployment with Oracle VM: Empowering Users—Dawid Wojcik and Eric Grancher, DBA, CERN Sunday, 2.45 pm, Room Golden Gate 3: Database Scaling at Mozilla—Sheeri Cabral, Mozilla Sunday, 5.45 pm, Room Golden Gate 4: MySQL Cluster Carrier Grade Edition @ El Chavo, Latin America’s #1 Facebook Game—Carlos Morales, Playful Play You can check out the full program here as well as in the September edition of the MySQL newsletter. Not registered yet? You can still save US$ 300 over the on-site fee – Register Now!

  Read the article

• The Best Data Integration for Exadata Comes from Oracle

  - by maria costanzo

  Oracle Data Integrator and Oracle GoldenGate offer unique and optimized data integration solutions for Oracle Exadata. For example, customers that choose to feed their data warehouse or reporting database with near real-time throughout the day, can do so without decreasing  performance or availability of source and target systems. And if you ask why real-time, the short answer is: in today’s fast-paced, always-on world, business decisions need to use more relevant, timely data to be able to act fast and seize opportunities. A longer response to "why real-time" question can be found in a related blog post. If we look at the solution architecture, as shown on the diagram below,  Oracle Data Integrator and Oracle GoldenGate are both uniquely designed to take full advantage of the power of the database and to eliminate unnecessary middle-tier components. Oracle Data Integrator (ODI) is the best bulk data loading solution for Exadata. ODI is the only ETL platform that can leverage the full power of Exadata, integrate directly on the Exadata machine without any additional hardware, and by far provides the simplest setup and fastest overall performance on an Exadata system. We regularly see customers achieving a 5-10 times boost when they move their ETL to ODI on Exadata. For  some companies the performance gain is even much higher. For example a large insurance company did a proof of concept comparing ODI vs a traditional ETL tool (one of the market leaders) on Exadata. The same process that was taking 5hrs and 11 minutes to complete using the competing ETL product took 7 minutes and 20 seconds with ODI. Oracle Data Integrator was 42 times faster than the conventional ETL when running on Exadata.This shows that Oracle's own data integration offering helps you to gain the most out of your Exadata investment with a truly optimized solution. GoldenGate is the best solution for streaming data from heterogeneous sources into Exadata in real time. Oracle GoldenGate can also be used together with Data Integrator for hybrid use cases that also demand non-invasive capture, high-speed real time replication. Oracle GoldenGate enables real-time data feeds from heterogeneous sources non-invasively, and delivers to the staging area on the target Exadata system. ODI runs directly on Exadata to use the database engine power to perform in-database transformations. Enterprise Data Quality is integrated with Oracle Data integrator and enables ODI to load trusted data into the data warehouse tables. Only Oracle can offer all these technical benefits wrapped into a single intelligence data warehouse solution that runs on Exadata. Compared to traditional ETL with add-on CDC this solution offers: §  Non-invasive data capture from heterogeneous sources and avoids any performance impact on source §  No mid-tier; set based transformations use database power §  Mini-batches throughout the day –or- bulk processing nightly which means maximum availability for the DW §  Integrated solution with Enterprise Data Quality enables leveraging trusted data in the data warehouse In addition to Starwood Hotels and Resorts, Morrison Supermarkets, United Kingdom’s fourth-largest food retailer, has seen the power of this solution for their new BI platform and shared their story with us. Morrisons needed to analyze data across a large number of manufacturing, warehousing, retail, and financial applications with the goal to achieve single view into operations for improved customer service. The retailer deployed Oracle GoldenGate and Oracle Data Integrator to bring new data into Oracle Exadata in near real-time and replicate the data into reporting structures within the data warehouse—extending visibility into operations. Using Oracle's data integration offering for Exadata, Morrisons produced financial reports in seconds, rather than minutes, and improved staff productivity and agility. You can read more about Morrison’s success story here and hear from Starwood here. From an Irem Radzik article.

  Read the article

• MySQL documentation writer for MEM and Replication wanted!

  - by stefanhinz

  As MySQL is thriving and growing, we're looking for an experienced technical writer located in the UK or Ireland to join the MySQL documentation team. For this job, we need the best and most dedicated people around. You will be part of a geographically distributed documentation team responsible for the technical documentation of all MySQL products. Team members are expected to work independently, requiring discipline and excellent time-management skills as well as the technical facilities and experience to communicate across the Internet. Candidates should be prepared to work intensively with our engineers and support personnel. The overall team is highly distributed across different geographies and time zones. Our source format is DocBook XML. We're not just writing documentation, but also handling publication. This means you should be familiar with DocBook, and willing to learn our publication infrastructure. Your areas of responsibility would initially be MySQL Enterprise Monitor, and MySQL Replication. This means you should be familiar with MySQL in general, and preferably also with the MySQL Enterprise offerings. A MySQL certification will be considered an advantage. Other qualifications you should have: Native English speaker 5 or more years previous experience in writing software documentation Familiarity with distributed working environments and versioning systems such as SVN Comfortable with working on multiple operating systems, particularly Windows, Mac OS X, and Linux Ability to administer own workstations and test environment Excellent written and oral communication skills Ability to provide (online) samples of your work, e.g. books or articles If you're interested, contact me under [email protected]. For reference, the job offer can be viewed here.

  Read the article

• Building MySQL with boost on windows

  - by user13177919

  As you've probably heard already MySQL needs boost to build. However, in the good ol' MySQL tradition, the above link does give you only the instructions on how to build it on linux. And completely ignores the fact that there're other OSes too that people develop on. To fill in that gap, I've compiled a small step by step guide on how to do it on windows. Note that I always, as a principle, build out-of-source. The typical setup I have is : bzr clone lp:~mysql/mysql-server/5.7 mysql-trunkcd mysql-trunkmkdir bldcd bldcmake -DWITH_DEBUG=1 -DMYSQL_PROJECT_NAME=mysql-trunk ..devenv /build debug mysql-trunk.sln This has been tested to work on a 32 bit compile using VS2013 on a Windows7 64 bit build. Note that you'll need other things too (bison, eventually openssl etc) that I will assume you already have set up. Steps: Download Boost 1.55.0. It's the *only* version that is known to work currently. Extract boost_1_55_0/ from the zip to c:\boost\boost_1_55_0 Go to Control Panel/System/Environment variables and set WITH_BOOST=C:\boost\boost_1_55_0 in User variables. Make sure you restart your open command line terminal windows after this !  If you're upgrading from non-boost build, remove your bld/ directory and create a new one. run cmake as you'd typically do. You should get: -- Local boost dir C:/boost/boost_1_55_0 -- Local boost zip LOCAL_BOOST_ZIP-NOTFOUND -- BOOST_VERSION_NUMBER is #define BOOST_VERSION 105500 -- BOOST_INCLUDE_DIR C:/boost/boost_1_55_0 Build as normal (devenv /build debug ...). It should work.

  Read the article

• Which of the following relational database management systems would a company adopt (for migration), if any, MS Access, MS SQL Server or MySQL?

  - by Hassan Hagi

  Dear programmers, as part of my final year university project, I am conducting research into relational database management systems such as Microsoft Office Access 2007, Microsoft SQL Server 2008 and MySQL 5.1. The description does not need to be detailed however; I am trying to find empirical evidence and professional opinion/fact to determine which of the three databases are best suited for the required size of company (stated or unstated). OS: Microsoft windows (XP or newer) Please consider the following, but full details are not necessary: Memory management Migration Design constraints Integrity (data and others) Triggers User constraints Ease of use Performance Crash Recovery (not the operating system) Total Cost of Ownership (TCO) Also any info on Open source (to do with the three RDBMS) Thank you for your time and help. Hassan Hagi

  Read the article

• 127.0.0.1:9051 doesnt work after apache, mysql, php installation?

  - by Rana Muhammad Waqas

  I have installed apache2, mysql, and php and now it doesnt let Vidalia run on localhost. i tried to change the TCP connection (controlport) to any other ip 192.168.0.40 and tried to change the default port 9051 to any other but that doesnt work. I thought apache is running so i used this command sudo service apache2 stop but that still doesnt work. So now when i type 127.0.0.1:9051 in browser it says and if i type only type 127.0.0.1 after stopping the apapche2 service with the command mentioned above it says unable to connect I am not sure what to do now Help!

  Read the article

• android geting data from sql

  - by sagar

  Hello i m new to android. i wont to connect to sql server for store and get data so so me one can help me sending code of android for do it.. i had tried to do tht with java nd it was workink but now i wont to create a aplication for android my java code is :: import java.sql.*; public class MysqlTest { public static void main (String[] args) { Connection conn = null; try { String userName = "pietro"; //change it to your username String password = "pietro"; //change it to your password String url = "jdbc:mysql://192.168.0.67:3306/registro"; Class.forName("com.mysql.jdbc.Driver").newInstance(); conn = DriverManager.getConnection(url, userName, password); Statement s = (Statement) conn.createStatement(); // code for create a tabel in server s.execute("create table School2 (rolno integer,sub text)"); // code for create a tabel in server s.execute("insert into School2(rolno,sub)values(10,'java')"); //code for add value in tabel s.execute("select rolno,sub from School2");//code for add value in tabel s.close(); System.out.println("Database connection established"); } catch (Exception e) { System.err.println("Cannot connect to database server"); } finally { if (conn != null) { try { conn.close (); System.out.println("Database connection terminated"); } catch (Exception e) { /* ignore close errors */ } } } } }

  Read the article

• What is the best way to INSERT a large dataset into a MySQL database (or any database in general)

  - by Joe

  As part of a PHP project, I have to insert a row into a MySQL database. I'm obviously used to doing this, but this required inserting into 90 columns in one query. The resulting query looks horrible and monolithic (especially inserting my PHP variables as the values): INSERT INTO mytable (column1, colum2, ..., column90) VALUES ('value1', 'value2', ..., 'value90') and I'm concerned that I'm not going about this in the right way. It also took me a long (boring) time just to type everything in and testing writing the test code will be equally tedious I fear. How do professionals go about quickly writing and testing these queries? Is there a way I can speed up the process?

  Read the article

• How can I make my Java desktop application to run on different computers in a network locally and updating same mysql database?

  - by JaMpa

  I have developed Java desktop application for registration of students in the computer laboratories in our college, but what I need is to make this application whenever it runs on any computer (Windows XP machines) on the laboratory each and every data filled by the college students goes to the same MySql database (through WAMP server) running on the server machine (Win Server 2003). Also, I need this application to run during booting of windows computers in the laboratory for the purpose when any student tries to switch on any PC in the comp lab before interfacing the desktop icons should sign in through user name and password when he has already registered or signing up in order for getting the access.

  Read the article

• showing null rows using join

  - by Pradyut Bhattacharya

  Hi, In mysql i m selecting from a table shouts having a foreign key to another table named "roleuser" with the matching column as user_id Now the user_id column in the shouts table for some rows is null (not actually null but with no inserts in mysql) How to show all the rows of the shouts table either with user_id null or not I m executing the sql statement SELECT s.*, r.firstname, r.lastname FROM shouts s left join roleuser r where r.user_id = s.user_id limit 50; which does not executes and shows 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 'where r.user_id = s.user_id limit 50' at line 2 but using inner join the sql executes which shows rows which only have user_id values in the shouts table. the nulls are not shown. SELECT s.*, r.firstname, r.lastname FROM shouts s inner join roleuser r where r.user_id = s.user_id limit 50; How can i show all the rows from the shouts table and null values in the firstname and lastname columns where the user_id is null in the shouts table. If not at all possible with sql may be using stored procedures... Thanks Pradyut

  Read the article

• Getting percentage of "Count(*)" to the number of all items in "GROUP BY"

  - by celalo

  Let's say I need to have the ratio of "number of items available from certain category" to "the the number of all items". Please consider a MySQL table like this: /* mysql> select * from Item; +----+------------+----------+ | ID | Department | Category | +----+------------+----------+ | 1 | Popular | Rock | | 2 | Classical | Opera | | 3 | Popular | Jazz | | 4 | Classical | Dance | | 5 | Classical | General | | 6 | Classical | Vocal | | 7 | Popular | Blues | | 8 | Popular | Jazz | | 9 | Popular | Country | | 10 | Popular | New Age | | 11 | Popular | New Age | | 12 | Classical | General | | 13 | Classical | Dance | | 14 | Classical | Opera | | 15 | Popular | Blues | | 16 | Popular | Blues | +----+------------+----------+ 16 rows in set (0.03 sec) mysql> SELECT Category, COUNT(*) AS Total -> FROM Item -> WHERE Department='Popular' -> GROUP BY Category; +----------+-------+ | Category | Total | +----------+-------+ | Blues | 3 | | Country | 1 | | Jazz | 2 | | New Age | 2 | | Rock | 1 | +----------+-------+ 5 rows in set (0.02 sec) */ What I need is basically a result set resembles this one: /* +----------+-------+-----------------------------+ | Category | Total | percentage to the all items | (Note that number of all available items is "9") +----------+-------+-----------------------------+ | Blues | 3 | 33 | (3/9)*100 | Country | 1 | 11 | (1/9)*100 | Jazz | 2 | 22 | (2/9)*100 | New Age | 2 | 22 | (2/9)*100 | Rock | 1 | 11 | (1/9)*100 +----------+-------+-----------------------------+ 5 rows in set (0.02 sec) */ How can I achieve such a result set in a single query? Thanks in advance.

  Read the article

• GROUP BY ID range?

  - by d0ugal

  Given a data set like this; +-----+---------------------+--------+ | id | date | result | +-----+---------------------+--------+ | 121 | 2009-07-11 13:23:24 | -1 | | 122 | 2009-07-11 13:23:24 | -1 | | 123 | 2009-07-11 13:23:24 | -1 | | 124 | 2009-07-11 13:23:24 | -1 | | 125 | 2009-07-11 13:23:24 | -1 | | 126 | 2009-07-11 13:23:24 | -1 | | 127 | 2009-07-11 13:23:24 | -1 | | 128 | 2009-07-11 13:23:24 | -1 | | 129 | 2009-07-11 13:23:24 | -1 | | 130 | 2009-07-11 13:23:24 | -1 | | 131 | 2009-07-11 13:23:24 | -1 | | 132 | 2009-07-11 13:23:24 | -1 | | 133 | 2009-07-11 13:23:24 | -1 | | 134 | 2009-07-11 13:23:24 | -1 | | 135 | 2009-07-11 13:23:24 | -1 | | 136 | 2009-07-11 13:23:24 | -1 | | 137 | 2009-07-11 13:23:24 | -1 | | 138 | 2009-07-11 13:23:24 | 1 | | 139 | 2009-07-11 13:23:24 | 0 | | 140 | 2009-07-11 13:23:24 | -1 | +-----+---------------------+--------+ How would I go about grouping the results by day 5 records at a time. The above results is part of the live data, there is over 100,000 results rows in the table and its growing. Basically I want to measure the change over time, so want to take a SUM of the result every X records. In the real data I'll be doing it ever 100 or 1000 but for the data above perhaps every 5. If i could sort it by date I would do something like this; SELECT DATE_FORMAT(date, '%h%i') ym, COUNT(result) 'Total Games', SUM(result) as 'Score' FROM nn_log GROUP BY ym; I can't figure out a way of doing something similar with numbers. The order is sorted by the date but I hope to split the data up every x results. It's safe to assume there are no blank rows. Doing it above with the data you could do multiple selects like; SELECT SUM(result) FROM table LIMIT 0,5; SELECT SUM(result) FROM table LIMIT 5,5; SELECT SUM(result) FROM table LIMIT 10,5; Thats obviously not a very good way to scale up to a bigger problem. I could just write a loop but I'd like to reduce the number of queries.

  Read the article

• Creating PHP strings using other variables...works manually, can't figure out how automatically

  - by Matt

  Hello, I'm trying to get a variable to be formed automatically using data pulled from a mysql database. I know the data is being pulled from the database in some form resembling its original form, but that data does not act the same as data that is manually typed and assigned to a string. For example, if a cell in a mysql table says... I said "goodbye" before I left. She also said "goodbye." ...and I manually copy/paste it and add the necessary escapes... $string1 = " I said \"goodbye\" before I left. She also said \"goodbye.\" "; ...that does not equal... $string1 = $mysqlResultArray['specificCellWithQuoteShownAbove'] Interestingly, if I echo both versions of $string1 and view the output, they appear to be exactly the same. But they do not function the same when put through various functions I've created. The functions only work if I do the manual copy/paste method--which is not what I want, obviously. I'm not sure if it has to do with the line breaks or the escapes--or some combination of the two. But while both strings are superficially the same, they are apparently functionally different and I don't know why. So how can I create $string1 without manually copy/pasting the contents from the mysql entry and instead querying for the data and assigning it to $string1 in such a way that it's exactly functionally equivalent as the manual copy/pasted string?

  Read the article

• Ordering by number of rows?

  - by Rob

  Alright, so I have a table outputting data from a MySQL table in a while loop. Well one of the columns it outputs isn't stored statically in the table, instead it's the sum of how many times it appears in a different MySQL table. Sorry I'm not sure this is easy to understand. Here's my code: $query="SELECT * FROM list WHERE added='$addedby' ORDER BY time DESC"; $result=mysql_query($query); while($row=mysql_fetch_array($result, MYSQL_ASSOC)){ $loghwid = $row['hwid']; $sql="SELECT * FROM logs WHERE hwid='$loghwid' AND time < now() + interval 1 hour"; $query = mysql_query($sql) OR DIE(mysql_error()); $boots = mysql_num_rows($query); //Display the table } The above is the code displaying the table. As you can see it's grabbing data from two different MySQL tables. However I want to be able to ORDER BY $boots DESC. But as its a counting of a completely different table, I have no idea of how to go about doing that. I would appreciate any help, thank you.

  Read the article

< Previous Page | 93 94 95 96 97 98 99 100 101 102 103 104  | Next Page >