MYSQL :: Setting Variables In My.cnf

Setting Variables
I am using win and mysql 4.1, I found that I can't set variables in my.ini config file, msqld part, such as:
---
character_set_results=latin1
character_set_connection=latin1
---

error, it seems no such variables for setting,

but some variables such as:
---
slow_launch_time = 1
---
can be set in the config file.

Setting MySQL Variables At Start Using My.ini
I am battling to set the default datetime_format in my.ini, if i add the line i want to change the setting, the service refuses to start!

The line i am adding is as follows:

set-variable = datetime_format="%y/%m/%d %H:%i:%s"

Am i doing something wrong here?

Setting Server Variables On A Hosted Site
I want to change my MySQL server variable to ft_min_word_len = 1; and get have it not read the stopword file (ft_stopword_file). This is running on my website's webhost. It is already running and I believe they have the access to the config files..

I tried setting it through a query, and while I can view all the server variables, trying to set them to anything gives me an "unknown variable" error. (I don't think I have access to mysqld, just queries and trying to convince my host that they are the ones to change this...)

Complicated Sql Using Variables - How To Throw In Exogenous Variables
The "avg" from the following SQL code gives me the number of weekdays between two dates.

Now I need to throw in holidays to see if there is a holiday between the two dates. I have holidays in a serialized array in a database field. In php I would unserialize it into a session variable.

How can I incorporate holidays into the calculation of result2?

SELECT
@days:=((UNIX_TIMESTAMP(dateEnded) - UNIX_TIMESTAMP(dateStarted)) / 86400) + 1,
@d:= MOD(@days,7),
@w:=DAYOFWEEK(dateEnded),
@a:=IF(((@d + @w) >= 7), 1,0),
@b:=IF(((@d + @w) >= 8), 1,0),
@c:=IF((@w = 0), 1,0),
@result2:=(FLOOR(@days / 7) * 5) + @d - @a - @b - @c,
ROUND(SUM(@result2)/COUNT(id), 1)as avg
...
GROUP BY month

Here's how I do it in php:

PHP

function sdsd(startDate, endDate) {
...
    //$arrHolidays = serialize(array(&#55614;&#57158;-07-04', &#55614;&#57158;-09-04', &#55614;&#57158;-11-10', &#55614;&#57158;-11-23', &#55614;&#57158;-11-24', &#55614;&#57158;-12-24', &#55614;&#57158;-12-25'));
    $arrHolidays = unserialize($arrHolidays);
    foreach ($arrHolidays as $a) {
        if ($startDate <= $a AND $endDate >= $a) {
            $result--;
        }
    }

SQL Variables
I want to declare some variable and use it a few times in a query e.g.

x = 30

SELECT count(*)
FROM table
WHERE field > x

SP Variables
I was trying to do something like this:

CREATE PROCEDURE `UpdateField` (FieldName VARCHAR(45), NewValue VARCHAR(45))
BEGIN
UPDATE users SET FieldName=NewValue;
END

I get a runtime error saying the field "FieldName" does not exist - I need the SP to look into what's stored in FieldName and not treat it literally. Is it possible?

PHP Variables
How would I do something like this:
$number = 1;
$result = mysql_query( "SELECT * FROM blogs WHERE id = $number" );

?

Using Php Variables
how to include a PHP variable in a MySQL query- eg:

<?
$id = 2;
$query = mysql_query ('SELECT * FROM users WHERE id = $id');

?>

Basically this doesn't work- I 'm wondering what is the correct sytax to put the variable $id in the query

Variables
I am passing variables into a stored procedure and want to use one in and ORDER BY clause like so:

order by paramContactSortBy, c.ID, eg.GroupName;
however this does not work.
Does anyone know why?
I know the value of the variable to be valid (c.FirstName for instance), and no errors are thrown.
does MySQL only see it as a reference to the variable and if so is there another approach I could take.

Mysql Variables.. Is This Possible?
I've got a table that stores positions of various elements along with other data, and when a user deletes one of these elements from their profile, I want to run an update query to reorganize the elements to fill the gaps left from the deleted rows.. If this sounds confusing, for example, say I have a table that looks like this after a few rows have been deleted:

id | title | pos_left
--------------------------
1 | abc | 100
2 | bcd | 200
3 | cde | 300
6 | fgh | 600
8 | hij | 800

now I want to update the rows so they look like this:

id | title | pos_left
--------------------------
1 | abc | 100
2 | bcd | 200
3 | cde | 300
6 | fgh | 400
8 | hij | 500

I know I can do this by running a query on each row with a for loop in PHP, but can I achieve this with a single UPDATE query, perhaps using a MySQL variable that increments on each updated row, and assign that incremented variable to the pos_left field in the next row?
I've tried the query below which doesn't work:

SET @num=0;
UPDATE admin_desktop SET pos_left = (@num = @num+100) WHERE admin_id = 3 ORDER BY title ASC

How Can I Change Variables?
I'd like to change these MySQL global variables:

character_set_database=utf8
character_set_server=utf8
collation_database=utf8_general_ci
collation_server=utf8_general_ci

How can I do this? I've tried to put these lines into my.cnf and restart server, but MySQL service doesn't start with these lines.

Global Variables In Sql
can we use global variables in sql and what do we use them for ?

Variables In MySQL
I am new to MySQL and am trying to acquire skills in MySQL without the
benefit of any formal training although I have been in IT for more
years than I care to admit (retired now). I have bought and read
several SQL and MySQL books and frequently reference the on-line MySQL
manual on http://www.mysql.com/doc/en/index.html. I have also browsed
this and related newsgroups with considerable benefit.

For all of my efforts I seem to have missed some basic points
regarding the use of variables in MySQL scripts.

I hope that the readers of this posting do not find the problem
described in the example code below too silly or trivial to comment
on.

Please advise me if I have chosen the wring newsgroup for this
posting.

As the example code shows, all I a trying to do is set a variable (v)
to the current contents of the cell in column i, row 1 of table t.

DROP TABLE IF EXISTS t;

CREATE TABLE t
(
i INT(2),
r int(2) NOT NULL auto_increment,
PRIMARY KEY (r)
);

INSERT t SET i = 1;

# This is the line that fails.
SET @v = SELECT i FROM t where r = 1;

The version of MySQL that I am using is 4.0.6-gamma-win. It throws
back the above code with a bland error message that does not indicate
what the problem is.

Am I wrong in thinking that variables can be used in this way in
MySQL?

Assuming that I eventually get variables to work I intend to set up a
while loop for going through the rows of a table referencing the cells
in several columns.

Declaring Variables
I cant declare a varible in mySQL 4
i've tried:

declare @id as int
declare @id int
declare id int

any ideas?

CONCAT And Variables
I have the following statement:

------------------
SELECT CONCAT('hxxp://www.whatever.com/all.html?t=4&p=1&u=',@c) into @c;

set @stmt3:=CONCAT("INSERT INTO webs values ('",@c,"')");
prepare query from @stmt3;
execute query;
--------------

I think that I'm using correctly the CONCAT function but it doesn't work.

INSERTing With Variables?
I'm having a problem using INSERT with variables. These variables ($FirstName, $LastName, $Address, $City, $AreaCode, $PhoneNumber, and $Email) are created through an html form using POST, from the previous page. I've tested the contents of the variables and they're being sent through, so I know that's not the problem. However, when I try to insert these values into a new row like this:

$query = 'INSERT INTO `Customer` (`ID`, `FirstName`, `LastName`, `Address`, `City`, `AreaCode`, `PhoneNumber`, `Email`) VALUES ('', $FirstName, $LastName, $Address, $City, $AreaCode, $PhoneNumber, $Email)';

It doesn't add the new row. It works if I use coded values (like, replacing $FirstName with 'Bill'), but not with references to variables.

Using Variables From Php In Mysql?
im geting variables from flash using php.but i dont know how to use them variables in these statement...

$placeEarnt = $_POST['placeEarnt'];

$earnings = mysql_query("SELECT '$placeEarnt' FROM users WHERE username = '$username'")or die(mysql_error());
if(mysql_num_rows($earnings)==0) echo "There is no data in the table";
else

for($i=0;$i<mysql_num_rows($earnings);$i++) {
$row=mysql_fetch_assoc($earnings);
}

$updateEarnings = $row[earnings] + $_POST['tokens'];
$result = mysql_query("UPDATE users SET $placeEarnt ='$updateEarnings' WHERE username = '$username'") or die(mysql_error());

this doesnt throw an error but only adds the value in $_POST['tokens'] to the database. does anyone know how i can make this work?

Using Variables In Scripts.
I am using SQLYog to do some MySQL development and I am finding that I can't just declare a variable and run a script. I've tried the same thing in MySQL Query Browser and it's the same thing.

For instance, i can't just type in the code below into the editor window and execute it - it comes back with a million errors. But if I wrap this code in a stored procedure, it works fine.

So my questions are as follows:

1. Does MySQL not support running adhoc scripts?
2. Is the problem SQLYog?
3. Is there another tool that can let me write adhoc queries?

DECLARE count_recs INT;

select count(*) into count_recs
from information_schema.key_column_usage
where constraint_schema = database()
and constraint_name = 'fk_address_city';

if count_recs > 0 then
select 'fk exists';
end if;

Show Variables
How can I see the value of master-connect-retry? show variables doesn't work
on it.

Show Variables
How can I see the value of master-connect-retry? show variables doesn't work on it.

SELECT INTO VARIABLES H
I have a table that has a date and a dollar amount.
For whatever the highest date is, I want to put that date and the summed up dollar amount for that date into varaibles.

I was doing this and it DOES work:

SELECT MAX(thedate) INTO @myDateVar FROM myTable;
SELECT SUM(amount) INTO @myAmntVar FROM myTable WHERE thedate=@myDateVar;

But then I thought I could simplify those two lines with the following one line:

SELECT thedate, SUM(amount) INTO @myDateVar,@myAmntVar FROM myTable GROUP BY thedate DESC LIMIT 1;

If I don't use the INTO part of the SELECT, the results on my screen are correct. But when I use the INTO part it only populates the @myDateVar.
I tried taking out the references to thedate and @myDateVar but I still can't get a vaule into the @myAmntVar with this new SELECT statement.
It seems no matter what I do the @myAmntVar always ends up NULL.

SQL Results As Variables
I hope someone here can help me figure this out. I was looking for a way to give each result from a SQL Query a variable. My reason for doing this is I'm trying to make each result an item in a drop down box to make searching the database easier for the users. Anyone have any ideas? I know how to sort & arrange the results, but how do you assign each one (no duplicates) a variable?

Variables In A Procedure
What's the proper way to use a variable in a procedure?
Whenever I try and initialize one, I'm getting an error that I'm not using the proper syntax. Because I can't initialize it, whenever I try and use it I'm getting a null result. The variable is to be used inside a case when statement that first uses a select to check if something exists. Then, instead of running the select over again, I'd like just to return the variable.right now all I have is

case when EXISTS(select @variable := column where CONDITIONS) then
@variable
when EXISTS(select @variable := column where OTHERCONDITIONS)
END

And it's not working.

Variables Not Being Passed To PHP
Just successfully loaded MySQL and PHP, running under Windows XP (SP2).

Using old documentation - Site Point "database driven Website" book by Kevin Yank.

Example 1 works fine, but his just displays the current date.

Example 2 & 3 which require the variables defined in the html file to be passed to PHP doesn't work. The message in the PHP file displays everything EXCEPT the value of the variable.

Here are the contents of my files ;

xwelcome2.html
<a href="xwelcome2.php?FirstName=Kevin&LastName=Yank"> Hi, I'm Kevin Yanks! </a>

xwelcome2.php
<?php
echo("Welcome to our web site, $FirstName $LastName!");
?>

User Variables
Is it possible to use user-defined variables in statements
such as:

"DROP TABLE @mytablename"
I've tried several options, to no avail.

VALUES From Variables
im sure this is basic stuff, but i cant find anything on this yet...

$insert_query = "INSERT INTO `error`
( `company` , `location`, `employee`, `problem` ) VALUES
( 'x', 'y', 'z', 'a' ) ";

this is my code, but instead of x,y,z i need to put the value of my selected option. using php, i have them assigned to str1 -str4, how do i put those variables in as values?

Variables In Mysql
I need to know how to define variables which will accept null values from the server .
I am new and if anybody know about variables.

Declaring Variables
I'm getting a syntax error when declaring a variable within an UPDATE statement. Here's how I'm declaring it:

DECLARE cnt int
SET cnt = 1

Computed Variables
is it better to have values stored in your database that are computed or to compute them each time. for instance you have 50 different records with say 10 fields
you always want to list every field in the record and 1 additional one that totals fields 1-10 is it better just to have that in the database or to total when you request the data each time?

Mysql Variables Not Set
Quote: OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET
why some db is giving me this?

Passing Variables
I'm a beginning with php/mysql sort of stuff. I have a form where a user selects an option from a combo box. I would like to use that as a portion of my mysql query, but I don't know how to pass the variable through to the query.
Once i'm in the query, I'm assuming it just becomes another "and" statement, but I'm not sure how I translate what the form information was to a sql statement.

Wildcards With Variables
I am trying to run a part number against other fields that may contain the part number. For example part number "abc" needs to match other fields lets say for example "123abc44".
That field contains abc so I need a query that would search wildcard. Here is what I came up with but is not working:
Code:

SELECT * FROM Table1 G join Table2 A WHERE (G.%partnumbercolumn%= A.miscnumbercolumn)

User Variables
I am working with MySQL user variables, and some really strange stuff is happening. Here is my query:

SELECT @var_parent_id := parent_id, forum_name
FROM forums
WHERE forum_id = '13' OR forum_id = @var_parent_id
ORDER BY parent_id ASC

Basically, there are 2 rows being returned by this query, and the parent_id column returns:

parent_id
---------
13
0

Now, I told ORDER BY to sort this by parent_id Ascending...and when I do this, the 1 row with the value of '0', dissapears completely! When I loop the query, it only returns 1 row! But if I remove the ORDER BY clause then it works perfectly...why is that? And how can I make this work?

Inserting With Variables
I want to insert something into a table by a form.
Could I do this?

<?php
// The configuration to connect to the database goes here
$name = $_POST['firstname'];
INSERT INTO Persons
VALUES ('$name')
?>

Bind Variables
What are bind variables? And does mysql support them?

Storing Unlimited Variables
I want to be able to store unlimited values for each field in my database. For an example if i was storing what colour a car is available in could be one colour or an unlimited optional number of colours for each car.

I dont want to create fields for say 10 colours as i dont want to limit it.

What is the best way to do this?

The only way i can think of doing this is for example having one field called carColours set as a varchar and storing colours all in one field like blue,black,red and then splitting the field when i extract the data.

Not sure about speed issues etc?

Need A Little Help In Variables For Database Admn
I just want a simple help on how to declare a temp variable where I Run SQL query/queries on database MySQL Server Farm 2 using the database Admn of the web hosting services NETFIRMS.COM.

I just want to temp store a single value fetched by a select command into a variable that is to be used by the UPDATE statment in the next step.

Pl give simple proper syntax or an example and do not refer me to documentation I failed to understand that totally.

Passing Multiple Variables
I have a catalogue built and it passes a variable to the sample page that
shows the larger pic on the page.

But I want to pass a second variable to the page as well, can that be done.

This variable is the simple line of text that talks about the product.

This page does not access the database when loaded. It just uses the
variable passed from the page before....

Checking My MySQL Variables
I feel a bit guilty posting so many questions up here but you guys have saved me from so many hair-pulling episodes it always seems like the best place to start.

I've been gradually trouble-shooting slow queries on the MySQL/PHP website I run - http://www.vorb.org.nz - Basically a script harvests all queries that take over 1 second to execute and saves them (with other bits of info) to a table.

It nows seems like the queries are no longer appearing on the top of the table for reasons such as badly configured indexes, or other syntax problems. I'm guessing that the main problem is that at certain times of the day the number of users is causing the MySQL engine to overload, so queries are queuing, and taking a long to execute.

Below is the variables from my MySQL engine - if any of these seem particular bad for supporting a website that can have 100 simulatenous users on it please let me know.

Variable Value
back log 50
basedir /
binlog cache size 32768
character set latin1
character sets latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent insert ON
connect timeout 5
datadir /home/mysql/
delay key write ON
delayed insert limit 100
delayed ins......................................................

Variables In Select Into Outfile
I wan to use a variable for the outfile file name in a select into clause. I am
doing something like:

set @fileName="/tmp/result.text"
SELECT col1, col2 INTO OUTFILE @fileName
FIELDS TERMINATED BY ','
FROM test_table;

the interpreter dies at the @fileName...
if I exchange @filename with "/tmp/result.text" it runs fine.

Using User Defined Variables
The following works using user defined variable:

mysql> SELECT count(*) FROM rpt_totalSales INTO @c;
mysql> SELECT @c;
+---------+
| @c |
+---------+
| 4851547 |
+---------+
1 row in set (0.00 sec)

However, the following fails:
mysql> SHOW CREATE TABLE rpt_totalSales INTO @c;
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 'INTO @c' at line 1

Is there a work around for this?

Tweaking MySQL Variables
I'm troubleshooting performance problems with my dynamic website that's run off MySQL. The MySQL variables are default and I'm wondering if I should look at changing any of them.

My Server:
2.4Ghz Celeron (400FSB)
512meg RAM (DDR)
40gig H/D (7200rpm)

Apache 2.0 Fedora
MySQL 4.0.20

Site Traffic:
Average Users Online: 67
Peak Users Online: 119 (average across hour)
Average Queries to generate page: 104
Average Pages per hour: 700
Peak pages per hour: 2653
Average Query execution time: 5.42ms

All MySQL queries are carried out through one user on localhost via PHP scripting. Keeping in mind that I'm currently using the default MySQL variables is there changes I should look at making (especially in regards to maximum user connections.)

Variables In Select Into Outfile
I want to use a variable for the outfile file name in a select into clause. I am
doing something like:

set @fileName="/tmp/result.text"
SELECT col1, col2 INTO OUTFILE @fileName
FIELDS TERMINATED BY ','
FROM test_table;

the interpreter dies at the @fileName...
if I exchange @filename with "/tmp/result.text" it runs fine.

any suggestions?

Global Vs. Session Variables
I have a question regarding setting my session variable. I want to set the wait_timeout value to 600 for both global and session variables.

I put this in my my.cnf file:
wait_timeout = 600

bounced mysql and then logged back into a new session. The 600 value is set for global wait_timeout:

mysql> show GLOBAL VARIABLES LIKE 'wait%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| wait_timeout | 600 |
+---------------+----------+

BUT the session variable is still set to :

mysql> show VARIABLES LIKE 'wait%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+

How can i get the session variable to take the global value as the default??? I don't want to have to issue the 'set session variable' command in each session.

Error 1054 & PHP Variables
$sql_insert_data = "INSERT INTO admin (username, password, directory) VALUES (`$admin_name`, `$admin_password`, `$admin_directory`)";

do the quotes need to be around the PHP variables?

User Defined Variables
Im trying to write an SQL file which contains SQL queries which are to
be run individually one after the other. Im using user defined
variables so that I can save time on having to manually insert
constant values. Hope this makes sense:

SET @sequence := SELECT COUNT(*) FROM tracks WHERE cd_id = 12;
INSERT INTO tracks (song, artist, sequence, cd_id) SET ('Fools Gold',
'Stone Roses', @sequence, 12);

Basically here Im trying to SET the value of $sequence based on the
result of the select statement. So when I first run the two queries
@sequence will have a zero value, the next time it will count the
tracks table and will now return a value of 1, then 2, 3,4 etc. Each
time I will replace the values of the INSERT query but the SET SELECT
query will remain. The SELECT statement runs fine on its own but
obviously Im either trying to do something that is not possible with
the user-defined variables or more likely Ive got the query wrong.