MYSQL :: Random Output

Random Rows, Non-random Ordering
Normally to select random rows from a table I'd do something like:

SELECT field1, field2, RAND() AS random
FROM mytable
ORDER BY random
LIMIT 5
Unfortunately, this time I need 5 random rows sorted on a different field:

SELECT field1, field2, RAND() AS random
FROM mytable
ORDER BY field2
LIMIT 5
Is there a way that I can still sort by field2 and yet still select 5 rows at random? Could the former query be run as a subquery and then a second ORDER BY clause run on those results? Worst-case I can re-sort the results in my host language, but a pure SQL solution would be best I think.

Bonus points if the answer can always put a particular row first in the results.

Xml Output
Can I get Xml output from a Select statement?. I Didn't see anything in the Docs.

Category Output
I am having some problems determining how to get php to query out a certian instance for me. I will try to describe it the best i can.

I am trying to display out all of the categories in my database to date..

My fields are set up as so..

field-> g_id, category, title, info, link

------------------------

now in my database i will have the different or the same categories in different fields.

PHP

function GuideCategory() { // Getting topic info $categories = $this->query("SELECT category FROM guides"); while ($Cat = $this->fetch_array($categories)) { echo $Cat[category]; }}

The code above is what i have now. But i don't want it to display the same category twice that way i don't get a huge list of replicas.

How To Loop An Output
I am having a go at creating a database and learnign how to use php to get the info onto a webpage.

I have a testing server on my local host I am using. I have a database with a table in it called hotels and fields (i think 9 I made up)

Anyway, I am simply trying to pull name (row 2) website (row 7) phone (row 8) from the table and display it.

I can't seem to get the code right .. I have tried a few different ways .. below is my most recent attempt and I just get a last line error.

Can someone point me in the right direction so I can get going ?

Output To File
how can i transfer data from table to file (ex. data.txt)?

According the book I'm raeding I should type this: '% mysql firstdb < rs.sql > data.txt' into system command line, but the substring '% mysql' looks a bit misleading (and I got an error here, the content of the file rs.sql : 'select * from sales;').

And again I encountered an error when I had type: 'firstdb < rs.sql;' in mysql server command line, ('source rs.sql;' - works fine :] ).

PHP Failing To Output First Row
I'm using the following code to output the contents of my database, which is working fine apart from the fact that it seems to be ignoring the first result.

$sql=mysql_connect ($host, $user, $pass) or die (mysql_error());
mysql_select_db ($data) or die (mysql_error());
$result = mysql_query ("SELECT tag, name, description FROM `tricktionary` WHERE alpha='a' ORDER BY `name` ASC") or die(mysql_error());
$trick = mysql_fetch_array ($result);

while($trick = mysql_fetch_array($result)){
echo '<div class="focus"><h2 id="'. $trick['tag'] .'">'. $trick['name'] .'</h2><p>'. $trick['description'] .'</p></div>';}

I've triple checked that the actual database entry in question has a value of 'a' for 'alpha', which it does, so is there something wrong with the while statement?

Restrict Output
i am sure it is a trivial question, but thanks for helping a newbie...
My problem:
I have say 2 tables
customers ___ attributions
linked with a key (custnb)

for any customers they are many attributions but I want to output only one record based on a given criteria (say smallest attribution.date )

