Visit Our UNIX and Linux User Community

Top Forums Shell Programming and Scripting Help needed to format mysql output Post 302395882 by Neo on Wednesday 17th of February 2010 07:53:26 AM
Old 02-17-2010
My experience is that you should know the nature of the MySQL query, i.e. number and size of rows and columns before deciding on how to format the output.

Generally, we should read most queries into an array first, or an array of arrays and then print the output in loop where we can format the output properly.

Simply adding a newline to a row with large fields, blobs, and or many columns, will be messy.

There should be some structure to the query unless the query is trivial like getting only a few small fields.
 
Test Your Knowledge in Computers #794
Difficulty: Medium
1/10 has a finite binary representation.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

help needed in date format

i need to grep date in the format year-month-day,,,,,,, actually i need to grep those dates other than current date.......... can anyone help me in this...........i need a format of date which would grep previous date except current date (1 Reply)
Discussion started by: ali560045
1 Replies

2. Shell Programming and Scripting

capturing output from top and format output

Hi all, I'd like to capture the output from the 'top' command to monitor my CPU and Mem utilisation.Currently my command isecho date `top -b -n1 | grep -e Cpu -e Mem` I get the output in 3 separate lines.Tue Feb 24 15:00:03 Cpu(s): 3.4% us, 8.5% sy .. .. Mem: 1011480k total, 226928k used, ....... (4 Replies)
Discussion started by: new2ss
4 Replies

3. Shell Programming and Scripting

convert date format to mysql date format in log file

I have a comma delimited log file which has the date as MM/DD/YY in the 2nd column, and HH:MM:SS in the 3rd column. I need to change the date format to YYYY-MM-DD and merge it with the the time HH:MM:SS. How will I got about this? Sample input 02/27/09,23:52:31 02/27/09,23:52:52... (3 Replies)
Discussion started by: hazno
3 Replies

4. UNIX for Dummies Questions & Answers

Changing from Excel date format to MySQL date format

I have a list of dates in the following format: mm/dd/yyyy and want to change these to the MySQL standard format: yyyy-mm-dd. The dates in the original file may or may not be zero padded, so April is sometimes "04" and other times simply "4". This is what I use to change the format: sed -i '' -e... (2 Replies)
Discussion started by: figaro
2 Replies

5. Shell Programming and Scripting

Need help with extracting data to MySQL format

Hi guys, I'm doing a project now and extracting tables from a webpage to MySQL table format. I dumped the webpage with lynx and it is like this id Spec 524543 Developed especially for seniors Spec No Java Spec Yes Java MIDP Spec ... (4 Replies)
Discussion started by: Johanni
4 Replies

6. Shell Programming and Scripting

Need help getting my output from MYSQL query into right format

Good afternoon! I have been lurking in this forum for awhile now. I have just recently started posting. I think this is a really good site. With that being said, I don't like to just run and get an answer before I try my best first. I have poured some blood, sweat and tears into... (4 Replies)
Discussion started by: brianjb
4 Replies

7. UNIX and Linux Applications

Please help: Oracle gqsql or sqlplus output format like mysql

On psql select titolo,lingua from titolo where titolo ~* 'brivid'; titolo | lingua ------- + ------ Brivido | 1 On Sqlplus/gqsql SQL> select titolo,genere,anno,lingua from titolo where titolo like '%rivid%'; TITOLO... (6 Replies)
Discussion started by: Linusolaradm1
6 Replies

8. UNIX for Dummies Questions & Answers

Help! needed to displaying an output in record format

Hi Friends, Am new to Unix world and this is my first post in this forum. I was stuck in displaying the content. while displaying the content the below points to be taken care 1 ) The header format is repeating 2) To display the value in table format... (2 Replies)
Discussion started by: rocky2013
2 Replies

9. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

10. UNIX for Advanced & Expert Users

Help needed on my $format

I am stuck with values in existing program perl script... my $format ="a40a9a*"; please help me to understand what is the value of "a40a9a*". Thanks in advance! (3 Replies)
Discussion started by: Vijay_Data
3 Replies
OCI_FETCH_ALL(3)														  OCI_FETCH_ALL(3)

oci_fetch_all - Fetches multiple rows from a query into a two-dimensional array

