Bind the data extracted from the query to some variables using MySQL 8

Databases
1

Hello there,

I have prepared a DB Fiddle so that i can test my queries against data.

I need to bind the data extracted from the query to some variables in mysql

E.g.:

if the query extracts rows containing 2023-10-07 and 2023-10-18 values I have to associate:

  • variable A >>> set value equal to 2023-10-07
  • variable B >>> set value equal to 2023-10-18

Can you help me?

2

I am sorry but I don’t understand your question. Can you try to be more detailed what do you mean with variables? What do you want to do with them?

3

Thanks for reply.

I need insert into a new MySql table the values extracted from the query DB Fiddle .

I thought I could store the values extracted from the query in a declared variable and insert them into the new table.

4

Why not do the insert in the same query?

Also, storing result of one table into another table is mostly a bad design as you store redundant data which is not needed

5

Because this query populate a gridview of randomly extracted data…

if I execute the “insert into” instead of “select” I lose the population of the gridview…

if I execute the “select” after the “insert into”, the random extracted data changes and are different from the “insert into”

this is the problem…

6

If the queried data is populating a grid view, why must you store if into another table instead of jut showing it into the grid?

7

Because with the new table I have to populate a dropdownlist with the two dates extracted from the random query, to be able to filter the gridview by single date

8

Sorry but that’s too complicated for me. Why do you need a new table with data you received from a query? Use the output of the query and fill your drop-down with this data. No need to write it to another table

9

I am sorry but I don’t understand your last reply.

10
  1. Prepare the statement. This means telling MySQL what SQL statement you want to execute, but without specifying the actual values for the parameters. Instead, you use placeholder characters, such as ? .
  2. Bind the values to the placeholders. This means telling MySQL the actual values that you want to use for the parameters in the prepared statement.
  3. Execute the prepared statement. This means running the SQL statement with the actual values bound to the parameters.

Here is an example:

# Prepare the statement.
PREPARE bind_statement FROM 'SELECT * FROM table WHERE date BETWEEN ? AND ?';

# Bind the values to the placeholders.
SET @variable_a = '2023-10-07';
SET @variable_b = '2023-10-18';

# Execute the prepared statement.
EXECUTE bind_statement USING @variable_a, @variable_b;

This will execute the following SQL statement:

SELECT * FROM table WHERE date BETWEEN '2023-10-07' AND '2023-10-18';

The results of the query will be stored in a temporary result set. You can then access the results of the query using the FETCH statement.

Here is an example of how to access the results of the query:

# Fetch the next row from the result set.
FETCH NEXT INTO @variable_a, @variable_b FROM bind_statement;

# Use the values of the variables.
SELECT @variable_a, @variable_b;

This will print the following output:

2023-10-07 2023-10-18

Binding variables is a useful technique for preventing SQL injection attacks and improving the performance of your queries.

11

Thanks for reply.

Sorry but that’s too complicated for me…

You could use your example on DB Fiddle?

12

Gonna be honest, if that post is too complicated for you, you’re out of your depth and just looking for someone to write the code for you for something you’re not ready to create or maintain.

Maybe you’re coming at this the wrong way.

What exactly are you trying to get out of the query at the end?

(I can’t really envision a scenario where a random sorting is a good thing, but you seem to be indicating one)

13

Thank you for your honesty

If I had been able to do it myself I would not have asked the “guru” on this forum for help…

In any case I solved it by using a server-side language (using C# AspNet and the DataTable Compute method, for get Min / Max Date value from DataTable), because my idea on MySql seemed really complicated to implement.

Thanks for your attention

2 Likes
14

If not yet solved, to bind data extracted from a query to variables in MySQL, you can use the SET command along with a SELECT statement. Below is an example of how you can set the values of the variables A and B using the data from your _tbl_login table:

-- Declare the variables
DECLARE @A DATE;
DECLARE @B DATE;

-- Extract the first date value and assign to variable A
SELECT @A = `_Date` 
FROM `_tbl_login` 
WHERE `_Date` = '2023-10-07'
LIMIT 1;

-- Extract the second date value and assign to variable B
SELECT @B = `_Date` 
FROM `_tbl_login` 
WHERE `_Date` = '2023-10-21'
LIMIT 1;

-- To check the assigned values
SELECT @A AS VariableA, @B AS VariableB;

In the above example:

  1. We declared two date variables @A and @B.
  2. The first SELECT query assigns the date '2023-10-07' from the _tbl_login table to the variable @A.
  3. The second SELECT query assigns the date '2023-10-21' from the _tbl_login table to the variable @B.
  4. Finally, we’re using a SELECT statement to display the values assigned to the variables @A and @B.

You can adjust the WHERE clause of the SELECT statements if you want to extract and bind different dates or other values from the table to the variables.

Thanks, @UncleVince for being honesty.
Good Luck

15

Well putting aside the unnecessary jab at the people helping you here, noone said you had to “do it yourself”. You did, after all, heed our advice and reexamined whether or not this was the correct approach to the problem, rather than trying to brute force it through query.

I’m glad you found a solution to your problem that works for you.

16

I would never allow jab to the people helping here

I just have to be grateful towards them

So no jabs… I’m sorry but you misunderstood my last reply.

Good luck

17

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.