Get MySQL Query Results as Their Native Data Type

Get MySQL Query Results as Their Native Data Type?

I don't think getting data in their native datatypes (i.e. anything else that strings) can be done in PHP 5.2...

In PHP 5.3, it becomes possible, if I remember correctly, when you are using the new (new as in PHP >= 5.3) mysqlnd (MySQL Native Driver) driver.

After more digging through my bookmarks I found this article about mysqlnd : PDO_MYSQLND: The new features of PDO_MYSQL in PHP 5.3

It says this (quote) :

Advantages of using mysqlnd for PDO

mysqlnd returns native data types when
using Server-side Prepared Statements,
for example an INT column is returned
as an integer variable not as a
string. That means fewer data
conversions internally.

But this is PHP 5.3 only (provided your version of PHP 5.3 is compiled with mysqlnd (and not the old libmysql)), and seems to only be the case for prepared statements :-(

Which doesn't quite help, in your situation, I guess...

And here's another one, still about the new features of mysqlnd, which talks about this for not only prepared statements : PHP: New network traffic, CPU and memory savings with mysqlnd.

Not sure this has been merged into the official mysqlnd driver, though -- best way would be to try ; but it'll still be PHP >= 5.3 only, anyway...

Another solution would be to have, on the PHP-side, some kind of a mapping-system (like an ORM) to convert results coming from the DB to PHP datatypes...

And yes, this is bad if you want to use operators like === and !==, which are type-sensitive...

Nodejs node-mysql query result data type

The callback with function (err, rows, fields) is much more appropriate for selecting data you are working with, and rows will then be populated by an array of arrays. So, in your case rows[0]['COUNT(*)'] would be your count.

For selects with multiple rows, you can loop through the results.

As a side note, I think you meant SELECT COUNT(*) AS count from card which would then be accessible much more simply as rows[0].count

How do I make sure that values from MySQL keep their type in PHP?

What exactly do I do to get my MySQL functions in PHP to give me the MySQL results in their native type?

You connect to the database, then you prepare your query, execute it, bind the result and then you fetch it.

Let's do these steps line-by-line:

$conn = new Mysqli('localhost', 'testuser', 'test', 'test');
$stmt = $conn->prepare("SELECT id FROM config LIMIT 1");
$stmt->execute();
$stmt->bind_result($id);
$stmt->fetch();
var_dump($id); # it's an int!

This works for me. As you wrote your code is more complex, you will need to locate the place where you query the database. Check that you're using Mysqli::prepare() and if not, introduce it.

You will also need to use Mysqli_Stmt::execute() and then Mysqli_Stmt::bind_result() otherwise the (here integer) type is not preserved for that result column.

How to return field type from MySQL query?

You can use

SHOW FIELDS
FROM tableName where Field ='nameOfField'

This will return you result in format of

Field   Type    Null    Key     Default     Extra 

How to get the same result in single mysql query

First things first: your queries are probably¹ invalid SQL because the column offertId that appears in the SELECT clause without being aggregated does not appear in the GROUP BY clause. Since version 5.7.5, MySQL rejects² such queries. Previous versions process them but they are free to return as offertId any value from the column offertId that is selected by the WHERE condition.

Are you sure you don't want to GROUP BY offertId in both queries?

I will assume both your queries contain GROUP BY offertId to get the correct results.

---

¹ The queries are valid and return the expected results if and only if offertId is functionally dependent on userId; i.e. all the rows having a certain value in userId also share the value of column offertId.

² Since version 5.7.5, using the default configuration, MySQL rejects the queries that use GROUP BY incorrectly. Previous versions of MySQL used to accept such queries but the values they return for columns that do not appear in the GROUP BY clause and are not used with GROUP BY aggregate functions were indeterminate.

To let the existing code run, the setting introduced by MySQL 5.7.7 can be turned off. It is not advisable to turn it off if you don't have old queries that run on the server; you better write valid SQL queries in your new code and don't rely on implementation details (they produce indeterminate results anyway).

---

The original solution, given the queries are changed to contain GROUP BY

You can JOIN the results returned by the two queries you already have:

SELECT *
FROM (
    SELECT offertId, count(*) as returnClicks from warehause where userId=8 GROUP BY offertId
) clicks
LEFT JOIN (
    SELECT offertId, count(*) as returnContacts from contact where userId=8 GROUP BY offertId
) contacts USING (offertId)

It will return all the rows generated by the first sub-query and the matching rows from the resultset generated by the second sub-query.

I assume the first sub-query returns in offertId all the values returned in offertId by the second sub-query. If they do the other way around then just replace LEFT JOIN with RIGHT JOIN.

Remark: If the sets of offertId values returned by the two sub-queries are not included one into the other then the above query does not return all the results you need. In this case you need to use FULL OUTER JOIN (it includes all rows from both sides) but, unfortunately, MySQL does not support it.

---

The no-GROUP BY solution

The op mentioned they don't need to use GROUP BY. In this case, each of their original queries returns at most one row. If the values returned in offertId by the sub-queries match, the code provided above also works and returns one row that contains offertId, returnClicks and returnContacts. If they don't match (or one of them doesn't return any rows) the final result set will miss the values produced by the second sub-query.

A possible solution to get all the values in the result set is to change the format of the result set.

This query:

SELECT 'clicks' as kind, offertId, count(*) as number from warehause where userId=8
UNION
SELECT 'contacts' as kind, offertId, count(*) as number from contact where userId=8

produces this result set:

| kind     | offertId | number |
| clicks   |    47    |    4   |
| contacts |    47    |    1   |

It contains all the rows returned by the two queries, no matter if they return 0 or 1 rows, no matter if the values of offertId match or not.

The column kind tells what sub-query generated the row, the column number tells the value produced by COUNT(*) from the corresponding sub-query.

The values can be analyzed, combined, processed in any way in the client code (assuming it is not a stored procedure and a more powerful language is used on the client side).

MySql Query result only first column

To produce your expected result you could use:

select group_concat(CONCAT_WS(' ',item1, item2, item3, item4) SEPARATOR ' ')  as my_column
FROM my_table  WHERE id =99
group by id;

Result:

my_column
1 2 3 4 5 6 7 8

Demo

How to save MySQL query output to excel or .txt file?

From Save MySQL query results into a text or CSV file:

MySQL provides an easy mechanism for writing the results of a select
statement into a text file on the server. Using extended options of
the INTO OUTFILE nomenclature, it is possible to create a comma
separated value (CSV) which can be imported into a spreadsheet
application such as OpenOffice or Excel or any other application which
accepts data in CSV format.

Given a query such as

SELECT order_id,product_name,qty FROM orders

which returns three columns of data, the results can be placed into
the file /tmp/orders.txt using the query:

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

This will create a tab-separated file, each row on its own line. To
alter this behavior, it is possible to add modifiers to the query:

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

In this example, each field will be enclosed in double quotes, the
fields will be separated by commas, and each row will be output on a
new line separated by a newline (\n). Sample output of this command
would look like:

"1","Tech-Recipes sock puppet","14.95" "2","Tech-Recipes chef's hat","18.95"

Keep in mind that the output file must not already exist and that the
user MySQL is running as has write permissions to the directory MySQL
is attempting to write the file to.

Syntax

   SELECT Your_Column_Name
    FROM Your_Table_Name
    INTO OUTFILE 'Filename.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

Or you could try to grab the output via the client:

You could try executing the query from the your local client and
redirect the output to a local file destination:

mysql -user -pass -e "select cols from table where cols not null" > /tmp/output

Hint: If you don't specify an absoulte path but use something like INTO OUTFILE 'output.csv' or INTO OUTFILE './output.csv', it will store the output file to the directory specified by show variables like 'datadir';.

Need a MySQL query that can filter the results

Try this:

SELECT id, chapter_id, question, answer 
FROM (SELECT IF(@chapterId=@chapterId:=chapter_id, @id:=@id+1, @id:=0) queNo, id, chapter_id, question, answer 
      FROM `questions`, (SELECT @chapterId:=0, @id:=0) AS A 
      WHERE `chapter_id` IN (19, 20, 21, 22, 23)
      ORDER BY `chapter_id`
     ) AS A 
WHERE queNo < 10

---

Related Topics