SYNOPSIS
int oci_fetch_all OCI_FETCHSTATEMENT_BY_COLUMN OCI_ASSOC (resource $statement, array &$output, [int $skip], [int $maxrows = -1], [int $flags = + ]) DESCRIPTION
Fetches multiple rows from a query into a two-dimensional array. By default, all rows are returned. This function can be called only once for each query executed with oci_execute(3). PARAMETERS
o $statement -A valid OCI8 statement identifier created by oci_parse(3) and executed by oci_execute(3), or a REF CURSOR statement identifier. o $output - The variable to contain the returned rows. LOB columns are returned as strings, where Oracle supports conversion. See oci_fetch_array(3) for more information on how data and types are fetched. o $skip - The number of initial rows to discard when fetching the result. The default value is 0, so the first row onwards is returned. o $maxrows - The number of rows to return. The default is -1 meaning return all the rows from $skip + 1 onwards. o $flags - Parameter $flags indicates the array structure and whether associative arrays should be used. oci_fetch_all(3) Array Structure Modes +-----------------------------+---------------------------------------------------+ | Constant | | | | | | | Description | | | | +-----------------------------+---------------------------------------------------+ | | | | OCI_FETCHSTATEMENT_BY_ROW | | | | | | | The outer array will contain one sub-array per | | | query row. | | | | | | | |OCI_FETCHSTATEMENT_BY_COLUMN | | | | | | | The outer array will contain one sub-array per | | | query column. This is the default. | | | | +-----------------------------+---------------------------------------------------+ Arrays can be indexed either by column heading or numerically. Only one index mode will be returned. oci_fetch_all(3) Array Index Modes +----------+---------------------------------------------------+ |Constant | | | | | | | Description | | | | +----------+---------------------------------------------------+ | | | | OCI_NUM | | | | | | | Numeric indexes are used for each column's array. | | | | | | | |OCI_ASSOC | | | | | | | Associative indexes are used for each column's | | | array. This is the default. | | | | +----------+---------------------------------------------------+ Use the addition operator "+" to choose a combination of array structure and index modes. Oracle's default, non-case sensitive column names will have uppercase array keys. Case-sensitive column names will have array keys using the exact column case. Use var_dump(3) on $output to verify the appropriate case to use for each query. Queries that have more than one column with the same name should use column aliases. Otherwise only one of the columns will appear in an associative array. RETURN VALUES
Returns the number of rows in $output, which may be 0 or more, or FALSE on failure. EXAMPLES
Example #1 oci_fetch_all(3) example <?php $conn = oci_connect('hr', 'welcome', 'localhost/XE'); if (!$conn) { $e = oci_error(); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR); } $stid = oci_parse($conn, 'SELECT POSTAL_CODE, CITY FROM locations WHERE ROWNUM < 3'); oci_execute($stid); $nrows = oci_fetch_all($stid, $res); echo "$nrows rows fetched<br> "; var_dump($res); // var_dump output is: // 2 rows fetched // array(2) { // ["POSTAL_CODE"]=> // array(2) { // [0]=> // string(6) "00989x" // [1]=> // string(6) "10934x" // } // ["CITY"]=> // array(2) { // [0]=> // string(4) "Roma" // [1]=> // string(6) "Venice" // } // } // Pretty-print the results echo "<table border='1'> "; foreach ($res as $col) { echo "<tr> "; foreach ($col as $item) { echo " <td>".($item !== null ? htmlentities($item, ENT_QUOTES) : "")."</td> "; } echo "</tr> "; } echo "</table> "; oci_free_statement($stid); oci_close($conn); ?> Example #2 oci_fetch_all(3) example with OCI_FETCHSTATEMENT_BY_ROW <?php $conn = oci_connect('hr', 'welcome', 'localhost/XE'); if (!$conn) { $e = oci_error(); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR); } $stid = oci_parse($conn, 'SELECT POSTAL_CODE, CITY FROM locations WHERE ROWNUM < 3'); oci_execute($stid); $nrows = oci_fetch_all($stid, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW); echo "$nrows rows fetched<br> "; var_dump($res); // Output is: // 2 rows fetched // array(2) { // [0]=> // array(2) { // ["POSTAL_CODE"]=> // string(6) "00989x" // ["CITY"]=> // string(4) "Roma" // } // [1]=> // array(2) { // ["POSTAL_CODE"]=> // string(6) "10934x" // ["CITY"]=> // string(6) "Venice" // } // } oci_free_statement($stid); oci_close($conn); ?> Example #3 oci_fetch_all(3) with OCI_NUM <?php $conn = oci_connect('hr', 'welcome', 'localhost/XE'); if (!$conn) { $e = oci_error(); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR); } $stid = oci_parse($conn, 'SELECT POSTAL_CODE, CITY FROM locations WHERE ROWNUM < 3'); oci_execute($stid); $nrows = oci_fetch_all($stid, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_NUM); echo "$nrows rows fetched<br> "; var_dump($res); // Output is: // 2 rows fetched // array(2) { // [0]=> // array(2) { // [0]=> // string(6) "00989x" // [1]=> // string(4) "Roma" // } // [1]=> // array(2) { // [0]=> // string(6) "10934x" // [1]=> // string(6) "Venice" // } // } oci_free_statement($stid); oci_close($conn); ?> NOTES
Note Using $skip is very inefficient. All the rows to be skipped are included in the result set that is returned from the database to PHP. They are then discarded. It is more efficient to use SQL to restrict the offset and range of rows in the query. See oci_fetch_array(3) for an example. Note Queries that return a large number of rows can be more memory efficient if a single-row fetching function like oci_fetch_array(3) is used. Note For queries returning a large number of rows, performance can be significantly improved by increasing oci8.default_prefetch or using oci_set_prefetch(3). Note Will not return rows from Oracle Database 12 c Implicit Result Sets. Use oci_fetch_array(3) instead. SEE ALSO
oci_fetch(3), oci_fetch_array(3), oci_fetch_assoc(3), oci_fetch_object(3), oci_fetch_row(3), oci_set_prefetch(3). PHP Documentation Group OCI_FETCH_ALL(3)

Featured Tech Videos

All times are GMT -4. The time now is 03:31 PM.
Unix & Linux Forums Content Copyright 1993-2021. All Rights Reserved.
Privacy Policy