How do I do that? if possible without creating an intermediary table (which is the combersome way I'm now doing it).

Output Process
I need to send MySql database records to another database via HTTP post to be interpreted by a post action script. It needs to appear as if the data came directly from an HTML webform. It would have to be a realtime post as the records are inserted into my database. Any idea what kind of process would have to be developed to make this happen?

Output Limitation
I am currently using MS Access to generate an index file which is sorted and formatted according to our Folio product sourcs file - (it contains all the tags etc... required for Folio to read)I wanted to know if MySQL can achieve the same result.
I have attached a table structure with the data that is in MS Access(PIPE delimited) and one of the output file generated.

If MySQL could do that same then all our Access databases will be removed.
the input file is called SACases.txt and the output file is called SACaseIndexFinal.txt

Output String
there is a function called "print" in SQLServer 2000, which returns a user-defined message to the client. and the message is returned as an informational error in ADO, OLE DB, and ODBC applications.
s there such a function in MySQL to output a string to client also?
i've searched the Docs but i did not find any thing like this.

Ordering Output
I originally posted this in the PostgreSQL area but it didn't get a response so I thought I'd post it here since the MySQL area gets more traffic (and it's sort of a general SQL question anyway).
I have a query:

SELECT new_id FROM list WHERE old_id IN (132, 147, 339...lots and lots of id's...498, 938, 372);

I would like the output (new_id) to be ordered in relation to the old_id's (as listed in parentheses), but instead the output just seems to be in some arbitrary order.

Output Parameter
I'd like to ask how to get the output parameter
from MySQL store procedure using C programming

MySQL Output
I have a leaderboard table which lists obivously users. One of the fields is "username". What I was wondering was how on earth would one make the username a link to say a profile of that user, i.e. on the click another SQL statement would be generated and bring up another set of details.
A wild guess of mine would say that you put href html round the output field, which links to another php script!?
Can anyone give me a one(or more if need be ) line example of how to achieve this?
I have seen it done on several websites, Ebay for example.

Displaying Output
i'm displaying output from table using a perl script.
i have a problem displaying the output of my table cause the content of the fields are in russian language.
in the table itself, for the fields i'm using:
Collation:utf8_bin
Type: Varchar(22)
it works normal when i look at it using PHPmyAdmin.
when i run a perl script to display the table output i see ?instead of the russian characters..How do i display it?

Output Only Displays Value If In Both Tables
I have two tables, Table1 and Table2. When i submit a name from my form ( eg : jimmy ), 'jimmy' is inserted into both tables. Now this part is fine. In the output, it displays the data from both tables ( output: jimmy - jimmy )

The problem i am having lies in my sql statement i believe.

Basically, the way i have it now, it says, if 'jimmy' is in Table1 AND in Table2, output the results, otherwise forget it!

It does not output anything if 'jimmy' is in NOT in Table1 but IS in Table2, or vice versa.

My code :

$result = mysql_query("SELECT".
"`TABLE1`.username,".
"`TABLE2`.username ".
FROM".
"`TABLE1`,".
"`TABLE2`".
"WHERE".
"`TABLE1`.username = `TABLE2`.username ".
"") or die(mysql_error());

Mysqlbinlog Wrong Output
I get following output from "mysqlbinlog mylog-bin-file.001":

# at 4
#030818 13:38:00 server id 1 Start: binlog v 4, server v created
700101 1:00:00

The file mylog-bin-file.001 contains many queries but they don't appear
in the output from mysqlbinlog.

What is wrong?

Query Output To Csv File
I'm using php/mysql for a website. One page the user can create a query to pull data from the database, but I want it to automatically save the data to a csv file. Is this possible, and how?

Query Output Alphabetacally?
I have a very simple website that is made for moderators of a game. We can insert a player ID, a screenshot link, and who inserted it. We have the option to search the database fopr a specific player ID and retrieve how many times he has been entered for an offence and the screenshot. We also have the option to query the entire database to output them all. However, it outputs in orfer of being inserted. Is there anyone for it to populate the table in alphabetical oder by the player ID?

Mysql Screen Output
I'm new to mysql. After executing a fairly simple select statement, the output appears in the dos window in a 'word wrap' format which makes it difficult to read especially when there are many columns and many rows. I can output the data to a file which is fine but that's an extra step to then specify the file and then open it.

Is there any way to modify the dos screen output( other than the 'display vertical' option) to a more readable format.

How To Output Sql Errors To A File
I am trying to output the errors of my INPUT operation to a file. I have created a list of sql statements from some input files. However, some of the data values set in the sql statements may contain outliers that are outside the domain of acceptable values in their respective fields.

How do I determine from a huge input file of sql statements which ones were acceptable and which ones were not?

For the time being, I am using the tee outputFile.txt command to save the console output. I am useing the source sqlFile.txt as the input file.

The outputFile.txt has all the output. Also, it gives messages like the following.

ERROR 1064 (42000): 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 '' at line 1

How do I know what sql statement caused this error? Is there anyway to indicate the particular sql statement caused this error?

Mysqldump Output Format
I'm trying to get a dump of my database that could be directly exploitable by a perl script that could read the txt file created line by line for my purposes. It was working very nice with previous version of MySQL. Now, the insertion block is outputed as 'in a row', with no more possibilities for my script to handle the informations as expected. Do you have a way to find out how parameter the mysqldump way to output the data in the database as wanted ? Code:

SQL Statement To Output Db To A File
Is there a way to issue some sort of sql statement to backup a database to a file?

Output To A File WITHOUT TAB As A Separator
I use this syntax "mysql -u$LOGIN -p$PSW < $tmp_sql > $res_out; " in order to get an output-file. This file will be forwarded to a routine that will read the file position for position, expecting fixed length on the strings. Today a TAB is used as a separator.

So, how do I get a fixed length on the columns in the output-file? (Server-version is 4.0.23)

Formatting 'group By' Output
i would like to display a group of events based on time like so...

6:00pm
event 1
event 2
event 3

6:30pm
event 4

7:00pm
event 5
event 6

9:00pm
event 7
event 8

know the query for this?

Returning Output Parameters To PHP
Is it possible to return output parameters from a MySQL stored procedure to PHP?

Controlling Output Of Mysqldump 10.9
My client is using a shared server running MySQL 2.4, however the in
house server is 4.1.12 with mysqldump 10.9. Is there a way to have the
10.9 version output a format that is fully compatible with 2.4?

Some of the CREATE TABLE statements in 10.9 are not accepted by 2.4.

I am not using any of the switches in creating the output
file other than the -u, -h and -p.

Output In Sql Mode Of GNU Emacs
I am using WinXP, GNU Emacs 23, MySQL 5.
When I start a mysql process with `sql-mysql' in emacs, no output is
displayed in the buffer until I quit or kill the process.

After I add the following line into the initialization file of emacs,

(setq sql-mysql-options '("-C" "-t" "-f" "-n"))

the output comes out, but the status line still doesn't show up. (e.g.
1
row in set (0.02 sec))

What's worse, I try to log the session with `T logfile', but no
logfile is generated.

Weird Output From Explain
I recently upgraded to mysql 5.1, now when I do
explain tablename

from the mysql command line it outputs, pages of "--------------------------------", then the fields, then pages of "---------------------------------"

Is this a mysql setting somewhere?
On previous versions it was just the top and bottom used to draw lines, but now I get pages, making explain almost useless from the command line.

These are small tables, too with just a few fields in them.

LOAD DATA Output
I have a batch file that logs into a MySQL database, and then calls a script file that loads a csv file into a table.

I pipe the output into a text file, but the text file is blank after I run. The LOAD worked as I see the records in the table. Does LOAD DATA output anything? What I am looking for is something like "5 records added to table" inside an output file. Is this possible?

OUTPUT RESULTS TO A FILE
Is there any way in MySQL to direct output from a query to a textfile?  I did a search on this and found the following code snippet here in tek-tips, however, I have been unable to make it work:

SELECT * INTO OUTFILE MyOutput.TXT
    FIELDS TERMINATED BY ","
    FROM MyFile
    WHERE Condition = MyCondition

Output Query Result
Can I output a mysql query result direct to an Access database? I know (probably) I could use outfile to csv and read that through ODBC but then I've got the problem of not being able to overwrite it. I'm thinking that if I can output direct into an mdb it would get around that limitation.

Limit Mysql Output By Letter
Hi I am building a drinks database/search site.

At present I have 26 tables 1 table for each letter like drinksa, drinkb, drinkc..ect which represent a, b, c...ect

I have realised this methed it not good practice as I can't search all 26 tables in one go.

What I would like to do now is put all the drink a to z in one table called drinks.

I would then be able to search that 1 table, this is fine I can do that without a problem.

But I am haing some difficulty getting my head around the front end what I mean is on the front end of the site on the drinks page there are links to each section like a, b ,c, d, e,......x, y, z.

But if all the data is in one table then all the drinks a to z would be output I know I can limit how many to output but I was wondering is it possible to limit by starting letter

For example when a user clicks "A" then all the drink starting with "A" are output but not any other drinks starting with a diffrent letter.

This would be done by usin an sql statment I belive by using limit but am not sure if its possble to limit by letter.

I have been looking at a lot of foums and so far I have not come across anything that may be of help so I though I'd ask

If anyone would point me in the right direction or could expmain if this is possible or a better way of doing this I would be very greatful

Column Names On Top Of The Query Output
Is it possible to get the columnnames on the first row of a query output? Is there a "flag" I have to add?

Formatted Output To A Text File
I'd like to be able to write selected fields from a table to a csv text file. However, I need to be able to write some header lines to the file first, then the csv data, then some footer lines.

Question 1: What is the best way to do this. Using the SELECT ... INTO OUTFILE won't allow me to write the headers and footers. TEE includes the mysql> prompts, etc. There doesn't seem to be a straight forward fopen, printf sort of commands.

Question 2: How do you write a string like the header lines? PRINT doesn't seem to work.

Joins Not Showing Proper Output
I have two tables I want to join, I only want the profile table to be counted if there is a user that matches the profile

SELECT COUNT(uid) as count
FROM users
WHERE created > 0 AND created < 1155655377 AND status > 0

this correctly outputs
164,142

When I join the profile values table I get

SELECT COUNT(u.uid) as count
FROM {users} u
INNER JOIN {profile_values} p
ON u.uid=p.uid
WHERE created > 0 AND created < 1155655377 AND status > 0

This incorrectly outputs
3,748,411

this is a test database so it has some problems but I want to know if there is a way to filter out all the problems with profile values table and only get back values that match the users table and discards anything with the profile values table that is not in the users table.

Correct Order By For Logical Output
I have a database with laws. Every law is a table. Every paragraph of the law is a row with among others one special column where the paragraph number (1,2,2a,2b,3,etc.) is saved.

Now I want to list the paragraphs in the correct way (2 befor 2a). I always thought that "order by column +0" is the correct syntax but it seems that I was wrong.

How Make Editable Output Fields?
How can I make the data that I get from the database in the browser editable? So make it that the data is just filled into forms and you can just change the content and press a button that updates the data on the MySQL server and shows the new values?

Or maybe a regular output field but with a button next to each row that brings up a popup (for example) and enables you to edit, and update the database. Then, after changing the data, I would also like the page to refresh itself.

How To Output A Row Of Data To Another Database Table
How can I output a row of data from one table to another table in the same database and to a table in another database?

I know I can dump the whole database to a file. Is there any way of making a file of just a single row of data?

Aborting Output In Windows Console
Occasionally, a mis-constructed SELECT statement will result in tens of thousands of data elements streaming by on my Windows console. How to I abort this output, and return to the MySQL prompt? (Note: <ctrl>-C aborts the entire window, requiring me to restart the window and logging back into MySQL.)

How To Use -H ( Produce Output In HTML Format )
I was trying to use -H to produce output in HTML Format, but in unable to , can somebody tell me how to use it

following stuff i tried

C:emp>mysql -u root -b -P 3306 -C -B -H -p
Enter password: ********

after entering Password , it got hanged , so i believe -H is suppose to be used somewhere else.

How To Limit Output Of Text Field
I would like to know what querie I need to print just the first few words of a text field record. For example, like the abridged listings in a page of search results.

Sorting Output - Question About ORDER By
I've got a data table named "admin" which stores real esate properties and I want it to pull waterfront properties, then display by price, sorted ASC

$sql="SELECT * FROM admin WHERE cat='waterfront' ORDER BY price ASC";

prices range from 99,000 to 1,575,000

but instead of recognizing 99,000 as lower than the one-million number, it shows the 1,575,000 first - obviously sorting by "1-9," regardless of the real number value.

Is there a better way to get it to sort numbers from smallest to largest?

Store And Output Formatted Text
I would like to build up a database of poetry but if I store the body text as 'text' it looses its formatting and then prints without the line breaks. How do I get around this?

Generate Script As An Output Line
I know you can generate a script as an output such as

select 'update table1 set item=''' || itemnum || ''' where item = ' || itemnum || ';'
from table2 where item='101';

however...i'm looking to bump it up a notch.

table two contains multiple records (4) with that itemnum so i need to use the distinct command to get only one occurance of it.

So...executing the above script will get me four lines of code. When I try to only get one line by using something like this:

select 'update table1 set item=''' || distinct itemnum || ''' where item = ' || distinct itemnum || ';'
from table2 where itemnum='101';

I get an error saying missing expression.

Sending Mysql Output To File
i have a bat file that has the following statement:
c:mysqlinmysql -u root tms < d:/manish/spooled_files/tms_compliance_trend.sql

this reads the sql script file and populates the mysql table

now suppose this process yielded some errors and i want to
say make a log file for it how can i do that

i tried this
c:mysqlinmysql -u root tms < d:/manish/spooled_files/tms_compliance_trend.sql > path/to/error.txt.

Wrong Output, Multiple Duplicated Rows, Why?
I don't use MySQL very often so every time I come back to it I am reminded that I am not an expert - much of what I learn after using it fades between sittings. I am trying to do what I thought would be a relatively easy join using a mapping table.

At the bottom of the post I have provide the script to quickly recreate the tables and data in this example.

When I query the database using the following query:

Limit Text Output In SELECT Query
I'm looking to output a list of news stories, but only outputting the first 100 or so characters of the text and only limited to the newest 4 entries in descending order.

At the moment I'm using this nugget: But it doesn't work.

$result=mysql_query('SELECT newsheading, (LEFT newsbody,100), newsdate FROM news ORDER BY newsid DESC LIMIT 4');

Random Output

is there any way to do a mysql RANDOM output? Like e.g. if I got a table and I want to have every row displayed of the table, but by random (changing whenever you look at it), but every entry being displayed once